| Invalid number exception returned by Store Procedure (merged) [message #431025] |
Fri, 13 November 2009 12:48  |
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 (merged) [message #431036 is a reply to message #431025] |
Fri, 13 November 2009 13:24  |
flyboy
Messages: 1903
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 |
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 |
 |
BlackSwan
Messages: 26766
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 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
|
|
| Re: Invalid number exception returned by Store Procedure (merged) [message #431042 is a reply to message #431025] |
Fri, 13 November 2009 14:12 |
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 |
ThomasG
Messages: 3212
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 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
joeller wrote on Fri, 13 November 2009 21:00I 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. 
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 
[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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
