 |
| Total Answers and Comments: 5 |
Last Update: November 14, 2007 Asked by: Vinoth |
|
| | |
|
No best answer available. Please pick the good answer available or submit your answer. | |
March 01, 2006 12:57:14 | #1 |
| Navin |
|
|
RE: How can we execute dynamic SQL From PL/SQL block?
| You can use EXECUTE IMMEDIATE to execute a dynamically constructed SQL in a PL/SQL program. -- HTH Navin.
| | | Is this answer useful? Yes | No | | |
|
| |
|
March 18, 2006 11:09:54 | #2 |
| Cnu |
|
|
RE: How can we execute dynamic SQL From PL/SQL block?
| If ur dynamic sql stament returns morethan one record then you have to use Ref Cursors to execute the sql statments. Regards. Cnu.
| | | Is this answer useful? Yes | No | | |
|
| |
|
November 14, 2007 07:14:03 | #5 |
| rajakumar_na |
|
Member Since: November 2007 Contribution: 26 |
RE: How can we execute dynamic SQL From PL/SQL block?
| here is the example for DBMS_SQL:
CREATE OR REPLACE PROCEDURE delete_all_rows(p_tab_name IN VARCHAR2 p_rows_del OUT NUMBER)IScursor_name INTEGER;BEGINcursor_name : DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(cursor_name ’DELETE FROM ’||p_tab_name DBMS_SQL.NATIVE );p_rows_del : DBMS_SQL.EXECUTE (cursor_name);DBMS_SQL.CLOSE_CURSOR(cursor_name);END;/VARIABLE deleted NUMBEREXECUTE delete_all_rows(’employees’ :deleted)PRINT deleted
AND ANOTHER EXAMPLE FOR EXECUTE IMMEDIATE:
CREATE PROCEDURE del_rows (p_table_name IN VARCHAR2 p_rows_deld OUT NUMBER) IS BEGIN EXECUTE IMMEDIATE ’delete from ’||p_table_name; p_rows_deld : SQL ROWCOUNT; END; /
VARIABLE deleted NUMBER EXECUTE del_rows(’test_employees’ :deleted) PRINT deleted
| | | Is this answer useful? Yes | No | | |
|
| |
Go To Top
|
