syntax error for execute immediate!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: syntax error for execute immediate!!

  1. #1
    Join Date
    Nov 2002
    Posts
    170

    syntax error for execute immediate!!

    CREATE OR REPLACE PROCEDURE index_rebuilt (
    table_name IN VARCHAR2)
    IS
    TYPE cv_type IS REF CURSOR;
    cv cv_type;
    val VARCHAR2(32767);
    BEGIN
    /* Construct the very dynamic query and open the cursor. */
    OPEN cv FOR
    'select index_name from dba_indexes where table_name = ' || table_name ||
    'and status = "UNUSABLE" ';
    LOOP
    /* Fetch the next row, and stop if no more rows. */
    FETCH cv INTO val;
    EXIT WHEN cv%NOTFOUND;
    EXECUTE IMMEDIATE 'ALTER INDEX MDM3.' || val || ' REBUILD ONLINE PARALLEL 7';
    END LOOP;
    CLOSE cv;
    END;
    /

    =======================================
    procedure gets created but getting syntax error during execution for the select stmt.

    The following
    +++++++++++++++++++++++++++++++++++++++++
    CREATE OR REPLACE PROCEDURE index_rebuilt (
    table_name IN VARCHAR2)
    IS
    TYPE cv_type IS REF CURSOR;
    cv cv_type;
    val VARCHAR2(32767);
    BEGIN
    /* Construct the very dynamic query and open the cursor. */
    OPEN cv FOR
    "select index_name from dba_indexes where table_name = ' || table_name ||
    'and status = 'UNUSABLE' ";
    LOOP
    /* Fetch the next row, and stop if no more rows. */
    FETCH cv INTO val;
    EXIT WHEN cv%NOTFOUND;
    EXECUTE IMMEDIATE 'ALTER INDEX MDM3.' || val || ' REBUILD ONLINE PARALLEL 7';
    END LOOP;
    CLOSE cv;
    END;
    /
    =============================================
    Geting arning during creation of procedure.

    How do I use the single coates in the select stmt like above ???

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Code:
    OPEN cv FOR
    'select index_name from dba_indexes where table_name = ''' || table_name ||
    '''and status = ''UNUSABLE''';
    LOOP
    All single quotes in the above
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Nov 2002
    Posts
    170
    SQL> CREATE OR REPLACE PROCEDURE index_rebuilt (
    2 table_name IN VARCHAR2)
    3 IS
    4 TYPE cv_type IS REF CURSOR;
    5 cv cv_type;
    6 val VARCHAR2(32767);
    7 BEGIN
    8 /* Construct the very dynamic query and open the cursor. */
    9 OPEN cv FOR
    10 'select index_name from dba_indexes where table_name = ' || table_name ||
    11 'and status = ''UNUSABLE''';
    12 LOOP
    13 /* Fetch the next row, and stop if no more rows. */
    14 FETCH cv INTO val;
    15 EXIT WHEN cv%NOTFOUND;
    16 EXECUTE IMMEDIATE 'ALTER INDEX MDM3.' || val || ' REBUILD ONLINE PARALLEL 7';
    17 END LOOP;
    18 CLOSE cv;
    19 END;
    20 /

    Procedure created.

    SQL>
    SQL> exec index_rebuilt('T_OLD_CMPN_HIST_XTRCT');
    BEGIN index_rebuilt('T_OLD_CMPN_HIST_XTRCT'); END;

    *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended
    ORA-06512: at "MDM3.INDEX_REBUILT", line 9
    ORA-06512: at line 1


    SQL>

  4. #4
    Join Date
    Sep 2001
    Posts
    34
    use this sql instead.

    OPEN cv FOR
    'select index_name from dba_indexes where table_name = ''' || table_name ||
    ''' and status = ''UNUSABLE''';

  5. #5
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    51
    Using bind variables would clean this up by getting rid of all the escaping of quotes.

    CREATE OR REPLACE PROCEDURE index_rebuilt (
    pi_table IN VARCHAR2)
    IS
    TYPE cv_type IS REF CURSOR;
    cv cv_type;
    val varchar2(32767);
    l_sql varchar2(32767);
    BEGIN
    /* Construct the very dynamic query and open the cursor. */
    l_sql := 'select index_name from dba_indexes where table_name = :myTable ' ||
    'and status = :myStatus';

    OPEN cv FOR l_sql using pi_table, 'UNUSABLE';
    LOOP
    /* Fetch the next row, and stop if no more rows. */
    FETCH cv INTO val;
    EXIT WHEN cv%NOTFOUND;
    EXECUTE IMMEDIATE 'ALTER INDEX MDM3.' || val || ' REBUILD ONLINE PARALLEL 7';
    END LOOP;

    CLOSE cv;
    END;
    ____________________
    Pete

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