Hi Tom,
I would like to store the result of a sub-query into a single record of a table...
I found the CURSOR keyword to "collapse" the records of the sub-query :
select sql_text, sql_fulltext, sql_id, hash_value, child_address ,
cursor(select name, position, datatype, datatype_string, max_length, last_captured, value_string
from v$sql_bind_capture b where a.hash_value=b.hash_value and a.child_address=b.child_address
) bind_values
from v$sql a
In Toad, the column "bind_values" displays as "(CURSOR)". If a double-clic, it displays the 7 detail columns
But how could I store the result of this query into a table ?
"CREATE TABLE AS..." does not work because "CURSOR is not authorized"...
Is it possible to "convert" the result of "CURSOR" into a table-compatible datatype ?
Should I better use an other method (not using "CURSOR") ?
Of course, I want to be able to decode the stored field to extract the values later (for example with a "select a, b, table(...) from xxx" instruction)
I know that v$sql already store the bind values into column "BIND_DATA" of type "RAW(2000)"
I can decode this field using
select a.sql_id, a.sql_fulltext, b.position, b.datatype_string, b.value_string, b.name
from v$sql a, table(dbms_sqltune.extract_binds(a.bind_data)) b
...but unfortunately the "NAME" of bind values is not populated... Is it a bug ?
Thanks for your help
Regards,
Christophe
Cursor () returns a result set. It is equivalent ref cursors in PL/SQL. You can't store these in a table - that doesn't make sense.
You can store subqueries in a single column in a number of other ways, e.g. XML, JSON or nested tables.
Here's how to do it using nested tables:
create table par (
id integer not null primary key
);
create table chld (
par_id integer not null
references par (id),
seq integer not null,
primary key (par_id, seq)
);
insert into par
select rownum from dual connect by level <= 3;
insert into chld
with rws as (
select rownum r from dual connect by level <= 3
)
select id, r from par, rws
where id <= r;
create or replace type tp as object (
par_id integer,
seq integer
);
/
create or replace type tp_arr as table of tp;
/You can then create a table with the nested table type, populating it with cast(collect()). Once these are in place, you can extract the values with a table() operator:
create table temp (
id, children
) nested table children store as chl_tab
as
select id,
(select cast (collect (tp(par_id, seq)) as tp_arr) from chld where par_id = id) children
from par;
select c.*
from temp, table(temp.children) c;
PAR_ID SEQ
---------- ----------
1 1
1 2
1 3
2 2
2 3
3 3That said, unless you have a particular reason to use nested tables I would prefer to store the results in two separate tables. One for the query without the subquery, the other to store the results of the subquery.
Note that dbms_sqltune.extract_binds is undocumented, so take care using it. That said, if you inspect the package spec you can see:
-- NOTE:
-- name of the bind in SQL_BIND object is not populated by this function
For more info on collect, see:
http://www.oracle-developer.net/display.php?id=514