cursor not fetching long column [message #427620] |
Fri, 23 October 2009 10:18  |
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   |
ThomasG
Messages: 3212 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 #427709 is a reply to message #427620] |
Sat, 24 October 2009 03:28   |
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 #428712 is a reply to message #427716] |
Thu, 29 October 2009 10:35   |
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 #428818 is a reply to message #428719] |
Fri, 30 October 2009 03:04  |
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
|
|
|