Home » SQL & PL/SQL » SQL & PL/SQL » problem in a procedure
problem in a procedure [message #223445] Thu, 08 March 2007 23:56 Go to next message
meeta
Messages: 28
Registered: January 2000
Junior Member
I have 1 procedure

CREATE OR REPLACE PROCEDURE Conversion_To_Russian IS

CURSOR convert_data IS SELECT 'alter table '||table_name||' modify '||column_name||' '||
data_type||'('||data_length||' CHAR);' COL
FROM user_tab_columns
WHERE data_type LIKE '%CHAR%' AND char_used='B' --AND owner NOT IN ('SYS','SYSTEM','OUTLN')
AND table_name NOT LIKE 'VW%' ORDER BY table_name ;

BEGIN

FOR A IN CONVERT_DATA
LOOP
EXECUTE IMMEDIATE 'A.COL';END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'NO TABLES TO BE CONVERTED INTO RUSSIAN');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002,SQLCODE||SQLERRM);

END Conversion_To_Russian ;

WHEN I AM EXECUTING This Procedure It is giving me invalid SQL statement in line EXECUTE IMMEDIATE 'A.COL' whereas if i execute the statements from result of cursor query individually , sql statements r running
Can anyone tell me why I am getting this error

/
Re: problem in a procedure [message #223453 is a reply to message #223445] Fri, 09 March 2007 00:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Get rid of the quotes

FOR A IN CONVERT_DATA
LOOP
  EXECUTE IMMEDIATE A.COL;
END LOOP;


Ross Leishman
Re: problem in a procedure [message #223475 is a reply to message #223445] Fri, 09 March 2007 01:39 Go to previous message
jkmurthy
Messages: 22
Registered: May 2005
Location: India
Junior Member

Hi Meeta,

Please do remove the quotes in the dynamic SQL statement and make it look like:


EXECUTE IMMEDIATE A.COL;


Also, remove the semicolon in the following line of the select statement in the cursor:

data_type||'('||data_length||' CHAR);' COL

and alter it to be like:

data_type||'('||data_length||' CHAR)' COL

Regards
Keshav
Previous Topic: How can we load a LOBS data usng Sql loader?
Next Topic: How to create a directory (merged)
Goto Forum:
  


Current Time: Sat Dec 10 09:24:45 CST 2016

Total time taken to generate the page: 0.09911 seconds