Use a package...
Is there a way to have that process submit only if everything passes validation?
and also, would I have to write it in PL/SQL or would the way it's written now work?
Are you talking about an database package or is there a package option in APEX?
I had the code in an database package as a function that ran every 6 hours, but the customer wants the data transfered over as soon as the user clicks submit.
Is there a way to call that function within APEX?
Or maybe call it in the update trigger?
Database package called from page process that fires upon Page Submit (whatever button you're using).
Ok. So a pl/sql page process with the code
begin package_name.update_function_name(); end;
I'm not very familiar with PL/SQL so I don't know if that's the correct format to call the fucntion?
That format works if the package is in the same schema that the APEX project is pointing to.
Would it be better to put the code into a procedure instaed of a function inside of the package?
Or would it not matter?
That's generally a preference issue. IMO, a package gives more flexibility for future development. So put the function in a package or put a procedure in the package.
If you get Oracle Magazine, Steven Feuerstein has some really good articles on APEX coding of SQL and PL/SQL. See the current issue and the one before.
I created the procedure inside my package (the package is stored in my APEX schema and I copied the format of another procedure)
procedure update_records is begin merge into A a using( select distinct pk1, pk2, date1, date2 from B) b on(a.pk1 = b.pk1 and a.pk2 = b.pk2) when matched then update set a.date1 = greatest(nvl(a.date1, TO_DATE('01011950','MMDDYYY')), nvl(b.date1, TO_DATE('01011950','MMDDYYYY'))); a.date2 = greatest(nvl(a.date2, TO_DATE('01011950','MMDDYYY')), nvl(b.date2, TO_DATE('01011950','MMDDYYYY'))); COMMIT; exception when OTHERS then null; end update_records
When I create the pl/sql process on page submit and enter the code
begin apex_package.update_records; end;
I get the error that component update_records must be declared
I created the procedure as a stand alone within apex, and the process compiled correctly.
begin update_records; end;
As you indicate unfamiliarity w/ PL/SQL...you can do the following from a SQL session, from APEX directly or from any SQL tool (like SQL Developer)...
create or replace package <your package> is
end <your package>;
create or replace package body <your package> is
procedure update_records() is
end <your package>;
So your call from APEX would be <your package>.update_records;
As you have the procedure working, up to you whether to move to a package.
I initially had the procedure in my package, but when I made my call:
begin package_name.update_records; end;
I was tring to call an individual procedure within the package body. (not referenced in the initial package)
Right now the package only calls one procedure, that then calls everything else within the package body.
create package pkg1 is procedure run_everything; end pkg1; create package body pkg1_body is procedure proc_one is ......... end; procedure proc_two is ......... end; procedure run_everything is begin proc_one; proc_two; end;
say I added in my update_records procedure. Would it work if I changed the initial package to:
create package pkg1 is procedure run_everything; procedure update_records; end pkg1;
or can there only be one procedure call in the initial package?