DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Execute Immediate error !!

  1. #1
    Join Date
    Nov 2002
    Posts
    170

    Execute Immediate error !!

    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>

  2. #2
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    Re-write your sql stmt as
    Code:
    OPEN cv FOR
     'select index_name from dba_indexes where table_name = ''' || table_name ||
     ''' and status = ''UNUSABLE''';
    thomasp

  3. #3
    Join Date
    Nov 2002
    Posts
    170
    1 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;
    SQL> /

    Procedure created.

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

    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at "MDM3.INDEX_REBUILT", line 9
    ORA-06512: at line 1


    SQL>

  4. #4
    Join Date
    Nov 2002
    Posts
    170
    1 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;
    SQL> /

    Procedure created.

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

    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at "MDM3.INDEX_REBUILT", line 9
    ORA-06512: at line 1


    SQL>
    SQL> select table_name from dba_indexes
    2 where owner='MDM3'
    3 and status='UNUSABLE';

    TABLE_NAME
    ------------------------------
    T_OLD_CMPN_HIST_XTRCT

    SQL>

  5. #5
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155

    Cool

    Are you logged in as user MDM3?

  6. #6
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    Any reason why you would want to use DBA_INDEXES rather than USER_INDEXES.
    You will need to grant select privileges explicitly for this table if you want to use them within stored procedures.
    thomasp

  7. #7
    Join Date
    Nov 2002
    Posts
    170
    I am logged in as MDM3...


    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>
    SQL>
    SQL> exec index_rebuilt('T_OLD_CMPN_HIST_XTRCT');
    BEGIN index_rebuilt('T_OLD_CMPN_HIST_XTRCT'); END;

    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at "MDM3.INDEX_REBUILT", line 9
    ORA-06512: at line 1


    SQL>
    SQL>
    SQL> show user
    USER is "MDM3"
    SQL>

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    and do you have direct access to dba_tables (NOT through a role)

  9. #9
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155

    Wink

    Execute this:

    select owner, index_name
    from dba_indexes
    where table_owner='MDM3'
    and table_name = 'T_OLD_CMPN_HIST_XTRCT';

    See if any indexes on this table are created in a different schema (other than MDM3).

  10. #10
    Join Date
    Nov 2002
    Posts
    170
    SQL> show user
    USER is "MDM3"
    SQL>
    SQL>
    SQL> select table_name from dba_indexes
    2 where owner='MDM3'
    3 and status='UNUSABLE';

    TABLE_NAME
    ------------------------------
    T_OLD_CMPN_HIST_XTRCT
    SQL>
    SQL> select owner, index_name
    2 from dba_indexes
    3 where table_owner='MDM3'
    4 and table_name = 'T_OLD_CMPN_HIST_XTRCT';

    OWNER INDEX_NAME
    ------------------------------ ------------------------------
    MDM3 SYS_C007499

    SQL>
    SQL>
    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>
    SQL>
    SQL> exec index_rebuilt('T_OLD_CMPN_HIST_XTRCT');
    BEGIN index_rebuilt('T_OLD_CMPN_HIST_XTRCT'); END;

    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at "MDM3.INDEX_REBUILT", line 9
    ORA-06512: at line 1


    SQL>
    SQL>

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