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

    Unanswered: Getting Problem while excecuting the procedure.

    I am getting the error as invalid sql statement

    Code:
    DECLARE count NUMBER;
    tname NVARCHAR2(255);
    indexname NVARCHAR2(255);
    table_name 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......');
    EXECUTE IMMEDIATE 'DROP INDEX '||indexname;
    END IF;
    END LOOP;
    CLOSE indexCursor;
    COMMIT;

    END;
    /



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

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    usually you would have DECLARE on its own line.
    that sure seems complicated for something you could easily do with a for loop or dynamic sql in way less lines.

    Code:
    declare
      sql_stmt varchar2(2000);
    
    begin
      for i in (SELECT index_name
                 FROM user_indexes 
                 where table_name = 'TABLE_PART_ACTION'
                 and index_name != 'SYS_C0014096')
      loop
    
        sql_stmt:= 'drop index '||i.index_name;
        execute immediate sql_stmt;
    
    end loop;
    end;
    /

    Code:
    spool drop_i.sql
    SELECT 'drop index '||index_name||';' 
    FROM user_indexes 
    where table_name = 'TABLE_PART_ACTION' and index_name != 'SYS_C0014096';
    spool off;
    @drop_i.sql
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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