0

I have PL/SQL function which is dynamically creating select statement an return this statement as varchar.Because I need this statement work dynamically(each time return different column count/name).For example it can return this select

'select id,name,currency,note from tabel t where t.id in(1,2,3,4,5,6);'

And I have another function must use this select statement result. But the second select statement return that this string and cannot execute this select statement.

How can make first function return result as sql ?

Bahruz Aghalarov
  • 1
  • 1
  • 5
  • 3
    Using ExecuteImmediate? https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm – vc 74 Feb 22 '18 at 11:17
  • Does the function executing this query know the structure of the result (columns)? – vc 74 Feb 22 '18 at 11:20
  • Deleted my answer!!! I read it as SQL only :( – BlindSniper Feb 22 '18 at 11:21
  • How will the calling function process the results if it can get different column counts/names each time? Is that already dynamic (using `dbms_sql` I guess)? More context would be helpful to understand what you're actually trying to do, and a suitable approach. – Alex Poole Feb 22 '18 at 11:24
  • @vc74 I have read this documentation.But have not find my case to execute varchar as sql and this varchar came from other function.If it is not hard for you give real example. – Bahruz Aghalarov Feb 22 '18 at 11:28
  • @AlexPoole I cannot give your this sql(security) but I can say that I have made this with concat.Like this. 'select'|| d.column_name'||'as blabla'||"then other columns"||' from table d'||';' and Listagg function for make separately – Bahruz Aghalarov Feb 22 '18 at 11:29
  • 1
    I'm asking about the function that must use the select statement result, not how that string is generated. What are you ultimately going to do with the results of the query? – Alex Poole Feb 22 '18 at 11:40
  • This function must show result this result in Apex interactive grid. And the 2nd function I add this result to a collection and give header for columns. – Bahruz Aghalarov Feb 22 '18 at 11:50
  • I don't use Apex so not sure what it will expect; it's easy enough to open and return a ref cursor if that is something it would accept. Someone with Apex knowledge will hopefully chip in on that. I don't get the collection part though - how can you add it to a collection if you don't know what data you're expecting? – Alex Poole Feb 22 '18 at 12:59

2 Answers2

1

Provided the caller knows the structure of the result:

CREATE OR REPLACE PROCEDURE execute_query(query IN VARCHAR2)
    TYPE cur_typ IS REF CURSOR;
    c cur_typ;
    ID NUMBER;
    Name VARCHAR2(20);
    Currency VARCHAR2(20);
    Note VARCHAR2(200);
BEGIN
    OPEN c FOR query;
    LOOP
        FETCH c INTO ID, Name, Currency, Note;
        EXIT WHEN c%NOTFOUND;
        ....
    END LOOP;

    CLOSE c;
END;
/
vc 74
  • 37,131
  • 7
  • 73
  • 89
  • this good code but not work for me :) As I said above I don't how many columns come from my dynamic query. If this column count static it will be like your procedure. – Bahruz Aghalarov Feb 22 '18 at 11:37
  • @BahruzAghalarov What do you want to do with the resulting cursor of the dynamic query? – vc 74 Feb 22 '18 at 11:49
  • This function must show result this result in Apex interactive grid. And the 2nd function I add this result to a collection and give header for columns. – Bahruz Aghalarov Feb 22 '18 at 11:53
0

Use EXECUTE IMMEDIATE Statement

as said in documentation: https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems017.htm

Sh. Pavel
  • 1,584
  • 15
  • 28