This discussion is archived
12 Replies Latest reply: Nov 15, 2012 6:33 AM by 592815 RSS

dynamical SQL with compile error ora06550

592815 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    writing PL/SQL to write PL/SQL results in infinite recursion.
  • 2. Re: dynamical SQL with compile error ora06550
    KeithJamieson Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    we need to wrap codes by SYS.DBMS_DDL.WRAP(ddl => l_source);
    any advise?
  • 4. Re: dynamical SQL with compile error ora06550
    KeithJamieson Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    KeithJamieson Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    KeithJamieson Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    KeithJamieson Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks Keith help!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points