I am trying to run an Oracle user function that returns a SQL query. This SQL Query is used for an SSRS report. I want to get the exact query that the report sends to the Oracle DB.
I am trying to query the following user function to get the exact SQL query that the report sends:
SELECT REPORT_PREPARE_QUERY('Report Name', 1, 12345678) as Test FROM DUAL;
When the above statement executes, it only returns a truncated result:
<code>"
WITH CTE
AS (
select distinct A.First, A.Second, A."
</code>
<code>"
WITH CTE
AS (
select distinct A.First, A.Second, A."
</code>
"
WITH CTE
AS (
select distinct A.First, A.Second, A."
The above query is stored in another table. Of course, I can go to that table and get the full query, but the query has parameters passed by the report, for example like this
” :Test_Parameter “, and I want the exact query with parameters passed by the report.
These are the function parameters:
<code>create or replace function REPORT_PREPARE_QUERY (
report_name varchar2,
query_num number,
transaction_num number,
empty_dataset number default 0
) RETURN clob
AS
report_query clob;
report_num number;
report_filters clob;
</code>
<code>create or replace function REPORT_PREPARE_QUERY (
report_name varchar2,
query_num number,
transaction_num number,
empty_dataset number default 0
) RETURN clob
AS
report_query clob;
report_num number;
report_filters clob;
</code>
create or replace function REPORT_PREPARE_QUERY (
report_name varchar2,
query_num number,
transaction_num number,
empty_dataset number default 0
) RETURN clob
AS
report_query clob;
report_num number;
report_filters clob;
I am using Oracle SQL Developer and this is the DB version:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
I have tried to use the following query to get history, but it also truncates (SQL_Text column) the result of the query sent:
<code> select v.SQL_TEXT,
v.PARSING_SCHEMA_NAME,
v.FIRST_LOAD_TIME,
v.DISK_READS,
v.ROWS_PROCESSED,
v.ELAPSED_TIME,
v.service
from v$sql v
order by FIRST_LOAD_TIME DESC ;
--This does not update when the report runs
select FIRST_LOAD_TIME, SQL_TEXT from v$sqlarea
order by FIRST_LOAD_TIME desc
</code>
<code> select v.SQL_TEXT,
v.PARSING_SCHEMA_NAME,
v.FIRST_LOAD_TIME,
v.DISK_READS,
v.ROWS_PROCESSED,
v.ELAPSED_TIME,
v.service
from v$sql v
order by FIRST_LOAD_TIME DESC ;
--This does not update when the report runs
select FIRST_LOAD_TIME, SQL_TEXT from v$sqlarea
order by FIRST_LOAD_TIME desc
</code>
select v.SQL_TEXT,
v.PARSING_SCHEMA_NAME,
v.FIRST_LOAD_TIME,
v.DISK_READS,
v.ROWS_PROCESSED,
v.ELAPSED_TIME,
v.service
from v$sql v
order by FIRST_LOAD_TIME DESC ;
--This does not update when the report runs
select FIRST_LOAD_TIME, SQL_TEXT from v$sqlarea
order by FIRST_LOAD_TIME desc