Home » SQL & PL/SQL » SQL & PL/SQL » Sequence.currval problem
Sequence.currval problem [message #200220] Sun, 29 October 2006 11:52 Go to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi everyone,
i am really sorry if anything happen while i was working on updating my question ,well i will return to the question of the Type but right now am really busy with the SEQUENCES nextval and currval ...
if anyone can help me with this error i really appreciate it..
thanks in advance

My problem with sequence is that i try to get the currval in a function so i tried the following:
CREATE OR REPLACE FUNCTION fn_GetCrval(p_Seqname IN VARCHAR2) RETURN PLS_INTEGER
IS
str VARCHAR2(32767):=UPPER(p_Seqname);
v PLS_INTEGER;
BEGIN
SELECT str||'.NEXTVAL' INTO v FROM dual;
RETURN v;
END;

and when i tried to get the sequence currval ..Like this:
DECLARE
vs PLS_INTEGER;
BEGIN
vs:=fn_GetCrval('SEQ_CONTENTS_NR')-1;
DBMS_OUTPUT.PUT_LINE('the value is:'||vs);
END;
i've got this error:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

is there anyting that make it easy to get the currval in a function..

thanks for everyone
bahy91
Re: Sequence.currval problem [message #200222 is a reply to message #200220] Sun, 29 October 2006 12:57 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Best solution is not to write generic code, but write a function pr. sequence or even just select nexval directly in Your code.

I mean, why is

v_seq := fn_GetCrval('cur_emp');

so much better than

select cur_emp.nextval into v_seq from dual;

????

If You really insist on doing it generic, You should use execute immediate, but it will not perform as nice as the select into.

execute immediate ('select '||p_Seqname||' from dual') into s_seq;


Br
Kim Anthonisen
Re: Sequence.currval problem [message #200229 is a reply to message #200222] Sun, 29 October 2006 14:41 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Actually, it should be

EXECUTE IMMEDIATE ('select ' || p_seqname || '.nextval from dual') INTO v;

./fa/1598/0/

Or, the whole thing could look like
CREATE OR REPLACE FUNCTION fn_GetCrval(p_Seqname IN VARCHAR2) 
  RETURN PLS_INTEGER
IS
  v PLS_INTEGER;
BEGIN
  EXECUTE IMMEDIATE ('select ' || p_seqname || '.nextval from dual') INTO v;
  RETURN v;
END;
Re: Sequence.currval problem [message #200232 is a reply to message #200229] Sun, 29 October 2006 18:29 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thank you for your replay,
i need the currval from the sequence to compare it with another input parameter in another procedure..well maybe we got to that also ..but for now ..i wrote two functions to return the cuurvalue and the next value from the sequence but the problem is that i need to run the main procedure twice to get result from the sequence?? ..the two functions are:
CREATE OR REPLACE FUNCTION
fn_GetLastGeneratedId_A(cTableName IN VARCHAR2,cColumnname IN VARCHAR2)
RETURN NUMBER IS
v_result NUMBER;
v_sqlstmt1 VARCHAR2(200);
v_sqlstmt2 VARCHAR2(200);
BEGIN
v_sqlstmt1 := 'SELECT ' ||'SEQ_'|| UPPER(cTableName)||'_' ||UPPER(cColumnname)||'.nextval from dual';
v_sqlstmt2:= 'SELECT ' ||'SEQ_'|| UPPER(cTableName)||'_' ||UPPER(cColumnname)||'.currval from dual';
EXECUTE IMMEDIATE v_sqlstmt1 INTO v_result;
EXECUTE IMMEDIATE v_sqlstmt2 INTO v_result;
RETURN v_result;

END fn_GetLastGeneratedId_A;
/
show errors

and :

CREATE OR REPLACE FUNCTION
fn_GetNextValue_A(cTableName IN VARCHAR2,cColumnname IN VARCHAR2)
RETURN NUMBER IS
v_result NUMBER;
v_sqlstmt VARCHAR2(200);
BEGIN
v_sqlstmt := 'SELECT ' ||'SEQ_'|| UPPER(cTableName)||'_' ||UPPER(cColumnname)||'.nextval from dual';
EXECUTE IMMEDIATE v_sqlstmt INTO v_result;
RETURN v_result;

END fn_GetNextValue_A;
/

in the main procedure i need to check if the input parameter has the same value like the current value of the sequence..depending on that the insert into a table will be change to get the nextval from the sequence or the input parameter...


Thank you again ..and if anyone know why i must run these functions twice within the main procedure to get the result from the sequence ..it will be really nice.

thanks in advance,
bahy91
Re: Sequence.currval problem [message #200252 is a reply to message #200232] Mon, 30 October 2006 01:26 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Could You please explain what You are trying to achieve here?

Why do You need to write this so generic?

Br
Kim
Re: Sequence.currval problem [message #200256 is a reply to message #200252] Mon, 30 October 2006 01:43 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Bahy
I need to check if the input parameter has the same value like the current value of the sequence. Depending on that, insert into a table will be changed to get the nextval from the sequence or the input parameter.

If all you want to do is to make sure that this column value (whichever it is) is unique, why don't you simply use sequence, no matter input parameter value?

Because, could it happen that input parameter value is smaller than the sequence current value? What will you do in that case? Insert parameter value and get "ORA-00001 Unique constraint violated" error because such a value already exists in the table?
Re: Sequence.currval problem [message #200377 is a reply to message #200256] Mon, 30 October 2006 09:58 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi everybody,
i want actually to use the currentvalue of the sequence to compare it with the input parameter like this:
create or replace
procedure pr_MakeNewTrigger
(p_tablename IN varchar2,P_Rangestart IN Number,P_Rangeend IN Number,P_Mode IN Number default 0)is
....
this procedure will create new trigger on the giving tablename by using the input of tow values(Rangestart and Rangeend )to update an temporary table and keep the values there. the old trigger will be disabled to let the new trigger work on the table with the new Min and Max values with one condition that the Rangstart is not les than the current value of the existing sequence......

So in this way i need to know the current value of the sequence and keep it in a variable to use it when it need to compare with the Rangestart value to now which way the rest of the procedure will follow in creating new sequence and new trigger..


Thanks in advance,
bahaa
Re: Sequence.currval problem [message #200518 is a reply to message #200377] Tue, 31 October 2006 04:07 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Why do You need to create objects dynamically?

It seems like You should not have the need to do this....

Br
Kim
Re: Sequence.currval problem [message #200526 is a reply to message #200518] Tue, 31 October 2006 04:42 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
well..i have to create a new trigger that will use another or the same sequence on a table..my idea was to create a new sequence and leave the old sequence in the database,in this way i can use the currval of the old sequence in creating the new sequence(using the currval of the old sequence as the minval of the new sequence).this new sequence will be then used to enable the creation of the new trigger while off course the old trigger will be meanwhile disabled...

do you need to see the code??!

thanks in advance,
bahaa
Re: Sequence.currval problem [message #200532 is a reply to message #200526] Tue, 31 October 2006 05:00 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi again

I was not thinking about ther code, more WHY You need to create a sequence on a table dynamically.

What are the business rules applied here?

In the systems I have worked on, once they are in production, there are not run any DDL, unless in a service window.

Br
Kim
Re: Sequence.currval problem [message #200536 is a reply to message #200532] Tue, 31 October 2006 05:11 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi again,
we are working on creating applications can work on both sqlserver and oracle ,the oracle version of the application need to be compatible with the sqlserver one ..
so the mean business roles here are the creating of new trigger that can switch from one counter to another in case of 2 users trying to update one record on two different servers..

the procedure i wrote ..work fine but it need to trigger twice to get the currval in the next time one of the users update that record,because the currval of the sequence can not obtained at that moment unless the nextval will be first initialized..

thanks in advance,
bahaa
Re: Sequence.currval problem [message #200550 is a reply to message #200536] Tue, 31 October 2006 05:54 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

The recommended way of doing this is to write an API, which resides on the database.

Your application then calls the API, regardless if this is on Oracle, SQL Server, DB2 or whatever.

The beauty of this os that You then have total control of the API, so that You can write real Oracle code for the Oracle database, real SQL Server code for the SQL Server etc.

Writing generic code which needs to function *AND PERFORM* on all/many databases will be extremely hard and in many cases fail.

Good luck!

Kim Anthonisen
Re: Sequence.currval problem [message #200577 is a reply to message #200550] Tue, 31 October 2006 07:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
kimant wrote on Tue, 31 October 2006 12:54
Writing generic code which needs to function *AND PERFORM* on all/many databases will be extremely hard and in many cases fail.


Make that "and in ANY case fail"

Tip to OP: Read and reread this last post of Kimant. He is SO right!
Re: Sequence.currval problem [message #200606 is a reply to message #200577] Tue, 31 October 2006 08:18 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thanks for you all,
How to do it..i mean where can i find information over the creating and using API in Oracle..?

Thanks in advance,
bahaa
Re: Sequence.currval problem [message #200607 is a reply to message #200606] Tue, 31 October 2006 08:22 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Stored procedures/packages!

They exists most/all of the the major databases.

So tx. You would call "api_customer.create_customer(?,?,?)", and then bind the variables.....
All You have do do is open the correct database connection....

Smile
Kim
Re: Sequence.currval problem [message #200621 is a reply to message #200607] Tue, 31 October 2006 09:11 Go to previous message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi again,
I think i need more information about the using of API in oracle..maybe google will help!

thanks in advance,
bahaa
Previous Topic: REF CURSOR problem
Next Topic: 'Dynamic' Records with Dynamic Cursors
Goto Forum:
  


Current Time: Mon Dec 05 10:55:05 CST 2016

Total time taken to generate the page: 0.14878 seconds