CODEWITHSUNDEEP
oracle-databaseplsqlsequence
anria
anria

Reputation: 657

How correctly to assign a value

I need to assign a value of my sequence to parameter id_nu. How can I do it?

sname - name of the schema

When I try in such way,I got the below error

PROCEDURE proc_createUser(sname IN varchar2, uname IN varchar2,ts IN varchar2) 
 IS

id_nu number(6);

BEGIN

id_nu := sname.UsersSeq.NextVal;

-- something

END proc_createUser;

Upvotes: 0

Views: 66

Answers (2)

Dba
Dba

Reputation: 6639

Try like this,

CREATE OR REPLACE 
PROCEDURE proc_createuser(sname IN VARCHAR2, 
                          uname IN VARCHAR2,
                          ts IN VARCHAR2) 
IS
     id_nu NUMBER(6);
     v_sql VARCHAR2(200);
BEGIN
     v_sql := 'SELECT '|| sname||'.UsersSeq.NextVal FROM dual';
     EXECUTE IMMEDIATE v_sql INTO id_nu;
     dbms_output.put_line(id_nu);

END proc_createuser;

Upvotes: 3

Aditya Kakirde
Aditya Kakirde

Reputation: 5225

This is done in 11g where you can directly assign a sequence value to a variable. In earlier versions, you need to write like this - 

PROCEDURE proc_createUser(sname IN varchar2, uname IN varchar2,ts IN varchar2) IS

id_nu number(6);

BEGIN


select sname.UsersSeq.NextVal into id_nu
from dual;

END proc_createUser;

Upvotes: 0

Related Questions