1 2 Previous Next 24 Replies Latest reply: Jan 10, 2011 4:12 AM by 648421 RSS

    How to assign variable with dynamic page item?

    648421
      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
          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
            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
              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
                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
                  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
                    <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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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