How can we execute dynamic SQL From PL/SQL block?

Showing Answers 1 - 9 of 9 Answers

Navin

  • Mar 1st, 2006
 

You can use EXECUTE IMMEDIATE to execute a dynamically constructed SQL in a PL/SQL program.

--

HTH,

Navin.

  Was this answer useful?  Yes

Cnu

  • Mar 18th, 2006
 

If ur dynamic sql stament returns morethan one record then you have to use Ref Cursors to execute the sql statments.

Regards.,

Cnu.,

  Was this answer useful?  Yes

sudheer

  • Oct 6th, 2006
 

u can use EXECUTE IMMEDIATE command after using this command u wrote ur stmt within single quotes.

eg:- EXECUTE IMMEDIATE 'sql statement'

  Was this answer useful?  Yes

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



  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions