Stored Procedure Problem/Question

[shans]

I have the following coded for a stored procedure on an oracle 7.xx 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
-- this is the PROBLEM --
-- doesn't like using a variable in the from clause --
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

[RIVES]

What you are creating are dynamic sql statements. According to standards of SQL, you could only use tables in the FROM statement. What you should do is to create an SQL String and then execute it. I have forgotten the syntax but I'm sure you could check you oracle manual. Its Execute in MS/Sybase SQL server.

Goodluck.