Tags: apex, apex_public_user, application, call, database, functions, mysql, oracle, procedures, requires, schema, sql, stored
Can I call procedures and functions stored in any other schema in apex?
Can I call procedures and functions stored in any other schema in apex? I mean to say a schema other then apex_public_user.??
My application requires the use of lots of pl/sql procedures. I want to be able to call procedures in the databse on click of a button. I also want to provide parameters to this procedure which would be application level items.
Leave a comment...
- 7 Comments
- yes you can. A few things you need to do before that.
1) Make sure the HTMLDB schema has exucute permission for the proc / fn in another schema.
2) For calling the proc you will have to qualify it with schema name. e.g. say my HTMLDB schema is "ABC" and i have a proc "printwelcome" in another schema "XYZ" and the package name is "asd" then i will call the proc by
XYZ.asd.printwelcome;#1; Sat, 23 Feb 2008 16:31:00 GMT
- where do i write this command in my application ??
Should I keep this in annonymous plsql block in my application ?? I think it doesn't work . it tells me its not a valid plsql command#2; Sat, 23 Feb 2008 16:32:00 GMT
There are many places where you can put PL/SQL in your application: Computations, Region Sources, Item Sources, Processes etc..
So the answer to: "where do i write this command in my application ??" is:
It depends entirely on what your function/procedure does and when you want it to do it.
As an example: If you have Procedure "do_something" in schema "scott" which you want to be fired when you click a button, you can do the following:
* Create an "After Submit" page process of type "PL/SQL"
* Set its source to:
* Set its "When Button Pressed" to refer to the relevant page button.
If this doesn't answer your question please give more detail about exactly what it is you are trying to do, what code you have typed where and what error it gave.
Andy#3; Sat, 23 Feb 2008 16:33:00 GMT
- I think you probably need to issue the grants from your schema's so they can use each other so something like grant execute on package name to schema_name from within your other schema should then allow you to do what Andy is suggesting.
Suzanne#4; Sat, 23 Feb 2008 16:34:00 GMT
- Hi all
Okay I am still not able to achieve the desired result. Here is what I did
I logged into my server with the schema flexui and created the following procedure out there
create or replace procedure test(num int)
insert into selected_origin_cities values(num,'ASD');
Then I created a process in my apex application. The type of this application is annonymous plsql block. The source contains just one line
This process is set to fire on the press of the NEXT button. When I press the next button, this executes fine without any errors but the corresponding row doesnt get inserted in the selected_origin_cities, as expected.
When I ran this process from sqlplus command prompt,
this works absolutely fine and the row gets inserted as well.#5; Sat, 23 Feb 2008 16:35:00 GMT
Check the Next button. If it has an Optional URL Redirect attribute then it won't submit the page and your process won't fire. Take that URL out and make sure the button submits with the request value you expect (Next, NEXT, whatever).
Scott#6; Sat, 23 Feb 2008 16:36:00 GMT
That works fine . Scott , bang on ..
Thank you all for your help.#7; Sat, 23 Feb 2008 16:37:00 GMT