I've a query that creates a SQL Statement as a field. I want to execute this statement and return the recordset in SSRS report.
select 'select '||FILE_ID||' FILE_ID,'||
ltrim(sys_connect_by_path('REC_FLD_'||FIELD_NUMBER||' "'||FIELD_NAME||'"',','),',')||
' from RESPONSE_DETAILS where FILE_ID=' ||FILE_ID||';'
from (select t.*,count(*) over (partition by FILE_ID) cnt from RESPONSE_METADATA t)
where cnt=FIELD_NUMBER start with FIELD_NUMBER=1
connect by prior FILE_ID=FILE_ID and prior FIELD_NUMBER=FIELD_NUMBER-1
This generates a SQL stetment - however I want this SQL to be executed.
This is an extension of this question.
I've tried to use execute immediate , cursors, dbms_sql but it does not produce output. Using it on toad. All it says is "PL/SQL procedure successfully completed"
Using the following
Declare
sql_stmt VARCHAR2(3000);
l_cursor SYS_REFCURSOR;
TYPE RefCurTyp IS REF CURSOR;
v_cursor RefCurTyp;
CURSOR c1 is
select 'select '||FILE_ID||' FILE_ID,'||
ltrim(sys_connect_by_path('REC_FLD_'||FIELD_NUMBER||' "'||FIELD_NAME||'"',','),',')||
' from RESPONSE_DETAILS where FILE_ID=' ||FILE_ID||';'
from (select t.*,count(*) over (partition by FILE_ID) cnt from RESPONSE_METADATA t)
where cnt=FIELD_NUMBER start with FIELD_NUMBER=1
connect by prior FILE_ID=FILE_ID and prior FIELD_NUMBER=FIELD_NUMBER-1;
BEGIN
open c1;
FETCH C1 into sql_stmt ;
dbms_output.put_line(sql_stmt);
close c1;
EXECUTE IMMEDIATE sql_stmt;
open v_cursor for sql_stmt;
return l_cursor;
close l_cursor ;
END;
An anonymous PL/SQL block cannot return any data to the caller. If you want to return a SYS_REFCURSOR
to the calling application, you would need to create a function (or a procedure). For example
CREATE OR REPLACE FUNCTION get_results
RETURN sys_refcursor
IS
l_sql_stmt VARCHAR2(3000);
l_cursor SYS_REFCURSOR;
BEGIN
select 'select '||FILE_ID||' FILE_ID,'||
ltrim(sys_connect_by_path('REC_FLD_'||FIELD_NUMBER||' "'||FIELD_NAME||'"',','),',')||
' from RESPONSE_DETAILS where FILE_ID = ' ||FILE_ID||';'
into l_sql_stmt
from (select t.*,count(*) over (partition by FILE_ID) cnt from RESPONSE_METADATA t)
where cnt=FIELD_NUMBER
start with FIELD_NUMBER=1
connect by prior FILE_ID=FILE_ID
and prior FIELD_NUMBER=FIELD_NUMBER-1;
dbms_output.put_line(l_sql_stmt);
open l_cursor for sql_stmt;
return l_cursor;
END;
I am assuming from your code that you expect your SELECT
statement to return a single SQL statement-- your code is fetching only one row from a query that potentially returns multiple SQL statements. I'm assuming that you only fetch one because you only expect the SELECT
statement to return one row. Otherwise, since your query lacks an ORDER BY
, you are executing arbitrarily one of N SQL statements that your code is generating.
If you are regularly going to be calling this method, you would almost certainly want to use bind variables in your dynamic SQL statement for the file_id
rather than generating non-sharable SQL statements. I haven't made that change here.
There is another StackOverflow thread on calling a stored function returning a sys_refcursor
from SSRS.