Execute immediate should work fine for you if you like it. In this case the bind variable is not necessary believe it or not. I personally prefer to use ref cursors for select statements because i find them easier to work with than execute immediate. You can use it to issue sql statements that cannot be represented directly in plsql, or to build up statements where you do not know all the table names, where clauses, and so on in advance. Performing sql operations with native dynamic sql oracle. There is an unknown number of input or output variables, such as the number of column values returned by a query, that are used in a dynamic sql statement method 4 for dynamic sql. How to execute dynamic query using cursors in sqlserver how to execute dynamic query using cursors in sqlserver answered rss 1 reply. The days of taking a where clause in and just concatenating it to a string and trying to execute it they died with client server computing.
It has an in parameter which is the table nam, the cursor will select data based on that parameter. Types of cursors available in oracle plsql dzone database. Examples of native dynamic sql method 3 oracle live sql. I have a stored procedure which used the execute immediate command to execute a very long string. Script name examples of native dynamic sql method 3 description method 3 dynamic sql. Execute immediate with bulk collect club oracle forums. How to execute dynamic query using cursors in sqlserver. Oracle execute immediate into a cursor stack overflow. With this query the value that is hard coded is also immutable, itll never change. My into can then deposit the set return by cursor directly into a cursor variable defined in my plsql block. Developers and dbas get help from oracle experts on.
Script name using dynamic sql for multirow queries description this script contains the code for my oracle magazine article of the same name. So far in this tutorial we learnt what the execute immediate statement is and how to use the same for executing a sql query dynamically using into clause in oracle database. Cursor with execute immediate 730428 jun 23, 2011 9. Native dynamic sql processes most dynamic sql statements by means of the execute immediate statement if the dynamic sql statement is a select statement that returns multiple rows, native dynamic sql gives you the following choices use the execute immediate statement with the bulk collect into clause use the openfor, fetch, and close statements. Matthew arnoldthis chapter shows you how to use native dynamic sql dynamic sql for short, a plsql interface that makes your applications more flexible and versatile. How to use execute immediate with into clause in oracle. Hi, i am trying to create a procedure that will display logs. Using execute immediate for a query in piplined function producing error.
1276 1062 82 1502 1150 469 549 1177 1491 939 683 952 134 1046 46 576 1572 1308 1150 472 967 898 1561 285 412 1252 616 1501 312 1474 946 348 325 19 63 1099 470 666 475 916