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.
– T.S., Commented Aug 14, 2021 at 4:21
your VARCAR2 is not declared properly. I can fiddle it just fine — T.S.
– T.S., Commented Aug 14, 2021 at 4: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
– EdStevens, Commented Aug 14, 2021 at 11:45

Littlefoot · Accepted Answer · 2021-08-14 05:25:32Z

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.

Collectives™ on Stack Overflow

Stored procedure using Dynamic SQL to create new table in Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related