OraFAQ Forum: SQL & PL/SQL » problem in updating table

Home » SQL & PL/SQL » SQL & PL/SQL » problem in updating table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem in updating table [message #256366]

Sat, 04 August 2007 01:16

mk_agg1969
Messages: 19
Registered: August 2006
Location: india

Junior Member

Hi Champs,

I am not very good in tiggering the tables.I am facing problem which is described as uder:

I have two tables:
SQL> desc inv_grn_hdr;
Name Null? Type
------------------------------- -------- ----
CONTROL_NO NOT NULL VARCHAR2(12)
GRN_DATE NOT NULL DATE
PO_REFERENCE VARCHAR2(20)
GRN_TYPE NOT NULL VARCHAR2(5)
SUPPLIER_CODE VARCHAR2(5)
DEEC_TYPE CHAR(1)
INVOICE_NO NOT NULL VARCHAR2(25)
INVOICE_DATE NOT NULL DATE
INVOICE_TYPE CHAR(1)
INVOICE_LOT_QTY NUMBER(6)
EXCHANGE_RATE NUMBER(5,2)
BOM_CODE VARCHAR2(14)
BOE_NO VARCHAR2(20)
BOE_DATE DATE
BOE_LOT_QTY NUMBER(6)
CLR_AGT_CODE VARCHAR2(5)
CLR_AGT_BILL_NO VARCHAR2(12)
CLR_AGT_BILL_DATE DATE
CLR_AGT_BILL_AMT NUMBER(10,2)
BL_AWB_NO VARCHAR2(12)
BL_AWB_DATE DATE
TRANSPORTER_CODE VARCHAR2(5)
LR_NO VARCHAR2(12)
LR_DATE DATE
REMARKS VARCHAR2(60)
BOE_EXCHANGE_RATE NUMBER(9,4)
REMITT_EXCHANGE_RATE NUMBER(7,2)
LANDING_CHARGES_TAG CHAR(1)
LANDING_CHARGES_RATE NUMBER(7,3)
LANDING_CHARGES_VALUE NUMBER(13,2)
BASIC_CUSTOM_DUTY_VALUE NUMBER(13,2)
CESS_CUSTOM_DUTY_VALUE NUMBER(13,2)
CVD_VALUE NUMBER(13,2)
SPL_DUTY_VALUE NUMBER(13,2)
SUR_CHARGES_VALUE NUMBER(13,2)
CLEARING_CHARGES_TAG CHAR(1)
CLEARING_CHARGES_RATE NUMBER(7,3)
CLEARING_CHARGES_VALUE NUMBER(13,2)
WAREHOUSE_CHARGES_TAG CHAR(1)
WAREHOUSE_CHARGES_RATE NUMBER(7,3)
WAREHOUSE_CHARGES_VALUE NUMBER(13,2)
DO_CHARGES_TAG CHAR(1)
DO_CHARGES_RATE NUMBER(7,3)
DO_CHARGES_VALUE NUMBER(13,2)
CONTAINER_DETENTION_TAG CHAR(1)
CONTAINER_DETENTION_RATE NUMBER(7,3)
CONTAINER_DETENTION_VALUE NUMBER(13,2)
IMP_FREIGHT_CHARGES_TAG CHAR(1)
IMP_FREIGHT_CHARGES_RATE NUMBER(7,3)
IMP_FREIGHT_CHARGES_VALUE NUMBER(13,2)
IMP_INSURANCE_CHARGES_TAG CHAR(1)
IMP_INSURANCE_CHARGES_RATE NUMBER(7,3)
IMP_INSURANCE_CHARGES_VALUE NUMBER(13,2)
LOCAL_FREIGHT_CHARGES_TAG CHAR(1)
LOCAL_FREIGHT_CHARGES_RATE NUMBER(7,3)
LOCAL_FREIGHT_CHARGES_VALUE NUMBER(13,2)
IMP_STAMP_DUTY_CHARGES_TAG CHAR(1)
IMP_STAMP_DUTY_CHARGES_RATE NUMBER(7,3)
IMP_STAMP_DUTY_VALUE NUMBER(13,2)
DEPB_LICENSE_TAG CHAR(1)
DEPB_LICENSE_RATE NUMBER(7,3)
DEPB_LICENSE_VALUE NUMBER(13,2)
DEPB_PREMIUM_VALUE NUMBER(13,2)
DEPB_COMMISSION_VALUE NUMBER(13,2)
TRANSIT_INSURANCE_TAG CHAR(1)
TRANSIT_INSURANCE_RATE NUMBER(7,3)
TRANSIT_INSURANCE_VALUE NUMBER(13,2)
TOTAL_PURCHASE_VALUE NUMBER(13,2)
MISC_CHARGES_TAG CHAR(1)
MISC_CHARGES_RATE NUMBER(7,3)
MISC_CHARGES_VALUE NUMBER(13,2)
TOTAL_BASIC_VALUE NUMBER(17,2)
TOTAL_ASSESSABLE_VALUE NUMBER(17,2)
STAMP_ID VARCHAR2(5)
STAMP_DATE DATE
OTHER_MISC_EXP_TAG CHAR(1)
OTHER_MISC_EXP_RATE NUMBER(7,3)
OTHER_MISC_EXP_VALUE NUMBER(17,2)
DOM_FREIGHT_TAG CHAR(1)
DOM_FREIGHT_RATE NUMBER(7,3)
DOM_FREIGHT_VALUE NUMBER(17,2)
DOM_INSURANCE_TAG CHAR(1)
DOM_INSURANCE_RATE NUMBER(7,3)
DOM_INSURANCE_VALUE NUMBER(17,2)
DOM_SALESTAX_TAG CHAR(1)
DOM_SALESTAX_RATE NUMBER(7,3)
DOM_SALESTAX_VALUE NUMBER(17,2)
DOM_MISC_TAG CHAR(1)
DOM_MISC_RATE NUMBER(7,3)
DOM_MISC_VALUE NUMBER(17,2)
DOM_OTHEREXP_TAG CHAR(1)
DOM_OTHEREXP_RATE NUMBER(7,3)
DOM_OTHEREXP_VALUE NUMBER(17,2)
DOM_CVD_TAG CHAR(1)
DOM_CVD_RATE NUMBER(7,3)
DOM_CVD_VALUE NUMBER(17,2)
DOM_PFCHARGES_TAG CHAR(1)
DOM_PFCHARGES_RATE NUMBER(7,3)
DOM_PFCHARGES_VALUE NUMBER(17,2)
LOG_ID VARCHAR2(5)
LOG_DATE DATE
TERMINAL_ID VARCHAR2(20)
ACTION_TAG CHAR(1)
VCHR_NO NUMBER(6)
BOOK_TYPE CHAR(2)
EDU_CESS_CVD_RATE NUMBER(7,3)
EDU_CESS_CVD_VALUE NUMBER(17,2)
CE_CESS_RATE NUMBER(7,3)
CE_CESS_VALUE NUMBER(17,2)
PO_CTRL_NO VARCHAR2(20)
BILL_VALUE NUMBER(17,2)
EDU_CESS_CVD_TAG CHAR(1)
ROUNDING_OFF NUMBER(17,2)
VALUED_FLAG CHAR(1)
PAY_REF_NO VARCHAR2(20)
PAY_REF_DATE DATE
PJ_POST_NO NUMBER( Cool

AC_REF_NO VARCHAR2(10)
QC_APPLICABLE CHAR(1)
QC_COMPLETED CHAR(1)
PJ_VALID CHAR(1)
PJ_POST CHAR(1)
DOLLAR_VALUE NUMBER(17,4)
LOCKED CHAR(1)
SH_CESS_RATE NUMBER(5,2)
SH_CESS_VALUE NUMBER(13,2)
SH_EDU_CESS_RATE NUMBER(5,2)
SH_EDU_CESS_VALUE NUMBER(13,2)
AUTO_ID VARCHAR2(5)
INC_BKUP CHAR(1)
BKUP CHAR(1)

SQL> desc inv_grn_dtl;
Name Null? Type
------------------------------- -------- ----
CONTROL_NO NOT NULL VARCHAR2(12)
PART_CODE NOT NULL VARCHAR2(14)
CHALLAN_QTY NOT NULL NUMBER(14,3)
RECD_QTY NOT NULL NUMBER(14,3)
BOE_EXCHANGE_RATE NUMBER(9,4)
REMITT_EXCHANGE_RATE NUMBER(7,2)
UNIT_RATE NUMBER(17,5)
BASIC_VALUE NUMBER(13,2)
ASSESSABLE_VALUE NUMBER(13,2)
DOLLAR_VALUE NUMBER(10,4)
LANDING_CHARGES_TAG CHAR(1)
LANDING_CHARGES_RATE NUMBER(7,3)
LANDING_CHARGES_VALUE NUMBER(13,2)
BASIC_CUSTOM_DUTY_TAG CHAR(1)
BASIC_CUSTOM_DUTY_RATE NUMBER(7,3)
BASIC_CUSTOM_DUTY_VALUE NUMBER(13,2)
CESS_CUSTOM_DUTY_TAG CHAR(1)
CESS_CUSTOM_DUTY_RATE NUMBER(7,3)
CESS_CUSTOM_DUTY_VALUE NUMBER(13,2)
CVD_TAG CHAR(1)
CVD_RATE NUMBER(7,3)
CVD_VALUE NUMBER(13,2)
SPL_DUTY_TAG CHAR(1)
SPL_DUTY_RATE NUMBER(7,3)
SPL_DUTY_VALUE NUMBER(13,2)
SUR_CHARGES_TAG CHAR(1)
SUR_CHARGES_RATE NUMBER(7,3)
SUR_CHARGES_VALUE NUMBER(13,2)
CLEARING_CHARGES_TAG CHAR(1)
CLEARING_CHARGES_RATE NUMBER(7,3)
CLEARING_CHARGES_VALUE NUMBER(13,2)
WAREHOUSE_CHARGES_TAG CHAR(1)
WAREHOUSE_CHARGES_RATE NUMBER(7,3)
WAREHOUSE_CHARGES_VALUE NUMBER(13,2)
DO_CHARGES_TAG CHAR(1)
DO_CHARGES_RATE NUMBER(7,3)
DO_CHARGES_VALUE NUMBER(13,2)
CONTAINER_DETENTION_TAG CHAR(1)
CONTAINER_DETENTION_RATE NUMBER(7,3)
CONTAINER_DETENTION_VALUE NUMBER(13,2)
IMP_FREIGHT_CHARGES_TAG CHAR(1)
IMP_FREIGHT_CHARGES_RATE NUMBER(7,3)
IMP_FREIGHT_CHARGES_VALUE NUMBER(13,2)
IMP_INSURANCE_CHARGES_TAG CHAR(1)
IMP_INSURANCE_CHARGES_RATE NUMBER(7,3)
IMP_INSURANCE_CHARGES_VALUE NUMBER(13,2)
LOCAL_FREIGHT_CHARGES_TAG CHAR(1)
LOCAL_FREIGHT_CHARGES_RATE NUMBER(7,3)
LOCAL_FREIGHT_CHARGES_VALUE NUMBER(13,2)
IMP_STAMP_DUTY_CHARGES_TAG CHAR(1)
IMP_STAMP_DUTY_CHARGES_RATE NUMBER(7,3)
IMP_STAMP_DUTY_VALUE NUMBER(13,2)
DEPB_LICENSE_TAG CHAR(1)
DEPB_LICENSE_RATE NUMBER(7,3)
DEPB_LICENSE_VALUE NUMBER(13,2)
DEPB_LICENSE_NO VARCHAR2(20)
DEPB_PREMIUM_VALUE NUMBER(13,2)
DEPB_COMMISSION_VALUE NUMBER(13,2)
TRANSIT_INSURANCE_TAG CHAR(1)
TRANSIT_INSURANCE_RATE NUMBER(7,3)
TRANSIT_INSURANCE_VALUE NUMBER(13,2)
PART_COST NUMBER(13,5)
PART_VALUE NUMBER(13,2)
MISC_CHARGES_TAG CHAR(1)
MISC_CHARGES_RATE NUMBER(7,3)
MISC_CHARGES_VALUE NUMBER(13,2)
PW_DATE DATE
DOM_FREIGHT_TAG CHAR(1)
DOM_FREIGHT_RATE NUMBER(7,3)
DOM_FREIGHT_VALUE NUMBER(17,2)
DOM_INSURANCE_TAG CHAR(1)
DOM_INSURANCE_RATE NUMBER(7,3)
DOM_INSURANCE_VALUE NUMBER(17,2)
DOM_SALESTAX_TAG CHAR(1)
DOM_SALESTAX_RATE NUMBER(7,3)
DOM_SALESTAX_VALUE NUMBER(17,2)
DOM_MISC_TAG CHAR(1)
DOM_MISC_RATE NUMBER(7,3)
DOM_MISC_VALUE NUMBER(17,2)
DOM_OTHEREXP_TAG CHAR(1)
DOM_OTHEREXP_RATE NUMBER(7,3)
DOM_OTHEREXP_VALUE NUMBER(17,2)
DOM_CVD_TAG CHAR(1)
DOM_CVD_RATE NUMBER(7,3)
DOM_CVD_VALUE NUMBER(17,2)
OTHER_MISC_EXP_TAG CHAR(1)
OTHER_MISC_EXP_RATE NUMBER(7,3)
OTHER_MISC_EXP_VALUE NUMBER(17,2)
DOM_PFCHARGES_TAG CHAR(1)
DOM_PFCHARGES_RATE NUMBER(7,3)
DOM_PFCHARGES_VALUE NUMBER(17,2)
FOREIGN_VALUE NUMBER(13,2)
EDU_CESS_CVD_RATE NUMBER(7,3)
EDU_CESS_CVD_VALUE NUMBER(17,2)
CE_CESS_RATE NUMBER(7,3)
CE_CESS_VALUE NUMBER(17,2)
PRTY_AMOUNT NUMBER(17,2)
REQ_QTY NUMBER(14,3)
T_PART_COST NUMBER(13,5)
LICENSE_UTL_QTY NUMBER(14,3)
LICENSE_UTL_VALUE NUMBER(17,2)
LICENSE_UTL_DUTY NUMBER(17,2)
REJ_QTY NUMBER(14,3)
PO_BALANCE_QTY NUMBER(14,3)
DEPB_UTL_VALUE NUMBER(17,2)
ADV_UTL_VALUE NUMBER(17,2)
EPCG_UTL_VALUE NUMBER(17,2)
TPLUS_UTL_VALUE NUMBER(17,2)
LICENSE_TYPE CHAR(1)
LEDGER_CODE NUMBER( Cool

ACC_GRP NUMBER( Cool

CAL_UNIT NUMBER( Cool

BAL_QTY NUMBER(14,3)
SH_CESS_RATE NUMBER(5,2)
SH_CESS_VALUE NUMBER(13,2)
SH_EDU_CESS_RATE NUMBER(5,2)
SH_EDU_CESS_VALUE NUMBER(13,2)

I was given the assignment to update bkup column of inv_grn_hdr if any user changes any field of inv_grn_hdr or inv_grn_dtl. I have tried with trigger on insert or update, but faced mutation error.

After searching the solution I have done the following :

Create or replace package grn_pkg_try is
grn_var_try varchar2(12);
end grn_pkg_try;

create or replace trigger grn_trig_row_try before insert or update on inv_grn_hdr for each row
begin
grn_pkg_try.grn_var_try := :new.control_no;
End;

Create or replace trigger grn_trig_stat_try after insert or update on inv_grn_hdr
begin
update inv_grn_hdr set bkup = 'Y' where control_no = grn_pkg_try.grn_var_try;
end;

Now oracle gives me error like:
ORA-03113: end-of-file on communication channel

Please help me.

Regards,

Manoj Aggarwal

Report message to a moderator

Re: problem in updating table [message #256376 is a reply to message #256366]

Sat, 04 August 2007 01:44

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

First,
Read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Always post your Oracle version (4 decimals).

Then, 3113 is a generic error, often pointing to a bug and the standard answer is: search on Metalin and/or call Oracle support.

Regards
Michel

Report message to a moderator

Previous Topic:	Listing the contents of the scott/tiger schema
Next Topic:	delete progress

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 21:50:34 CST 2024