So when I wrote "Oracle does not provide any built-in functionality to return this information [about placeholders]", Bryn went exploring and reported the following:
It seems that there is no way to find out what placeholders there are in a SQL statement without first executing it — to do which you must have bound properly to all the placeholders.
The script below shows what you can do.
List_Placeholders.sql
CONNECT Sys/pwd AS SYSDBA
/*
V_$SQL_BIND_CAPTURE
V_$SQL_BIND_DATA
V_$SQL_BIND_METADATA
select View_Name from DBA_Views
where Owner = 'SYS'
and View_Name like 'V_$%BIND%'
*/
declare
Table_Doesnt_Exist exception;
pragma Exception_Init(Table_Doesnt_Exist, -00942);
begin
begin
execute immediate 'drop table Things';
exception when Table_Doesnt_Exist then null; end;
execute immediate 'create table Things(n number, v varchar2(10))';
end;
/
begin
for j in 1..10 loop
insert into Things(n, v) Values(j, Lpad(j, 10, '.'));
end loop;
commit;
end;
/
alter system flush shared_pool
/
declare
Stmt varchar2(32767) :=
'select v from Things where n = :bind_1 or v = :bind_2';
Cur number := DBMS_Sql.Open_Cursor();
Dummy number;
Answer Things.v%type;
Cursor v$Sql_Bind_Metadata_Cur is
select
Position,
Datatype,
Bind_Name
from v$Sql_Bind_Metadata M, v$Sql S
where M.Address = S.Child_Address
and S.Sql_Text = Stmt
order by Position;
type Bind_Metadata_Records_t is table of v$Sql_Bind_Metadata_Cur%rowtype;
Bind_Metadata_Records Bind_Metadata_Records_t;
Cursor v$Sql_Bind_Data_Cur is
select Position, Datatype, Value
from v$Sql_Bind_Data
where Cursor_Num = Cur
order by Position;
type Bind_Data_Records_t is table of v$Sql_Bind_Data_Cur%rowtype;
Bind_Data_Records Bind_Data_Records_t;
begin
DBMS_Sql.Parse(Cur, Stmt, DBMS_Sql.Native);
DBMS_Sql.Define_Column(Cur, 1, Answer, 10);
DBMS_Sql.Bind_Variable(Cur, ':bind_1', 3);
--DBMS_Sql.Bind_Variable(Cur, ':bind_2', '.........7');
dummy := DBMS_Sql.Execute(Cur);
open v$Sql_Bind_Metadata_Cur;
fetch v$Sql_Bind_Metadata_Cur bulk collect into Bind_Metadata_Records;
close v$Sql_Bind_Metadata_Cur;
open v$Sql_Bind_Data_Cur;
fetch v$Sql_Bind_Data_Cur bulk collect into Bind_Data_Records;
close v$Sql_Bind_Data_Cur;
if Bind_Metadata_Records.Count() <> Bind_Data_Records.Count() then
raise Program_Error;
end if;
for j in 1..Bind_Metadata_Records.Count() loop
if Bind_Metadata_Records(j).Position <> Bind_Data_Records(j).Position then
raise Program_Error;
end if;
if Bind_Metadata_Records(j).Datatype <> Bind_Data_Records(j).Datatype then
raise Program_Error;
end if;
DBMS_Output.Put_Line('');
DBMS_Output.Put_Line('Position: '||Bind_Metadata_Records(j).Position);
DBMS_Output.Put_Line('Bind_Name: '||Bind_Metadata_Records(j).Bind_Name);
DBMS_Output.Put_Line('Datatype: '||Bind_Metadata_Records(j).Datatype);
DBMS_Output.Put_Line('Value: '||Bind_Data_Records(j).Value);
end loop;
DBMS_Output.Put_Line(Chr(10)||'Sanity check -- the results of the query:');
while DBMS_Sql.Fetch_Rows (Cur) > 0
loop
DBMS_Sql.Column_Value(Cur, 1, Answer);
DBMS_Output.Put_Line('Answer: '||Answer);
end loop;
DBMS_Sql.Close_Cursor(Cur);
end;
/
There's some useful information listed in v$Sql_Bind_Metadata. This needs to be joined to v$Sql thus:
select ... from v$Sql_Bind_Metadata M, v$Sql S
where M.Address = S.Child_Address
and S.Sql_Text = Stmt
As far as I can see, this has to be done while the cursor that parse, bound, and executed the SQL is still open. If you arranged to have two or more child cursors open for the same statement text (using, for example, different bind-variable datatypes) then the select above would need to be more elaborate.
There's other information available in v$Sql_Bind_Data. The rows you want have Cursor_Num equal to the value you got back from DBMS_Sql.Open_Cursor().
The List_Placeholders.sql script demonstrates this. You should realize that the datatype is — in general — a property of the bind variable rather than of the placeholder, as this demonstrates:
declare
Stmt constant varchar2(32767) := 'select 1 from dual where :x = :y';
Answer number;
n1 number := 1; n2 number := 1;
v1 varchar2(10) := '1'; v2 varchar2(10) := '1';
begin
execute immediate Stmt into Answer using in n1, in n2;
DBMS_Output.Put_Line(Answer);
execute immediate Stmt into Answer using in v1, in v2;
DBMS_Output.Put_Line(Answer);
execute immediate Stmt into Answer using in n1, in v2;
DBMS_Output.Put_Line(Answer);
end;
That's why the v$ views I mention require you to have done the binding (and executed the cursor).
I'm afraid that the datatype of the bind is given as a number. I couldn't find where we document the translation to type name. (You can see the mapping — as a big decode — in DBA_Views.Text for DBA_View_Name = 'DBA_TAB_COLS'.)
If your questioner had a table where he listed (in a detail table) the name and datatype for each SQL statement he intended to run, then the code in my List_Placeholders.sql would allow that data to be QA'd.
It seems that there is no way to discover the names of the placeholders unless you first bind to them — which I'll admit is a little circular. The information is there under the covers (as it must be to allow "ORA-01008: not all variables bound" to be caused when appropriate).
Dynamic Placeholder Package
CREATE OR REPLACE PACKAGE dyn_placeholder
/*
Dynamic PL/SQL Placeholder Analyzer Package
Author: Steven Feuerstein, steven@stevenfeuerstein.com
Created: December 20, 2005
Overview: Helps you analyze strings for placeholders, which is defined as the
colon character ":" followed by anything but =.
Most programs have a parameter named dyn_plsql_in. If your string is part of a dynamic PL/SQL block, you should pass TRUE for this argument. If you pass NULL and the string ends with a semi-colon, then I will assume it is a dynamic PL/SQL block. Why bother with this? In a dynamic SQL block, you need to provide a bind variable for each placeholder, even if there are multiple with the same name. In a dynamic PL/SQL block, you only provide a value for the first occurrence of each placeholder (by name).
Note: these programs do not take into account : characters that occur within a comment or within a literal.
*/
IS
/* Return two pieces of information about each placeholder:
1. The name (without the colon) upper-cased.
2. The location in the string (starting with the colon)
*/
TYPE placeholder_rt IS RECORD (
NAME VARCHAR2 ( 100 )
, POSITION PLS_INTEGER
);
-- Associative array type to hold information about all placeholders.
TYPE placeholder_aat IS TABLE OF placeholder_rt
INDEX BY PLS_INTEGER;
-- Return the number of placeholders found in the string.
FUNCTION count_in_string (
string_in IN VARCHAR2
, dyn_plsql_in IN BOOLEAN DEFAULT FALSE
)
RETURN PLS_INTEGER;
-- Return the nth placeholder in the string.
FUNCTION nth_in_string (
string_in IN VARCHAR2
, nth_in IN PLS_INTEGER
, dyn_plsql_in IN BOOLEAN DEFAULT FALSE
)
RETURN placeholder_rt;
-- Return all the placeholders in an associative array.
FUNCTION all_in_string (
string_in IN VARCHAR2
, dyn_plsql_in IN BOOLEAN DEFAULT FALSE
)
RETURN placeholder_aat;
-- Display all the placeholders.
PROCEDURE show_placeholders (
list_in IN placeholder_aat
, dyn_plsql_in IN BOOLEAN DEFAULT FALSE
);
PROCEDURE show_placeholders (
string_in IN VARCHAR2
, dyn_plsql_in IN BOOLEAN DEFAULT FALSE
);
FUNCTION eq (
rec1_in IN placeholder_rt
, rec2_in IN placeholder_rt
, nullq_eq_in IN BOOLEAN DEFAULT TRUE
)
RETURN BOOLEAN;
END dyn_placeholder;
/
CREATE OR REPLACE PACKAGE BODY dyn_placeholder
IS
FUNCTION only_identifier_from (
string_in IN VARCHAR2
, position_in IN PLS_INTEGER
)
RETURN VARCHAR2
IS
c_delimiters CONSTANT VARCHAR2 ( 100 )
:= '!@%^&*()-=+\|`~{[]};:''",<.>/? '
|| CHR ( 10 )
|| CHR ( 13 )
|| CHR ( 9 );
l_end PLS_INTEGER;
BEGIN
l_end :=
INSTR ( TRANSLATE ( string_in
, c_delimiters
, RPAD ( CHR ( 2 )
, LENGTH ( c_delimiters )
, CHR ( 2 )
)
)
, CHR ( 2 )
, position_in
);
IF l_end = 0
THEN
RETURN SUBSTR ( string_in, position_in );
ELSE
RETURN SUBSTR ( string_in, position_in, l_end - position_in );
END IF;
END only_identifier_from;
FUNCTION all_in_string (
string_in IN VARCHAR2
, dyn_plsql_in IN BOOLEAN DEFAULT FALSE
)
RETURN placeholder_aat
IS
c_is_dynplsql CONSTANT BOOLEAN
:= NVL ( dyn_plsql_in, SUBSTR ( string_in, -1 ) =
';' );
l_start PLS_INTEGER := 1;
l_loc PLS_INTEGER;
l_placeholders placeholder_aat;
PROCEDURE add_placeholder ( loc_in IN PLS_INTEGER )
IS
l_row PLS_INTEGER := l_placeholders.FIRST;
c_last CONSTANT PLS_INTEGER := l_placeholders.LAST;
l_name VARCHAR2 ( 32767 );
l_already_used BOOLEAN := FALSE;
BEGIN
l_name := UPPER ( only_identifier_from ( string_in, loc_in + 1 ));
IF c_is_dynplsql
THEN
WHILE ( NOT l_already_used AND l_row <= c_last )
LOOP
l_already_used := l_name = l_placeholders ( l_row ).NAME;
l_row := l_row + 1;
END LOOP;
END IF;
IF NOT l_already_used
THEN
l_row := l_placeholders.COUNT + 1;
l_placeholders ( l_row ).NAME := l_name;
l_placeholders ( l_row ).POSITION := loc_in;
END IF;
END add_placeholder;
BEGIN
LOOP
l_loc := INSTR ( string_in, ':', l_start );
EXIT WHEN l_loc = 0 OR l_loc IS NULL;
IF SUBSTR ( string_in, l_loc + 1, 1 ) != '='
THEN
add_placeholder ( l_loc );
END IF;
l_start := l_loc + 1;
END LOOP;
RETURN l_placeholders;
END all_in_string;
FUNCTION count_in_string (
string_in IN VARCHAR2
, dyn_plsql_in IN BOOLEAN DEFAULT FALSE
)
RETURN PLS_INTEGER
IS
l_placeholders placeholder_aat;
BEGIN
l_placeholders := all_in_string ( string_in , dyn_plsql_in);
RETURN l_placeholders.COUNT;
END count_in_string;
FUNCTION nth_in_string (
string_in IN VARCHAR2
, nth_in IN PLS_INTEGER
, dyn_plsql_in IN BOOLEAN DEFAULT FALSE
)
RETURN placeholder_rt
IS
l_placeholders placeholder_aat;
BEGIN
l_placeholders := all_in_string ( string_in );
IF nth_in > l_placeholders.COUNT
THEN
RETURN NULL;
ELSE
RETURN l_placeholders ( nth_in );
END IF;
END nth_in_string;
PROCEDURE show_placeholders (
list_in IN placeholder_aat
, dyn_plsql_in IN BOOLEAN DEFAULT FALSE
)
IS
l_index PLS_INTEGER := list_in.FIRST;
BEGIN
WHILE ( l_index IS NOT NULL )
LOOP
DBMS_OUTPUT.put_line ( list_in ( l_index ).NAME
|| ' - '
|| list_in ( l_index ).POSITION
);
l_index := list_in.NEXT ( l_index );
END LOOP;
END show_placeholders;
PROCEDURE show_placeholders (
string_in IN VARCHAR2
, dyn_plsql_in IN BOOLEAN DEFAULT FALSE
)
IS
BEGIN
show_placeholders ( all_in_string ( string_in, dyn_plsql_in ));
END show_placeholders;
FUNCTION eq (
rec1_in IN placeholder_rt
, rec2_in IN placeholder_rt
, nullq_eq_in IN BOOLEAN DEFAULT TRUE
)
RETURN BOOLEAN
IS
BEGIN
RETURN ( ( rec1_in.NAME = rec2_in.NAME
OR ( rec1_in.NAME IS NULL AND rec2_in.NAME IS NULL )
)
AND ( rec1_in.POSITION = rec2_in.POSITION
OR ( rec1_in.POSITION IS NULL
AND rec2_in.POSITION IS NULL
)
)
);
END eq;
END dyn_placeholder;
/
No comments:
Post a Comment