Skip to content

Commit 85b02a3

Browse files
jonheller1jonheller1
committed
Converted from parameter-driven to type-driven.
1 parent ee4c6db commit 85b02a3

11 files changed

Lines changed: 237 additions & 152 deletions

‎README.md‎

Lines changed: 17 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -5,28 +5,27 @@ Method4 is a PL/SQL application to run dynamic SQL in SQL.
55

66
## Example
77

8-
The simplest way to call Method4 is to pass in a simple literal query to evaluate:
8+
The simplest way to call Method4 is to pass in a literal query to evaluate:
99

10-
select * from table(method4.run(q'[select * from dual]'));
10+
select * from table(method4.query('select * from dual'));
1111
1212
D
1313
-
1414
X
1515

16-
At first that query seems pointless - why not just directly query `select * from dual`? Method4 allows custom code to control how the code is run and what is returned.
16+
At first that query seems pointless - why not just directly query `select * from dual`? Method4 provides two benefits - it silently converts LONGs to CLOBs and it allows custom code to control exactly what query is run and returned.
1717

18-
As a simple and useful example, Method4 includes a mode that runs queries generated by queries. This can solve challenging problems such as "count the rows for every table".
18+
Method4 includes a dynamic mode that runs queries generated by queries and concatenated with UNION ALLs. This can solve challenging problems such as "count the rows for every table", all within a single SQL statement.
1919

20-
To enable this re-evaluation mode, set the parameter `p_re_eval` to YES. In this mode, Method4 runs a query that returns a query string. Those queries are concatenated with UNION ALL and then run.
21-
22-
select * from table(method4.run(
23-
p_stmt =>
24-
q'[
25-
select 'select '''||table_name||''' table_name, count(*) a from '||table_name sql
26-
from user_tables
27-
where table_name like 'TEST%'
28-
]',
29-
p_re_eval => 'YES'
20+
select * from table(method4.dynamic_query(
21+
q'[
22+
select replace(
23+
q'!
24+
select '#TABLE_NAME#' table_name, count(*) a from #TABLE_NAME#
25+
!', '#TABLE_NAME#', table_name) sql_statement
26+
from user_tables
27+
where table_name like 'TEST%'
28+
]'
3029
));
3130
3231
TABLE_NAME A
@@ -37,6 +36,9 @@ To enable this re-evaluation mode, set the parameter `p_re_eval` to YES. In thi
3736
TEST3 1
3837
...
3938

39+
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).
40+
41+
4042
## Notes
4143

4244
Method4 is based on the Dictionary Long Application, (c) Adrian Billington www.oracle-developer.net. Much of this code contains advanced methods thoroughly discussed on his website, http://www.oracle-developer.net/display.php?id=422
@@ -53,7 +55,7 @@ Click the "Download ZIP" button, extract the files, CD to the directory with tho
5355

5456
@install
5557

56-
2. Unintall Method4:
58+
2. Uninstall Method4:
5759

5860
@uninstall
5961

‎install.sql‎

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -6,14 +6,18 @@ prompt (c) oracle-developer.net
66
prompt **************************************************************************
77
prompt
88

9-
prompt Installing method4_ot type specification...
9+
prompt Installing type specifications...
1010
@method4_ot.tps
11-
prompt Installing method4 package specification...
11+
@method4_dynamic_ot.tps
12+
prompt Installing package specification...
1213
@method4.spc
13-
prompt Installing method4_ot type body...
14+
prompt Installing type bodies...
1415
@method4_ot.tpb
16+
@method4_dynamic_ot.tpb
1517

1618
prompt
1719
prompt **************************************************************************
18-
prompt Installation complete.
20+
set serveroutput on feedback off
21+
exec dbms_output.put_line(' Method4 version '||method4.c_version||' installation complete.');
22+
set feedback on
1923
prompt **************************************************************************

‎method4.spc‎

Lines changed: 32 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -1,13 +1,15 @@
11
CREATE OR REPLACE PACKAGE method4 AS
22

3+
C_VERSION constant varchar2(10) := '2.0.0';
4+
35
/*
46
|| ---------------------------------------------------------------------------------
57
||
6-
|| Name: method4
8+
|| Name: Method4
79
||
810
|| Description: A PL/SQL application to run dynamic SQL in SQL. This package
9-
|| contains a single interface to a pipelined function implemented by
10-
|| an object type (DLA_OT) using ANYDATASET.
11+
|| contains interfaces to pipelind functions implemented by
12+
|| types (method4_ot, method4_dynamic_ot) using ANYDATASET.
1113
||
1214
|| Version: This version is for Oracle 10.2.0.x and upwards.
1315
||
@@ -16,8 +18,8 @@ CREATE OR REPLACE PACKAGE method4 AS
1618
|| to be a bug in the way ANYDATASET fetches CLOBs.
1719
||
1820
|| Notes: 1. The pipelined function returns a record structure that matches
19-
|| the column structure of the underlying DBA_% view (or query
20-
|| from that view). The only exception to this is of course the
21+
|| the column structure of the underlying query.
22+
|| The only exception to this is of course the
2123
|| LONG column, which is returned from each DBA_% view as a CLOB.
2224
||
2325
|| 2. The ANYDATASET interface has been available as a Data Cartridge
@@ -27,23 +29,33 @@ CREATE OR REPLACE PACKAGE method4 AS
2729
|| us to combine DBMS_SQL with ANYDATASET/ANYTYPE methods to build
2830
|| a self-describing return structure for the first time.
2931
||
32+
|| 3. The boring type-conversion logic is stored in the type
33+
|| method4_ot. To intercept and modify SQL statements,
34+
|| extend method4_ot. See method4_dynamic_ot for an example.
35+
||
3036
||
3137
|| Usage: a) Run a query.
3238
|| --------------------------------------------
33-
|| select * from table(method4.run('select * from dual'));
39+
|| select * from table(method4.query('select * from dual'));
3440
||
3541
|| b) Run a query generated by another query.
3642
|| ------------------------------------------------------
43+
|| These queries are powerful but they can also be confusing
44+
|| because of all the quotation marks required to build strings
45+
|| inside strings. Simplify your queries with the alternative
46+
|| quoting syntax (the "q" strings) and templating (use REPLACE
47+
|| instead of concatenating strings).
3748
||
38-
|| select * from table(method4.run(
39-
|| p_stmt =>
40-
|| q'[
41-
|| select 'select '''||table_name||''' table_name, count(*) a from '||table_name sql
42-
|| from user_tables
43-
|| where table_name like 'TEST%'
44-
|| ]',
45-
|| p_re_eval => 'YES'
46-
|| ));
49+
|| select * from table(method4.dynamic_query(
50+
|| q'[
51+
|| select replace(
52+
|| q'!
53+
|| select '#TABLE_NAME#' table_name, count(*) a from #TABLE_NAME#
54+
|| !', '#TABLE_NAME#', table_name) sql_statement
55+
|| from user_tables
56+
|| where table_name like 'TEST%'
57+
|| ]'
58+
|| ));
4759
||
4860
|| ------------------------------------------------------
4961
|| (c) Adrian Billington, www.oracle-developer.net.
@@ -54,10 +66,12 @@ CREATE OR REPLACE PACKAGE method4 AS
5466
/*
5567
|| Pipelined function interface.
5668
*/
57-
FUNCTION run(
58-
p_stmt IN VARCHAR2,
59-
p_re_eval IN VARCHAR2 DEFAULT 'NO'
69+
FUNCTION query(
70+
p_stmt IN VARCHAR2
6071
) RETURN ANYDATASET PIPELINED USING method4_ot;
72+
FUNCTION dynamic_query(
73+
p_stmt IN NVARCHAR2
74+
) RETURN ANYDATASET PIPELINED USING method4_dynamic_ot;
6175

6276
/*
6377
|| Record types for use across multiple DLA_OT methods.

‎method4_dynamic_ot.tpb‎

Lines changed: 86 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,86 @@
1+
CREATE OR REPLACE TYPE BODY method4_dynamic_ot AS
2+
--See Method4 package specification for details.
3+
4+
----------------------------------------------------------------------------
5+
--Purpose: Create new SQL statement by concatenating result of original
6+
-- statement with UNION ALLs.
7+
--
8+
--If you want to modify Method4, this is probably the spot to add your code.
9+
--
10+
--re_eval: "YES" to re-evaluate SQL statement to generate a new statement.
11+
-- "NO" to use the original string as-is.
12+
static function re_evaluate_statement(
13+
stmt in varchar2
14+
) return varchar2 is
15+
v_new_stmt clob;
16+
--pre-defind table of varchar2(4000).
17+
sql_statements sys.ku$_vcnt;
18+
begin
19+
--Use cached statement if available.
20+
if method4.r_statement_cache.exists(stmt) then
21+
v_new_stmt := method4.r_statement_cache(stmt);
22+
--Else retrieve the statement.
23+
else
24+
--Get all the statements.
25+
execute immediate stmt
26+
bulk collect into sql_statements;
27+
28+
--Throw error if it returned no rows.
29+
if sql_statements.count = 0 then
30+
raise_application_error(-20000, 'The SQL statement did not generate any other SQL statements.');
31+
end if;
32+
33+
--Convert them into a single large union-all statement.
34+
for i in 1 .. sql_statements.count loop
35+
if i = 1 then
36+
v_new_stmt := sql_statements(i);
37+
else
38+
v_new_stmt := v_new_stmt || chr(10) || 'union all' || chr(10) || sql_statements(i);
39+
end if;
40+
end loop;
41+
42+
--Save it in the cache.
43+
method4.r_statement_cache(stmt) := v_new_stmt;
44+
end if;
45+
46+
return v_new_stmt;
47+
end re_evaluate_statement;
48+
49+
50+
----------------------------------------------------------------------------
51+
STATIC FUNCTION ODCITableDescribe(
52+
rtype OUT ANYTYPE,
53+
stmt IN VARCHAR2
54+
) RETURN NUMBER IS
55+
BEGIN
56+
RETURN method4_ot.ODCITableDescribe(rtype, re_evaluate_statement(stmt));
57+
END;
58+
59+
----------------------------------------------------------------------------
60+
STATIC FUNCTION ODCITablePrepare(
61+
sctx OUT method4_dynamic_ot,
62+
tf_info IN sys.ODCITabFuncInfo,
63+
stmt IN VARCHAR2
64+
) RETURN NUMBER IS
65+
66+
super_sctx method4_ot;
67+
status number;
68+
69+
BEGIN
70+
super_sctx := sctx;
71+
status := method4_ot.ODCITablePrepare(super_sctx, tf_info, re_evaluate_statement(stmt));
72+
sctx := method4_dynamic_ot(super_sctx.atype);
73+
return odciconst.success;
74+
END;
75+
76+
----------------------------------------------------------------------------
77+
STATIC FUNCTION ODCITableStart(
78+
sctx IN OUT method4_dynamic_ot,
79+
stmt IN VARCHAR2
80+
) RETURN NUMBER IS
81+
BEGIN
82+
RETURN method4_ot.ODCITableStart(sctx, re_evaluate_statement(stmt));
83+
END;
84+
85+
END;
86+
/

‎method4_dynamic_ot.tps‎

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
CREATE OR REPLACE TYPE method4_dynamic_ot UNDER method4_ot
2+
--See Method4 package specification for details.
3+
(
4+
STATIC FUNCTION Re_Evaluate_Statement(
5+
stmt IN VARCHAR2
6+
) RETURN VARCHAR2
7+
8+
, STATIC FUNCTION ODCITableDescribe(
9+
rtype OUT ANYTYPE,
10+
stmt IN VARCHAR2
11+
) RETURN NUMBER
12+
13+
, STATIC FUNCTION ODCITablePrepare(
14+
sctx OUT method4_dynamic_ot,
15+
tf_info IN sys.ODCITabFuncInfo,
16+
stmt IN VARCHAR2
17+
) RETURN NUMBER
18+
19+
, STATIC FUNCTION ODCITableStart(
20+
sctx IN OUT method4_dynamic_ot,
21+
stmt IN VARCHAR2
22+
) RETURN NUMBER
23+
24+
) NOT FINAL INSTANTIABLE;
25+
/

‎method4_ot.tpb‎

Lines changed: 6 additions & 64 deletions
Original file line numberDiff line numberDiff line change
@@ -1,66 +1,10 @@
11
CREATE OR REPLACE TYPE BODY method4_ot AS
2-
3-
----------------------------------------------------------------------------
4-
--Purpose: Create new SQL statement by concatenating result of original
5-
-- statement with UNION ALLs.
6-
--
7-
--If you want to modify Method4, this is probably the spot to add your code.
8-
--
9-
--re_eval: "YES" to re-evaluate SQL statement to generate a new statement.
10-
-- "NO" to use the original string as-is.
11-
static function re_evaluate_statement(
12-
stmt in varchar2,
13-
re_eval in varchar2
14-
) return varchar2 is
15-
v_new_stmt clob;
16-
--pre-defind table of varchar2(4000).
17-
sql_statements sys.ku$_vcnt;
18-
begin
19-
--Re-evaluate the sql as a group of select statements if the flag is set.
20-
if trim(upper(re_eval)) = 'YES' then
21-
--Use cached statement if available.
22-
if method4.r_statement_cache.exists(stmt) then
23-
v_new_stmt := method4.r_statement_cache(stmt);
24-
--Else retrieve the statement.
25-
else
26-
--Get all the statements.
27-
execute immediate stmt
28-
bulk collect into sql_statements;
29-
30-
--Throw error if it returned no rows.
31-
if sql_statements.count = 0 then
32-
raise_application_error(-20000, 'The SQL statement did not generate any other SQL statements.');
33-
end if;
34-
35-
--Convert them into a single large union-all statement.
36-
for i in 1 .. sql_statements.count loop
37-
if i = 1 then
38-
v_new_stmt := sql_statements(i);
39-
else
40-
v_new_stmt := v_new_stmt || chr(10) || 'union all' || chr(10) || sql_statements(i);
41-
end if;
42-
end loop;
43-
44-
--Save it in the cache.
45-
method4.r_statement_cache(stmt) := v_new_stmt;
46-
end if;
47-
--Do nothing to string if no re-evaluation.
48-
elsif trim(upper(re_eval)) = 'NO' then
49-
v_new_stmt := stmt;
50-
--Else throw error that string is unexpected.
51-
else
52-
raise_application_error(-20000, 'The parameter RE_EVAL must be either YES or NO.');
53-
end if;
54-
55-
return v_new_stmt;
56-
end re_evaluate_statement;
57-
2+
--See Method4 package specification for details.
583

594
----------------------------------------------------------------------------
605
STATIC FUNCTION ODCITableDescribe(
616
rtype OUT ANYTYPE,
62-
stmt IN VARCHAR2,
63-
re_eval IN VARCHAR2 DEFAULT 'NO'
7+
stmt IN VARCHAR2
648
) RETURN NUMBER IS
659

6610
r_sql method4.rt_dynamic_sql;
@@ -72,7 +16,7 @@ CREATE OR REPLACE TYPE BODY method4_ot AS
7216
|| Parse the SQL and describe its format and structure.
7317
*/
7418
r_sql.cursor := DBMS_SQL.OPEN_CURSOR;
75-
DBMS_SQL.PARSE( r_sql.cursor, RE_EVALUATE_STATEMENT(stmt, re_eval), DBMS_SQL.NATIVE );
19+
DBMS_SQL.PARSE( r_sql.cursor, stmt, DBMS_SQL.NATIVE );
7620
DBMS_SQL.DESCRIBE_COLUMNS2( r_sql.cursor, r_sql.column_cnt, r_sql.description );
7721
DBMS_SQL.CLOSE_CURSOR( r_sql.cursor );
7822

@@ -170,8 +114,7 @@ CREATE OR REPLACE TYPE BODY method4_ot AS
170114
STATIC FUNCTION ODCITablePrepare(
171115
sctx OUT method4_ot,
172116
tf_info IN sys.ODCITabFuncInfo,
173-
stmt IN VARCHAR2,
174-
re_eval IN VARCHAR2 DEFAULT 'NO'
117+
stmt IN VARCHAR2
175118
) RETURN NUMBER IS
176119

177120
r_meta method4.rt_anytype_metadata;
@@ -200,8 +143,7 @@ CREATE OR REPLACE TYPE BODY method4_ot AS
200143
----------------------------------------------------------------------------
201144
STATIC FUNCTION ODCITableStart(
202145
sctx IN OUT method4_ot,
203-
stmt IN VARCHAR2,
204-
re_eval IN VARCHAR2 DEFAULT 'NO'
146+
stmt IN VARCHAR2
205147
) RETURN NUMBER IS
206148

207149
r_meta method4.rt_anytype_metadata;
@@ -213,7 +155,7 @@ CREATE OR REPLACE TYPE BODY method4_ot AS
213155
|| ANYTYPE structure to define and execute the SQL statement...
214156
*/
215157
method4.r_sql.cursor := DBMS_SQL.OPEN_CURSOR;
216-
DBMS_SQL.PARSE( method4.r_sql.cursor, RE_EVALUATE_STATEMENT(stmt, re_eval), DBMS_SQL.NATIVE );
158+
DBMS_SQL.PARSE( method4.r_sql.cursor, stmt, DBMS_SQL.NATIVE );
217159
DBMS_SQL.DESCRIBE_COLUMNS2( method4.r_sql.cursor,
218160
method4.r_sql.column_cnt,
219161
method4.r_sql.description );

0 commit comments

Comments
 (0)