CODEWITHSUNDEEP
plsql
anudeepks
anudeepks

Reputation: 1132

How to place a plsql block inside a sequence

I am trying to create a user generated sequence. According to usual syntax of oracle sequence we can start with a number and increment a value. Is there a method to write a plsql block (declare begin end) inside a sequence and generate my own sequnce.

example : ABC001 When i call the next val of sequence , the value should be ABC002

Upvotes: 0

Views: 1068

Answers (2)

Sachin
Sachin

Reputation: 1003

PLEASE CLEAR FIRST WHAT YOU EXACTLY WANT TO ASK.

If you are asking HOW TO DYNAMICALLY CREATE SEQUENCE USING PL/SQL, then check below.

Simplest way.

DECLARE
    SQL_S VARCHAR2(100);
BEGIN
    SQL_S := 'CREATE SEQUENCE SQN_NAME INCREMENT BY 1 START WITH 1';
    EXECUTE IMMEDIATE SQL_S;
END;
/

If you want to dynamically create sequence with some DYNAMIC name as argument passed to procedure, then it will be like

CREATE OR REPLACE PROCEDURE DYNAMIC_SQN (ARG IN VARCHAR2) IS
    SQL_S VARCHAR2(100);
    PARAM1 VARCHAR2(20);
BEGIN
    PARAM1 := 'SQN_NAME_' || ARG;
    SQL_S := 'CREATE SEQUENCE ' || PARAM1 || ' INCREMENT BY 1 START WITH 1';
    EXECUTE IMMEDIATE SQL_S;
END;
/

And if you simply want to insert in to any column, and create the PK using it in addition to some String, then

INSERT INTO TABLE_T VALUES('ABC'|| SEQUENCE_NAME.nextval, OTHER_VALUES);

It will still give you values like : ABC1, ABC2, .... ABC12, ABC13, .... ABC99, ABC100 and so on...

Upvotes: 1

theDbGuy
theDbGuy

Reputation: 931

Considering the sample example you have given i m writing the following code

create your sequence, then

insert into seq values('ABC'||YOURSEQUENCENAME.nextval,YOUR_VALUE);

Upvotes: 0

Related Questions