CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqldynamic-sql
Snoke
Snoke

Reputation: 105

Stored procedure using Dynamic SQL to create new table in Oracle

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

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.

Upvotes: 0

Views: 1779

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

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.

Upvotes: 2

Related Questions