| Dynamic SQL [message #38292] |
Mon, 08 April 2002 08:45  |
Gloria
Messages: 4
Registered: October 2001
|
Junior Member |
|
|
Hi there,
I used dynamic sql in my plsql function. A table name is passed as parameter. Then I build a del_stmt for deleting records from the table. I used execute immdediate to run the del_stmt. However, I got invalid column name error. Could someone please tell me about the error. The following is a brief example about my function:
del_str:='DELETE FROM '||del_tbl||' dt'||
' WHERE dt.f1 = purge_rec.f1||
' AND dt.f2= purge_rec.f2'||
' AND dt.f3= purge_rec.f3;
FOR purge_rec IN purge_list LOOP
EXECUTE IMMEDIATE del_str;
total_row := total_row + 1;
dbms_output.put_line(total_row);
END LOOP;
Thanks in advance.
Gloria
|
|
|
|
|
|
|
|
| Re: Dynamic SQL [message #38300 is a reply to message #38292] |
Tue, 09 April 2002 00:12  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
I would imagine that the problem is caused by the scope of the execute immediate statement.
Specifically, the purge_rec row is not when the statement is executed, and so it is unable to recognise the columns you specify.
To fix this you need to move the definition of del_str to inside the cursor loop and replace "dt.f1 = purge_rec.f1" with "dt.f1 = '||purge_rec.f1" if the field is a number, or with "dt.f1 = '''||purge_rec.f1||'''' if it is a string.
|
|
|
|
| Re: Dynamic SQL [message #38305 is a reply to message #38292] |
Tue, 09 April 2002 02:43 |
Raj Mathur
Messages: 11
Registered: April 2002
|
Junior Member |
|
|
PROCEDURE PROC_TEST_DY (TName VARCHAR2) AS
SQL_STMT VARCHAR2(2000);
BEGIN
SQL_STMT :='INSERT INTO TAB_CLOB(ID,NAME)
SELECT COUNT(*), '||''''||TNAME||''''||'
FROM '||TNAME;
EXECUTE IMMEDIATE SQL_STMT;
COMMIT;
END ;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
