This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Jan 10, 2011 2:12 AM by 648421 RSS

How to assign variable with dynamic page item?

648421 Newbie
Currently Being Moderated
Hi there,

I'm facing following problem:
I need to assign a variable with a value of a page item - e.g. apex_application.g_f04(i).
My problem is, that f04 might be everthing between f04 and f52 (or whatelse was max. number of page items?).
I tried several things, among others something like this:

v_test := apex_application.g_f || x || ( i) /* where x might be another variable */

or

v_stat := 'SELECT apex_application.g_f' || x || '( i) from DUAL'
EXECUTE IMMEDIATE v_stat INTO v_test

but I didn't get it till now...
Any ideas?

Edited by: DFiles on 06.01.2011 13:57
  • 1. Re: How to assign variable with dynamic page item?
    ATD Guru
    Currently Being Moderated
    Hi,

    In your statement, ( i ) would just be seen as static text, also you need the ; at the end of the lines, so try:
    v_stat := 'SELECT apex_application.g_f' || x || '(' || i || ') from DUAL';
    EXECUTE IMMEDIATE v_stat INTO v_test;
    The 'x' there needs to be 2 digits as the numbers range from 00 to 50 (which is the max)

    Andy
  • 2. Re: How to assign variable with dynamic page item?
    648421 Newbie
    Currently Being Moderated
    Simple statement:
    DECLARE
      v_test varchar2(999) := NULL;
    
    BEGIN
    
      v_test := 'SELECT apex_application.g_f0' || '4' || '(1) FROM DUAL';
    
      return v_test;
    
    END;
    Throws:
    ORA-06553: PLS-221: 'G_F04' is not a procedure or is undefined
  • 3. Re: How to assign variable with dynamic page item?
    ATD Guru
    Currently Being Moderated
    Hi

    Are you testing that by submitting the page that has a tabular form on it?

    You could also try using uppercase: APEX_APPLICATION.G_F04(1)

    Andy
  • 4. Re: How to assign variable with dynamic page item?
    648421 Newbie
    Currently Being Moderated
    No, there is no tab. form on it - just html and pl/sql regions, reports, a list and a tree.
    Uppercase throws the same error... :(
  • 5. Re: How to assign variable with dynamic page item?
    ATD Guru
    Currently Being Moderated
    Hi,

    OK - G_F04 will not exist until there is tabular form on the page. G_Fnn represents a collection of page items submitted on the page and these items are created as part of a tabular form (though you could do it manually if you want?)

    Andy
  • 6. Re: How to assign variable with dynamic page item?
    fac586 Guru
    Currently Being Moderated
    <tt>apex_application.g_f0x</tt> package variables are associative arrays and can't be accessed from dynamic SQL. You have to use dynamic PL/SQL:
    declare
    
      i pls_integer;
      x pls_integer;
      v varchar2(32767);
    
    begin
     
      /* However you get values for x and i... */
      i := 1;
      x := 4;
      
      execute immediate 'begin :1 := apex_application.g_f' || to_char(x, 'fm09') || '(:2); end;'
        using out v, i;
        
      return v;
    
    end;
  • 7. Re: How to assign variable with dynamic page item?
    648421 Newbie
    Currently Being Moderated
    Ok, now I unterstand, why you asked.
    As mentioned, there is no tab. form on the page, but I'm dynamically buildung a report with text fields - so G_F04 does exist.

    But maybe it just works with a "real" tab. form?
  • 8. Re: How to assign variable with dynamic page item?
    ATD Guru
    Currently Being Moderated
    Hi,

    First, try Paul's (fac586's) code - just in case!

    If that doesn't work (and it probably should with valid page items involved), we would need to see how you are constructing your fields

    Andy
  • 9. Re: How to assign variable with dynamic page item?
    648421 Newbie
    Currently Being Moderated
    That looks strange and I unterstand nearly nothing of it :D
    I created it as report, returning sql and I get: ORA-01403: no data found
  • 10. Re: How to assign variable with dynamic page item?
    ATD Guru
    Currently Being Moderated
    Hi,

    OK - the report's SQL should be something like:
    SELECT 1 "DummyField",
    APEX_ITEM.TEXT(4, 'Somevalue') "TestField"
    FROM DUAL
    That adds in an item that will be submitted as F04

    Andy

    ps - Paul's code (and he could probably explain it better than I could!) uses bind variables - :1 and :2 are placeholders for the v and i USING variables and OUT indicates that a value should be returned from the statement back to the main PL/SQL code - I think ;)
  • 11. Re: How to assign variable with dynamic page item?
    fac586 Guru
    Currently Being Moderated
    That looks strange and I unterstand nearly nothing of it
    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/executeimmediate_statement.htm#LNPLS01317
    I created it as report, returning sql and I get: ORA-01403: no data found
    Why?

    You said:
    I need to assign a variable with a value of a page item - e.g. apex_application.g_f04(i).
    Where does a report come into it?

    If by "variable" you mean a variable in a PL/SQL block/program unit, then just extend the example as necessary, using whatever variable is your equivalent of <tt>v</tt>. If your "variable" is an APEX item then use a computation/source of type PL/SQL Function Body with something like the posted code.

    The code posted was simply an example of using dynamic PL/SQL to access an associative array that is unknown until runtime. You've given no indication of why this is necessary, or where the <tt>x</tt> and <tt>i</tt> values come from...
  • 12. Re: How to assign variable with dynamic page item?
    648421 Newbie
    Currently Being Moderated
    Sounds logical...
    But as what should i create it (Paul's one)?
    I guess, report was wrong. But what else? Computation? Process?
  • 13. Re: How to assign variable with dynamic page item?
    648421 Newbie
    Currently Being Moderated
    Oh, ok.
    Then I'll trie, to implement it into my code.

    I meant a variable in a PL/SQL block.
    Currently I'm doing it this way, because it was the only way I got running:
      case
        when x = 1  then v_var := apex_application.g_f04(i);
        when x = 2  then v_var := apex_application.g_f06(i);
        when x = 3  then v_var := apex_application.g_f08(i);
        when x = 4  then v_var := apex_application.g_f10(i);
        when x = 5  then v_var := apex_application.g_f12(i);
        when x = 6  then v_var := apex_application.g_f14(i);
        when x = 7  then v_var := apex_application.g_f16(i);
        when x = 8  then v_var := apex_application.g_f18(i);
        when x = 9  then v_var := apex_application.g_f20(i);
        when x = 10 then v_var := apex_application.g_f22(i);
        when x = 11 then v_var := apex_application.g_f24(i);
        when x = 12 then v_var := apex_application.g_f26(i);
        when x = 13 then v_var := apex_application.g_f28(i);
        when x = 14 then v_var := apex_application.g_f30(i);
        when x = 15 then v_var := apex_application.g_f32(i);
        when x = 16 then v_var := apex_application.g_f34(i);
        else null;
      end case;
    Where x and i are control variables.
    My problem is, that it also might look like this "when x = 3 then v_var := apex_application.g_f07(i)" or "when x = 3 then v_var := apex_application.g_f06(i)".
    That's why I'd like do get g_fxx dynamically

    Edited by: DFiles on 07.01.2011 10:27
  • 14. Re: How to assign variable with dynamic page item?
    ATD Guru
    Currently Being Moderated
    Ok, create a report using that SQL (or something similar to it).

    Make sure that you have a button on the page that will do the Submit.

    Create a process on your page that is triggered by the button and set the code to something like:
    BEGIN
     wwv_flow.debug('Value is ' || APEX_APPLICATION.G_F04(1));
    END;
    Load the page and click the Debug option at the bottom of the screen. Now click the Submit button. When the page has reloaded, click the View Debug option at the bottom of the page and select one of the links listed - this shows debug messages. Try to find one of the listed links (there may be more than one listed) that contains "Value is " - if you do find it, does it show the value?

    If it does, we have a page that submits correctly, so you now need something like Paul's code to use the value in an execute immediate statement

    Andy
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points