Ejemplos de PL/SQL dbms_sql bind_variable

The bind variables supplied to a dynamic query performing any DML operation needs to use the BIND_VARIABLE procedure from the DBMS_SQL package. When this dynamic query is executed, the bind values supplied through this procedure will be used in the placeholders.

The prototype for defining the BIND_VARIABLE procedure is shown below,

BIND_VARIABLE(<Cursor_ID>, <Placeholder_name>, <Variable | Value>);

In the below example, a table is created using the PARSE procedure and a row is inserted into this table with a bind variable using the BIND_VARIABLE procedure which is then executed using the EXECUTE function in the DBMS_SQL package.

 

1.  SET SERVEROUTPUT ON 200000;

2.  DECLARE

3.  l_i_cursor_id INTEGER;

4.  l_n_rowcount  NUMBER;

5.  BEGIN

6.  l_i_cursor_id:=dbms_sql.open_cursor;

7.  dbms_sql.parse(l_i_cursor_id,'Create table bind_test1(col varchar2(30))',dbms_sql.native);

8.  dbms_sql.parse(l_i_cursor_id,'insert into bind_test1 values (:col)',dbms_sql.native);

9.  dbms_sql.bind_variable(l_i_cursor_id,':col','Oracle');

10. l_n_rowcount:=dbms_sql.execute(l_i_cursor_id);

11. dbms_output.put_line('The row count of the last executed DML statement is '||l_n_rowcount);

12. Dbms_sql.close_cursor(l_i_cursor_id);

13. END;

14. /

 

Result:

The row count of the last executed DML statement is 1 

.....

seguir viendo el la web original:

URL: http://www.dba-oracle.com/t_plsql_dbms_sql_bind_variable.htm

Fuentes.

Artículo:   "PL/SQL dbms_sql bind_variable tips" Publicado en http://www.dba-oracle.com/ por Burleson Consulting. Consultado el 10/10/2022.

URL: http://www.dba-oracle.com/t_plsql_dbms_sql_bind_variable.htm