Home » SQL & PL/SQL » Client Tools » ORA-06502 in SQL*Plus, not in Toad (10.2.0.4 for both SQL*Plus and DB on Solaris 10)
ORA-06502 in SQL*Plus, not in Toad [message #580861] Thu, 28 March 2013 14:42 Go to next message
jimkd1yv
Messages: 3
Registered: March 2013
Location: CT, USA
Junior Member
Trying to learn and understand Triggers, PL/SQL code, etc. When I execute a simple insert using Toad for Oracle, all is fine. When I try the identical insert in SQL*Plus, it throws error ORA-06502 during execution of the trigger. What have I done wrong?

Here is the error info:
insert into AAAJOB(PROCEDURENAME,DESCRIPTION) VALUES('OOO','PPP')
            *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "PMSC0.AAAJOB_TIMING", line 10
ORA-04088: error during execution of trigger 'PMSC0.AAAJOB_TIMING'
===


Here is the table and the trigger:

CREATE TABLE PMSC0.AAAJOB
(
  CREATETS       TIMESTAMP(0)                   DEFAULT current_timestamp,
  PROCEDURENAME  VARCHAR2(100 CHAR),
  DESCRIPTION    VARCHAR2(100 CHAR),
  LASTUPDATEDBY  VARCHAR2(9 CHAR)
)
TABLESPACE PMSC0_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE OR REPLACE TRIGGER PMSC0.AAAjob_timing
   BEFORE INSERT OR UPDATE
   ON PMSC0.AAAJOB    REFERENCING OLD AS old_row NEW AS new_row
   FOR EACH ROW
BEGIN
   IF    :new_row.createTS <> SYStimestamp
      OR :new_row.createTS IS NULL
   THEN
      SELECT current_timestamp
        INTO :new_row.createTS
        FROM DUAL;
   END IF;

   SELECT SYS_CONTEXT ( 'USERENV', 'OS_USER' )
     INTO :new_row.lastupdatedby
     FROM DUAL;
END;
/


The error seems to be on this:
      SELECT current_timestamp
        INTO :new_row.createTS


Thanks,

JimR
Re: ORA-06502 in SQL*Plus, not in Toad [message #580864 is a reply to message #580861] Thu, 28 March 2013 15:17 Go to previous messageGo to next message
Littlefoot
Messages: 19638
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
On the contrary, I believe that the problem is this (create table statement):
LASTUPDATEDBY  VARCHAR2(9 CHAR)

Enlarge that column to, say, VARCHAR2(30) and try again.
Re: ORA-06502 in SQL*Plus, not in Toad [message #580865 is a reply to message #580861] Thu, 28 March 2013 15:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT post schema name, tablespace name and storage parameters in your test case, we have not the same ones:
SQL> CREATE TABLE PMSC0.AAAJOB
  2  (
  3    CREATETS       TIMESTAMP(0)                   DEFAULT current_timestamp,
  4    PROCEDURENAME  VARCHAR2(100 CHAR),
  5    DESCRIPTION    VARCHAR2(100 CHAR),
  6    LASTUPDATEDBY  VARCHAR2(9 CHAR)
  7  )
  8  TABLESPACE PMSC0_DATA
  9  PCTUSED    0
 10  PCTFREE    10
 11  INITRANS   1
 12  MAXTRANS   255
 13  STORAGE    (
 14              INITIAL          64K
 15              MINEXTENTS       1
 16              MAXEXTENTS       UNLIMITED
 17              PCTINCREASE      0
 18              BUFFER_POOL      DEFAULT
 19             )
 20  LOGGING 
 21  NOCOMPRESS 
 22  NOCACHE
 23  NOPARALLEL
 24  MONITORING;
CREATE TABLE PMSC0.AAAJOB
*
ERROR at line 1:
ORA-01918: user 'PMSC0' does not exist


Use SQL*Plus and copy and paste your session, the WHOLE session, including all object creations and statement generating the error.

SQL> CREATE TABLE AAAJOB
  2  (
  3    CREATETS       TIMESTAMP(0)                   DEFAULT current_timestamp,
  4    PROCEDURENAME  VARCHAR2(100 CHAR),
  5    DESCRIPTION    VARCHAR2(100 CHAR),
  6    LASTUPDATEDBY  VARCHAR2(9 CHAR)
  7  )
  8  /

Table created.

SQL> CREATE OR REPLACE TRIGGER AAAjob_timing
  2     BEFORE INSERT OR UPDATE
  3     ON AAAJOB    REFERENCING OLD AS old_row NEW AS new_row
  4     FOR EACH ROW
  5  BEGIN
  6     IF    :new_row.createTS <> SYStimestamp
  7        OR :new_row.createTS IS NULL
  8     THEN
  9        SELECT current_timestamp
 10          INTO :new_row.createTS
 11          FROM DUAL;
 12     END IF;
 13  
 14     SELECT SYS_CONTEXT ( 'USERENV', 'OS_USER' )
 15       INTO :new_row.lastupdatedby
 16       FROM DUAL;
 17  END;
 18  /

Trigger created.

SQL> insert into AAAJOB(PROCEDURENAME,DESCRIPTION) VALUES('OOO','PPP');

1 row created.

No error for me.
So do the same thing and add:
select SYS_CONTEXT ( 'USERENV', 'OS_USER' ) from dual;


Regards
Michel
Re: ORA-06502 in SQL*Plus, not in Toad [message #581069 is a reply to message #580864] Mon, 01 April 2013 08:45 Go to previous messageGo to next message
jimkd1yv
Messages: 3
Registered: March 2013
Location: CT, USA
Junior Member
Correct, thanks. Our user IDs are normally 9, but due to some confusion at time of startup a few of them got an extra letter pre-pended. Toad did not add this, but SQL*Plus did.

Thanks for your help.
Re: ORA-06502 in SQL*Plus, not in Toad [message #581082 is a reply to message #580864] Mon, 01 April 2013 13:07 Go to previous messageGo to next message
jimkd1yv
Messages: 3
Registered: March 2013
Location: CT, USA
Junior Member
Littlefoot wrote on Thu, 28 March 2013 16:17
On the contrary, I believe that the problem is this (create table statement):
LASTUPDATEDBY  VARCHAR2(9 CHAR)

Enlarge that column to, say, VARCHAR2(30) and try again.


I meant to specify that this is the correct answer. My previous reply was unclear, since it did not reference that I was replying to this.
Re: ORA-06502 in SQL*Plus, not in Toad [message #581086 is a reply to message #581082] Mon, 01 April 2013 15:13 Go to previous message
Littlefoot
Messages: 19638
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No problem; thank you for letting us know the outcome.
Previous Topic: usage of Package in 10g Form
Next Topic: Controlling prompt based on previous parameter
Goto Forum:
  


Current Time: Tue Sep 23 17:53:53 CDT 2014

Total time taken to generate the page: 0.78515 seconds