Wrap dbms_sql package : DBMS_SQL « System Packages « Oracle PL/SQL Tutorial






SQL>
SQL> create or replace package demo_pkg
  2  as
  3      procedure parse_bind_execute_close( p_input in varchar2 );
  4      procedure bind_execute( p_input in varchar2 );
  5  end;
  6  /

Package created.

SQL>
SQL>
SQL> create or replace package body demo_pkg
  2  as
  3  g_first_time boolean := TRUE;
  4  g_cursor     number;
  5
  6  procedure parse_bind_execute_close( p_input in varchar2 )
  7  as
  8      l_cursor   number;
  9      strValue   varchar2(4000);
 10      returnValue   number;
 11  begin
 12      l_cursor := dbms_sql.open_cursor;
 13      dbms_sql.parse( l_cursor,'select * from dual where dummy = :x',dbms_sql.native );
 14
 15      dbms_sql.bind_variable( l_cursor, ':x', p_input );
 16
 17      dbms_sql.define_column( l_cursor, 1, strValue, 4000 );
 18
 19      returnValue := dbms_sql.execute( l_cursor );
 20
 21      if ( dbms_sql.fetch_rows( l_cursor ) <= 0 )
 22
 23      then
 24
 25          strValue := null;
 26
 27      else
 28
 29          dbms_sql.column_value( l_cursor, 1, strValue );
 30
 31      end if;
 32
 33      dbms_sql.close_cursor( l_cursor );
 34
 35  end parse_bind_execute_close;
 36
 37  procedure bind_execute( p_input in varchar2 )
 38  as
 39      strValue   varchar2(4000);
 40      returnValue   number;
 41  begin
 42      if ( g_first_Time )
 43      then
 44
 45          g_cursor := dbms_sql.open_cursor;
 46
 47          dbms_sql.parse( g_cursor,'select * from dual where dummy = :x',dbms_sql.native );
 48
 49          dbms_sql.define_column( g_cursor, 1, strValue, 4000 );
 50
 51          g_first_time := FALSE;
 52
 53      end if;
 54
 55      dbms_sql.bind_variable( g_cursor, ':x', p_input );
 56
 57      returnValue := dbms_sql.execute( g_cursor );
 58
 59      if ( dbms_sql.fetch_rows( g_cursor ) <= 0 )
 60
 61      then
 62
 63          strValue := null;
 64
 65      else
 66
 67          dbms_sql.column_value( g_cursor, 1, strValue );
 68
 69      end if;
 70  end bind_execute;
 71
 72  end;
 73  /

Package body created.

SQL>








31.26.DBMS_SQL
31.26.1.dbms_sql.number_table
31.26.2.Close a cursor
31.26.3.Assign date with DBMS_SQL package
31.26.4.Create Pl/SQL block dynamically and then execute it by calling 'DBMS_SQL.EXECUTE'
31.26.5.DBMS_SQL.PARSE
31.26.6.An example of using DBMS_SQL.OPEN_CURSOR
31.26.7.Package for running dynamic sql based on DBMS_SQL package
31.26.8.Wrap dbms_sql package
31.26.9.Dump Column long with DBMS_SQL.DEFINE_COLUMN_LONG
31.26.10.DBMS_SQL.BIND_VARIABLE and DBMS_SQL.EXECUTE
31.26.11.DBMS_SQL.EXECUTE an update statement
31.26.12.Use DBMS_SQL to update a table and get the number of rows updated
31.26.13.DBMS_SQL.VARCHAR2_TABLE and DBMS_SQL.NUMBER_TABLE
31.26.14.Use dynamic SQL to check the business logic
31.26.15.Use DBMS_SQL package to parse math expression
31.26.16.Use a character string of arithmetic logic, selecting against the DUAL table to return a number value.
31.26.17.DBMS_SQL.LAST_ERROR_POSITION
31.26.18.Dump query with dbms_sql
31.26.19.Use dbms_sql.describe_columns
31.26.20.Print table with dynamic query