Home » SQL & PL/SQL » SQL & PL/SQL » Creating Function using NEXTVAL
icon7.gif  Creating Function using NEXTVAL [message #245812] Mon, 18 June 2007 19:45 Go to next message
HIMULE
Messages: 8
Registered: May 2007
Location: Pleasanton, CA
Junior Member

I am creating a function of an order sequence no. Please find the code I created below;

CREATE OR REPLACE FUNCTION get_nbr_seq
(nbr_sequence IN NUMBER)
RETURN CHAR IS
new_sequence char (9);
temp_sequence number;
BEGIN
SELECT nbr_sequence.NEXTVAL
INTO temp_sequence
FROM DUAL;
new_sequence := 'XX'||LPAD(temp_sequence,7,'0');

END get_sonbr_seq;

And I am getting the following error;

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/16 PLS-00487: Invalid reference to variable 'NBR_SEQUENCE'

Please advise on how I can handle this error.


Thanks,
Hazel
Re: Creating Function using NEXTVAL [message #245815 is a reply to message #245812] Mon, 18 June 2007 20:46 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You'd get your problem solve faster if you'd read & FOLLOWED the STICKY posts at the top of this forum.

We (TINW) have no idea which user is invoking the posted code,
who owns the posted code, if the sequence exists & who own it.

Yes, you have a problem. Without you posting many more details, you'll continue to have this problem unresolved.

[Updated on: Mon, 18 June 2007 21:56] by Moderator

Report message to a moderator

Re: Creating Function using NEXTVAL [message #245820 is a reply to message #245812] Mon, 18 June 2007 21:55 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Rename your function parameter to the name other than your sequence (and another database objects) name.

To avoid this, you should establish naming convention in your project to avoid naming the sequence and the function parameter with the same name.

By the way, the function, you provided, does not return anything.
Re: Creating Function using NEXTVAL [message #245850 is a reply to message #245812] Tue, 19 June 2007 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If "nbr_sequence" is a sequence your parameter must be of VARCHAR2 datatype and you have to use dynamic SQL.

If it is really a number, don't use "nextval" use "+1" and you don't have the use of SQL.

Regards
Michel
icon7.gif  Re: Creating Function using NEXTVAL [message #246066 is a reply to message #245812] Tue, 19 June 2007 14:28 Go to previous messageGo to next message
HIMULE
Messages: 8
Registered: May 2007
Location: Pleasanton, CA
Junior Member

Thanks for all your comments. They all helped.

The following code worked.

CREATE OR REPLACE FUNCTION get_sonbr_seq
(ws_so_nbr IN CHAR)
RETURN CHAR IS
new_sequence char (9);
temp_sequence number;
BEGIN
SELECT so_nbr_sequence.NEXTVAL
INTO temp_sequence
FROM DUAL;
new_sequence := 'XX'||LPAD(temp_sequence,7,'0');

END get_sonbr_seq;
Re: Creating Function using NEXTVAL [message #246067 is a reply to message #246066] Tue, 19 June 2007 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One question: what is the use of the parameter?

Regards
Michel
Re: Creating Function using NEXTVAL [message #247377 is a reply to message #245812] Mon, 25 June 2007 13:25 Go to previous messageGo to next message
HIMULE
Messages: 8
Registered: May 2007
Location: Pleasanton, CA
Junior Member
Actually, I removed the parameter and recompiled the function and it worked fine. Sorry about that.
Re: Creating Function using NEXTVAL [message #247393 is a reply to message #245812] Mon, 25 June 2007 14:41 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
You still haven't returned the value. Do the following

CREATE OR REPLACE FUNCTION get_sonbr_seq
RETURN VARCHAR2 IS
new_sequence char (9);
BEGIN
  SELECT 'XX'||TO_CHAR(so_nbr_sequence.NEXTVAL,'FM0000000')
  INTO NEW_sequence
  FROM DUAL;

  RETURN NEW_SEQUENCE;

END get_sonbr_seq;
Re: Creating Function using NEXTVAL [message #247975 is a reply to message #245812] Wed, 27 June 2007 12:49 Go to previous messageGo to next message
HIMULE
Messages: 8
Registered: May 2007
Location: Pleasanton, CA
Junior Member
Thanks Bill! Your code looks better. Just wondering why you have to use VARCHAR2.
Re: Creating Function using NEXTVAL [message #247976 is a reply to message #245812] Wed, 27 June 2007 12:52 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
As a general good rule, I never use CHAR. It has a number of limitations and is generally hard to work with because it always pads with blanks. VARCHAR2 is space efficient and is easier to use in comparisons and string manipulation. Normally when I develop code or tables, I avoid CHAR's and LONG's.
Re: Creating Function using NEXTVAL [message #247978 is a reply to message #247976] Wed, 27 June 2007 13:00 Go to previous message
HIMULE
Messages: 8
Registered: May 2007
Location: Pleasanton, CA
Junior Member
Thanks Bill for the information. Nice to have a senior memebr around like you.
Previous Topic: Avoiding Full table scan
Next Topic: Index on a view
Goto Forum:
  


Current Time: Fri Dec 09 15:31:01 CST 2016

Total time taken to generate the page: 0.17418 seconds