-
Notifications
You must be signed in to change notification settings - Fork 14
Expand file tree
/
Copy path11g_to_refcursor.sql
More file actions
85 lines (73 loc) · 2.57 KB
/
Copy path11g_to_refcursor.sql
File metadata and controls
85 lines (73 loc) · 2.57 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
DECLARE
TYPE strings_t IS TABLE OF VARCHAR2 (200);
l_cv sys_refcursor;
l_placeholders strings_t := strings_t ('dept_id');
l_values strings_t := strings_t ('20');
l_names strings_t;
FUNCTION employee_names (
where_in IN VARCHAR2
, bind_variables_in IN strings_t
, placeholders_in IN strings_t
)
RETURN sys_refcursor
IS
l_dyn_cursor NUMBER;
l_cv sys_refcursor;
l_dummy PLS_INTEGER;
BEGIN
/* Parse the retrieval of last names after appending the WHERE clause.
NOTE: if you ever write code like this yourself, you MUST take steps
to minimize the risk of SQL injecction. This topic is also covered in
this chapter. READ IT!
*/
l_dyn_cursor := DBMS_SQL.open_cursor;
DBMS_SQL.parse (l_dyn_cursor
, 'SELECT last_name FROM employees WHERE ' || where_in
, DBMS_SQL.native
);
/*
Bind each of the variables to the named placeholders;
You cannot use EXECUTE IMMEDIATE for this step if you have
a variable number of placeholders!
*/
FOR indx IN 1 .. placeholders_in.COUNT
LOOP
DBMS_SQL.bind_variable (l_dyn_cursor
, placeholders_in (indx)
, bind_variables_in (indx)
);
END LOOP;
/*
Execute the query now that all variables are bound.
*/
l_dummy := DBMS_SQL.EXECUTE (l_dyn_cursor);
/*
Now it's time to convert to a cursor variable so that the front end
program or another PL/SQL program can easily fetch the values.
*/
l_cv := DBMS_SQL.to_refcursor (l_dyn_cursor);
/*
Do not close with DBMS_SQL; you can ONLY manipulate the cursor
through the cursor variable at this point.
DBMS_SQL.close_cursor (l_dyn_cursor);
*/
RETURN l_cv;
END employee_names;
BEGIN
l_cv :=
employee_names ('DEPARTMENT_ID = :dept_id', l_values, l_placeholders);
FETCH l_cv
BULK COLLECT INTO l_names;
FOR indx IN 1 .. l_names.COUNT
LOOP
DBMS_OUTPUT.put_line (l_names(indx));
END LOOP;
CLOSE l_cv;
END;
/
/*======================================================================
| Supplement to the fifth edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2009 O'Reilly Media, Inc.
| To submit corrections or find more code samples visit
| http://oreilly.com/catalog/9780596514464/
*/