Home » SQL & PL/SQL » SQL & PL/SQL » USING DYNAMIC SQL within PL/SQL
USING DYNAMIC SQL within PL/SQL [message #206580] Thu, 30 November 2006 10:55 Go to next message
jinpark75
Messages: 11
Registered: October 2005
Junior Member
I'm having issues with using dynamic sql to return the max value on a column. The first sql statement does not work. It includes the INTO keyword. However, when i remove the INTO keyword in the second sql statement, it works fine.

DECLARE
stmt_str VARCHAR2(200);
d_MAX_DT DATE;
TABLE_NAME VARCHAR2(20):=&1;
BEGIN
stmt_str := 'SELECT MAX(SYS_REC_CHG_DTM) INTO d_MAX_DT ' || 'FROM ' || table_name;

--stmt_str := 'SELECT MAX(SYS_REC_CHG_DTM) FROM ' || table_name;


EXECUTE IMMEDIATE stmt_str;

END;
/
Re: USING DYNAMIC SQL within PL/SQL [message #206601 is a reply to message #206580] Thu, 30 November 2006 13:54 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
It's Dynamic SQL not Dynamic PL/SQL.

Into is a PL/SQL clause
Re: USING DYNAMIC SQL within PL/SQL [message #206644 is a reply to message #206580] Thu, 30 November 2006 23:40 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You should use
execute immediate('select bla from '||l_table) into l_variable;
Previous Topic: My SQL script (transaction) desnt work
Next Topic: count() function doubt
Goto Forum:
  


Current Time: Sat Dec 03 06:04:58 CST 2016

Total time taken to generate the page: 0.25156 seconds