Dynamic Sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Dynamic Sql

  1. #1
    Join Date
    Mar 2002
    Posts
    46

    Dynamic Sql

    Hi,

    I'm trying to create table, trigger and procedure with dynamic name. My script works but with compilation error in my trigger and procedure. However, if I spool the source code executed and execute it in sqlplus everything work! why???
    I user Oracle 8.1.7
    Here is an example on the creation of the table and a trigger:

    DECLARE
    var_schm_name VARCHAR2(30) := '&schema_name';
    var_str VARCHAR2(1000);
    BEGIN
    var_str := 'CREATE TABLE '||var_schm_name||'_schema_version('||
    'schema_version VARCHAR2(5),'||
    'modification_date DATE,'||
    'modified_by VARCHAR2(30))';
    EXECUTE IMMEDIATE var_str;
    var_str := 'CREATE OR REPLACE TRIGGER '||var_schm_name||'_tg_schema_version_iu'||CHR(13)||
    ' BEFORE INSERT OR UPDATE'||CHR(13)||
    ' ON '||var_schm_name||'_schema_version'||CHR(13)||
    ' FOR EACH ROW '||CHR(13)||
    ' DECLARE'||CHR(13)||
    ' v_nb_row NUMBER(1);'||CHR(13)||
    ' BEGIN'||CHR(13)||
    ... other code
    ' EXCEPTION'||CHR(13)||
    ' WHEN OTHERS THEN'||CHR(13)||
    ' RAISE_APPLICATION_ERROR(SQLCODE,SQLERRM);'||CHR(13)||
    ' END;';
    EXECUTE IMMEDIATE var_str;
    END;
    /

    Thanks
    DONTBSHY

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Perhaps you could post the error message
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Mar 2002
    Posts
    46
    OK here is the message:
    ERROR:
    ORA-24344: success with compilation error
    ORA-06512: at line 22



    Warning: PL/SQL compilation errors.

    SQL> show error
    No errors.

    If I look in Toad, the trigger is invalid. If I try to compile it from toad I receive this error:

    The following error has occurred:

    TRIGGER PRACA.PR_TG_SCHEMA_VERSION_IU
    On line: 1
    PLS-00103: Encountered the symbol "" when expecting one of the following:

    begin function package pragma procedure subtype type use
    cursor




    Details:
    TRIGGER PRACA.PR_TG_SCHEMA_VERSION_IU
    On line: 1
    PLS-00103: Encountered the symbol "" when expecting one of the following:

    begin function package pragma procedure subtype type use
    cursor
    And if I copy the code of the trigger that appears in Toad and paste it in sql plus the trigger is created without compilation error.
    DONTBSHY

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if you dont see anything when you do show error look user_errors

  5. #5
    Join Date
    Mar 2002
    Posts
    46
    In user_errors there is the same error than in toad...
    DONTBSHY

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    may I ask why you use chr(13) instead of chr(10)

    also itīs trigger error not your PL/SQL code error how do we suppose to know whatīs the error if we dont see any trigger code

  7. #7
    Join Date
    Mar 2002
    Posts
    46
    Thanks Pando,

    I don't know why I used the carriage return ascii code instead of the new line code... The script works with chr(10).

    Thanks a lot
    DONTBSHY

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