I have a polymorphic table function that select only the given columns.
CREATE OR REPLACE PACKAGE pkg_select_col
AS
FUNCTION select_col (tab TABLE, col SYS.odcivarchar2list)
RETURN TABLE
PIPELINED ROW POLYMORPHIC USING pkg_select_col;
FUNCTION describe (tab IN OUT DBMS_TF.TABLE_T,
col IN SYS.odcivarchar2list)
RETURN DBMS_TF.DESCRIBE_T;
END;
CREATE OR REPLACE PACKAGE BODY pkg_select_col
AS
FUNCTION describe (tab IN OUT DBMS_TF.TABLE_T, col SYS.odcivarchar2list)
RETURN DBMS_TF.DESCRIBE_T
AS
BEGIN
FOR i IN 1 .. tab.column.COUNT ()
LOOP
FOR j IN 1 .. col.COUNT ()
LOOP
tab.column (i).PASS_THROUGH :=
UPPER (
tab.column (i).DESCRIPTION.NAME) =
'"' || UPPER (col (j)) || '"'; --TODO do not cover all case. "columnn 1" for instance
EXIT WHEN tab.column (i).PASS_THROUGH;
END LOOP;
END LOOP;
RETURN NULL;
END;
END;
I can use it in a query without problems.
WITH from_dynamic_query as (select 1 a from dual)
select count(*) from PKG_SELECT_COL.SELECT_COL (from_dynamic_query,
sys.odcivarchar2list ('a'))
but it doesn't work in a dynamic query
DECLARE
c CLOB;
towtimestwo SYS.odcivarchar2list := sys.odcivarchar2list ('a');
BEGIN
EXECUTE IMMEDIATE '
WITH from_dynamic_query AS (SELECT 1 a FROM DUAL)
SELECT json_arrayagg (json_object (*))
FROM TABLE (PKG_SELECT_COL.SELECT_COL (from_dynamic_query, :1))'
INTO c
USING towtimestwo;
DBMS_OUTPUT.put_line (c);
END;
[Error] Execution (6: 1): ORA-62565: The Describe method failed with error(s). ORA-06531: Reference to uninitialized collection ORA-06512: at "PKG_SELECT_COL", line 9 ORA-06512: at line 22 ORA-06512: at line 5
why?
CodePudding user response：
In short: bind variable's content is a data. It is not a part of SQL statement. It cannot be used to parse a query.
Explanation.
The information about bind variables is mentioned briefly inside the Concepts documentation, section Concepts for Database Developers:
When a query uses bind variables, the database can compile it once and store the query plan in the shared pool.
and in Glossary:
A placeholder in a SQL statement that must be replaced with a valid value or value address for the statement to execute successfully.
Stages of SQL processing section describes how query plan is built. It shows that the first two steps of the query processing are syntax and semantic checks. The first one validates the query text agains language specification (i.e. that you "say" correct words in the correct order using Oracle's SQL language). The second validates that your SQL sentence has meaning in your particular context (database), i.e. objects and their attributes actually exist.
Unless Oracle has all the identifiers it cannot build the query plan. So bind variable's content is not used in these steps.
PTF doesn't brake this processing flow and documentation states that DESCRIBE method is called at the cursor compilation time (which results in a built query execution plan). This call is a part of SQL parsing, it needs all the statement parts to validate SQL statement.
Successfull execution of the original statement may be misleading, because sys.odcivarchar2list ('a') is a constant and is processed as constant during the parsing. This may be seen in, for example, Predicate Information section below.
explain plan for select * from dual where sys.odcivarchar2list('A') is null
select * from dbms_xplan.display(format => 'BASIC PREDICATE')| PLAN_TABLE_OUTPUT | | :--------------------------------------------------- | | Plan hash value: 3752461848 | | | | ----------------------------------- | | | Id | Operation | Name | | | ----------------------------------- | | | 0 | SELECT STATEMENT | | | | |* 1 | FILTER | | | | | 2 | TABLE ACCESS FULL| DUAL | | | ----------------------------------- | | | | Predicate Information (identified by operation id): | | --------------------------------------------------- | | | | 1 - filter("SYS"."ODCIVARCHAR2LIST"('A') IS NULL) |
And you'll get an error in your original code if you replace collection constructor with the subquery:
WITH from_dynamic_query as ( select 1 a from dual ) select count(*) from PKG_SELECT_COL.SELECT_COL ( from_dynamic_query, ( select sys.odcivarchar2list ('a') from dual ) )ORA-62565: The Describe method failed with error(s). ORA-06531: Reference to uninitialized collection ORA-06512: at "FIDDLE_YXHYCWPMHMYFGTOLHJOX.PKG_SELECT_COL", line 7 ORA-06512: at line 22
To build a dynamic SQL you need to generate the statement in the way you do for a regular dynamic query (by concatenation and identifier preparation/validation or using dbms_sql package).
Note: According to the errors, SQL Macro uses PTF mechanism internally and the body of the SQL Macro function is also executed at the prepare time. So it also doesn't allow to pass external variables to the statement if they are used to build a query.
create or replace function f_test_sqlmacro(p_cols in sys.odcivarchar2list)
return varchar2 sql_macro(table)
as
cols varchar2(1000);
begin
select listagg(column_value, ', ')
into cols
from table(p_cols);
return 'select ' || cols || ' from dual';
end;
/
declare
ret varchar2(1000);
begin
execute immediate 'select * from f_test_sqlmacro(:1)'
into ret
using sys.odcivarchar2list('DUMMY');
dbms_output.put_line(ret);
end;
/
Error starting at line : 38 in command -
declare
ret varchar2(1000);
begin
execute immediate 'select * from f_test_sqlmacro(:1)'
into ret
using sys.odcivarchar2list('DUMMY');
dbms_output.put_line(ret);
end;
Error report -
ORA-62565: Сбой метода SQL Macro из-за ошибок.
ORA-03137: Отправленный клиентом пакет TTC сформирован неправильно и отклонен: [opibnd0-3] [0] [] [] [] [] [] []
ORA-06512: на line 4
62565. 00000 - "The %s method failed with error(s). %s"
*Cause: This method of polymorphic table function had error(s) during SQL compilation.
*Action: Look at the errors and resolve them.
CodePudding user response：
It's because you're using a bind variable to determine which columns are in the result set. This happens in static SQL as well as dynamic SQL:
var col_name varchar2(128);
exec :col_name := 'a';
with from_dynamic_query as
(select 1 a from dual)
select count(*) from pkg_select_col.select_col (
from_dynamic_query,
sys.odcivarchar2list ( :col_name )
);
ORA-62565: The Describe method failed with error(s).
ORA-06531: Reference to uninitialized collection
Any "shape determining" arguments (those that affect which columns are in the result set) must be constants:
The scalar arguments of a PTF can be any SQL scalar expression. While the constant scalar values are passed as-is to the DESCRIBE function, all other values are passed as NULLs. This is usually not a problem for the PTF implementation if these values are not row shape determining, but otherwise the DESCRIBE function can raise an error; typically the documentation accompanying the PTF will state which scalar parameters, if any, are shape defining and thus must have constant non-null values