CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloperdynamic-sql
Steve
Steve

Reputation: 551

Struggling with Oracle dynamic SQL

I'm working on an Oracle procedure that needs to be very automated, and so I'm using dynamic SQL. I'm new to using dynamic SQL.

I'm using a varchar2 variable to build the SQL that needs to be executed. I've used a dbms_output to capture the SQL that's being generated, and it executes fine:

sql to create Basket Swap table = CREATE TABLE INBOUND.BASKET_CTAS_SWP COMPRESS FOR QUERY HIGH AS SELECT * FROM INBOUND.BASKET_FCT PARTITION (SYS_1234);

However, when I execute the procedure using this, it generates the error below

Execute MyProcedure;

Thank you for any guidance!

oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

IDE: Oracle SQL Developer Version 4.0.2.15 Build 15.21

Code:

note: all variables set at beginning of procedure...
Sqlexec:= 'CREATE TABLE ' || Basket_Swap_Schema ||  '.' || Basket_Swap_Table_Name  || ' ';
Sqlexec:= Sqlexec || 'COMPRESS FOR QUERY HIGH  ';
Sqlexec:= Sqlexec || 'AS SELECT * FROM ' || Basket_Source_Schema_Name || '.' || Basket_Source_Table_Name || '  ';
Sqlexec:= Sqlexec || 'PARTITION ('||Basket_Partition_To_Process||'); ';

Dbms_Output.Put_Line('sql to create Basket Swap table = ' || Sqlexec);

Execute Immediate Sqlexec;

Error Message:

Error starting at line : 1 in command - Execute Process_Wic_Snap_Partition Error report - ORA-00911: invalid character ORA-06512: at "MY_SCHEMA.MY_PROCEDURE", line 102 ORA-06512: at line 1 00911. 00000 - "invalid character" *Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.

Upvotes: 3

Views: 3111

Answers (1)

gvenzl
gvenzl

Reputation: 1891

Your query is fine but with DynamicSQL you're not allowed to have a semicolon at the end of your statement. Change the line Sqlexec:= Sqlexec || 'PARTITION ('||Basket_Partition_To_Process||'); '; to not include the semicolon at the end of the statement: Sqlexec:= Sqlexec || 'PARTITION ('||Basket_Partition_To_Process||')';

Upvotes: 6

Related Questions