Using the PL/SQL Function body returning SQL query

Question

I am working with Oracle APEX and I'm trying to store a dynamic sql query in the PL/SQL Function body. Here is my current SQL query for a classic report:

SELECT 
    RELEASE,
    COUNT(*) as "Total Tests", --total
    SUM(CASE WHEN TYPE = 'P1' THEN 1 ELSE 0 END) as "P1",
    SUM(CASE WHEN TYPE = 'P2' THEN 1 ELSE 0 END) as "P2",
    SUM(CASE WHEN TYPE = 'P3' THEN 1 ELSE 0 END) as "P3",
    SUM(CASE WHEN TYPE = 'P4' THEN 1 ELSE 0 END) as "P4"
    FROM TABLENAME
group by RELEASE
ORDER BY case
    when RELEASE = '19.3' then 1
    when RELEASE = '18.11' then 2
    when RELEASE = '18.9' then 3
    when RELEASE = '18.7' then 4
    when RELEASE = '17.3' then 5
    else 6
end asc

I am trying to store the 'tablename' in a page item so that whenever a user changes the page item, this query will automatically pull from the given table. I've attempted to build a pl/sql function around this but continue to run into errors with the 'strings'.

Does anyone know how to convert this in to a dynamic pl/sql function?

Thanks in advance.

ORA-20999: Parsing returned query results in "ORA-20999: Failed to parse SQL query! <p>ORA-06550: line 24, column 8: ORA-00904: "18.7": invalid identifier</p>". — aphibui

Littlefoot Littlefoot · Accepted Answer · 2018-11-15T20:47:13

You should

create a classic report whose source is a function that returns a query

that query should look like this:

return '
  SELECT 
    RELEASE,
    COUNT(*) as "Total Tests", --total
    SUM(CASE WHEN TYPE = ''P1'' THEN 1 ELSE 0 END) as "P1",
    SUM(CASE WHEN TYPE = ''P2'' THEN 1 ELSE 0 END) as "P2",
    SUM(CASE WHEN TYPE = ''P3'' THEN 1 ELSE 0 END) as "P3",
    SUM(CASE WHEN TYPE = ''P4'' THEN 1 ELSE 0 END) as "P4"
    FROM ' || 
    SYS.DBMS_ASSERT.sql_object_name(:P1_TABLE_NAME) ||
  ' group by RELEASE 
  ORDER BY case
    when RELEASE = ''19.3''  then 1
    when RELEASE = ''18.11'' then 2
    when RELEASE = ''18.9''  then 3
    when RELEASE = ''18.7''  then 4
    when RELEASE = ''17.3''  then 5
    else 6
  end asc';

DBMS_ASSERT is here to prevent SQL Injection
set "use generic column names" to "Yes"
page item's name is :P1_TABLE_NAME (in my example); it should be a text field, submit when press Enter

That's all, I presume.

Using the PL/SQL Function body returning SQL query

1 Answers