12 Replies Latest reply: Nov 15, 2012 8:33 AM by 592815 RSS

    dynamical SQL with compile error ora06550

    592815
      Friends,
      I created a procedure and works well. Now I want to dynamical wrap procedure
      I copy my source code and assigned to variable. But I failure to pass compile.
      problem line as '*********************************************************************************************************/'||
      message as
      ERROR at line 16:
      ORA-06550: line 16, column 5:
      PLS-00103: Encountered the symbol "*" when expecting one of the following:
      ( case mod new null <an identifier>
      <a double-quoted delimited-identifier> <a bind variable>
      continue avg count current max min sql stddev sum variance
      execute forall merge time timestamp interval date
      <a string literal with character set specification>
      <a number> <a single-quoted SQL string> pipe
      <an alternatively-quoted string literal with character set specification>
      <an alternatively-quoted SQL string> p

      Thanks for help!
      newdba
      ============= Source SQL
      SET SERVERO
      DECLARE
      l_source VA
      l_wrap VARC
      BEGIN
      l_source := 'create or replace procedure test as'||
      '/***************************************************************************************************** '||
      'NAME: developer '||
      'PURPOSE: account processing'||
      'REVISIONS:               '||
      'Ver Date Author           Description '||
      ' --------- ---------- --------------------      ---------------------------------------------'||
      '2.1 10-05-2012 Philip                1. Created this procedure '||
      'NOTES: 1)new requirement'||
           '2)source codes have be test'||
           'based on client requirement‘ ||
      '*********************************************************************************************************/'||

      ---
      --
      End;
        • 1. Re: dynamical SQL with compile error ora06550
          sb92075
          writing PL/SQL to write PL/SQL results in infinite recursion.
          • 2. Re: dynamical SQL with compile error ora06550
            Keith Jamieson
            You need to wrap the file , not the code.
            Then life all becomes much easier.

            so from sqlplus


            host wrap iname=test.sp oname = test.plb

            then to install it , use
            @test.plb instead of @test.sp

            You will see that the code is now wrapped.

            Edited by: Keith Jamieson on Nov 9, 2012 5:26 PM
            • 3. Re: dynamical SQL with compile error ora06550
              592815
              we need to wrap codes by SYS.DBMS_DDL.WRAP(ddl => l_source);
              any advise?
              • 4. Re: dynamical SQL with compile error ora06550
                Keith Jamieson
                Well just looking at it, it's quite simple.
                select dbms_ddl.wrap('create or replace procedure test as BEGIN null; end test;') from dual;
                Basically your string has to make a legal wrappable plsql unit.
                declare
                v_source varchar2(32767) := 'create or replace procedure test as BEGIN null; end test;';
                v_wrapped varchar2(32767) ;
                begin
                v_wrapped := dbms_ddl.wrap('create or replace procedure test as BEGIN null; end test;');
                end;
                /
                • 5. Re: dynamical SQL with compile error ora06550
                  592815
                  DECLARE
                  l_source VARCHAR2(32767);
                  l_wrap VARCHAR2(32767);
                  BEGIN
                  l_source := 'create or replace procedure test as'||
                  'wallet_open exception;'||
                  'pragma exception_init(wallet_open,-28000);'||
                  'v_stmt varchar2(100);'||
                  'v_password varchar2(20);'||
                  'begin'||
                  'v_password := 'abcdefg';'||
                  'v_stmt :='ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "abcdefg"';'||
                  'execute immediate v_stmt ;'||
                  'exception'||
                  'when wallet_open then'||
                  'null;'||
                  'END;’;
                  l_wrap :=SYS.DBMS_DDL.WRAP(l_source);
                  END;
                  /

                  However test procedure works well but I receive message when try to warp test procedure
                  ERROR:
                  ORA-01756: quoted string not properly terminated
                  • 6. Re: dynamical SQL with compile error ora06550
                    Keith Jamieson
                    Your problem is that you have embedded quotes. You need to replace each embedded single quote with a double quote. Also you had a " ’ " to terminate your string, not a " ' "
                    declare
                    l_source VARCHAR2(32767);
                    l_wrap VARCHAR2(32767);
                    BEGIN
                    l_source := 'create or replace procedure test as'||
                    'wallet_open exception;'||
                    'pragma exception_init(wallet_open,-28000);'||
                    'v_stmt varchar2(100);'||
                    'v_password varchar2(20);'||
                    'begin'||
                    'v_password := ''ABCDEFG'';'||
                    'v_stmt :=''ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "abcdefg"'';'||
                    'execute immediate v_stmt ;'||
                    'exception'||
                    'when wallet_open then'||
                    'null;'||
                    'END;';
                    l_wrap :=SYS.DBMS_DDL.WRAP(l_source);
                    END;
                    /
                    • 7. Re: dynamical SQL with compile error ora06550
                      592815
                      Thanks for your help.
                      I received PL/SQL procedure successfully completed and saw wrapped codes.
                      However, I am l not able to see code by sqlplus
                      SELECT text FROM USER(or DBA)_SOURCE WHERE name = 'TEST';

                      I could not to drop wrapped test procedure too.

                      also my test trigger receive issue as
                      Compilation errors for TRIGGER SYS.OPEN_test

                      Error: PLS-00201: identifier 'test' must be declared
                      Line: 5
                      Text: test();

                      Error: PL/SQL: Statement ignored
                      Line: 5
                      Text: test();


                      see below as

                      SQL> drop procedure test;

                      drop procedure test

                      ORA-04043: object TEST does not exist

                      ===trigger codes as
                      create or replace trigger open_test
                      after startup on database
                      begin
                      -- call wrapped
                      sys.test();
                      end;
                      /

                      Edited by: Oradb on Nov 13, 2012 8:39 AM

                      Edited by: Oradb on Nov 13, 2012 9:29 AM
                      • 8. Re: dynamical SQL with compile error ora06550
                        Keith Jamieson
                        Most likely you created your procedure using double quotes , eg "test" or "Test".
                        Oracle by default looks for the names in uppercase and stores the names in uppercase, unless you create the object using double quotes.


                        select distinct object_name from user_objects where upper(object_name)="TEST"
                        should find it for you. (Note : not tested )
                        • 9. Re: dynamical SQL with compile error ora06550
                          592815
                          Hi Keith,
                          I run your code for test procedure and wrapped it. message said
                          PL/SQL procedure successfully completed with wrapped code

                          I found this issue due trigger compiled error and not call test procedure.

                          I able to saw wrapped codes by added one line as
                          l_wrap := SYS.DBMS_DDL.WRAP(l_source);
                          DBMS_OUTPUT.put_line(l_wrap);


                          Thanks in advance!

                          ===working records==
                          SQL>
                          SQL> declare
                          2 l_source VARCHAR2(32767);
                          3 l_wrap VARCHAR2(32767);
                          4 BEGIN
                          5 l_source := 'create or replace procedure test as'||
                          6 'wallet_open exception;'||
                          7 'pragma exception_init(wallet_open,-28000);'||
                          8 'v_stmt varchar2(100);'||
                          9 'v_password varchar2(20);'||
                          10 'begin'||
                          11 'v_password := ''ABCDEFG'';'||
                          12 'v_stmt :=''ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "abcdefg"'';'||
                          13 'execute immediate v_stmt ;'||
                          14 'exception'||
                          15 'when wallet_open then'||
                          16 'null;'||
                          17 'END;';
                          18 l_wrap :=SYS.DBMS_DDL.WRAP(l_source);
                          19 END;
                          20 /

                          PL/SQL procedure successfully completed

                          SQL> select distinct object_name from user_objects where upper(object_name)='TEST';

                          OBJECT_NAME
                          --------------------------------------------------------------------------------

                          Edited by: Oradb on Nov 13, 2012 10:24 AM
                          • 10. Re: dynamical SQL with compile error ora06550
                            592815
                            I added a DBMS_OUTPUT.put_line(l_wrap) in keith codes
                            and run out put wrapped test codes in sqlplus and can see wrapped test procedure.
                            create or replace procedure test wrapped
                            a000000
                            b2
                            abcd
                            abcd
                            abcd
                            abcd
                            abcd
                            abcd
                            abcd
                            abcd
                            abcd
                            abcd
                            abcd
                            abcd
                            abcd
                            abcd
                            abcd
                            7
                            11d 124
                            ohyVALi7ang26ROCF0CZ3wLg6ngwgy7Q2SdqfC/p+D6E39xrRLEK0/eVVEVSORSTWoZXk1gi
                            JT9nTrV3IXmGVbi5uMlIl+0C/WV9wPlFL5z37QfcEOYUdmLx8iwul2hEvDehUX0jLfiltHqx
                            MhAgy16zDvWPfv5uE4HrlBvRAYoDmETXR7r10x/uyQyUxDw4sVyq6Ndh4GSFw9zp801nKSN1
                            P0GOB03CtlcnrqAjQhASJKrP4sXW74oOyr373DBBP/CLndRTT0TZ1HvWVzAgL5C++Dl6PNyQ

                            /
                            But I got compiled errors as
                            Compilation errors for PROCEDURE SYS.TEST

                            Error: PLS-00103: Encountered the symbol "ASWALLET_OPEN" when expecting one of the following:

                            ( ; is with authid as cluster compress order using compiled
                            wrapped external deterministic parallel_enable pipelined
                            result_cache
                            The symbol "is" was substituted for "ASWALLET_OPEN" to continue.
                            Line: 1
                            Text: create or replace procedure test wrapped

                            Error: PLS-00103: Encountered the symbol "=" when expecting one of the following:

                            constant exception <an identifier>
                            <a double-quoted delimited-identifier> table long double ref
                            char time timestamp interval date binary national character
                            nchar
                            The symbol "<an identifier>" was substituted for "=" to continue.
                            Line: 1
                            Text: create or replace procedure test wrapped

                            Error: PLS-00103: Encountered the symbol "=" when expecting one of the following:

                            constant exception <an identifier>
                            <a double-quoted delimited-identifier> table long double ref
                            char time timestamp interval date binary national character
                            nchar
                            Line: 1
                            Text: create or replace procedure test wrapped

                            .Bot trigger and procedure works well before wrapped.
                            I use oracle 11g2 at window 2003.

                            Thanks
                            newdba
                            • 11. Re: dynamical SQL with compile error ora06550
                              Keith Jamieson
                              Sigh: All your problems are related to your dynamic sql.

                              Now your problem is that when you build your code up , you are not leaving spaces in the appropriate places:

                              When you build up your string

                              you want
                              create or replace procedure test as wallet_open 
                              what you have is the following:
                              create or replace procedure test aswallet_open 
                              Now the compiler is looking for the word as but cant find it because it is expecting it to be surrounded by white space.

                              Take your entire sql string , put it in a variable
                              and then display the contents of that variable.

                              That should be a full working procedure if its done correctly.

                              If not, you need to fix any errors in it.


                              Top Tip:

                              Add a space immediately after the single quote that begins the string and another one imemdiately before the last single_quote.

                              so for example we should get this which should work (Not tested)

                              v_source varchar2(32767) := ' CREATE OR REPLACE '||' PROCEDURE TEST AS '||' wallet_open varchar2(40) '||' begin null; end; ';

                              instead of this which definitely won't work.

                              v_source varchar2(32767) := 'CREATE OR REPLACE'||'PROCEDURE TEST AS'||' wallet_openvarchar2(40)'||'begin null; end;';

                              Edited by: Keith Jamieson on Nov 15, 2012 2:45 PM
                              • 12. Re: dynamical SQL with compile error ora06550
                                592815
                                Thanks Keith help!