Home » SQL & PL/SQL » SQL & PL/SQL » Invalid number exception returned by Store Procedure (merged) (Oracle Release 11.1.0.6. 0)
Invalid number exception returned by Store Procedure (merged) [message #431025] Fri, 13 November 2009 12:48 Go to next message
joeller
Messages: 17
Registered: September 2007
Location: Indian Head, Maryland
Junior Member
My Oracle version is 11.1.0.6
My OS is Windows XP SP3
Third party access from Visual Studio 2006 Oracle Data Access Tools.

I created a package with a stored procedure using the Oracle Data access tools. I added an in parameter of CONID of type integer, and an OUT parameter of CONTACTSCUR of the type SYS_REFCURSOR. The CONID originally defaulted to 0 (zero)

that end up looking like this;
PROCEDURE "SEL_CONTACTS" (
"CONID" IN NUMBER DEFAULT 0,
"CONTACTSCUR" OUT SYS_REFCURSOR) IS
BEGIN
IF CONID = 0 THEN
OPEN CONTACTSCUR FOR SELECT CONTACTID, CONTACTLASTNAME + ', ' + CONTACTFIRSTNAME + ' ' + NVL(CONTACTMIDNAME, '') CONTACTNAME,
ORGANIZATIONID,
POSITIONID,
ADDRESS,
CITY,
STATE,
ZIPCODE
FROM ERFO.CONTACTS;

ELSE
OPEN CONTACTSCUR FOR SELECT CONTACTID, CONTACTLASTNAME + ', ' + CONTACTFIRSTNAME + ' ' + NVL(CONTACTMIDNAME, '') CONTACTNAME

FROM ERFO.CONTACTS
WHERE CONTACTID = CONID;
END IF;
END "SEL_CONTACTS";


I kept getting a report of "invalid number" when I attempt to run it from the Oracle Data Tools GUI.

I go the same thing after removing the default and changing the IF line to read
IF CONID IS NULL THEN

The output reads
------ Running Procedure ERFO.PAK_INOI.SEL_CONTACTS@ERFO.ORCL --

ORA-01722: invalid number


By the way
CONTACTID in the table is of datatype nummber(38); (as the oracle em kept changing the key column from integer to number(38)).

As the if and the where clause are the only two place were a number is involved I was wondering if there is an issue with with the Where, and how to fix it
Re: Invalid number exception returned by Store Procedure [message #431027 is a reply to message #431025] Fri, 13 November 2009 12:58 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>ORA-01722: invalid number

Means you are attempting to store a non-numeric value into NUMERIC variable.

find & fix it.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed
Re: Invalid number exception returned by Store Procedure [message #431029 is a reply to message #431027] Fri, 13 November 2009 13:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And you might find it more easily with SQL*Plus, which actually tells you in which row and column of the code the error is, than with GUI tools.
Re: Invalid number exception returned by Store Procedure [message #431033 is a reply to message #431025] Fri, 13 November 2009 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
There is no need for any of the double quote marks that exist in your code.
Re: Invalid number exception returned by Store Procedure (merged) [message #431034 is a reply to message #431025] Fri, 13 November 2009 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As a general rule:

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, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Invalid number exception returned by Store Procedure (merged) [message #431036 is a reply to message #431025] Fri, 13 November 2009 13:24 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
joeller wrote on Fri, 13 November 2009 19:48

OPEN CONTACTSCUR FOR SELECT CONTACTID, CONTACTLASTNAME + ', ' + CONTACTFIRSTNAME + ' ' + NVL(CONTACTMIDNAME, '') CONTACTNAME,
...

When using Oracle PL/SQL, you shall firstly get familiar with its syntax. It is described in PL/SQL User's Guide and Reference, available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/.

In Oracle SQL, '+' operator is used only for numeric addition; string concatenation may be achieved by using '||' operator or CONCAT function.
Re: Invalid number exception returned by Store Procedure (merged) [message #431037 is a reply to message #431025] Fri, 13 November 2009 13:39 Go to previous messageGo to next message
joeller
Messages: 17
Registered: September 2007
Location: Indian Head, Maryland
Junior Member
Actually I am writing this for the second time because something happened to my post when I attempted to submit it and I was not able to view it. So if this post appears twice, that's why.

BlackSwan and Michael, I did read the orafaq forum protocols and I thought I had followed them. Where did I go wrong?

BlackSwan The only variables in this code are the parameters. The ony parameter set is CONID an integer. It is defaulted to 0 (zero). The only place where the variable (currently) is mentioned is in the "If statement" where it is compared to 0 (zero). I cannot for the life of me find anywhere I am trying to set a numeric variable to a non-number.
I have been fighting with this for the last 24 hours. I have checked all sorts of documentation and forums and blogs and each one says that your are trying to assign a non-numeric value to a numeric variable. But for the life of me I can't find where this is so. I even removed all reference to CONID in the body of the procedure and removed the default. I set the if statement to IF 1 = 1 THEN. Still the same error. I tried to run the procedure in SQLPLUS and it blew up on seeing the "." after the package name.

SQL> declare refcur sys_refcursor;
2 PAK_INOI.SEL_CONTACTS(0,REFCUR OUT);
3 /
PAK_INOI.SEL_CONTACTS(0,REFCUR OUT);
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00103: Encountered the symbol "." when expecting one of the following:

Any ideas would be helpful.
Re: Invalid number exception returned by Store Procedure (merged) [message #431038 is a reply to message #431037] Fri, 13 November 2009 13:57 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Where did I go wrong?
Posting Guidelines say -
"show us what you did (if you tried it yourself) and how Oracle responded (COPY & PASTE your SQL*Plus session),
including errors and/or why the result is not what you want.
Do not describe, explain or report - show us!"

  1  create or replace PROCEDURE "SEL_CONTACTS" (
  2  "CONID" IN NUMBER DEFAULT 0,
  3  "CONTACTSCUR" OUT SYS_REFCURSOR) IS
  4  BEGIN
  5  IF CONID = 0 THEN
  6  OPEN CONTACTSCUR FOR SELECT	CONTACTID, CONTACTLASTNAME + ', ' + CONTACTFIRSTNAME + ' ' + NVL(CONTACTMIDNAME, '') CONTACTNAME,
  7  ORGANIZATIONID,
  8  POSITIONID,
  9  ADDRESS,
 10  CITY,
 11  STATE,
 12  ZIPCODE
 13  FROM	ERFO.CONTACTS;
 14  ELSE
 15  OPEN CONTACTSCUR FOR SELECT	CONTACTID, CONTACTLASTNAME + ', ' + CONTACTFIRSTNAME + ' ' + NVL(CONTACTMIDNAME, '') CONTACTNAME
 16  FROM	ERFO.CONTACTS
 17  WHERE CONTACTID = CONID;
 18  END IF;
 19* END "SEL_CONTACTS";
SQL> /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE "SEL_CONTACTS":

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/22	 PL/SQL: SQL Statement ignored
13/11	 PL/SQL: ORA-00942: table or view does not exist
15/22	 PL/SQL: SQL Statement ignored
16/11	 PL/SQL: ORA-00942: table or view does not exist



Try CUT & PASTE the SELECT from line #6 into SQLPLUS to determine its validity!

[Updated on: Fri, 13 November 2009 13:59]

Report message to a moderator

Re: Invalid number exception returned by Store Procedure (merged) [message #431039 is a reply to message #431037] Fri, 13 November 2009 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Where did I go wrong?

Quote:
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, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always use SQL*Plus and copy and paste your session.

Regards
Michel

[Updated on: Fri, 13 November 2009 13:59]

Report message to a moderator

icon7.gif  Re: Invalid number exception returned by Store Procedure (merged) [message #431041 is a reply to message #431036] Fri, 13 November 2009 14:00 Go to previous messageGo to next message
joeller
Messages: 17
Registered: September 2007
Location: Indian Head, Maryland
Junior Member
Whoo HOOOO!!!!!! That did it. Now I can get back to my primary task of building the web site. That link you sent me did not work but I have a PL SQL book here. (although it made no mention about the difference in concatenation operator and emphesized how much like ANSI SQL it was.) So I will have to find some other PL SQL reference, and view the book I have as incomplete and false. It's true there ain't no such thing as a free lunch. I cut my commuting time to 1/3 of what it was but I have to pay for it by switching from SQL Server T-SQL and DB2's SQL to PL SQL and relearning everything all over again. I feel like I am back in kindergarten. Surprised
Re: Invalid number exception returned by Store Procedure (merged) [message #431042 is a reply to message #431025] Fri, 13 November 2009 14:12 Go to previous messageGo to next message
joeller
Messages: 17
Registered: September 2007
Location: Indian Head, Maryland
Junior Member
Since I created the package using the Oracle Data Access tools, I did not run it in sQL Plus so I had no SQL Plus code to give you except what was produced in Visual Studio which I provided. Since I could not get it to even start to run in SQL Plus see above post I gave you the out produced by Oracle Data Access Tools in the output pane of Visual Studio. I supposed I could have provided screen shots of the various Visual Studio Windows and their contents but it was my impression from the forum protocols that you didn't want any complex files like that. I tried my best to comply with them, but if that is not enough, I will respect your wishes and confine my and the rest of the company's inquires to Experts-Exchange and code guru.
Re: Invalid number exception returned by Store Procedure (merged) [message #431047 is a reply to message #431042] Fri, 13 November 2009 16:35 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If you intend to work with Oracle, get to know SQL*Plus.

Running problematic things in SQL*Plus is for Oracle troubleshooting the same as "Look at the fuel gauge" for car troubleshooting.

Sure, you can skip the look at the fuel gauge and start disassembling the engine first. But it won't make you happy in the long run.
Re: Invalid number exception returned by Store Procedure (merged) [message #431068 is a reply to message #431041] Sat, 14 November 2009 05:08 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
joeller wrote on Fri, 13 November 2009 21:00
I cut my commuting time to 1/3 of what it was but I have to pay for it by switching from SQL Server T-SQL and DB2's SQL to PL SQL and relearning everything all over again. I feel like I am back in kindergarten. Surprised

Make sure you also read up on the way Oracle's locking and transaction strategies differ (vastly) from SQL Server's.
Another big difference is that in Oracle you typically would not store intermediate query results in a temporary table (you would do the whole query in one go, instead of in steps). Temporary tables and other objects should NEVER be created at runtime in Oracle.

A lot to learn.
Welcome to the bright side Wink

[Edit: In addition, stop using double quotes for identifiers, object names, etc.]

[Updated on: Sat, 14 November 2009 05:14]

Report message to a moderator

Previous Topic: 'ytd'||To_char(sysdate,'yyyy') in alias
Next Topic: Triggers (merged)
Goto Forum:
  


Current Time: Mon Dec 05 02:51:04 CST 2016

Total time taken to generate the page: 0.08331 seconds