stored procedure question

[shans]

I have the following coded for a stored on an oracle server:


CREATE OR REPLACE PROCEDURE NIS.CUR_TEST(p_job_no IN NUMBER,
p_cli_name IN VARCHAR2,
p_error_msg OUT VARCHAR2,
p_ret_val OUT VARCHAR2)
IS
/*********GETS TABLE NAMES/COLUMN NAMES FROM DATABASE******/
CURSOR l_cur IS
SELECT table_name, column_name
FROM cols
WHERE table_name LIKE 'SF_%'
AND column_name = 'JOB_NO';
BEGIN
FOR l_rec IN l_cur LOOP
DECLARE
/*** GETS MAX JOB NUMBER IN EACH TABLE ****/
CURSOR l_cur_table IS
SELECT MAX(job_no) l_job_no
FROM l_rec.table_name;
BEGIN
FOR l_rec_table IN l_cur_table LOOP
/** delete all but the 1000 highest job nos
DELETE
FROM l_rec.table_name
WHERE job_no < l_rec_table.l_job_no - 1000;
COMMIT;
END LOOP;
END;
END LOOP;
END CUR_TEST;
/



The problem is that when I get the table and column names in the first cursor, the next query shown below, the one in the second cursor (l_cur_table) errors:


SELECT MAX(job_no) l_job_no
FROM l_rec.table_name

The error says something about l_rec.table_name not being accessible but really the problem is that I'm trying to use a variable (l_rec.table_name) in the FROM clause. Even if I store l_rec.table_name in a local variable before I get to this query and use that variable in the FROM clause it still errors because it can't interpret a variable value in the FROM clause. Any ideas.

Thanks so much.

Sue

[jfg]

Sue

I had the same problem with an SQL Server sp and solved it with a Execute() function. I had to assign the whole sql & table name to a string and then
Execute(string) which worked and could still reference each field. I don't have SQL Server anymore (at uni) and can't remember the exact syntax.

You could try searching oracles help with keywords like
dynamically executing sql (stored procedure) or oracle's native language psql (i think)

John