This content has been marked as final. Show 16 replies
Are you trying to call a stored procedure from within an Apex application?
An application can be any PL/SQL - even calls to stored procedures. You can even kick off scheduled jobs in DBMS_SCHEDULER.
You can reward this reply by marking it as either Helpful or Correct ;-)
Yes, I am trying to call the stored procedure, according to the demo (the last paragraph).
I will try your method tomorrow morning at my development machine. Thanks!
I havent really gone through the links on this post and I know that the solution that I am suggesting is not an elegant one but it will definately solve the issue
Using window.open call the same page again
Now have a page process which gets called onload and depends on the value in this application item.
From this page process call the stored function
i guess that this line
won't work in your application. If you uploaded the file ac.js to your static files that line should read:
I recently also ran into problems with this and I will post my solution here:
1) if you need to pass parameters to your procedure, create it using "Flexible Parameter Passing". Then parse the parameters out of the array and put them in local variables inside your PL/SQL procedure.
2) grant EXECUTE rights to APEX_PUBLIC_USER (the user APEX uses to connect to the database) on the procedure
CREATE OR REPLACE PROCEDURE MATTHIASH.incsearch(name_array IN owa.vc_arr, value_array IN owa.vc_arr) as l_List1 varchar2(4000); l_List2 varchar2(4000); l_query varchar2(255); l_separator varchar2(10) default ''; qu varchar2(4000) default ''; hl varchar2(4000) default ''; BEGIN FOR i IN 1 .. name_array.COUNT LOOP IF name_array(i) = 'qu' THEN qu := value_array(i); ELSIF name_array(i) = 'hl' THEN hl := value_array(i); END IF; END LOOP; l_query := qu||'%'; FOR x IN ( select object_name, object_id from user_objects where upper(object_name) like upper(l_query) and upper(object_type) = upper(hl) order by 1 asc) LOOP l_list1 := l_List1 || l_separator || '"' || x.object_name || '"'; l_list2 := l_List2 || l_separator || '"' || x.object_id || '"'; l_separator := ','; END LOOP; owa_util.mime_header('text/html', false); owa_util.http_header_close; --htp.p('sendRPCDone(frameElement, "'|| qu ||'", new Array(' || l_List1 || '), new Array(' || l_List2 || '), new Array(""));'); htp.p('sendRPCDone(frameElement, "' || qu || '", new Array(' || l_List1 || '), new Array(' || l_List2 || '), new Array(""));'); END; /
3) upload the ac.js file as static file to your application
grant execute on incsearch to apex_public_user;
In my example, P1_X is a text field and P1_OBJECT_TYPE is a dropdown list with all user object types.
The thing that I'm not sure is whether the ac.js called the stored procedure. Thanks!
Hi Matthias, thanks for your help!
I would like to know what changed you made to ac.js file. Did you change the virtual directory location? (from "/complete/" to "/apex/")
In your script,
I just kept "en" as the last parameter, and according to the demo document, it seems to be the language setting. Is that the problem?
Now I redo everything just as the demo instructed.
It still doesn't work.
The only difference is this: the demo is hosted at the <b>htmldb.oracle.com</b> site while mine is running locally with <b>Oracle Express Edition and APEX 3.2 on Windows XP</b>. The demo's author changed the <b>virtual path</b> according to the configuration of the dads.conf or marvel.conf, but I don't have either of them, because I installed the APEX this way:
[Installing from the Database and Configure the Embedded PL/SQL Gateway|http://download.oracle.com/docs/cd/E14373_01/install.32/e13366/db_install.htm#insertedID4]
I can only guesstimate from my local URL that my virtual path is "/apex/" (or should I use "\\apex\\" instead on Windows?), which I use to make change to the ac.js file. Totally frustrated..
Yes, I made a change to the ac.js file.
Our APEX pages are accessed as follows: http://<servername>:7777/pls/apex/f?p=4550:1:0.
Also in my example I used the "hl" parameter not as the language variable but as an extra variable that can be selected from a dropdown list. Guess in your case you can leave it to "en", if you don't do anything with it in your procedure.
Did you try leaving the language parameter empty?
I managed to get it working on XE with APEX 3.2!
It took some more time than I expected because my XE installation was still on an older version of Apex and I had to upgrade it first...
But this is what I did:
- in the ac.js file, I changed the path to "/apex/" and I added the ac.js file to my application as static file. So like you did.
- I copied my application to XE and copied my procedure to the schema that is linked to my application
- I granted execute rights to ANONYMOUS on my procedure
Now initially this didn't work...
Then I suddenly remembered something: if you want to execute a procedure through the pl/sql gateway in XE, you have to tell XE what procedure this will be.
You do this by modifying the APEX_030200.wwv_flow_epg_include_mod_local function!
This is the code of the modified function for my example:
After this, the AJAX function worked as expected...
CREATE OR REPLACE function APEX_030200.wwv_flow_epg_include_mod_local( procedure_name in varchar2) return boolean is begin --return false; -- remove this statement when you modify this function -- -- Administrator note: the procedure_name input parameter may be in the format: -- -- procedure -- schema.procedure -- package.procedure -- schema.package.procedure -- -- If the expected input parameter is a procedure name only, the IN list code shown below -- can be modified to itemize the expected procedure names. Otherwise you must parse the -- procedure_name parameter and replace the simple code below with code that will evaluate -- all of the cases listed above. -- if upper(procedure_name) in ( 'MATTHIASH.INCSEARCH') then return TRUE; else return FALSE; end if; end wwv_flow_epg_include_mod_local; /
Edited by: mhoys on Aug 13, 2009 3:24 PM
Dear Matthias, you're amazing! Thanks so much! It finally works! Thanks to the modification of APEX_030200.wwv_flow_epg_include_mod_local function.
Actually, I almost killed myself when modifying that function, because [I dropped it and everything went crazy|http://forums.oracle.com/forums/thread.jspa?threadID=943486] ! Now it gets back to normal :-)
Thanks for your efforts!