Home » SQL & PL/SQL » SQL & PL/SQL » cursor not fetching long column (forms6i, windowsXP prof.)
cursor not fetching long column [message #427620] Fri, 23 October 2009 10:18 Go to next message
sreejithmenon
Messages: 15
Registered: October 2008
Junior Member
Hi,
I want to bring a view from one schema to another schema . For that i have a PLSQL script with an explicit cursor performing a select on ALL_VIEWS. The script is getting an error on the cursor fetch because it's trying to access the TEXT column from ALL_VIEWS, which is a LONG datatype.
error is ORA-06502: PL/SQL: numeric or value error.

how can i access a long column in a cursor.. or any other way to create the view which is existing in other schema..?

I'm in trouble.. please help me..

Thanks in advance
sreejith
Re: cursor not fetching long column [message #427621 is a reply to message #427620] Fri, 23 October 2009 10:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
LONGS have been de-supported long ago, and *basically* the only way to access them is in a client program, not with PL/SQL.

For what you want to do, I would suggest the DBMS_METADATA package, for example:

SELECT DBMS_METADATA.get_ddl ('VIEW', 'VIEW_NAME', 'OWNER') 
  FROM dual;
Re: cursor not fetching long column [message #427624 is a reply to message #427621] Fri, 23 October 2009 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ThomasG wrote on Fri, 23 October 2009 17:29
For what you want to do, I would suggest the DBMS_METADATA package,

Of course if your version that you did not post support it.

Please read OraFAQ Forum Guide.

Regards
Michel

Re: cursor not fetching long column [message #427709 is a reply to message #427620] Sat, 24 October 2009 03:28 Go to previous messageGo to next message
sreejithmenon
Messages: 15
Registered: October 2008
Junior Member
hi sirs,
thanks for the reply.
But when i use the following query
'select dbms_metadata.get_ddl('VIEW', 'ITEM_BASKET', 'USER!1')' from dual',
i get the error ora-24813, cannot send or receive unsupported lob.
my versions are database-10g and forms6i

please give me a solution

thanking you
sreejith
Re: cursor not fetching long column [message #427710 is a reply to message #427709] Sat, 24 October 2009 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you read the forum guide as asked, you'd see you have to post your version WITH 4 DECIMALS.
You'd also see you have to COPY AND PASTE YOUR SESSION.

ORA-24813: cannot send or receive an unsupported LOB
 *Cause:  An attempt was made to send a LOB across the network, but either
          the server does not support the LOB sent by the client, or the
          client does not support the LOB sent by the server. This error
          usually occurs when the client and server are running different
          versions of Oracle.
 *Action: Use a version of the Oracle that supports the LOB on both
          the client and the server.

Upgrade your client.

Regards
Michel
Re: cursor not fetching long column [message #427716 is a reply to message #427710] Sat, 24 October 2009 04:51 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And what you are trying to do has nothing to do with forms, so don't try to do it in forms. (if that is where you got the error)
Re: cursor not fetching long column [message #428712 is a reply to message #427716] Thu, 29 October 2009 10:35 Go to previous messageGo to next message
sreejithmenon
Messages: 15
Registered: October 2008
Junior Member
hi all
thanking you for the informations..
I'm facing a problem when taking the create script of views by using dbms_metadata.get_ddl, as the script comes in small letters.
if my view in schema1 has a code like
'IF EMP_ID LIKE'%A'....', when i take it through metadata, it comes as 'if emp_id like '%a'....'.
and it become meaningless if i create the view with this script in schema2...

any solution for this...

thanks again
sreejith

Re: cursor not fetching long column [message #428714 is a reply to message #428712] Thu, 29 October 2009 10:42 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>'IF EMP_ID LIKE'%A'....', when i take it through metadata, it comes as 'if emp_id like '%a'....'.

using sqlplus along with CUT & PASTE please provide actual example of above.
Re: cursor not fetching long column [message #428719 is a reply to message #428712] Thu, 29 October 2009 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if my view in schema1 has a code like
'IF EMP_ID LIKE'%A'....', when i take it through metadata, it comes as 'if emp_id like '%a'....'.

1/ A view cannot contain something like that
2/ Prove your claim and copy and paste your session and a test case we can reproduce.

Regards
Michel
Re: cursor not fetching long column [message #428818 is a reply to message #428719] Fri, 30 October 2009 03:04 Go to previous message
sreejithmenon
Messages: 15
Registered: October 2008
Junior Member
[quote][if my view in schema1 has a code like
'IF EMP_ID LIKE'%A'....', when i take it through metadata, it comes as 'if emp_id like '%a'....'.]

sirs,
first of all a heartfelt sorry..
it was my mistake, i had put the command 'LOWER' in the code to check some cases. and that is why all the script came in lower case..

thanks for your kind support...
sreejith
Previous Topic: MERGE statment in oracle pl/sql
Next Topic: debugging resource busy error ORA-00054
Goto Forum:
  


Current Time: Sat Dec 03 16:05:37 CST 2016

Total time taken to generate the page: 0.07320 seconds