1

Much like this question, I am looking to return a resultset from function and work with this resultset just like with an ordinary table, but with the extra twist that I want to do this from within a procedure (using Oracle versions >= 11).

My basic intent is to allow the results of a dynamic query be used as a starting point for a very complex static SQL statement.

For calls directly from a SQL*PLUS prompt, multiple solutions exist, but they all seem to fail when run in a procedure. For example, using 'method4': 

SQL> WITH q AS (SELECT * FROM TABLE(method4.query('select id, pid from 
  2  my_table where rownum < 100')))
  3  SELECT * FROM q where rownum <4;

             ID        PID
--------------- ----------
             72        499
             11         89
             13         23

3 rows selected.

SQL> CREATE OR REPLACE PROCEDURE test_x (
  2     p_sql IN VARCHAR2,
  3     p_results OUT SYS_REFCURSOR
  4     ) IS
  5     BEGIN
  6        open p_results for
  7        WITH q AS (SELECT * FROM TABLE(method4.query(p_sql)))
  8        SELECT * FROM q where rownum <4; -- this is where the complex logic would go
  9     END test_x;
 10  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST_X:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/4      PL/SQL: SQL Statement ignored
7/29     PL/SQL: ORA-22905: cannot access rows from a non-nested table
         item

SQL>

In my real application, the query in the procedure will be quite long and complex. I want to avoid generating dynamic SQL for each call. (That is my current 'fall-back' solution.)

Is there a way to leverage 'ANYDATASET table functions' from within stored procedures?

Update: I have been informed that receiving pipelined content in a PL/SQL context is not possible. I believe that only leaves me with a non-pipelined approach - which I believe has the same problem...

Given the following non-pipelined analog of the initial SQL:

SQL> CREATE OR REPLACE FUNCTION myQuery (
  2     p_1 IN VARCHAR2
  3  ) RETURN ANYDATASET AS p_result ANYDATASET;
  4     BEGIN
  5             EXECUTE IMMEDIATE p_1 INTO p_result;
  6             RETURN(p_result);
  7  END myQuery;
  8  /

Function created.

SQL> CREATE OR REPLACE PROCEDURE test_x2 (
  2     p_sql IN VARCHAR2,
  3     p_results OUT SYS_REFCURSOR
  4     ) IS
  5        myset anydataset;
  6     BEGIN
  7        myset := method4.query(p_sql);
  8        open p_results for
  9        WITH q AS (SELECT * FROM TABLE(myset))
 10        SELECT * FROM q where rownum <4; -- this is where the complex logic would go
 11     END test_x2;
 12  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST_X2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/7      PL/SQL: SQL Statement ignored
9/32     PL/SQL: ORA-22905: cannot access rows from a non-nested table
         item

106/13   PLS-00653: aggregate/table functions are not allowed in PL/SQL
         scope

It appears that the use of table functions is not allowed in PL/SQL - independent of pipelining or not.

If so, are there any other ways to take take a dynamic query for which the resulting schema can be anything, and feed it into a subsequent static query as a 'with' input, all within a PL/SQL procedure?

Community
  • 1
  • 1
anon
  • 87
  • 1
  • 10
  • I believe you should assign values from the function to some variable first. Inside your procedure, declare variable of the type returned by the function and assign function result to that variable. Then, use your variable in `SELECT * FROM TABLE` statement. – AndrewMcCoist Sep 05 '16 at 08:53
  • @AndrewMcCoist - thanks for the suggestion, but it just seemed to move the error to the line where I set the variable. (The line "myset := method4.query(p_sql);" picks up the ORA-22905 error.) – anon Sep 06 '16 at 14:46
  • Oh. I Forgot. Is your `method4.query` a `PIPELINED` function? If it is, you won't be able to retrieve it like this in PL/SQL context. It has to return you the regular table type. – AndrewMcCoist Sep 07 '16 at 10:32
  • @AndrewMcCoist - Yes, `method4.query` is a pipelined function. However, while pipelining would be nice for scalability, the solution does not require it. I'll update the question accordingly. – anon Sep 07 '16 at 17:01

1 Answers1

0

I do not believe that this can be done because it appears that having a non-fixed set of columns as part of the PL/SQL code (as CTE input or other similar approaches) does not allow for the code to be pre-compiled. It seems that the only answer is to keep the whole procedure as dynamic.

I feel bad answering my own question, but I don't want to leave it open when I have come to this conclusion, and deleting the question might be bad as someone else might be trying to do this in the future...

anon
  • 87
  • 1
  • 10