6 Replies Latest reply: Nov 15, 2012 6:56 AM by jeneesh RSS

    ORA-01031: insufficient privileges

    user12093849
      Oracle 10g

      I am running the below oracle script through a.sql file. I am runnin thro TEST1 schema and i want the below sequence to be created in TEST2 schema. is there any way we can transfer the schema name in between the procedure?
      SET TIMING ON
      SET ECHO ON
      SET ESCAPE ON
      SET SERVEROUTPUT ON SIZE 100000
      SET FEEDBACK ON
      set timing on
      set serveroutput on
      WHENEVER SQLERROR EXIT SQL.SQLCODE;
      
      DECLARE
          cursor c1 is select SEQUENCE_NAME from USER_SEQUENCES
                   where SEQUENCE_NAME in ('U_TEST');
      BEGIN
           for rec in c1 loop
                 execute immediate 'DROP SEQUENCE ' || rec.sequence_name;
          end loop;
      end;
      /
      
      
      CREATE SEQUENCE TEST2.U_TEST
        MINVALUE 1
        MAXVALUE 999999999999999999999999999
        INCREMENT BY 1
        START WITH 1
        CACHE 100
        NOORDER
        NOCYCLE;
      
      exit 0
      I am getting the following error
      CREATE SEQUENCE SCPOMGR.U_RPT_MARKET_MOE_SEQ
      *
      ERROR at line 1:
      ORA-01031: insufficient privileges