Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2010
    Posts
    9

    Unanswered: Getting problem while executing the procedure

    Please help me in resolving the error below

    Code:
    =====
    DECLARE --count NUMBER;
    tname NVARCHAR2(255);
    indexname NVARCHAR2(255);
    tablename NVARCHAR2(255);

    --tname:= 'TABLE_PART_ACTION';


    CURSOR indexCursor IS
    SELECT index_name,table_name
    FROM user_indexes where table_name='TABLE_PART_ACTION';

    BEGIN

    OPEN indexCursor;
    LOOP
    FETCH indexCursor INTO indexname, table_name;
    EXIT WHEN indexCursor%NOTFOUND;


    --select count INTO count from user_indexes where table_name=tname;

    IF (indexname!='SYS_C0014096') then
    dbms_output.put_line('Testing......');
    DROP index indexname;
    END IF;
    END LOOP;
    CLOSE indexCursor;
    END;
    /

    Error:
    =====
    ORA-06550: line 25, column 6:
    PLS-00103: Encountered the symbol "DROP" when expecting one of the following:

    begin case declare else elsif end exit for goto if loop mod
    null pragma raise return select update while with
    <an identifier> <a double-quoted delimited-identifier>
    <a bind variable> << close current delete fetch lock insert
    open rollback savepoint set sql execute commit forall merge
    pipe

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by dineshmurs View Post
    ORA-06550: line 25, column 6:
    PLS-00103: Encountered the symbol "DROP" when expecting one of the following:
    You cannot use DDL statements directly with PL/SQL, you have to use EXECUTE IMMEDIATE:

    Code:
    EXECUTE IMMEDIATE 'DROP INDEX '||indexname;

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I see that you are using DROP INDEX SYS_... This has been created with some constraint (most likely primary key). You should name your primary key constraint and corresponding index so that your code is reusable.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Apr 2010
    Posts
    9
    i am getting the following error after using the
    code:
    EXECUTE IMMEDIATE 'DROP INDEX '||indexname;


    Error at line 1
    ORA-00900: invalid SQL statement
    ORA-06512: at line 25

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    The best way to determine the SQL being executed is to create a variable and display the contents of the variable.

    lv_sql VARCHAR(1000);
    ...
    lv_sql := 'DROP INDEX '||indexname;
    DBMS_OUTPUT.PUT_LINE('Command: '||lv_sql);

    This will show the SQL statement from within SQL*Plus that you are executing to see whether syntactically it is correct. Try executing the line of code manually to see if it works. Sometimes, an index has been created by another user for your schema, hence, the use of USER_INDEXES is incorrect and should be ALL_INDEXES.

    Try DROP INDEX <owner>.<index_name>;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Try the following

    Code:
    DECLARE
    CURSOR indexCursor IS
    SELECT OWNER,index_name
    FROM ALL_indexes where table_name='TABLE_PART_ACTION'
    AND OWNER = USER
    AND indexname <> 'SYS_C0014096';
    BEGIN
    
    for pnt in indexCursor loop
      execute immediate 'DROP index '||pnt.owner||'.'||pnt.index_name;
    END LOOP;
    END;
    /
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    dineshmurs,

    there's a couple of problems with your pl/sql block:

    1.) As shammat already stated, you have to use "EXECUTE IMMEDIATE" for DDL statements in PL/SQL

    2.) As it-iss stated, SYS_ named indexes should be avoided (they are not guaranteed to have the same name after i.e. exporting/importing a table)

    3.) The "IF indexname != <whatever>" should be moved into the query for performance reasons

    4.) In the query you should exclude LOB-indexes to avoid ORA-22864 errors

    5.) Looping over cursors is one of the most efficient performance killers in PL/SQL.
    The reason is, that every loop cycle generates 2 context switches (PLSQL->SQL and back).
    Usually you will gain factors in performance by looping over a PL/SQL table instead.

    6.) You should code this as stored procedure to avoid n SQL*Net roundtrips when deleting n indexes


    My take on that (ignoring my own advise #6) would be:

    Code:
    -- tested on 11.2.0.1.0
    DECLARE
      type tIndArr is table of user_indexes&#37;rowtype index by pls_integer;
    
      cursor indexCursor IS 
           SELECT  *
           FROM    user_indexes 
           WHERE   table_name    = 'TEST' 
           AND     index_name   != 'PK_TEST'  -- index implementing the primary key, like your SYS_C0014096
           AND     index_type   != 'LOB';     -- avoid ORA-22864 on LOB indexes
    
           vIndArr  tIndArr;
           vCmdStr  varchar2(4000);
    
    BEGIN
       OPEN  indexCursor;
       FETCH indexCursor bulk collect into vIndArr;
       CLOSE indexCursor;
       if vIndArr.COUNT > 0 then
          for i in vIndArr.FIRST..vIndArr.LAST loop
             vCmdStr := 'DROP INDEX ' || vIndArr(i).index_name; 
             dbms_output.put_line('About to execute: ' || vCmdStr);
             execute immediate vCmdStr;
          END LOOP;
       end if;
    END;
    /
    Last edited by magicwand; 04-21-10 at 09:20. Reason: typos fixed
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •