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>