Home » SQL & PL/SQL » SQL & PL/SQL » DB link creating problem. (Oralce 10g , forms 6i)
DB link creating problem. [message #448011] Fri, 19 March 2010 01:47 Go to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear All,
I have written a Database procedure to move data from one data to another database using Database link. When i run that procedure on Sql it is running fine, but i use same procedure in a button in form using "When-Button-Pressed" trigger it generates error, error code is ORA-02055 and ORA-02067.

how can i over ride it?

Thanks In Advance.

Regards.
Muhammad Asif.

Re: DB link creating problem. [message #448012 is a reply to message #448011] Fri, 19 March 2010 01:55 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

02055, 00000, "distributed update operation failed; rollback required"
// *Cause: a failure during distributed update operation may not have
//      rolled back all effects of the operation.  Since
//  	some sites may be inconsistent, the transaction must roll back to
//	a savepoint or entirely
// *Action: rollback to a savepoint or rollback transaction and resubmit
bcm@bcm-laptop:~$ oerr ora 2067
02067, 00000, "transaction or savepoint rollback required"
// *Cause: A failure (typically a trigger or stored procedure with multiple
//	   remote updates) occurred such that the all-or-nothing execution
//	   of a previous Oracle call cannot be guaranteed.
// *Action: rollback to a previous savepoint or rollback the transaction
//	    and resubmit.


>how can i over ride it?
Exactly what is "it" that you desire to over ride?

It appears you have a bug in your code.

Your unknown code has unknown problem.

[Updated on: Fri, 19 March 2010 01:55]

Report message to a moderator

Re: DB link creating problem. [message #448022 is a reply to message #448011] Fri, 19 March 2010 02:20 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As your code works properly from SQL*Plus and not from a form, create a stored procedure and call it in the WHEN-BUTTON-PRESSED trigger.
Re: DB link creating problem. [message #448036 is a reply to message #448011] Fri, 19 March 2010 02:38 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear,
I have created a database procedure and calling it in form by using "When-Button_Pressed" trigger. code is
CREATE OR REPLACE Procedure NML.prl_pro_upd(empcod in number,bascod in number) IS
Begin
    dbms_output.put_line('starting');
        INSERT INTO pay.COMPANY@att_bhk.com (EMP_COD,
                                     FIRST_NAME,
                                     MIDDLE_NAME,
                                     LAST_NAME,
                                     FULL_NAME,
                                     CALL_ME_AS,
                                     BRANCH_ID,
                                     DIVISION_ID,
                                     DEPARTMENT_ID,
                                     SECTION_ID,
                                     CONFIRMATION_DATE,
                                     HIRE_DATE,
                                     SHIFT_GROUP_ID,
                                     OVERTIME_ALLOWED,
                                     C_P_I_ALLOWED,
                                     OVT_HRS_PER_DAY,
                                     POSITION_ID,
                                     CATEGORY_ID,
                                     EMPLOYEE_TYPE_ID,
                                     EMPLOYEE_GROUP,
                                     RANK_ID,
                                     STATUS,
                                     DATE_OF_ARRIVAL,
                                     WEEKLY_OF_DAY,
                                     SALARY,
                                     upd_flg)
   SELECT   h34.emp_cod,
            NVL (h0.FST_NOM, h34.NOM_FST) FST_NOM,
            NULL MIDDLE_NAME,
            NVL (h0.LST_NOM, h34.NOM_LST) LAST_NAME,
               NVL (h0.FST_NOM, h34.NOM_FST)
            || ' '
            || NVL (h0.LST_NOM, h34.NOM_LST)
               FULL_NAM,
            NVL (h0.FST_NOM, h34.NOM_FST) call_me_as,
            NVL (c8iii.bas_cod, 500) - 500 branch_id,
            '1' division_id,
            p10.dpt_cod sec_cod,
            c8i.bas_cod dpt_cod,
            P10.CON_DTE,
            p10.jon_dte,
            1,
            DECODE (p10.ovt_flg, 'Y', 0, -1) ovt,
            -1 cpl,
            NULL ovt_hrs,
            0 pod_id,
            0 cat_id,
            32 emp_typ,
            NULL grp,
            0 rank_id,
            DECODE (h34.dut_flg, 0, 'Active', 1, 'Active', 'InActive') sta,
            NVL (h0.LTR_DTE, p10.jon_dte) arv_dte,
            h34.res_day,
            NVL (H0.DMD_SAL, grs.gross2) grs,
            1 flg
     FROM   pay.hrm_00_00 h0,
            pay.hrm_01_34 h34,
            pay.prl_10_01 p10,
            nml.emp_grs grs,
            nml.crp_00_08 c8,
            nml.crp_00_08 c8i,
            nml.crp_00_08 c8ii,
            nml.crp_00_08 c8iii
    WHERE       h0.emp_cod(+) = H34.emp_cod
            AND grs.emp_cod(+) = H34.emp_cod
            AND P10.DPT_COD = c8.bas_cod
            AND c8.bas_typ = 14
            AND c8i.bas_cod = c8.pnt_cod
            AND c8i.bas_typ = 13
            AND c8ii.bas_cod = c8i.pnt_cod
            AND c8ii.bas_typ = 12
            AND c8iii.bas_cod = c8ii.pnt_cod
            AND c8iii.bas_typ = 11
            AND p10.emp_cod = H34.emp_cod
            AND h34.prs_bas = bascod
            AND h34.emp_cod = empcod
            AND h34.dut_flg IN (0, 1);        dbms_output.put_line('step 4');
    End;
Re: DB link creating problem. [message #448071 is a reply to message #448011] Fri, 19 March 2010 06:35 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

when i run procdure in sql it is executed successfully,
SQL> 
SQL>  EXECUTE NML.PRL_PRO_UPD(2800101906,302);
step 1
step 2
step 3
starting
step 4
step 2
PL/SQL procedure successfully completed.
SQL> /


but when i run same in antoher session then follwing error
SQL>  EXECUTE NML.PRL_PRO_UPD(2800101906,302);
BEGIN NML.PRL_PRO_UPD(2800101906,302); END;

*
ERROR at line 1:
ORA-02068: following severe error from ATT_BHK
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-02063: preceding line from NML.COM
ORA-06512: at "NML.PRL_PRO_UPD", line 61
ORA-06512: at line 1


Now i can to know that the remote database which am accessing using db link is 8i while current database is 10g.

Whay may be problem.

Asif.
Re: DB link creating problem. [message #448074 is a reply to message #448071] Fri, 19 March 2010 06:45 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
ORA-00600: internal error code, arguments: [string], [string], [string], [string], [string], [string], [string], [string]
Cause: This is the generic internal error number for Oracle program exceptions. This indicates that a process has encountered an exceptional condition.
Action: Report as a bug - the first argument is the internal error number


Call Oracle Support.
You are connecting to 8i database using dblink with Oracle 10g right ?

sriram Smile
Re: DB link creating problem. [message #448078 is a reply to message #448011] Fri, 19 March 2010 06:56 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

yes ,I am connecting to 8i database using dblink with Oracle 10g .
Re: DB link creating problem. [message #448080 is a reply to message #448071] Fri, 19 March 2010 07:00 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
mamalik wrote on Fri, 19 March 2010 06:35
Now i can to know that the remote database which am accessing using db link is 8i while current database is 10g.

Do you, by any chance, have Oracle 9i database available? If so, try to create a database link which will have 9i as a "bridge" between 8i and 10g.
8i --DB link--> 9i --DB link--> 10g

Re: DB link creating problem. [message #448082 is a reply to message #448078] Fri, 19 March 2010 07:07 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before using search things, please to compatibility matrice between versions: Compatibility matrices and differences between editions.

Regards
Michel
Previous Topic: Preventing duplicate column value using Trigger
Next Topic: Column Reappears Again (merged)
Goto Forum:
  


Current Time: Sat Dec 03 14:09:22 CST 2016

Total time taken to generate the page: 0.11958 seconds