CODEWITHSUNDEEP
plsqloracle-apex
Zander
Zander

Reputation: 7

Using a page item inside a PL/SQL function body that returns an SQL Query

I am trying to create source code for a list view region that will pick a different table based on the values of page items passed to that page in oracle apex. Whenever I hard code values for these page items in the PL/SQL function body, the code validates and runs as intended. But the moment I substitute the hard code for page items it doesn't work.

Here is the code I have that compiles:

DECLARE
    list_query varchar2(4000);
    table_name varchar2(400);
    part_id number(6,1);
    table_prefix varchar2(400);
    table_suffix varchar2(400);
    
BEGIN
    table_suffix := 'placeholder value';
    table_prefix := 'placeholder value';
    table_name := table_prefix || table_suffix;
    part_id := 1001;
    list_query := 'select
                      CRITERIA_ID,
                      CRITERIA_NAME,
                      PART_ID,
                      PART_NAME
                   from
                      ' || table_name || '
                   where 
                      PART_ID = ' || part_id || '';
    return(list_query);
END;

As long as the place holder values for table_prefix and table_suffix that are hardcoded combine to make a table name that does exist the code runs and the intended table is displayed in the list view region. However I need the value of table_prefix to populate from a select into statement, while table_suffix and part_id need to be populated from page items.

So far I haven't been able to get either of these implementations to work. When trying to populate table_suffix page item I receive the following error:

table_suffix := :P21_PASSED_TABLE;
table_prefix := 'CABLES';
table_name := table_prefix || table_suffix;
part_id := 1001;

When I try to populate part_id with a page item, I get this error:

table_suffix := '_YES';
table_prefix := 'CABLES';
table_name := table_prefix || table_suffix;
part_id := :P21_PASSED_PART;

And when I try to use the select into statement for table_prefix , I get this error:

table_suffix := '_YES';
SELECT TABLE_NAME into table_prefix FROM ASSEMBLIES_TABLE WHERE ASSEMBLY_ID = :P21_PASSED_ASSEMBLY;
table_name := table_prefix || table_suffix;
part_id := 1001;

The variable type that I am trying to put into table_prefix is a varchar2(4000) and is pulled directly from a table, part_id needs to be populated from a page item that is passed from a different pages page item, that item is a select list and return value (the one being passed onto the current page in question) is a number(6,1). :P21_PASSED_ASSEMBLY is a hidden page item needed in the where clause of the select into statement and is also the return value of a select list on a different page of type number(5,2). Finally table_suffix is pulled from a table and passed to a hidden page item, in the table it is stored as a varchar2(400).

Any help would be immensely appreciated.

Upvotes: 0

Views: 1817

Answers (1)

Koen Lostrie
Koen Lostrie

Reputation: 18650

Here is a working example. I changed to be on the emp table since I don't have tables with suffixes. The idea is similar. A couple of notes:

  • Don't concatenate, you're getting messed up with the quotes and it becomes hard to read. The package apex_string has a format function that does the replacement for you.
  • Apex will validate the statement when it tries to compile. If the the table name is null at compile time, it's not a valid sql statement and you can't save. So I added NVL(:P142_SUFFIX,'EMP')
  • debug your code. apex_debug is a useful api. Add calls to it in your code, turn debug on and examine the output in the debug report
DECLARE
    list_query varchar2(4000);
    table_name varchar2(400);
    part_id number(6,1);
    table_prefix varchar2(400);
    table_suffix varchar2(400);
BEGIN
    table_name := NVL(:P142_TABLE_NAME,'EMP'); --added a default value
    part_id := :P142_EMPNO;
    list_query := 
    q'!select
                      EMPNO,
                      ENAME
                   from
                      %1
                   where 
                      EMPNO = '%0' !';
    list_query := apex_string.format(list_query, part_id,table_name);
    apex_debug.info(p_message =>'Koen Debug: '||list_query);
    return(list_query);
END;

Tip for future questions. Strip everything that is not necessary out of your question and reproduce it based on the emp/dept sample schema. That way anyone on this forum can easily reproduce it... and maybe you'll find the answer while writing out your question.

Upvotes: 1

Related Questions