This content has been marked as final. Show 24 replies
In your statement, ( i ) would just be seen as static text, also you need the ; at the end of the lines, so try:
The 'x' there needs to be 2 digits as the numbers range from 00 to 50 (which is the max)
v_stat := 'SELECT apex_application.g_f' || x || '(' || i || ') from DUAL'; EXECUTE IMMEDIATE v_stat INTO v_test;
DECLARE v_test varchar2(999) := NULL; BEGIN v_test := 'SELECT apex_application.g_f0' || '4' || '(1) FROM DUAL'; return v_test; END;
ORA-06553: PLS-221: 'G_F04' is not a procedure or is undefined
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)
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... :(
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?)
<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;
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?
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
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
OK - the report's SQL should be something like:
That adds in an item that will be submitted as F04
SELECT 1 "DummyField", APEX_ITEM.TEXT(4, 'Somevalue') "TestField" FROM DUAL
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 ;)
That looks strange and I unterstand nearly nothing of ithttp://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 foundWhy?
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...
But as what should i create it (Paul's one)?
I guess, report was wrong. But what else? Computation? Process?
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:
Where x and i are control variables.
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;
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
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:
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?
BEGIN wwv_flow.debug('Value is ' || APEX_APPLICATION.G_F04(1)); END;
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