Home » Server Options » Text & interMedia » while updating error recursive SQL level 1 (Oracle 11G)
while updating error recursive SQL level 1 [message #590349] Wed, 17 July 2013 08:15 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

I have executed the following update statement,
it's executed successfully.
But once I commit it's giving the below error.
Why I am getting this error.
Please provide the resolution.

UPDATE APPS_NL.GEDIS_CONTACT 
SET LAST_UPDATE_DATE = TZ.local_to_db_date(TO_DATE('7/17/2013 11:20:19 AM','MM/dd/yyyy hh:mi:ss AM')), 
EMAIL = 'test@dell.com', 
SUBSCRIBE_TO_CFO = 'N' 
WHERE CONTACT_ID= 90005911;

ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SYNCRN'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


CREATE OR REPLACE procedure CTXSYS.syncrn (
  ownid IN binary_integer,
  oname IN varchar2,
  idxid IN binary_integer,
  ixpid IN binary_integer,
  rtabnm IN varchar2,
  srcflg IN binary_integer
)
  authid definer
  as external
  name "comt_cb"
  library dr$lib
  with context
  parameters(
    context,
    ownid  ub4,
    oname  OCISTRING,
    idxid  ub4,
    ixpid  ub4,
    rtabnm OCISTRING,
    srcflg ub1
);
/


Please help me.

Thanks in advance.
Re: while updating error recursive SQL level 1 [message #590350 is a reply to message #590349] Wed, 17 July 2013 08:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

COPY & PASTE the whole session including the invocation of SYNCRN procedure
Re: while updating error recursive SQL level 1 [message #590371 is a reply to message #590350] Wed, 17 July 2013 09:20 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

Sorry for violating posting guidelines.
I have attached session info.
I have extracted the session information using the below query.
SELECT saddr,sid,serial#,user#,username,lockwait,status,
schemaname,type,SQL_EXEC_START,PREV_EXEC_START,logon_time,BLOCKING_SESSION_STATUS FROM v$session
WHERE TRUNC(logon_time)= '17-JUL-2013'



I forgot one thing to post.
Before update statement I am using.

ALTER SESSION SET CURRENT_SCHEMA = APPS_NL;


Please help me.
Thanks.
Re: while updating error recursive SQL level 1 [message #590374 is a reply to message #590371] Wed, 17 July 2013 09:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT what we asked.
We asked to:
1/ COPY & PASTE the whole session including the invocation of SYNCRN procedure
2/ Read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
3/ Post accordingly
4/ Don't feel sorry, just do it

Regards
Michel
Re: while updating error recursive SQL level 1 [message #590376 is a reply to message #590371] Wed, 17 July 2013 09:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Blackswan wanted you to copy and paste the code you were running to get that error from sqlplus and the exact respsonse oracle gave. Queries of v$session aren't much use here.

We have zero idea what code is calling the procedure. We can't find it for you as we do not have access to your DB. You need to find it.
Re: while updating error recursive SQL level 1 [message #590400 is a reply to message #590376] Wed, 17 July 2013 13:07 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

I have executed this statement.

UPDATE APPS_NL.GEDIS_CONTACT  
SET LAST_UPDATE_DATE =TO_DATE('7/17/2013 11:20:19 AM','MM/dd/yyyy hh:mi:ss AM'),  
EMAIL = 'test@dell.com',  
SUBSCRIBE_TO_CFO = 'N'  
WHERE CONTACT_ID= 90005911;  
commit;

1 row updated.
commit
Error at line 6
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SYNCRN'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


I am providing desc of APPS_NL.GEDIS_CONTACT table.
Some triggers are created on this table.
But no trigger is calling this procedure SYNCRN.
I have checked this by using below query.It's returning only SYNCRN procedure.

SELECT * FROM DBA_SOURCE WHERE UPPER(TEXT) LIKE '%SYNCRN%'

CREATE TABLE APPS_NL.GEDIS_CONTACT
(
  CONTACT_ID               NUMBER(20),
  LAST_UPDATE_DATE         DATE                 NOT NULL,
  LAST_UPDATED_BY          NUMBER(15)           NOT NULL,
  CREATION_DATE            DATE                 NOT NULL,
  CREATED_BY               NUMBER(15)           NOT NULL,
  ORIG_SYSTEM_CONTACT_REF  VARCHAR2(240 CHAR),
  ADDRESS_ID               NUMBER(20)           NOT NULL,
  TITLE                    VARCHAR2(25 CHAR),
  FIRST_NAME               VARCHAR2(40 CHAR),
  LAST_NAME                VARCHAR2(50 CHAR)    NOT NULL,
  EMAIL                    VARCHAR2(240 CHAR),
  DEPARTMENT               VARCHAR2(60 CHAR),
  INTERFACE_REQUIRED       VARCHAR2(1 CHAR)     DEFAULT 'N'                   NOT NULL,
  PRIMARY                  VARCHAR2(1 CHAR),
  UPPER_LAST_NAME          VARCHAR2(50 CHAR),
  MAIL_FLAG                VARCHAR2(1 CHAR),
  FAX_FLAG                 VARCHAR2(1 CHAR),
  EMAIL_FLAG               VARCHAR2(1 CHAR),
  MARKETING_LANGUAGE_CODE  VARCHAR2(20 CHAR),
  ITEM_LANGUAGE_CODE       VARCHAR2(20 CHAR),
  UPPER_FIRST_NAME         VARCHAR2(40 CHAR),
  INACTIVE                 VARCHAR2(1 CHAR)     DEFAULT 'N',
  CREATED_BY_NT            VARCHAR2(30 CHAR),
  LAST_UPDATED_BY_NT       VARCHAR2(30 CHAR),
  ORIG_SYSTEM              VARCHAR2(30 CHAR),
  LAST_UPDATED_BY_SYSTEM   VARCHAR2(30 CHAR),
  EN_FIRST_NAME            VARCHAR2(40 CHAR),
  EN_LAST_NAME             VARCHAR2(50 CHAR),
  LAST_UPDATE_DATE_UTC     DATE                 DEFAULT null,
  CREATION_DATE_UTC        DATE                 DEFAULT null,
  JP_FIRST_NAME            VARCHAR2(40 CHAR),
  JP_LAST_NAME             VARCHAR2(50 CHAR),
  PHONE_FLAG               VARCHAR2(1 CHAR),
  SUBSCRIBE_TO_CFO         VARCHAR2(1 CHAR)
);

CREATE INDEX APPS_NL.GEDIS_CONTACT_N1 ON APPS_NL.GEDIS_CONTACT(ADDRESS_ID);
CREATE INDEX APPS_NL.GEDIS_CONTACT_N2 ON APPS_NL.GEDIS_CONTACT(LAST_NAME);
CREATE INDEX APPS_NL.GEDIS_CONTACT_N3 ON APPS_NL.GEDIS_CONTACT(UPPER_LAST_NAME);
CREATE INDEX APPS_NL.GEDIS_CONTACT_N4 ON APPS_NL.GEDIS_CONTACT(ORIG_SYSTEM_CONTACT_REF);
CREATE INDEX APPS_NL.GEDIS_CONTACT_N5 ON APPS_NL.GEDIS_CONTACT(LAST_UPDATE_DATE);
CREATE INDEX APPS_NL.GEDIS_CONTACT_N6 ON APPS_NL.GEDIS_CONTACT(UPPER_FIRST_NAME);
CREATE UNIQUE INDEX APPS_NL.PK_GEDIS_CONTACT ON APPS_NL.GEDIS_CONTACT(CONTACT_ID);


CREATE OR REPLACE TRIGGER APPS_NL."TR_GEDIS_CONTACT_DEL" 
before DELETE
ON GEDIS_contact
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
  delete from gedis_customer_summary
    where contact_id = :old.contact_id;
END ;
/


CREATE OR REPLACE TRIGGER APPS_NL."GEDIS_CONTACT_AUI" 
  AFTER INSERT OR UPDATE
  ON APPS_NL.GEDIS_CONTACT   FOR EACH ROW
DECLARE
  v_type VARCHAR2(6);
  v_save BOOLEAN;
BEGIN
  IF INSERTING THEN
    v_type := 'INSERT';
    v_save := TRUE;
  ELSE
    v_type := 'UPDATE';

    IF  --(:NEW.LAST_UPDATE_DATE - :OLD.LAST_UPDATE_DATE < 1/86400)
    /*OR*/ (:NEW.CREATED_BY_NT = :OLD.CREATED_BY_NT OR (:NEW.CREATED_BY_NT IS NULL AND :OLD.CREATED_BY_NT IS NULL))
    AND (:NEW.LAST_UPDATED_BY_NT = :OLD.LAST_UPDATED_BY_NT OR (:NEW.LAST_UPDATED_BY_NT IS NULL AND :OLD.LAST_UPDATED_BY_NT IS NULL))
    AND (:NEW.ORIG_SYSTEM = :OLD.ORIG_SYSTEM OR (:NEW.ORIG_SYSTEM IS NULL AND :OLD.ORIG_SYSTEM IS NULL))
    AND (:NEW.LAST_UPDATED_BY_SYSTEM = :OLD.LAST_UPDATED_BY_SYSTEM OR (:NEW.LAST_UPDATED_BY_SYSTEM IS NULL AND :OLD.LAST_UPDATED_BY_SYSTEM IS NULL))
    AND (:NEW.EN_FIRST_NAME = :OLD.EN_FIRST_NAME OR (:NEW.EN_FIRST_NAME IS NULL AND :OLD.EN_FIRST_NAME IS NULL))
    AND (:NEW.EN_LAST_NAME = :OLD.EN_LAST_NAME OR (:NEW.EN_LAST_NAME IS NULL AND :OLD.EN_LAST_NAME IS NULL))
    AND (:NEW.CONTACT_ID = :OLD.CONTACT_ID OR (:NEW.CONTACT_ID IS NULL AND :OLD.CONTACT_ID IS NULL))
    AND (:NEW.LAST_UPDATED_BY = :OLD.LAST_UPDATED_BY OR (:NEW.LAST_UPDATED_BY IS NULL AND :OLD.LAST_UPDATED_BY IS NULL))
    AND (:NEW.CREATION_DATE = :OLD.CREATION_DATE OR (:NEW.CREATION_DATE IS NULL AND :OLD.CREATION_DATE IS NULL))
    AND (:NEW.CREATED_BY = :OLD.CREATED_BY OR (:NEW.CREATED_BY IS NULL AND :OLD.CREATED_BY IS NULL))
    AND (:NEW.ORIG_SYSTEM_CONTACT_REF = :OLD.ORIG_SYSTEM_CONTACT_REF OR (:NEW.ORIG_SYSTEM_CONTACT_REF IS NULL AND :OLD.ORIG_SYSTEM_CONTACT_REF IS NULL))
    AND (:NEW.ADDRESS_ID = :OLD.ADDRESS_ID OR (:NEW.ADDRESS_ID IS NULL AND :OLD.ADDRESS_ID IS NULL))
    AND (:NEW.TITLE = :OLD.TITLE OR (:NEW.TITLE IS NULL AND :OLD.TITLE IS NULL))
    AND (:NEW.FIRST_NAME = :OLD.FIRST_NAME OR (:NEW.FIRST_NAME IS NULL AND :OLD.FIRST_NAME IS NULL))
    AND (:NEW.LAST_NAME = :OLD.LAST_NAME OR (:NEW.LAST_NAME IS NULL AND :OLD.LAST_NAME IS NULL))
    AND (:NEW.EMAIL = :OLD.EMAIL OR (:NEW.EMAIL IS NULL AND :OLD.EMAIL IS NULL))
    AND (:NEW.DEPARTMENT = :OLD.DEPARTMENT OR (:NEW.DEPARTMENT IS NULL AND :OLD.DEPARTMENT IS NULL))
    AND (:NEW.INTERFACE_REQUIRED = :OLD.INTERFACE_REQUIRED OR (:NEW.INTERFACE_REQUIRED IS NULL AND :OLD.INTERFACE_REQUIRED IS NULL))
    AND (:NEW.PRIMARY = :OLD.PRIMARY OR (:NEW.PRIMARY IS NULL AND :OLD.PRIMARY IS NULL))
    AND (:NEW.UPPER_LAST_NAME = :OLD.UPPER_LAST_NAME OR (:NEW.UPPER_LAST_NAME IS NULL AND :OLD.UPPER_LAST_NAME IS NULL))
    AND (:NEW.MAIL_FLAG = :OLD.MAIL_FLAG OR (:NEW.MAIL_FLAG IS NULL AND :OLD.MAIL_FLAG IS NULL))
    AND (:NEW.FAX_FLAG = :OLD.FAX_FLAG OR (:NEW.FAX_FLAG IS NULL AND :OLD.FAX_FLAG IS NULL))
    AND (:NEW.EMAIL_FLAG = :OLD.EMAIL_FLAG OR (:NEW.EMAIL_FLAG IS NULL AND :OLD.EMAIL_FLAG IS NULL))
    AND (:NEW.MARKETING_LANGUAGE_CODE = :OLD.MARKETING_LANGUAGE_CODE OR (:NEW.MARKETING_LANGUAGE_CODE IS NULL AND :OLD.MARKETING_LANGUAGE_CODE IS NULL))
    AND (:NEW.ITEM_LANGUAGE_CODE = :OLD.ITEM_LANGUAGE_CODE OR (:NEW.ITEM_LANGUAGE_CODE IS NULL AND :OLD.ITEM_LANGUAGE_CODE IS NULL))
    AND (:NEW.UPPER_FIRST_NAME = :OLD.UPPER_FIRST_NAME OR (:NEW.UPPER_FIRST_NAME IS NULL AND :OLD.UPPER_FIRST_NAME IS NULL))
    AND (:NEW.INACTIVE = :OLD.INACTIVE OR (:NEW.INACTIVE IS NULL AND :OLD.INACTIVE IS NULL)) THEN
      v_save := FALSE;
    ELSE
      v_save := TRUE;
    END IF;
  END IF;

  IF v_save THEN
    xxeom_order_queue_buf.ddw_insert_row('CONTACT'
                                       , :NEW.CONTACT_ID
                                       , /*:NEW.LAST_UPDATE_DATE D-14925*/ SYSDATE
                                       , :NEW.LAST_UPDATED_BY
                                       , v_type);
  END IF;
END;
/


CREATE OR REPLACE TRIGGER APPS_NL.GEDIS_CONTACT_ARU_GDAS
    after update ON APPS_NL.GEDIS_CONTACT for each row
WHEN (
not(
         (old.CONTACT_ID    = new.CONTACT_ID    or (old.CONTACT_ID    is NULL and new.CONTACT_ID    is NULL))
     and (old.TITLE         = new.TITLE         or (old.TITLE         is NULL and new.TITLE         is NULL))
     and (old.FIRST_NAME    = new.FIRST_NAME    or (old.FIRST_NAME    is NULL and new.FIRST_NAME    is NULL))
     and (old.LAST_NAME     = new.LAST_NAME     or (old.LAST_NAME     is NULL and new.LAST_NAME     is NULL))
     and (old.EN_FIRST_NAME = new.EN_FIRST_NAME or (old.EN_FIRST_NAME is NULL and new.EN_FIRST_NAME is NULL))
     and (old.EN_LAST_NAME  = new.EN_LAST_NAME  or (old.EN_LAST_NAME  is NULL and new.EN_LAST_NAME  is NULL))
     and (old.PRIMARY       = new.PRIMARY       or (old.PRIMARY       is NULL and new.PRIMARY       is NULL))
     and (old.EMAIL         = new.EMAIL         or (old.EMAIL         is NULL and new.EMAIL         is NULL))
     and (old.INACTIVE      = new.INACTIVE      or (old.INACTIVE      is NULL and new.INACTIVE      is NULL))
    )
      )
begin
    update GEDIS_CUSTOMER u
       set u.LAST_PUBLISH_TIME = systimestamp at time zone 'GMT'
     where u.FIRST_BOOKED_ORDER_DATE is not NULL
       and u.CUSTOMER_ID in (select a.CUSTOMER_ID
                               from GEDIS_ADDRESS a
                              where a.ADDRESS_ID = :new.ADDRESS_ID)
    ;
end;
/


CREATE OR REPLACE TRIGGER APPS_NL.GEDIS_CONTACT_CHUB_AUI
  AFTER UPDATE ON APPS_NL.GEDIS_CONTACT   FOR EACH ROW
DECLARE
   V_PHONE_ID NUMBER;
BEGIN
   -- Check is the Customer HUB Interface enabled
   IF XXEOM_ORDER_QUEUE_BUF.GET_GEDIS_BUCONFIG_PARAMETER('ENABLE_CUSTOMER_HUB') = 'Y' THEN
      NULL;
   ELSE
      RETURN;
   END IF;
   -- Check Inactive changes
   IF  UPPER ( nvl( :NEW.INACTIVE, '-11111' ) ) !=
       UPPER ( nvl( :OLD.INACTIVE, '-11111' ) )
   THEN
     NULL;
   ELSE
     RETURN;
   END IF;
   BEGIN
        -- Get Phone Identifier for INT1/2 queue type
        SELECT    PHONE_ID
           INTO   V_PHONE_ID
           FROM   GEDIS_TELEPHONE
          WHERE
                  CONTACT_ID = :NEW.CONTACT_ID
              AND PRIMARY    = 'Y';
             -- Message for Customer Hub
             XXEOM_ORDER_QUEUE_BUF.ADD_MESSAGE
                       (
                          P_MESSAGE_TYPE => 'CHUB',
                          P_PHONE_ID     => V_PHONE_ID
                       );
   EXCEPTION
         WHEN OTHERS THEN
           xxeom_Order_queue_buf.Fix_Error_In_Audit(
               p_Interface => audit_interface.Interface_Customer,
               p_Consumer_name => 'INT1_CHUB',
               p_Error_Text =>
               'Contact_id: '||:NEW.CONTACT_ID||
               ',p_phone_id: '||V_PHONE_ID||'. Stage: '||
               'TRIGGER GEDIS_CONTACT_CHUB_AUI'||'. Error: '||SQLERRM);
   END;
END GEDIS_CONTACT_CHUB_AUI;
/


CREATE OR REPLACE TRIGGER APPS_NL.TR_GEDIS_CONTACT_UPD
AFTER UPDATE
OF ORIG_SYSTEM_CONTACT_REF
  ,FIRST_NAME
  ,LAST_NAME
  ,UPPER_LAST_NAME
  ,UPPER_FIRST_NAME
  ,INACTIVE
  ,EN_FIRST_NAME
  ,EN_LAST_NAME
  ,EMAIL
  ,JP_FIRST_NAME
  ,JP_LAST_NAME
ON APPS_NL.GEDIS_CONTACT REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
  UPDATE GEDIS_CUSTOMER_SUMMARY
     SET ORIG_SYSTEM_CONTACT_REF = :new.ORIG_SYSTEM_CONTACT_REF
        ,FIRST_NAME = :new.FIRST_NAME
        ,LAST_NAME = :new.LAST_NAME
        ,UPPER_LAST_NAME = :new.UPPER_LAST_NAME
        ,UPPER_FIRST_NAME = :new.UPPER_FIRST_NAME
        ,CONTACT_INACTIVE = DECODE(:new.INACTIVE, 'Y', 'Y', 'N')
        ,EN_FIRST_NAME = :new.EN_FIRST_NAME
        ,EN_LAST_NAME = :new.EN_LAST_NAME
        ,UPPER_EN_FIRST_NAME = UPPER(:new.EN_FIRST_NAME)
        ,UPPER_EN_LAST_NAME = UPPER(:new.EN_LAST_NAME)
      --,OT_DUMMY = null
        ,EMAIL = :new.EMAIL
        ,UPPER_EMAIL = UPPER(:new.EMAIL)
        ,JP_FIRST_NAME = :new.JP_FIRST_NAME
        ,JP_LAST_NAME = :new.JP_LAST_NAME
   WHERE CONTACT_ID = :old.CONTACT_ID;
END ;
/


ALTER TABLE APPS_NL.GEDIS_CONTACT ADD (
  CONSTRAINT PK_GEDIS_CONTACT
 PRIMARY KEY
 (CONTACT_ID)
    USING INDEX 
    TABLESPACE XXDELL_TS_TX_IDX
    PCTFREE    10
    INITRANS   11
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

AUDIT DELETE ON APPS_NL.GEDIS_CONTACT BY SESSION WHENEVER SUCCESSFUL;
AUDIT DELETE ON APPS_NL.GEDIS_CONTACT BY SESSION WHENEVER NOT SUCCESSFUL;
AUDIT UPDATE ON APPS_NL.GEDIS_CONTACT BY SESSION WHENEVER SUCCESSFUL;
AUDIT UPDATE ON APPS_NL.GEDIS_CONTACT BY SESSION WHENEVER NOT SUCCESSFUL;

GRANT SELECT ON APPS_NL.GEDIS_CONTACT TO ALL_BU_READ_ONLY;

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON APPS_NL.GEDIS_CONTACT TO APPS_GLOBAL;

GRANT DELETE, INSERT, SELECT, UPDATE ON APPS_NL.GEDIS_CONTACT TO GDAS_USER;

GRANT SELECT ON APPS_NL.GEDIS_CONTACT TO GEDIS_READ_ONLY;

GRANT INSERT, SELECT, UPDATE ON APPS_NL.GEDIS_CONTACT TO GSL_USER;

GRANT SELECT ON APPS_NL.GEDIS_CONTACT TO SQL_USER;


Please help me.

Thanks.
Re: while updating error recursive SQL level 1 [message #590404 is a reply to message #590400] Wed, 17 July 2013 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have checked this by using below query.It's returning only SYNCRN procedure.

SELECT * FROM DBA_SOURCE WHERE UPPER(TEXT) LIKE '%SYNCRN%'


Some sources are wrapped and so you can't see them.
Check dba_dependencies instead.

Regards
Michel
Re: while updating error recursive SQL level 1 [message #590419 is a reply to message #590404] Thu, 18 July 2013 01:43 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

I have got below output.

SELECT * FROM dba_dependencies WHERE name='SYNCRN'

OWNER      NAME   TYPE         REFERENCED_OWNER    REFERENCED_NAME               REFERENCED_TYPE    REFERENCED_LINK_NAME   DEPENDENCY_TYPE
-------------- ------------------------------ ------------------ ----------------------------------------- -------------------------------- 
CTXSYS    SYNCRN    PROCEDURE   SYS                 STANDARD                         PACKAGE                                HARD
CTXSYS    SYNCRN    PROCEDURE   SYS                 SYS_STUB_FOR_PURITY_ANALYSIS     PACKAGE                                HARD
CTXSYS    SYNCRN    PROCEDURE   CTXSYS              DR$LIB                           LIBRARY                                HARD


Please help me.

Thanks.
Re: while updating error recursive SQL level 1 [message #590420 is a reply to message #590419] Thu, 18 July 2013 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to search "where REFERENCED_NAME='SYNCRN'".

Regards
Michel
Re: while updating error recursive SQL level 1 [message #590421 is a reply to message #590419] Thu, 18 July 2013 02:33 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi All,

If I disable the below trigger I am not getting any error.
Why this trigger is causing problem.

CREATE OR REPLACE TRIGGER APPS_NL.TR_GEDIS_CONTACT_UPD
AFTER UPDATE
OF ORIG_SYSTEM_CONTACT_REF
  ,FIRST_NAME
  ,LAST_NAME
  ,UPPER_LAST_NAME
  ,UPPER_FIRST_NAME
  ,INACTIVE
  ,EN_FIRST_NAME
  ,EN_LAST_NAME
  ,EMAIL
  ,JP_FIRST_NAME
  ,JP_LAST_NAME
ON APPS_NL.GEDIS_CONTACT REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DISABLE
DECLARE
BEGIN
  UPDATE GEDIS_CUSTOMER_SUMMARY
     SET ORIG_SYSTEM_CONTACT_REF = :new.ORIG_SYSTEM_CONTACT_REF
        ,FIRST_NAME = :new.FIRST_NAME
        ,LAST_NAME = :new.LAST_NAME
        ,UPPER_LAST_NAME = :new.UPPER_LAST_NAME
        ,UPPER_FIRST_NAME = :new.UPPER_FIRST_NAME
        ,CONTACT_INACTIVE = DECODE(:new.INACTIVE, 'Y', 'Y', 'N')
        ,EN_FIRST_NAME = :new.EN_FIRST_NAME
        ,EN_LAST_NAME = :new.EN_LAST_NAME
        ,UPPER_EN_FIRST_NAME = UPPER(:new.EN_FIRST_NAME)
        ,UPPER_EN_LAST_NAME = UPPER(:new.EN_LAST_NAME)
      --,OT_DUMMY = null
        ,EMAIL = :new.EMAIL
        ,UPPER_EMAIL = UPPER(:new.EMAIL)
        ,JP_FIRST_NAME = :new.JP_FIRST_NAME
        ,JP_LAST_NAME = :new.JP_LAST_NAME
   WHERE CONTACT_ID = :old.CONTACT_ID;
END ;
/

Please help me.

Thanks in advance.
Re: while updating error recursive SQL level 1 [message #590422 is a reply to message #590421] Thu, 18 July 2013 02:38 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Is there a trigger on GEDIS_CUSTOMER_SUMMARY?
Re: while updating error recursive SQL level 1 [message #590423 is a reply to message #590422] Thu, 18 July 2013 02:42 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And what does dba_dependencies show when you use the correct where clause (see Michel's post above)?
Re: while updating error recursive SQL level 1 [message #590424 is a reply to message #590423] Thu, 18 July 2013 03:02 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

SELECT * FROM dba_dependencies WHERE REFERENCED_NAME='SYNCRN'


No rows returned.

Yes trigger on GEDIS_CUSTOMER_SUMMARY.

Please help me.

Thanks.
Re: while updating error recursive SQL level 1 [message #590425 is a reply to message #590424] Thu, 18 July 2013 03:13 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

This is the trigger created on GEDIS_CUSTOMER_SUMMARY table.

CREATE OR REPLACE TRIGGER APPS_NL.TR_OT_GCS_IDX_REFRESH
    before update of
        CUSTOMER_ID,
        OMEGA_CUSTOMER_NUMBER,
        CUSTOMER_NAME,
        ADDRESS1,
        ADDRESS2,
        FIRST_NAME,
        LAST_NAME,
        CITY,
        POSTAL_CODE,
        TELEPHONE,
        EN_CUSTOMER_NAME,
        EN_CUSTOMER_NAME_EXT,
        EN_CUSTOMER_NAME_EXT2,
        EN_ADDRESS1,
        EN_ADDRESS2,
        EN_COUNTRY,
        EN_COUNTY,
        EN_CITY,
        EN_FIRST_NAME,
        EN_LAST_NAME,
        EMAIL,
        CUSTOMER_NAME_EXT,
        JP_ADDRESS1,
        JP_ADDRESS2,
        JP_ADDRESS3,
        JP_FIRST_NAME,
        JP_LAST_NAME,
        ORIG_SYSTEM_CUSTOMER_REF,
        ORIG_SYSTEM_ADDRESS_REF
    ON APPS_NL.GEDIS_CUSTOMER_SUMMARY     for each row
WHEN (
decode(new.CUSTOMER_ID             , old.CUSTOMER_ID             , 'N', 'Y') = 'Y'
       or decode(new.OMEGA_CUSTOMER_NUMBER   , old.OMEGA_CUSTOMER_NUMBER   , 'N', 'Y') = 'Y'
       or decode(new.CUSTOMER_NAME           , old.CUSTOMER_NAME           , 'N', 'Y') = 'Y'
       or decode(new.ADDRESS1                , old.ADDRESS1                , 'N', 'Y') = 'Y'
       or decode(new.ADDRESS2                , old.ADDRESS2                , 'N', 'Y') = 'Y'
       or decode(new.FIRST_NAME              , old.FIRST_NAME              , 'N', 'Y') = 'Y'
       or decode(new.LAST_NAME               , old.LAST_NAME               , 'N', 'Y') = 'Y'
       or decode(new.CITY                    , old.CITY                    , 'N', 'Y') = 'Y'
       or decode(new.POSTAL_CODE             , old.POSTAL_CODE             , 'N', 'Y') = 'Y'
       or decode(new.TELEPHONE               , old.TELEPHONE               , 'N', 'Y') = 'Y'
       or decode(new.EN_CUSTOMER_NAME        , old.EN_CUSTOMER_NAME        , 'N', 'Y') = 'Y'
       or decode(new.EN_CUSTOMER_NAME_EXT    , old.EN_CUSTOMER_NAME_EXT    , 'N', 'Y') = 'Y'
       or decode(new.EN_CUSTOMER_NAME_EXT2   , old.EN_CUSTOMER_NAME_EXT2   , 'N', 'Y') = 'Y'
       or decode(new.EN_ADDRESS1             , old.EN_ADDRESS1             , 'N', 'Y') = 'Y'
       or decode(new.EN_ADDRESS2             , old.EN_ADDRESS2             , 'N', 'Y') = 'Y'
       or decode(new.EN_COUNTRY              , old.EN_COUNTRY              , 'N', 'Y') = 'Y'
       or decode(new.EN_COUNTY               , old.EN_COUNTY               , 'N', 'Y') = 'Y'
       or decode(new.EN_CITY                 , old.EN_CITY                 , 'N', 'Y') = 'Y'
       or decode(new.EN_FIRST_NAME           , old.EN_FIRST_NAME           , 'N', 'Y') = 'Y'
       or decode(new.EN_LAST_NAME            , old.EN_LAST_NAME            , 'N', 'Y') = 'Y'
       or decode(new.EMAIL                   , old.EMAIL                   , 'N', 'Y') = 'Y'
       or decode(new.CUSTOMER_NAME_EXT       , old.CUSTOMER_NAME_EXT       , 'N', 'Y') = 'Y'
       or decode(new.JP_ADDRESS1             , old.JP_ADDRESS1             , 'N', 'Y') = 'Y'
       or decode(new.JP_ADDRESS2             , old.JP_ADDRESS2             , 'N', 'Y') = 'Y'
       or decode(new.JP_ADDRESS3             , old.JP_ADDRESS3             , 'N', 'Y') = 'Y'
       or decode(new.JP_FIRST_NAME           , old.JP_FIRST_NAME           , 'N', 'Y') = 'Y'
       or decode(new.JP_LAST_NAME            , old.JP_LAST_NAME            , 'N', 'Y') = 'Y'
       or decode(new.ORIG_SYSTEM_CUSTOMER_REF, old.ORIG_SYSTEM_CUSTOMER_REF, 'N', 'Y') = 'Y'
       or decode(new.ORIG_SYSTEM_ADDRESS_REF , old.ORIG_SYSTEM_ADDRESS_REF , 'N', 'Y') = 'Y'
      )
begin
    :new.OT_DUMMY := null;
end TR_OT_GCS_IDX_REFRESH;
/


Please help me.

Thanks.
Re: while updating error recursive SQL level 1 [message #590426 is a reply to message #590425] Thu, 18 July 2013 03:26 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

We have some indexes on GEDIS_CUSTOMER_SUMMARY table.

CREATE INDEX APPS_NL.OT_CUSTOMER_NAME_EN_IDX ON APPS_NL.GEDIS_CUSTOMER_SUMMARY
(OT_CUSTOMER_NAME_EN)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('datastore APPS_NL.OT_CUSTOMER_NAME_EN_STORE
                      lexer APPS_GLOBAL.GCS_DEFAULT_LEXER
                      stoplist APPS_GLOBAL.GCS_STOPLIST
                      sync (on commit)')
PARALLEL ( DEGREE 8 INSTANCES 1 );


CREATE INDEX APPS_NL.OT_CUSTOMER_NAME_IDX ON APPS_NL.GEDIS_CUSTOMER_SUMMARY
(OT_CUSTOMER_NAME)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('datastore APPS_NL.OT_CUSTOMER_NAME_STORE
                      lexer APPS_GLOBAL.GCS_DEFAULT_LEXER
                      stoplist APPS_GLOBAL.GCS_STOPLIST
                      sync (on commit)')
PARALLEL ( DEGREE 8 INSTANCES 1 );


CREATE INDEX APPS_NL.OT_GCS_IDX ON APPS_NL.GEDIS_CUSTOMER_SUMMARY
(OT_DUMMY)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('datastore APPS_NL.OT_CUSTOMER_SUMMARY_STORE
                      lexer APPS_GLOBAL.GCS_DEFAULT_LEXER
                      stoplist APPS_GLOBAL.GCS_STOPLIST
                      sync (on commit)')
PARALLEL ( DEGREE 8 INSTANCES 1 );


Please help me.

Thanks.
Re: while updating error recursive SQL level 1 [message #590434 is a reply to message #590425] Thu, 18 July 2013 04:42 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,
I have found the problem only with this index.

CREATE INDEX APPS_NL.OT_GCS_IDX ON APPS_NL.GEDIS_CUSTOMER_SUMMARY
(OT_DUMMY)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('datastore APPS_NL.OT_CUSTOMER_SUMMARY_STORE
                      lexer APPS_GLOBAL.GCS_DEFAULT_LEXER
                      stoplist APPS_GLOBAL.GCS_STOPLIST
                      sync (on commit)')
PARALLEL ( DEGREE 8 INSTANCES 1 );

In this trigger if I change the below stattement with some other column
whcich is not having this type of CTXSYS.CONTEXT index.
without disabling any triggers I am not getting error.
begin
    :new.OT_DUMMY := null;


Please help me why I am getting this error
for this CTXSYS.CONTEXT column.

Thanks.
Re: while updating error recursive SQL level 1 [message #590436 is a reply to message #590434] Thu, 18 July 2013 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I move this topic to "Text & interMedia" forum, maybe Barbara will help you more tonight (for you).

Regards
Michel
Re: while updating error recursive SQL level 1 [message #590623 is a reply to message #590436] Sat, 20 July 2013 10:14 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

I have reported to moderator to delete this post from this site.

Please delete it ASAP.

Thanks.
Re: while updating error recursive SQL level 1 [message #590624 is a reply to message #590623] Sat, 20 July 2013 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We never remove topics from the site.
If there are some confidential information we will hide them but you have to tell us where there are, I don't see any.
You also said you have found a solution, please post it to help future readers.

Regards
Michel

[Edit: "remove" missing in first sentence]

[Updated on: Sat, 20 July 2013 13:20]

Report message to a moderator

Re: while updating error recursive SQL level 1 [message #590626 is a reply to message #590624] Sat, 20 July 2013 11:31 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I know I posted a reply to this topic. Where did it go? Who deleted it and why?
Previous Topic: performance issue with indexes
Next Topic: Fuzzy Matching Using Contains
Goto Forum:
  


Current Time: Thu Mar 28 14:11:42 CDT 2024