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.
