hard coded SQL string doesn't run with error PLS-00103
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: hard coded SQL string doesn't run with error PLS-00103

  1. #1
    Join Date
    Jan 2006
    Posts
    39

    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;

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Jan 2006
    Posts
    39
    PAVB:

    Thanks. I used quote operator like " q'(create or replace XXXX)'; to resolve the problem. You are right. It is char literal problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width