Have troubles with writing a procedure on PL/SQL

Question

So, basically, I need to create a procedure that takes names of two tables as parameter, then creates a new table, in which foreign keys are connected with primary keys of tables from parameters.

What do I need: I need this procedure to take two already existing in DB tables, find those columns that are constrained with primary key, create a table (that's why I use execute immediate, of cource) that references with its fks on primary keys of my two tables with those column names as datatypes for newly created columns, since we don't know what was the datatype for pk columns of parameter tables. As a result, this procedure should write a name of the table that was created or show error that was occured (but not ORA).

The main trouble is in creating a table part, I don't know how to put those column names in (I wish it was just one), I was told that I could use substring, but not sure how in that situation I can do this, since I don't know the length of the column's name, and I'm not sure if the info about my columns is full without binds, where should I use it.

Editing what was written previously, that's full text of my procedure.

create or replace procedure table_creation (table_a varchar2, table_b varchar2) 
is 
l_cnt_a number;
l_cnt_b number;
l_str_a varchar2 (32767); 
l_str_b varchar2 (32767); 
l_pk_a varchar2 (32767); 
l_pk_b varchar2 (32767); 
err1 exception;
err2 exception; 
sql_text varchar2 (32767); 
begin 
SELECT count(1), 
max(cols.constraint_name), listing(cols.column_name || '_1' ',') 
INTO l_cnt_a, l_str_a, l_pk_a 
FROM all_constraints cons 
INNER JOIN all_cons_columns cols ON cons.constraint_name = cols.constraint_name 
WHERE cons.table_name = table_a 
AND cons.constraint_type = 'P'; 
IF l_cnt = 0 then 
RAISE err; 
END IF;

SELECT count(1), 
max(cols.constraint_name), listing(cols.column_name || '_2' ',') 
INTO l_cnt_b, l_str_b, l_pk_b
FROM all_constraints cons 
INNER JOIN all_cons_columns cols ON cons.constraint_name = cols.constraint_name 
WHERE cons.table_name = table_b
AND cons.constraint_type = 'P'; 
IF l_cnt = 0 then 
RAISE err; 
END IF;

sql_text := 
'create or replace table table_c ('
|| CHR(10) 
|| 'for i in 1..l_cnt_a loop'
|| CHR(10) 
|| 'column_for_a_i a_i_column%TYPE, '
|| CHR(10) 
|| 'end loop'
|| CHR(10) 
|| 'for i in 1..l_cnt_b loop'
|| CHR(10) 
|| 'column_for_b_i b_i_column%TYPE, '
|| CHR(10) 
|| 'end loop'
|| CHR(10) 
|| 'constraint fk_a' 
|| CHR(10) 
|| 'foreign key (column_for_a_i) '
|| CHR(10) 
|| 'references table_a(l_pk_a), '
|| CHR(10) 
|| 'constraint fk_b' 
|| CHR(10) 
|| 'foreign key (column_for_b_i) '
|| CHR(10) 
|| 'references table_b(l_pk_b), '
|| CHR(10) 
|| ') ';

execute immediate sql_text;
dbms_output.put_line('done');
exception
when err1 then 
dbms_output.put_line('first table');
when err2 then 
dbms_output.put_line('second table');
end table_creation;

I'm sorry if it's a bit unreadable, I'm still learning( and hope that now the problem is more understandable, if not - I'll try again XD

Answer 1

The problem is that your code generates sql_text as a combination of DDL and PL/SQL - that will not work. You should create dynamic code that is either DDL or PL/SQL. I don't realy understand the quuestion but I'll try to propose solution for a part of it.
Quote: "The main trouble is in creating a table part, I don't know how to put those column names in" end quote.
You can generate the complete CREATE TABLE command using something like below.

Select  'CREATE TABLE ' || CHR(10) || CHR(9) || 
    'NEW_TABLE' || CHR(10) || CHR(9) || '(' || 
        SubStr(COL_DESC, 3, Length(COL_DESC) - 4) || CHR(10) || CHR(9) || 
    ')' "CREATE_TABLE_DDL"
From    (   Select   LISTAGG(CHR(10) || CHR(9) || CHR(9) || 
                Case When TABLE_NAME = 'A_TBL_1' Then COLUMN_NAME || '_FROM_T1 ' 
                Else COLUMN_NAME || '_FROM_T2 ' 
                End || 
                Case When DATA_TYPE = 'DATE' Then DATA_TYPE || ', '
                Else DATA_TYPE || '(' || CASE WHEN DATA_TYPE = 'NUMBER' 
                                              THEN To_Char(DATA_PRECISION) || ', ' || To_Char(DATA_SCALE) 
                                         ELSE To_Char(DATA_LENGTH) 
                                         END 
                                       || ')' || Case When NULLABLE = 'N' Then ' NOT NULL' End || ', '
                End
              ) WITHIN GROUP (Order By TABLE_NAME, COLUMN_ID) "COL_DESC"
      From all_tab_columns 
      Where TABLE_NAME IN('A_TBL_1', 'A_TBL_2')
      Order By TABLE_NAME, COLUMN_ID
    )
--  
--  R e s u l t :
--
--  CREATE_TABLE_DDL
--  ---------------------------------------------------
--  CREATE TABLE 
--      NEW_TABLE 
--      (   ID_FROM_T1 NUMBER(3, 0) NOT NULL,   
--          T2_ID_FROM_T1 VARCHAR2(20),   
--          SOME_COLUMN_FROM_T1 DATE,   
--          ID_FROM_T2 VARCHAR2(3, 0) NOT NULL,   
--          T1_ID_FROM_T2 NUMBER(22),   
--          SOME_OTHER_COLUMN_FROM_T2 VARCHAR2(20)  
--      )

NOTE: I used 2 dummy tables. You can use your procedure's parameters instead and store the result into your sql_text variable. Similarily you can generate other DDL statements too.
Dummy tables used:

Desc A_TBL_1;
Name        Null?    Type         
----------- -------- ------------ 
ID          NOT NULL NUMBER(3)    
T2_ID                VARCHAR2(20) 
SOME_COLUMN          DATE        

Desc A_TBL_2;
Name              Null?    Type         
----------------- -------- ------------ 
ID                NOT NULL VARCHAR2(20) 
T1_ID                      NUMBER(3)    
SOME_OTHER_COLUMN          VARCHAR2(20)