OraFAQ Forum: SQL & PL/SQL » ora-02063

Home » SQL & PL/SQL » SQL & PL/SQL » ora-02063

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ora-02063 [message #135938]

Mon, 05 September 2005 00:33

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

Report message to a moderator

Re: ora-02063 [message #135959 is a reply to message #135938]

Mon, 05 September 2005 02:41

dmitry.nikiforov
Messages: 723
Registered: March 2005

Senior Member

What exactly are you doing with your DBLink ?

Rgds.

Report message to a moderator

Re: ora-02063 [message #135963 is a reply to message #135938]

Mon, 05 September 2005 02:45

Frank Naude
Messages: 4596
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

Report message to a moderator

Re: ora-02063 [message #135966 is a reply to message #135959]

Mon, 05 September 2005 03:04

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...

Report message to a moderator

Re: ora-02063 [message #135975 is a reply to message #135966]

Mon, 05 September 2005 03:42

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.

Report message to a moderator

Re: ora-02063 [message #135984 is a reply to message #135963]

Mon, 05 September 2005 04:19

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.......

Report message to a moderator

Re: ora-02063 [message #135989 is a reply to message #135975]

Mon, 05 September 2005 04:32

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...

Report message to a moderator

Re: ora-02063 [message #136012 is a reply to message #135989]

Mon, 05 September 2005 06:49

dmitry.nikiforov
Messages: 723
Registered: March 2005

Senior Member

Then check if f_year contains only numeric data or something
else.

Rgds.

Report message to a moderator

Re: ora-02063 [message #136028 is a reply to message #136012]

Mon, 05 September 2005 08:21

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..

Report message to a moderator

Re: ora-02063 [message #136033 is a reply to message #136028]

Mon, 05 September 2005 08:34

ThomasG
Messages: 3212
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 ?

Report message to a moderator

Re: ora-02063 [message #136253 is a reply to message #135938]

Wed, 07 September 2005 01:49

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.

Report message to a moderator

Re: ora-02063 [message #274770 is a reply to message #136253]

Wed, 17 October 2007 01:55

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.

Report message to a moderator

Re: ora-02063 [message #274774 is a reply to message #274770]

Wed, 17 October 2007 02:03

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: ora-02063 [message #274778 is a reply to message #274774]

Wed, 17 October 2007 02:17

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

Report message to a moderator

Re: ora-02063 [message #274780 is a reply to message #274778]

Wed, 17 October 2007 02:28

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: ora-02063 [message #274860 is a reply to message #274778]

Wed, 17 October 2007 07:48

joy_division
Messages: 4963
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).

Report message to a moderator

Previous Topic:	how can open sqlnavigator with vb 6
Next Topic:	problem with set escape /

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 07 19:10:45 CDT 2025