Home » SQL & PL/SQL » SQL & PL/SQL » ora-02063
ora-02063 [message #135938] Mon, 05 September 2005 00:33 Go to next message
rishi_mahajan
Messages: 9
Registered: August 2005
Junior Member
i am exceuting a procedure which has a cursor in it... the cursor query is fetching data from a table in different db using DBlink
-----------------------------------------------------------
ORA-01722: invalid number ORA-02063: preceding line from RPS_RPSP_LINK
Re: ora-02063 [message #135959 is a reply to message #135938] Mon, 05 September 2005 02:41 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
What exactly are you doing with your DBLink ?

Rgds.
Re: ora-02063 [message #135963 is a reply to message #135938] Mon, 05 September 2005 02:45 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
This error indicates that Oracle is expecting a number, but gets something else. You need to debug your procedure and fix the problem.

Best regards.

Frank
Re: ora-02063 [message #135966 is a reply to message #135959] Mon, 05 September 2005 03:04 Go to previous messageGo to next message
rishi_mahajan
Messages: 9
Registered: August 2005
Junior Member
I am just selecting records from a table in a different database

eg...
this is how i am using select

select f_year, f_month, f_comp, f_area_code , comp
f_target pax, f_demand rev_lc, 0 rev_bc, 0 rev1_bc
from fin_year_data@fin_psp_link
where f_year = 4
and f_target > 0
and f_demand < 1000 and comp='Y'

the above select statement i am passing in a variable and using a ref cursor i am looping through this
when i reach at the fetch statment i am getting an error...
Re: ora-02063 [message #135975 is a reply to message #135966] Mon, 05 September 2005 03:42 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
What are the types of columns

f_year
f_target
f_demand

?

Is any of them varchar2 or char ?

Rgds.
Re: ora-02063 [message #135984 is a reply to message #135963] Mon, 05 September 2005 04:19 Go to previous messageGo to next message
rishi_mahajan
Messages: 9
Registered: August 2005
Junior Member
i ran through debug mode in PL/SQL developer... thats where i found the error ( in the fetch statement )... i am having the same table in the db from where i am executing the procedure.. if i use that table i.e. the same table without the link it is working fine..

plz help.......
Re: ora-02063 [message #135989 is a reply to message #135975] Mon, 05 September 2005 04:32 Go to previous messageGo to next message
rishi_mahajan
Messages: 9
Registered: August 2005
Junior Member
f_year is a varchar2... but in the fetch i am assigning it to a varchar2 variable only...
Re: ora-02063 [message #136012 is a reply to message #135989] Mon, 05 September 2005 06:49 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Then check if f_year contains only numeric data or something
else.

Rgds.
Re: ora-02063 [message #136028 is a reply to message #136012] Mon, 05 September 2005 08:21 Go to previous messageGo to next message
rishi_mahajan
Messages: 9
Registered: August 2005
Junior Member
The f_year field has data in numerical format even though it is a varchar2 field... for some other reasons..
Re: ora-02063 [message #136033 is a reply to message #136028] Mon, 05 September 2005 08:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What if you change the

where f_year = 4

to

where f_year = '4'

Does it work then ?

Re: ora-02063 [message #136253 is a reply to message #135938] Wed, 07 September 2005 01:49 Go to previous messageGo to next message
rishi_mahajan
Messages: 9
Registered: August 2005
Junior Member
Dear friends,
the problem is solved... i created a synonym for the table alongwith the dblink

eg. for this fin_year_data@fin_psp_link ... i created synonym fin_yr_data_link and used it ... it is working successfully.


Re: ora-02063 [message #274770 is a reply to message #136253] Wed, 17 October 2007 01:55 Go to previous messageGo to next message
sachin_more
Messages: 2
Registered: October 2007
Location: Bangalore
Junior Member
Hi Friends,

I tried creating synonym for the remote db tables but still it give me the same ORA-02063 and ORA-01722 error. Please help me solving this error.
Thanks very much.
Re: ora-02063 [message #274774 is a reply to message #274770] Wed, 17 October 2007 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste what you did from start to end.
Without forgetting to mention Oracle version (both sides with 4 decimals) and format your post:
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: ora-02063 [message #274778 is a reply to message #274774] Wed, 17 October 2007 02:17 Go to previous messageGo to next message
sachin_more
Messages: 2
Registered: October 2007
Location: Bangalore
Junior Member
INSERT INTO TEST
(col1,
col2,
col3,
Col4,
col5,
col6,
Col7)
SELECT 'ABC' As col1,
'TEST' AS col2,
C.col3,
C.col4,
A.id,
A.Name,
MAX(A.GENERATION)
FROM LIST_ATTR_VALUE A,
LIST_ATTRIBUTE B,
PRODUCT_LIST C
WHERE A.id = C.id
AND B.id = C.id
AND B.ATTR_ID = A.ATTR_ID
AND C.id = C.id
AND C.CNTRY = 'ABC'
AND C.CATEGORY = 'ND'
AND A.NAME = 'CATEGORIE' AND A.INFO_ID IS NOT NULL
GROUP BY C.col3, C.col4, A.id, A.NAME;
I am trying insert rows in to my local table by fetching data from remote tables A,B C there synonyms created for these tables.
Hope this information is sufficient. Please send your inputs.
regards,
Sachin
Re: ora-02063 [message #274780 is a reply to message #274778] Wed, 17 October 2007 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not what I asked and this is not formatted.
If you want help follow 1) the guidelines 2) what we post.

Regards
Michel
Re: ora-02063 [message #274860 is a reply to message #274778] Wed, 17 October 2007 07:48 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And DESCribe all your tables while you are at it. 1722 may be happening as a side effect of the DB link, or maybe just because you are relying on implicit conversion (bad bad bad).
Previous Topic: how can open sqlnavigator with vb 6
Next Topic: problem with set escape /
Goto Forum:
  


Current Time: Sun Dec 04 13:00:26 CST 2016

Total time taken to generate the page: 0.08701 seconds