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