| Sequence.currval problem [message #200220] |
Sun, 29 October 2006 11:52  |
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  |
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 #200232 is a reply to message #200229] |
Sun, 29 October 2006 18:29 |
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 #200377 is a reply to message #200256] |
Mon, 30 October 2006 09:58 |
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 #200526 is a reply to message #200518] |
Tue, 31 October 2006 04:42 |
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 |
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 |
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 |
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 |
Frank
Messages: 7901
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!
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
