SQL & PL/SQL

Hi All,

I have one table which has a set of queries , I need to run these queries and put back the output(count) to the same table . For this I am using a cursor to process all the sql staments present in the table . But I am getting invalid sql while running those sql statements using 'exceute immediate ' in my procedure .

Procedure :
create or replace PROCEDURE P_Health_Check
AS
V_sql_stmt health_check.sql_stmt%type;

RESULT NUMBER;

CURSOR health_cur
IS
SELECT sql_stmt
FROM HEALTH_CHECK;
BEGIN
OPEN health_cur;
loop
FETCH health_cur INTO V_sql_stmt;
exit when health_cur%notfound;

EXECUTE IMMEDIATE V_sql_stmt into result;
EXECUTE IMMEDIATE 'UPDATE HEALTH_CHECK SET res=:1 WHERE sql_stmt=:2' USING result,v_sql_stmt;

end loop ;
CLOSE health_cur;
END P_Health_Check;


out put: ORA-00933: SQL command not properly ended
ORA-06512: at "CMX_ORS.P_HEALTH_CHECK", line 17
ORA-06512: at line 2

Then I update the proc to have the sql stamt in single quotesas below :
create or replace
PROCEDURE P_Health_Check
AS
V_sql_stmt health_check.sql_stmt%type;
v_sql_string varchar2(2000);
RESULT NUMBER;

CURSOR health_cur
IS
SELECT sql_stmt
FROM HEALTH_CHECK;
BEGIN
OPEN health_cur;
loop
FETCH health_cur INTO V_sql_stmt;
exit when health_cur%notfound;
v_sql_string:=chr(39)||v_sql_stmt||chr(39);
dbms_output.PUT_LINE(v_sql_string);
EXECUTE IMMEDIATE V_sql_string into result;
EXECUTE IMMEDIATE 'UPDATE HEALTH_CHECK SET res=:1 WHERE sql_stmt=:2' USING result,v_sql_stmt;

end loop ;
CLOSE health_cur;
END P_Health_Check;

out put :
ORA-00900: invalid SQL statement
ORA-06512: at "CMX_ORS.P_HEALTH_CHECK", line 18
ORA-06512: at line 2

Any help on this . Or is there any other way to achive this.

Thanks,
Mahesh

Edited by: Mahesh.pcu on Aug 4, 2010 5:51 AM