CODEWITHSUNDEEP
oracle-sqldeveloper
abodvdv
abodvdv

Reputation: 99

Create Sequence if it not exists using oracle sql developer

i'm trying to create procedure to create Sequence if it not exists and return the sequence name so i can use it 

create or replace
PROCEDURE CREATESEQ 
(
  TableName IN VARCHAR2  
, CloumnName IN VARCHAR2  
, pSeq_name out VARCHAR2
) AS 
Seq_name varchar2(100);
vCounter number;
BEGIN

Seq_name:=tablename||cloumnname;
vCounter :=0;
  --  dbms_output.put_line(Seq_name);

SELECT count(1)
  INTO vCounter
  FROM user_sequences
  WHERE sequence_name = Seq_name;

IF vCounter > 0 THEN
    pSeq_name:=seq_name;
else
   EXECUTE IMMEDIATE  'create sequence '||Seq_name;
    pSeq_name:=seq_name;
END IF ;
END CREATESEQ;

when i try to execute CREATESEQ procedure like this

DECLARE
  TABLENAME VARCHAR2(200);
  CLOUMNNAME VARCHAR2(200);
  PSEQ_NAME VARCHAR2(200);
BEGIN
  TABLENAME := 'Sections';
  CLOUMNNAME := 'SectionID';

  CREATESEQ(
    TABLENAME => TABLENAME,
    CLOUMNNAME => CLOUMNNAME,
    PSEQ_NAME => PSEQ_NAME
  );

  :PSEQ_NAME := PSEQ_NAME;
END;

i get this error

ORA-01031: insufficient privileges
ORA-06512: at "TRAINING_AKHADIER.CREATESEQ", line 23
ORA-06512: at line 9

appreciate any suggestion

Upvotes: 2

Views: 2391

Answers (1)

kevinskio
kevinskio

Reputation: 4551

The user who calls this procedure needs to have CREATE_ANY_SEQUENCE granted to them by a user who has this privilege and the ability to grant it.

Effectively for a learning scenario:

  • log on as a user with DBA
  • execute GRANT CREATE_ANY_SEQUENCE TO YourSchemaOwnername;

Upvotes: 1

Related Questions