FAQ
I am brain dead and can't figure this one out probably something really basic.

8.1.6 database

I have a delete trigger when I delete a row from user #1 address table and I want to
insert a corresponding row into an archive address table that belongs to user #2.
If there is any error inserting into the archive address table, I want to insert some
information to an exception table owned by user #2

Insert priviledges have been granted to the 1st user on all the archive tables and the
exception table by user #2. The procedure that the trigger calls is valid. The
procedure is owned by user1.

The initial delete from user 1 works fine and populates the archive log table.
I reinserted the original row and tried to delete it again to get the exception to
write to the
exception table. I get:

SQL> delete from address where addr_id = 3;
delete from address where addr_id = 3
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "USER1.D_ARCH_ADDRESS", line 57
ORA-00001: unique constraint (USER2.PK_ARCH_ADDRESS) violated
ORA-04088: error during execution of trigger 'PKMSPROD.D_ARCH_ADDRESS'

There is only 1 row to delete from the address table. I inserted it and checked it
myself
I excepted the constraint to be violated for the archive address table
I don't get an entry into my exception table

TRIGGER CODE:

CREATE OR REPLACE TRIGGER D_ARCH_ADDRESS
BEFORE DELETE ON ADDRESS
FOR EACH ROW
DECLARE
v_err_msg VARCHAR2(255) := NULL;
v_err_code VARCHAR2(20) := NULL;
v_process VARCHAR2(9) := NULL;
V_ARCH_CREATED_BY VARCHAR2(30) := NULL;
BEGIN
select user into v_arch_created_by from dual;
INSERT INTO USER2.ARCH_ADDRESS
(
WHSE,
ADDR_ID,
ADDR_TYPE,
ADDR_KEY_1,
ADDR_KEY_2,
ADDR_LINE_1,
ADDR_LINE_2,
ADDR_LINE_3,
CITY,
STATE,
ZIP,
CNTRY,
CONTACT,
PHONE,
FAX,
EMAIL,
CREATE_DATE_TIME,
MOD_DATE_TIME,
USER_ID,
ARCH_CREATE_DATE_TIME,
ARCH_CREATED_BY
) VALUES
(
'IY',
:old.ADDR_ID,
:old.ADDR_TYPE,
:old.ADDR_KEY_1,
:old.ADDR_KEY_2,
:old.ADDR_LINE_1,
:old.ADDR_LINE_2,
:old.ADDR_LINE_3,
:old.CITY,
:old.STATE,
:old.ZIP,
:old.CNTRY,
:old.CONTACT,
:old.PHONE,
:old.FAX,
:old.EMAIL,
:old.CREATE_DATE_TIME,
:old.MOD_DATE_TIME,
:old.USER_ID,
sysdate,
v_arch_created_by
);
EXCEPTION
WHEN OTHERS THEN
select process into v_process from v$session;
v_err_code:=sqlcode;
v_err_msg :=sqlerrm;
trig_error_proc('IY',v_process,v_err_code,v_err_msg,sysdate,v_arch_created_
by);
END;
/

PROCEDURE ---
CREATE OR REPLACE PROCEDURE TRIG_ERROR_PROC
(in_whse varchar2,
in_process varchar2,
in_err_code varchar2,
in_err_msg varchar2,
in_create_date_time date,
in_created_by varchar2)
is
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO USER2.TRIGGER_EXCEPTIONS
(
WHSE,
PROCESS,
ERROR_CODE,
ERROR_MSG,
CREATE_DATE_TIME,
CREATED_BY
) VALUES
(
in_whse,
in_process,
in_err_code,
in_err_msg,
in_create_date_time,
in_created_by
);
commit;
END;
/
sho err

Confidential
This e-mail and any files transmitted with it are the property
of Belkin Components and/or its affiliates, are confidential,
and are intended solely for the use of the individual or
entity to whom this e-mail is addressed. If you are not one
of the named recipients or otherwise have reason to believe
that you have received this e-mail in error, please notify the
sender and delete this message immediately from your computer.
Any other use, retention, dissemination, forwarding, printing
or copying of this e-mail is strictly prohibited.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kathy Duret
INET: KathyD_at_belkin.com

Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Mercadante, Thomas F at Dec 7, 2001 at 2:42 pm
    Kathy,

    Read the error messages from the bottom up, and you can figure out what's
    happening.

    First, it looks to me that the primary key on the USER2.ARCH_ADDRESS table
    is being violated - like the record already exists so the insert statement
    fails.

    Then, in the exception clause, you 'select process into v_process from
    v$session;'. This causes the ORA-01422 error. This causes the trigger to
    fail, so that you do not get the opportunity to call the trig_error_proc
    procedure.

    Finally, in your TRIG_ERROR_PROC procedure, remove the 'commit' clause. You
    will eventually get an error here when you fix the other problems - you do
    not want a commit in a proc called by a trigger, as a commit is already
    underway.

    Hope this helps.

    Tom Mercadante
    Oracle Certified Professional

    -----Original Message-----
    Sent: Thursday, December 06, 2001 6:35 PM
    To: Multiple recipients of list ORACLE-L

    I am brain dead and can't figure this one out probably something really
    basic.

    8.1.6 database

    I have a delete trigger when I delete a row from user #1 address table and I
    want to insert a corresponding row into an archive address table that
    belongs to user #2.
    If there is any error inserting into the archive address table, I want to
    insert some information to an exception table owned by user #2

    Insert priviledges have been granted to the 1st user on all the archive
    tables and the exception table by user #2. The procedure that the trigger
    calls is valid. The procedure is owned by user1.

    The initial delete from user 1 works fine and populates the archive log
    table.
    I reinserted the original row and tried to delete it again to get the
    exception to write to the
    exception table. I get:

    SQL> delete from address where addr_id = 3;

    delete from address where addr_id = 3

    *

    ERROR at line 1:

    ORA-01422: exact fetch returns more than requested number of rows

    ORA-06512: at "USER1.D_ARCH_ADDRESS", line 57

    ORA-00001: unique constraint (USER2.PK_ARCH_ADDRESS) violated

    ORA-04088: error during execution of trigger 'PKMSPROD.D_ARCH_ADDRESS'



    There is only 1 row to delete from the address table. I inserted it and
    checked it myself
    I excepted the constraint to be violated for the archive address table
    I don't get an entry into my exception table

    TRIGGER CODE:

    CREATE OR REPLACE TRIGGER D_ARCH_ADDRESS

    BEFORE DELETE ON ADDRESS

    FOR EACH ROW

    DECLARE

    v_err_msg VARCHAR2(255) := NULL;

    v_err_code VARCHAR2(20) := NULL;

    v_process VARCHAR2(9) := NULL;

    V_ARCH_CREATED_BY VARCHAR2(30) := NULL;

    BEGIN

    select user into v_arch_created_by from dual;

    INSERT INTO USER2.ARCH_ADDRESS

    (

    WHSE,

    ADDR_ID,

    ADDR_TYPE,

    ADDR_KEY_1,

    ADDR_KEY_2,

    ADDR_LINE_1,

    ADDR_LINE_2,

    ADDR_LINE_3,

    CITY,

    STATE,

    ZIP,
    CNTRY,
    CONTACT,

    PHONE,

    FAX,

    EMAIL,

    CREATE_DATE_TIME,

    MOD_DATE_TIME,

    USER_ID,

    ARCH_CREATE_DATE_TIME,

    ARCH_CREATED_BY

    ) VALUES

    (

    'IY',

    :old.ADDR_ID,

    :old.ADDR_TYPE,

    :old.ADDR_KEY_1,

    :old.ADDR_KEY_2,

    :old.ADDR_LINE_1,

    :old.ADDR_LINE_2,

    :old.ADDR_LINE_3,

    :old.CITY,

    :old.STATE,

    :old.ZIP,
    :old.CNTRY,

    :old.CONTACT,

    :old.PHONE,

    :old.FAX,

    :old.EMAIL,

    :old.CREATE_DATE_TIME,

    :old.MOD_DATE_TIME,

    :old.USER_ID,

    sysdate,

    v_arch_created_by

    );

    EXCEPTION

    WHEN OTHERS THEN

    select process into v_process from v$session;

    v_err_code:=sqlcode;

    v_err_msg :=sqlerrm;



    trig_error_proc('IY',v_process,v_err_code,v_err_msg,sysdate,v_arch_created_
    by);

    END;

    /

    PROCEDURE ---

    CREATE OR REPLACE PROCEDURE TRIG_ERROR_PROC

    (in_whse varchar2,

    in_process varchar2,

    in_err_code varchar2,

    in_err_msg varchar2,

    in_create_date_time date,

    in_created_by varchar2)

    is

    PRAGMA AUTONOMOUS_TRANSACTION;

    BEGIN

    INSERT INTO USER2.TRIGGER_EXCEPTIONS

    (

    WHSE,

    PROCESS,

    ERROR_CODE,

    ERROR_MSG,

    CREATE_DATE_TIME,

    CREATED_BY

    ) VALUES

    (

    in_whse,

    in_process,

    in_err_code,
    in_err_msg,
    in_create_date_time,

    in_created_by

    );

    commit;

    END;

    /

    sho err

    Confidential
    This e-mail and any files transmitted with it are the property
    of Belkin Components and/or its affiliates, are confidential,
    and are intended solely for the use of the individual or
    entity to whom this e-mail is addressed. If you are not one
    of the named recipients or otherwise have reason to believe
    that you have received this e-mail in error, please notify the
    sender and delete this message immediately from your computer.
    Any other use, retention, dissemination, forwarding, printing
    or copying of this e-mail is strictly prohibited.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Kathy Duret
    INET: KathyD_at_belkin.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mercadante, Thomas F
    INET: NDATFM_at_labor.state.ny.us

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • JRicard982_at_aol.com at Dec 7, 2001 at 2:44 pm
    Kathy,

    I beleive the problem is in the exception handler:
    select process into v_process from v$session...This is returning multiple rows.

    Rick
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: JRicard982_at_aol.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Kathy Duret at Dec 7, 2001 at 5:45 pm
    I told you I knew it was something stupid. Thanks the process was it. I got rid of
    the process code and the duplicate error does now populate the exception table like I
    wanted!

    I know need to figure out how to capture the specific process. Probably next week
    when hopefully my head cold is gone, my kid isn't teething and I have had some sleep.

    Thanks to all and to all a good weekend!

    Kathy

    -----Original Message-----
    Sent: Friday, December 07, 2001 5:35 AM
    To: Multiple recipients of list ORACLE-L

    Kathy,

    I beleive the problem is in the exception handler:
    select process into v_process from v$session...This is returning multiple rows.

    Rick
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: JRicard982_at_aol.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    Confidential
    This e-mail and any files transmitted with it are the property
    of Belkin Components and/or its affiliates, are confidential,
    and are intended solely for the use of the individual or
    entity to whom this e-mail is addressed. If you are not one
    of the named recipients or otherwise have reason to believe
    that you have received this e-mail in error, please notify the
    sender and delete this message immediately from your computer.
    Any other use, retention, dissemination, forwarding, printing
    or copying of this e-mail is strictly prohibited.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Kathy Duret
    INET: KathyD_at_belkin.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Igor Neyman at Dec 7, 2001 at 6:31 pm
    If you need current process:

    select process from v$session where AUDSID = (select userenv('SESSIONID')
    from dual);

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, December 07, 2001 12:35 PM
    I told you I knew it was something stupid. Thanks the process was it. I
    got rid of the process code and the duplicate error does now populate the
    exception table like I wanted!
    I know need to figure out how to capture the specific process. Probably
    next week when hopefully my head cold is gone, my kid isn't teething and I
    have had some sleep.
    Thanks to all and to all a good weekend!

    Kathy

    -----Original Message-----
    Sent: Friday, December 07, 2001 5:35 AM
    To: Multiple recipients of list ORACLE-L


    Kathy,

    I beleive the problem is in the exception handler:
    select process into v_process from v$session...This is returning multiple rows.
    Rick
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: JRicard982_at_aol.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    Confidential
    This e-mail and any files transmitted with it are the property
    of Belkin Components and/or its affiliates, are confidential,
    and are intended solely for the use of the individual or
    entity to whom this e-mail is addressed. If you are not one
    of the named recipients or otherwise have reason to believe
    that you have received this e-mail in error, please notify the
    sender and delete this message immediately from your computer.
    Any other use, retention, dissemination, forwarding, printing
    or copying of this e-mail is strictly prohibited.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Kathy Duret
    INET: KathyD_at_belkin.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Paul Vallee at Dec 7, 2001 at 6:52 pm
    If you're gonna do that, then why not:

    select decode(process, process, process)
    from ( select * from ( select * from v$session ) )
    where decode(audsid, audsid, audsid) =
    ( select userenv('SESSIONID') from ( select * from dual ) );

    Seriously though, why not just use

    select * from v$session where AUDSID = userenv('SESSIONID')

    :-)

    -p

    ---
    www.pythian.com -- vallee_at_pythian.com -- 877-PYTHIAN
    Smarter than adding another team member, Pythian has new services for
    supplementing DBAs: get our help with monitoring, 24x7 on-call, daily
    verifications, storage management, performance and more.

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, December 07, 2001 1:23 PM

    If you need current process:

    select process from v$session where AUDSID = (select userenv('SESSIONID')
    from dual);

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, December 07, 2001 12:35 PM
    I told you I knew it was something stupid. Thanks the process was it. I
    got rid of the process code and the duplicate error does now populate the
    exception table like I wanted!
    I know need to figure out how to capture the specific process. Probably
    next week when hopefully my head cold is gone, my kid isn't teething and I
    have had some sleep.
    Thanks to all and to all a good weekend!

    Kathy

    -----Original Message-----
    Sent: Friday, December 07, 2001 5:35 AM
    To: Multiple recipients of list ORACLE-L


    Kathy,

    I beleive the problem is in the exception handler:
    select process into v_process from v$session...This is returning multiple rows.
    Rick
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: JRicard982_at_aol.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    Confidential
    This e-mail and any files transmitted with it are the property
    of Belkin Components and/or its affiliates, are confidential,
    and are intended solely for the use of the individual or
    entity to whom this e-mail is addressed. If you are not one
    of the named recipients or otherwise have reason to believe
    that you have received this e-mail in error, please notify the
    sender and delete this message immediately from your computer.
    Any other use, retention, dissemination, forwarding, printing
    or copying of this e-mail is strictly prohibited.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Kathy Duret
    INET: KathyD_at_belkin.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Paul Vallee
    INET: dbalist_at_pythian.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Igor Neyman at Dec 7, 2001 at 7:05 pm
    Oops:)

    I didn't remember exact parameter to use when calling 'userenv()'.

    So I just tried:

    select userenv('SESSIONID') from ( select * from dual );
    and then pasted it (as it was) into e-mail message.

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, December 07, 2001 1:43 PM
    If you're gonna do that, then why not:

    select decode(process, process, process)
    from ( select * from ( select * from v$session ) )
    where decode(audsid, audsid, audsid) =
    ( select userenv('SESSIONID') from ( select * from dual ) );

    Seriously though, why not just use

    select * from v$session where AUDSID = userenv('SESSIONID')

    :-)

    -p
    ---
    www.pythian.com -- vallee_at_pythian.com -- 877-PYTHIAN
    Smarter than adding another team member, Pythian has new services for
    supplementing DBAs: get our help with monitoring, 24x7 on-call, daily
    verifications, storage management, performance and more.

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, December 07, 2001 1:23 PM


    If you need current process:

    select process from v$session where AUDSID = (select userenv('SESSIONID')
    from dual);

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, December 07, 2001 12:35 PM

    I told you I knew it was something stupid. Thanks the process was it.
    I
    got rid of the process code and the duplicate error does now populate the
    exception table like I wanted!
    I know need to figure out how to capture the specific process. Probably
    next week when hopefully my head cold is gone, my kid isn't teething and I
    have had some sleep.
    Thanks to all and to all a good weekend!

    Kathy

    -----Original Message-----
    Sent: Friday, December 07, 2001 5:35 AM
    To: Multiple recipients of list ORACLE-L


    Kathy,

    I beleive the problem is in the exception handler:
    select process into v_process from v$session...This is returning
    multiple
    rows.
    Rick
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: JRicard982_at_aol.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    Confidential
    This e-mail and any files transmitted with it are the property
    of Belkin Components and/or its affiliates, are confidential,
    and are intended solely for the use of the individual or
    entity to whom this e-mail is addressed. If you are not one
    of the named recipients or otherwise have reason to believe
    that you have received this e-mail in error, please notify the
    sender and delete this message immediately from your computer.
    Any other use, retention, dissemination, forwarding, printing
    or copying of this e-mail is strictly prohibited.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Kathy Duret
    INET: KathyD_at_belkin.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).




    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Paul Vallee
    INET: dbalist_at_pythian.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Kathy Duret at Dec 7, 2001 at 7:29 pm
    Don't worry about it... I was just happy for the solution! Save me time and achoo...
    trying to strain my brain stuffed with a cold.

    Thanks again to all! This works like a charm! Just have to adjust my triggers now.
    And try to make it through today. TGIF!

    Kathy

    -----Original Message-----
    Sent: Friday, December 07, 2001 10:57 AM
    To: Multiple recipients of list ORACLE-L

    Oops:)

    I didn't remember exact parameter to use when calling 'userenv()'.

    So I just tried:

    select userenv('SESSIONID') from ( select * from dual );
    and then pasted it (as it was) into e-mail message.

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, December 07, 2001 1:43 PM
    If you're gonna do that, then why not:

    select decode(process, process, process)
    from ( select * from ( select * from v$session ) )
    where decode(audsid, audsid, audsid) =
    ( select userenv('SESSIONID') from ( select * from dual ) );

    Seriously though, why not just use

    select * from v$session where AUDSID = userenv('SESSIONID')

    :-)

    -p
    ---
    www.pythian.com -- vallee_at_pythian.com -- 877-PYTHIAN
    Smarter than adding another team member, Pythian has new services for
    supplementing DBAs: get our help with monitoring, 24x7 on-call, daily
    verifications, storage management, performance and more.

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, December 07, 2001 1:23 PM


    If you need current process:

    select process from v$session where AUDSID = (select userenv('SESSIONID')
    from dual);

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, December 07, 2001 12:35 PM

    I told you I knew it was something stupid. Thanks the process was it.
    I
    got rid of the process code and the duplicate error does now populate the
    exception table like I wanted!
    I know need to figure out how to capture the specific process. Probably
    next week when hopefully my head cold is gone, my kid isn't teething and I
    have had some sleep.
    Thanks to all and to all a good weekend!

    Kathy

    -----Original Message-----
    Sent: Friday, December 07, 2001 5:35 AM
    To: Multiple recipients of list ORACLE-L


    Kathy,

    I beleive the problem is in the exception handler:
    select process into v_process from v$session...This is returning
    multiple
    rows.
    Rick
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: JRicard982_at_aol.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    Confidential
    This e-mail and any files transmitted with it are the property
    of Belkin Components and/or its affiliates, are confidential,
    and are intended solely for the use of the individual or
    entity to whom this e-mail is addressed. If you are not one
    of the named recipients or otherwise have reason to believe
    that you have received this e-mail in error, please notify the
    sender and delete this message immediately from your computer.
    Any other use, retention, dissemination, forwarding, printing
    or copying of this e-mail is strictly prohibited.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Kathy Duret
    INET: KathyD_at_belkin.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).




    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Paul Vallee
    INET: dbalist_at_pythian.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    Confidential
    This e-mail and any files transmitted with it are the property
    of Belkin Components and/or its affiliates, are confidential,
    and are intended solely for the use of the individual or
    entity to whom this e-mail is addressed. If you are not one
    of the named recipients or otherwise have reason to believe
    that you have received this e-mail in error, please notify the
    sender and delete this message immediately from your computer.
    Any other use, retention, dissemination, forwarding, printing
    or copying of this e-mail is strictly prohibited.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Kathy Duret
    INET: KathyD_at_belkin.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 7, '01 at 12:28a
activeDec 7, '01 at 7:29p
posts8
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase