Automate Create tables procedure using Dynamic SQL using cursor or loop - PLSQL

Question

Syntax: PL SQL IDE: PL/SQL Developer

I'm attempting to generate 4 tables for each region using a series of Region codes.

I understand that it may be more efficient to generate one table with all regions included and truncate and replace this table however based on the structure needed for use I am not allowed to this.

The 4 regions come from a Specific table

SELECT DISTINCT REGION
FROM REGION_TABLE
;

What is the best method to generate this series of tables dynamically?

I've attempted to use a cursor and a loop but I am unsure of the method to integrate this cursor into the following Procedure.

DECLARE 
CURSOR C_REGION IS
       SELECT DISTINCT REGION FROM REGION_TABLE ;
BEGIN
     FOR L_IDX IN C_REGION
     LOOP
     DBMS_OUTPUT.PUTLINE(L_IDX.C_REGION);
     END LOOP;
END;

Using the cursor values above, how do I go about inserting these values into not only the table name used in the Create Table portion of the Sql statement but as a parameter in the where clause

-- 4 REGIONS  - NORTH SOUTH EAST WEST, 

CREATE OR REPLACE PROCEDURE SALES_ORDER_TBL_PROC
          (
          REGION IN VARCHAR2
          , RETURNCODE OUT NUMBER
          , ERROR_MESSAGE OUT VARCHAR2
          )
            IS 
  TABLE_NAME VARCHAR2(30);
  SQL_STR VARCHAR2;
  REGION_VAR VARCHAR;          
BEGIN 
  TABLE_NAME := 'SALES_COUNT_'||REGION_VAR;
  SQL_STR := 'CREATE TABLE '||TABLE_NAME||
             ' AS 
               (
               SELECT 
                      REGION
                          , COUNT(DISTINCT MEMBER_ID) AS NUM_MBR
                      FROM INCREMENTAL_SALES_TBL
                      WHERE 1=1
                          AND REGION = '||REGION_VAR||
                          'GROUP BY REGION)' 
               ;

 EXECUTE IMMEDIATE SQL_STR ;

         RETURNCODE := 0;

 EXCEPTION WHEN OTHERS THEN

         RETURNCODE := SQLCODE;

         ERROR_MESSAGE :='ERR:Creating/Altering table '||TABLE_NAME ||'-'||SQLERRM;
END;
/

BEGIN
     SALES_ORDER_TBL_PROC;
END;

Answer 1

How about putting the DDL in a for loop? I haven't tested the code below but it should at least put you on a path toward your goal, hopefully.

FOR rec IN (SELECT DISTINCT REGION FROM REGION_TABLE) LOOP
  EXECUTE IMMEDIATE
    'CREATE TABLE SALES_COUNT_' || UPPER(rec.region) || ' AS (
       SELECT REGION,
              COUNT(DISTINCT MEMBER_ID) AS NUM_MBR
         FROM INCREMENTAL_SALES_TBL
        WHERE REGION = ''' || rec.region || '''
        GROUP BY REGION)';
END LOOP;

I can't remember what the restrictions are when it comes to concatenation with EXECUTE IMMEDIATE, but if it's not working then it's possible to use bind variables and the USING clause.