CODEWITHSUNDEEP
oracle-databaseplsqlclob
Riya
Riya

Reputation: 25

Getting error ORA-06502: PL/SQL: numeric or value error

I am getting

ORA-06502: PL/SQL: numeric or value error in below code:

record_state := record_state || 'Inserting record Entry for student: ' || 
                roll_no || ' for date: ' || To_Char(admission_date,'yyyymmdd') ||
                ' @ ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || CHR(10);

Below are the datatypes:

roll_no        - NUMBER(10,0)
admission_date - TIMESTAMP(6)
record_state   - CLOB

Upvotes: 0

Views: 562

Answers (2)

hotfix
hotfix

Reputation: 3396

if you have lob objects, i would suggest to use a DBMS_LOB library.

DECLARE
  record_state  CLOB;  
BEGIN
  DBMS_LOB.CREATETEMPORARY(record_state);  

  -- here you should make sure that the string is not too long (max 32K), otherwise the concatenation with pipes will not work. There will be an exception.
  DBMS_LOB.append (record_state , 'Inserting record Entry for student: ' || roll_no || ' for date: ' || To_Char(admission_date,'yyyymmdd') || ' @ ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || CHR(10)); 

  -- do more operations
  dbms_lob.freetemporary(record_state);

END ;
/

Upvotes: 1

Popeye
Popeye

Reputation: 35900

You need to use to_clob to convert string to clob and then you can concat two clobs as following:

record_state := record_state || 
                to_clob(
                        'Inserting record Entry for student: ' || roll_no || ' for date: ' || To_Char(admission_date,'yyyymmdd') || ' @ ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || CHR(10)
                       );

Cheers!!

Upvotes: 1

Related Questions