How to pass a procedure for PRE-COMMIT using OO4O

[eja5866]

Hello.

I'm sending over a bunch of Insert statements from Excel straight into Oracle using OO4O.
Normally on the database side, I have before-insert and before-update triggers firing which populate modified_by and modified_date columns for auditing purposes.

In Forms, I would populate a procedure using a Pre-commit trigger which looked like this:
iplot.Pr1('username', SYSDATE,'username', SYSDATE);
And this worked fine.

However in this VBA environment, how do I successfully carry over these parameters into this procedure right BEFORE the commit?

Ive used: 'dmlstr = "execute iplot.Pr1('username', SYSDATE,'username', SYSDATE)"
right before and right after the INSERT statement, but with no success carrying over this info.
I've even tried to to populate the fields using a typical insert statement, but the Before-insert and before-update triggers fire istead with default values.

It has to carry over on the same time as precommit.
Any ideas?

Kind Regards,

Edward Apacible
International Flavors & Fragrances
Ph: 732-264-4500 Ext. 3469
Fax: 732-335-2350

 

[eja5866]

bump

Kind Regards,

Edward Apacible
International Flavors & Fragrances
Ph: 732-264-4500 Ext. 3469
Fax: 732-335-2350

 

[fredericofonseca]

I do not know exactly how you are doing this, but have you considered using a stored procedure to do the inserts? I am pretty sure that within the PL/SQL code you could do what you need.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[eja5866]

I am calling a stored package which has the procedure:

Create or Replace Package P1 as
created_by varchar2(30);
created_date date;
modified_by varchar2(30);
modified_date date;
End;
/

-- This procedure sets package values since procedures
-- can be called from front end
-- but packages cannot be directly called

Create or replace procedure pr1(p11 varchar2 ,p2 date ,p3 varchar2,p4 date)
as
begin
P1.created_by := p11;
P1.created_date := p2;
P1.modified_by := p3;
P1.modified_date := p4;
end;
/


An example trigger is this which checks to see if the P1 variables are populated:
CREATE OR REPLACE TRIGGER IPC_Master_bur BEFORE UPDATE ON
IPC_Master FOR EACH ROW
BEGIN
If P1.modified_by is NULL then
BEGIN
:new.modified_by := USER;
:new.modified_date := SYSDATE;
END;
else
:new.modified_by := P1.modified_by;
:new.modified_date := P1.modified_date;
end if;
END;


The problem is sending these values from Excel into Oracle.

I try populatting the procedure right before the insert since its a BUI trigger like so:
dmlstr = "execute iplot.Pr1('Apacible', SYSDATE,'Apacible', SYSDATE)"

But if doesn't work.

Any ideas?

Kind Regards,

Edward Apacible
International Flavors & Fragrances
Ph: 732-264-4500 Ext. 3469
Fax: 732-335-2350

 

[fredericofonseca]

Ignore excel for the time being.

If you create a procedure/package that the does the INSERTS

something like

CREATE OR REPLACE PROCEDURE "HR"."ADD_JOB_HISTORY" ( p_emp_id job_history.employee_id%type
, p_start_date job_history.start_date%type
, p_end_date job_history.end_date%type
, p_job_id job_history.job_id%type
, p_department_id job_history.department_id%type
)
IS
BEGIN
INSERT INTO job_history (employee_id, start_date, end_date,
job_id, department_id)
VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

This procedure is the use you use from Excel do do the inserts.

Within the procedure you then do all you need to do in terms of setting up your other variables and calling any other procedures you need.

This should work as long as it works if executed directly from SQL*Plus.
So once it is working from there you can then pass to the Excel bit, but not before.

Also why are you using OO40? any particular reason? ADO may be an easier solution unless you need some particular characteristic only available through OO40.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com