hard coded SQL string doesn't run with error PLS-00103
I have created a package with 34 stored procedures to create some materialized view with hard coded SQL string (client required for this hard coding). The first part with 21 simple create statements has worked fine. 2nd part with 11 complicated create statements, only first one has worked. all others have not worked.
I used dbms_output.put_line (sql_string) to check the individual create statement, it has looked fine. I have run individual SP within package. I have got error message as ORA-06550: line 1, column 45: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; The symbol ";" was substituted for "end-of-file" to continue.
I have checked SQL string between 1st part and 2nd part. They are the same and all with ';' to end SQL string and END SP name in the end. Please help me to identify where the problems are. Thanks.
Followings are some sample SQL string:
CREATE OR REPLACE PROCEDURE sp_1st_string IS
BEGIN EXECUTE IMMEDIATE 'CREATE MATERIALIZED VIEW mv_name1 TABLESPACE space_name PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT ) BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND WITH PRIMARY KEY AS SELECT col1, col2,col3, col4, col5 FROM tableone A, table_two B WHERE A.id = B.id';
CREATE OR REPLACE PROCEDURE sp_2nd_string IS
BEGIN EXECUTE IMMEDIATE ' CREATE MATERIALIZED VIEW mv_name2 TABLESPACE PDE_DATA PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT ) BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND WITH PRIMARY KEY AS select col1 .. col10 from tableone a, tabletwo b, tablethree c, tablefour d, tablefive e, tablesix f where clause;
Check your code, you got a syntax error.
Don't know if you have copy/pasted or typed in your statements but the second one does not has a literal terminator.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Thanks. I used quote operator like " q'(create or replace XXXX)'; to resolve the problem. You are right. It is char literal problem.
Click Here to Expand Forum to Full Width