Stored procedure using Dynamic SQL to create new table in Oracle

Question

The question is as follows: create a stored procedure (NEW_TABLE) with two strings as input parameters:

String 1: Name of the table to be generated
String 2: Name of columns and their datatypes.

For example: 'ID Number, ProductName varchar2(50), Quantity'

I implemented the following code:

CREATE OR REPLACE PROCEDURE GENERATE_NEW_TABLE
    (TEMP_PRODS varchar2, COLUMNS_DATATYPES varchar2)
is
begin
    EXECUTE IMMEDIATE
    'CREATE TABLE '||TEMP_PRODS||'('||COLUMNS_DATATYPES||')';
end;

I called it as follows:

EXEC GENERATE_NEW_TABLE('PRODUCTS','ID Number, PRODUCT_NAME VARCAR2(50), QUANTITY')

The procedure was created without compilation error, but when I executed this procedure there was error

missing right parenthesis

Kindly share how can I resolve this. I'm only required to take two strings as input parameters for procedure.

We will not answer this until you show what is **actual** parameter values are of the failing call — T.S., Aug 14 '21 at 04:21
I was wondering if I have created the stored procedure accurately or not. — Snoke, Aug 14 '21 at 04:45
your `VARCAR2` is not declared properly. I can fiddle it just fine — T.S., Aug 14 '21 at 04:48
While this obvious homework assignment may be able to teach several minor bits, I do hope that the lessons is _NOT_ that this is a good way to create tables. As a business requirement, it is atrocious. It is very very VERY rare to have a justification for using dynamic SQL to create objects in the database. — EdStevens, Aug 14 '21 at 11:45

score 2 · Accepted Answer · answered Aug 14 '21 at 05:25

When working with dynamic SQL, always display what you're going to execute (using dbms_output.put_line). Only if it is OK, then execute (immediate) it.

SQL> CREATE OR REPLACE PROCEDURE GENERATE_NEW_TABLE
  2      (TEMP_PRODS varchar2, COLUMNS_DATATYPES varchar2)
  3  is
  4    l_str varchar2(200);
  5  begin
  6    l_str :=
  7      'CREATE TABLE '||TEMP_PRODS||'('||COLUMNS_DATATYPES||')';
  8    dbms_output.put_line(l_str);
  9    --execute immediate l_str;     --> don't run it until you make sure L_STR is correct
 10  end;
 11  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> EXEC GENERATE_NEW_TABLE('PRODUCTS','ID Number, PRODUCT_NAME VARCAR2(50), QUANTITY')
CREATE TABLE PRODUCTS(ID Number, PRODUCT_NAME VARCAR2(50), QUANTITY)    --> this will be executed

PL/SQL procedure successfully completed.

SQL>

Statement you're about to execute is

CREATE TABLE PRODUCTS(ID Number, PRODUCT_NAME VARCAR2(50), QUANTITY)
                                              -------              ---
                                              typo             missing datatype

Does it look OK to you? Doesn't to me (and other as well).

When fixed:

SQL> EXEC GENERATE_NEW_TABLE('PRODUCTS','ID Number, PRODUCT_NAME VARCHAR2(50), QUANTITY NUMBER')
CREATE TABLE PRODUCTS(ID Number, PRODUCT_NAME VARCHAR2(50), QUANTITY NUMBER)

PL/SQL procedure successfully completed.

SQL>

Does CREATE TABLE look OK now? Yes, it does. So uncomment EXECUTE IMMEDIATE from the procedure and repeat everything:

SQL> CREATE OR REPLACE PROCEDURE GENERATE_NEW_TABLE
  2      (TEMP_PRODS varchar2, COLUMNS_DATATYPES varchar2)
  3  is
  4    l_str varchar2(200);
  5  begin
  6    l_str :=
  7      'CREATE TABLE '||TEMP_PRODS||'('||COLUMNS_DATATYPES||')';
  8    dbms_output.put_line(l_str);
  9    execute immediate l_str;
 10  end;
 11  /

Procedure created.

SQL> EXEC GENERATE_NEW_TABLE('PRODUCTS','ID Number, PRODUCT_NAME VARCHAR2(50), QUANTITY NUMBER')
CREATE TABLE PRODUCTS(ID Number, PRODUCT_NAME VARCHAR2(50), QUANTITY NUMBER)

PL/SQL procedure successfully completed.

SQL> DESC PRODUCTS
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----------------------------------
 ID                                                             NUMBER
 PRODUCT_NAME                                                   VARCHAR2(50)
 QUANTITY                                                       NUMBER

SQL>

The table is now created.

Stored procedure using Dynamic SQL to create new table in Oracle

1 Answers1