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';
COMMIT;
END sp_1st_string;
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;
COMMIT;
END sp_2nd_string;
