Home » Developer & Programmer » Forms » NVL2 not working (Windows 2000)
NVL2 not working [message #399877] Fri, 24 April 2009 06:12 Go to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
SELECT NVL2(value, NULL, value) FROM DUAL not working. It says NVL2 must be declared.
Any suggestions?
Re: NVL2 not working [message #399879 is a reply to message #399877] Fri, 24 April 2009 06:20 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Read the forum guidelines again, and post the required information.

Like the complete SQL*Plus session where this happened and your Oracle version.
Re: NVL2 not working [message #399880 is a reply to message #399877] Fri, 24 April 2009 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
From one of your previous topics:
Michel Cadot wrote on Tue, 18 November 2008 09:29
In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel


Re: NVL2 not working [message #399883 is a reply to message #399877] Fri, 24 April 2009 06:27 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Oracle version is 10G
we have a procedure in forms 10G where we are using this statement
Re: NVL2 not working [message #399886 is a reply to message #399883] Fri, 24 April 2009 06:34 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
ThomasG wrote on Fri, 24 April 2009 12:20
Read the forum guidelines again, and post the required information.

Like the complete SQL*Plus session where this happened and your Oracle version.

It's not difficult. Just read what Thomas wrote, go and read the forum guidelines and then post back with the information that was requested. If you are incapable of this, then please explain why.
Re: NVL2 not working [message #399887 is a reply to message #399877] Fri, 24 April 2009 06:38 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
sorry i mentioend the worng OS

we have
windows XP
oracle 10.2.0
Re: NVL2 not working [message #399889 is a reply to message #399886] Fri, 24 April 2009 06:40 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Example and hint on what might be the problem :

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serverout on
SQL> DECLARE
  2    v_i NUMBER;
  3  BEGIN
  4    SELECT Nvl2(1,1,1) INTO v_i FROM dual;
  5    Dbms_Output.put_line('Result :' || v_i);
  6  END;
  7  /
Result :1

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> BEGIN
  2    Dbms_Output.put_line('Result :' || Nvl2(1,1,1));
  3  END;
  4  /
  Dbms_Output.put_line('Result :' || Nvl2(1,1,1));
                                     *
ERROR at line 2:
ORA-06550: line 2, column 38:
PLS-00201: identifier 'NVL2' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored



And :

- "Oracle 10.2.0" is not four digits

- You still haven't posted your actual code, nor the actual exact error message.

[Updated on: Fri, 24 April 2009 06:41]

Report message to a moderator

Re: NVL2 not working [message #399892 is a reply to message #399877] Fri, 24 April 2009 06:59 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

select NVL2(supplier_city, 'Completed', 'n/a')
from suppliers;
Re: NVL2 not working [message #399893 is a reply to message #399892] Fri, 24 April 2009 07:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why are you unable to post in the same way ThomasG did?
I see no error in your post.
Use SQL*Plus and copy and paste your session.

Regards
Michel

[Updated on: Fri, 24 April 2009 07:01]

Report message to a moderator

Re: NVL2 not working [message #399929 is a reply to message #399893] Fri, 24 April 2009 09:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Because this piece of SQL is in a Forms program unit, and I'll bet that NVL2 isn't supported by Forms.
Re: NVL2 not working [message #399930 is a reply to message #399877] Fri, 24 April 2009 09:39 Go to previous message
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
Senior Member
Just checked - nvl2 isn't supported by forms 10g, which surprises me.

@waqasbhai - if you want to use this you're going to have to create a procedure in the database and call that from your form.
Previous Topic: crash ie7 with forms10
Next Topic: Function vs. Procedure
Goto Forum:
  


Current Time: Fri Dec 06 02:19:27 CST 2024