Method4 is a PL/SQL application to run dynamic SQL in SQL.
DYNAMIC_QUERY Run queries generated by queries. With a single SQL statement, you can solve challenging problems such as counting the number of rows of every table.
select * from table(method4.dynamic_query(
q'[
select replace(
q'!
select '#TABLE_NAME#' table_name, count(*) row_count from #TABLE_NAME#
!', '#TABLE_NAME#', table_name) sql_statement
from user_tables
where table_name like 'TEST%'
]'
));
TABLE_NAME ROW_COUNT
------------------------- ---------
TEST 19765
TEST1 1
TEST2 1
TEST3 1
...
PIVOT Dynamically transpose rows into columns. The last column defines the values and the second-to-last column defines the column names. The default aggregation function is MAX, which works well for common entity-attribute-value queries like the one below:
select * from table(method4.pivot(
q'[
select 1 user_id, 'Date of Birth', '2010-05-27' from dual union all
select 1 user_id, 'Name', 'Elliott' from dual union all
select 2 user_id, 'Name', 'Oliver' from dual union all
select 2 user_id, 'Gender', 'Male' from dual
]'
));
USER_ID Date of Birth Gender Name
------- ------------- ------ -------
1 2010-05-27 Elliott
2 Male Oliver
Use the second parameter to change the aggregation function. For example, the below query counts and compares the number of objects per object type, per user:
select * from table(method4.pivot(
q'[
select owner, object_type, object_name
from all_objects
where owner like 'SYS%'
]',
'count'))
order by owner;
OWNER CLUSTER CONSUMER GROUP DESTINATION DIRECTORY EDITION ...
------ ------- -------------- ----------- --------- ------- ...
SYS 10 18 2 13 1 ...
SYSTEM 0 0 0 0 0 ...
The dynamically generated column names are normally listed alphabetically, but add a column named PIVOT_COLUMN_ID if you want to control the order. The following example forces the columns to be listed in the order C, B, A, instead of the default A, B, C:
select *
from table(method4.pivot(
q'[
select 3 pivot_column_id, 'A' name, 1 value from dual union all
select 2 pivot_column_id, 'B' name, 1 value from dual union all
select 1 pivot_column_id, 'C' name, 1 value from dual
]'
));
C B A
- - -
1 1 1
QUERY This function simply returns the results of a string literal:
select * from table(method4.query('select * from dual'));
D
-
X
POLL_TABLE 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 is the table to be queried, 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 pseudo-column ORA_ROWSCN can be used.
P_SQL_STATEMENT is a SQL statement that should return the value 1 when the queries should stop.
P_REFRESH_SECONDS is the number of seconds to wait before querying the table again. The default is 3 seconds.
This function may leave temporary objects in your schema if a query was cancelled or failed before the cleanup could run. Objects like 'M4_TEMP_%' can be dropped after the query is done.
Notes for all functions
All functions convert LONGs to CLOBs, which can be helpful when querying the data dictionary. The simple QUERY function is a good way to understand how Data Cartridge and the ANY types work together. Compare the types method4_ot with method4_dynamic_ot to see how the query string can be intercepted and converted to do something interesting.
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).
Click the "Download ZIP" button, extract the files, CD to the directory with those files, connect to SQL*Plus, and run these commands:
-
Install Method4:
@install -
Uninstall Method4:
@uninstall -
Install unit tests (optional, only useful for development):
@tests/install_unit_tests -
Uninstall unit tests (optional, only useful for development):
@tests/uninstall_unit_tests
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
Method4 is a simpler, more generic version of that application. It can be useful for ad hoc queries in highly dynamic environments. For example, an application where the schemas, tables, and columns are table-driven and only known at run time.
Use this program with caution. Few database programs need to be this dynamic - you're usually better off creating this type of dynamic code in your application layer. This package will be slower and buggier than regular SQL.
This project uses the MIT License.