Use dbms_sql.bind_variable to bind variable : dbms_sql « System Packages « Oracle PL / SQL






Use dbms_sql.bind_variable to bind variable

 
SQL>
SQL> create table myTable( pid primary key )
  2    organization index
  3    as select rownum from all_objects
  4    where rownum <=0;

Table created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create or replace procedure binding is
  2    cursorValue number;
  3    
  4    returnValue number;
  5    returnValue1 number;
  6    begin
  7        cursorValue := dbms_sql.open_cursor;
  8        dbms_sql.parse(cursorValue, 'select pid from myTable '|| 'where pid = :b1', dbms_sql.native);
  9        for i in 1 ..0 loop
 10            dbms_sql.bind_variable(cursorValue,':b1',i);
 11            returnValue := dbms_sql.execute(cursorValue);
 12            returnValue1 := dbms_sql.fetch_rows(cursorValue);
 13        end loop;
 14         dbms_sql.close_cursor(cursorValue);
 15   end;
 16   /

Procedure created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> drop table myTable;

Table dropped.

SQL>
SQL>
SQL>

   
  








Related examples in the same category

1.First DBMS_SQL Example
2.Pass a query statement to a stored procedure
3.Use dbms_sql.open_cursor create a cursor
4.Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
5.Use dbms_sql.bind_array to bind array to a cursor
6.Use dbms_sql.parse to bind a select statement to a cursor
7.Call dbms_sql.describe_columns2 to get info for a column
8.Non-Query DML and DDL Statements
9.Use DBMS_SQL with the RETURNING clause.
10.DBMS_SQL.NATIVE;
11.DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
12.Dynamic sql statement with variable binding
13.Dynamic select statement and row fetch
14.Execute PL/SQL block
15.DBMS_SQL package: dynamic SQL
16.Pass where clause to a stored procedure
17.Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
18.Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
19.drop the supplied table using dynamic SQL.
20.DBMS_SQL.BIND_VARIABLE_CHAR
21.illustrate the importance of setting out_value_size.
22.Calling a function which uses dynamic SQL from within an SQL statement.
23.Illustrates the interaction of roles and dynamic SQL.
24.illustrates a DDL statement which is built dynamically from the procedure parameters.
25.Use dbms_sql to process query, cursor and value
26.dbms_sql.varchar2_table type variable