-
Notifications
You must be signed in to change notification settings - Fork 6
Expand file tree
/
Copy pathmethod4.spc
More file actions
162 lines (153 loc) · 6.86 KB
/
method4.spc
File metadata and controls
162 lines (153 loc) · 6.86 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
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
CREATE OR REPLACE PACKAGE method4 AS
C_VERSION constant varchar2(10) := '2.1.5';
/*
|| ---------------------------------------------------------------------------------
||
|| Name: Method4
||
|| Description: A PL/SQL application to run dynamic SQL in SQL. This package
|| contains interfaces to pipelind functions implemented by
|| types using ANYDATASET.
||
|| Version: This version is for Oracle 10.2.0.x and upwards.
||
|| Semantically and syntactically, this application should run on
|| 10.1.x databases, but there is an ORA-0600 error which appears
|| to be a bug in the way ANYDATASET fetches CLOBs.
||
|| Notes: 1. The pipelined function returns a record structure that matches
|| the column structure of the underlying query.
|| The only exception to this is of course the
|| LONG column, which is returned from each DBA_% view as a CLOB.
||
|| 2. The ANYDATASET interface has been available as a Data Cartridge
|| since Oracle 9i. However, the ODCI methods needed to enable dynamic
|| describe of a SQL statement were not available until 10g (that is,
|| we could only interface to a known query structure). 10g enables
|| us to combine DBMS_SQL with ANYDATASET/ANYTYPE methods to build
|| a self-describing return structure for the first time.
||
|| 3. The boring type-conversion logic is stored in the type
|| method4_ot. To intercept and modify SQL statements,
|| extend method4_ot. See method4_dynamic_ot for an example.
||
||
|| Usage: a) QUERY: Run a query.
|| --------------------------------------------
|| select * from table(method4.query('select * from dual'));
||
|| b) DYNAMIC_QUERY: Run a query generated by another query.
|| ------------------------------------------------------
|| These queries are powerful but they can also be confusing
|| because of all the quotation marks required to build strings
|| inside strings. Simplify your queries with the alternative
|| quoting syntax (the "q" strings) and templating (use REPLACE
|| instead of concatenating strings).
||
|| select * from table(method4.dynamic_query(
|| q'[
|| select replace(
|| q'!
|| select '#TABLE_NAME#' table_name, count(*) a from #TABLE_NAME#
|| !', '#TABLE_NAME#', table_name) sql_statement
|| from user_tables
|| where table_name like 'TEST%'
|| ]'
|| ));
||
|| c) POLL_TABLE: Poll a table and return new rows until a condition is met.
|| ------------------------------------------------------
|| This query will periodically poll a table and return new rows
|| until a condition is met. This can be useful for querying tables
|| populated by an asynchronous process.
||
|| create table table1(a number) rowdependencies;
|| insert into table1 values(1);
|| commit;
||
|| select * from table(method4.poll_table(
|| p_table_name => 'table1',
|| p_sql_statement_condition => 'select 1 from dual',
|| p_refresh_seconds => 2
|| ));
||
|| Results:
|| A
|| ----------
|| 1
||
|| P_TABLE_NAME - The table to be queries, which may include the
|| schema qualifier. This table must be created with
|| ROWDEPENDENCIES to track each row's System Change Number (SCN)
|| so that the psuedo-column ORA_ROWSCN can be used.
|| P_SQL_STATEMENT - A SQL statement that returns a 1 when the
|| queries should stop.
|| P_REFRESH_SECONDS - The number of seconds to wait before querying
|| the table again. Defaults to 3 secodns.
||
|| This method may leave some temporary objects in the schmea if a
|| query was cancelled or failed before the cleanup could run.
|| Objects like 'M4_TEMP_%' can be dropped after the query is done.
||
||
|| License: MIT License
|| Original work Copyright (c) 2007 Adrian Billington, www.oracle-developer.net
|| Modified work Copyright 2016 Jon Heller
||
||
|| ---------------------------------------------------------------------------------
*/
/*
|| Pipelined function interface.
*/
FUNCTION query(
p_stmt IN VARCHAR2
) RETURN ANYDATASET PIPELINED USING method4_ot;
FUNCTION dynamic_query(
p_stmt IN VARCHAR2
) RETURN ANYDATASET PIPELINED USING method4_dynamic_ot;
FUNCTION poll_table(
p_table_name IN VARCHAR2,
p_sql_statement_condition IN VARCHAR2,
p_refresh_seconds IN NUMBER DEFAULT 3
) RETURN ANYDATASET PIPELINED USING method4_poll_table_ot;
/*
|| Record types for use across multiple METHOD4 methods.
*/
TYPE rt_dynamic_sql IS RECORD
( cursor INTEGER
, column_cnt PLS_INTEGER
, description DBMS_SQL.DESC_TAB2
, execute INTEGER
);
TYPE rt_anytype_metadata IS RECORD
( precision PLS_INTEGER
, scale PLS_INTEGER
, length PLS_INTEGER
, csid PLS_INTEGER
, csfrm PLS_INTEGER
, schema VARCHAR2(128)
, type ANYTYPE
--This must be 129, not 128.
--For weird column names ANYTYPE.GetAttrElemInfo returns 129 bytes instead of 128.
--(It did something similar in previous versions with the 30 byte limit. In past
-- versions this value had to be 31 instead of 30.)
, name VARCHAR2(129)
, version VARCHAR2(30)
, attr_cnt PLS_INTEGER
, attr_type ANYTYPE
, attr_name VARCHAR2(129)
, typecode PLS_INTEGER
);
/*
|| State variable for use across multiple METHOD4 methods.
*/
r_sql rt_dynamic_sql;
TYPE statement_cache_type IS TABLE OF CLOB INDEX BY VARCHAR2(4000);
r_statement_cache statement_cache_type;
--Contexts used by METHOD4_POLL_TABLE.
procedure set_temp_object_id(p_temp_object_id varchar2);
procedure set_owner(p_owner varchar2);
procedure set_table_name(p_table_name varchar2);
END method4;
/