PL/SQL multi values example

Wanted to compare a sequence value with a max value in a corresponding table.

The declare -n option not available in the bash shell on the server (surprised as new OCI build and thought bash would be greater than 4.2) but this worked in ksh. 

#!/bin/ksh

monitor=(
[sys1]=(seqname="SEQ_SAMPLE_KEY0" tablename="SAMPLE" rowname="ID_NUMERIC" )
[sys2]=(seqname="SEQ_TEST_TEST_NUMBER" tablename="TEST" rowname="TEST_NUMBER")
[sys3]=(seqname="SEQ_ACCESS_LOG_KEY0" tablename="ACCESS_LOG" rowname="SESSION_ID")
[sys4]=(seqname="SEQ_AUDITOR_DATA" tablename="AUDIT_DATA" rowname="DATA")
[sys5]=(seqname="SEQ_AUDITOR_EVENT" tablename="AUDIT_EVENT" rowname="EVENT")
[sys6]=(seqname="SEQ_AUDITOR_TRANSACTION" tablename="AUDIT_TRANSACTION" rowname="TRANSACTION")
)

for sys in ${!monitor[*]}; do
exit | sqlplus -silent user/password@TESTDB << EOF

set head off
set feedback off
set lines 200
set serveroutput on

DECLARE

x integer;
y integer;

CURSOR cur1 is select last_number from dba_sequences where sequence_name = '${monitor[$sys].seqname}';

CURSOR cur2 is select max(${monitor[$sys].rowname}) from ${monitor[$sys].tablename};

BEGIN

OPEN cur1;
OPEN cur2;

FETCH cur1 into x;
FETCH cur2 into y;

dbms_output.put_line ('${monitor[$sys].seqname}' || ' is ' || x || ' but max(' ||'${monitor[$sys].rowname}' || ')' || ' from '||'${monitor[$sys].tablename}' ||' is ' || y );

IF (x > y) then dbms_output.put_line('sequence is greater');
IF (x < y) then dbms_output.put_line('sequence is lower');
END IF;
ELSE
dbms_output.put_line('values the same');
END IF;

CLOSE cur1;
CLOSE cur2;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

END;
/
EOF

done

SEQ_SAMPLE_KEY0 is 2867468 but max(ID_NUMERIC) from SAMPLE is 2867467
sequence is greater
SEQ_TEST_TEST_NUMBER is 22019029 but max(TEST_NUMBER) from TEST is 22019028
sequence is greater
etc....

ORA-02374 ORA-12899 ORA-02372 multi-byte character issues

Multi-byte characters and problem resolution when going from on-prem to OCI

When doing a data pump from on-prem to the cloud you will often hit errors with conversion errors.

Plenty of background information but little on resolving this, so putting this together as a reminder when going forward.

Typical errors when doing datapump -

ORA-02374: conversion error loading table "ABC"."STANDARD_LETTERS"
ORA-12899: value too large for column NAME (actual: 51, maximum: 50)
ORA-02372: data for row: NAME : 0X'4D65746572205374726174656779202D204D6574657220696E'
……..
. . imported "ABC"."STANDARD_LETTERS" 45.80 MB 1143 out of 1146 rows
…….
ORA-02374: conversion error loading table "ABC"."AFFECTED_ADDRESSES"
ORA-12899: value too large for column OCCUPIER_NAME (actual: 51, maximum: 50)
ORA-02372: data for row: OCCUPIER_NAME : 0X'4D52205354414E49534C41555320434841464553554B41202D'
…..
. . imported "ABC"."AFFECTED_ADDRESSES" 94.30 MB 10799942 out of 10799944 rows
......

One approach would be “data cleanse” the offending tables but this means having to search for multi-byte characters and often this can mean millions of rows.

Also, there can be a reluctance to change any source data (and can be problematic identifying those characters).

To avoid this, we have been changing the table data type and re-importing the data.

Using the above errors as examples -

For test db from source:

SQL> desc ABC.AFFECTED_ADDRESSES

Name Null? Type
----------------------------------------- -------- ----------------------------
..
OCCUPIER_NAME VARCHAR2(50)
..

For the test db in OCI (we change to 50 char to allow for multi-byte characters) :

SQL>alter table ABC.AFFECTED_ADDRESSES modify OCCUPIER_NAME varchar2 (50 char);

SQL> desc ABC.AFFECTED_ADDRESSES

Name Null? Type
----------------------------------------- -------- ----------------------------
..
OCCUPIER_NAME VARCHAR2(50 CHAR)
..

Now on running the import for the table –

. . imported "ABC"."AFFECTED_ADDRESSES" 94.30 MB 10799944 rows

Remember table_exists_action=truncate in the parameter file to “clear” the table prior to importing the data.

While this approach works, if you have 1 row fail for a few million rows this is not ideal having to run an import again.

To get around this and to avoid having to change the column datatype, an option is to generate the missing rows from the source database.

From the datapump log we can see the offending row -

ORA-02374: conversion error loading table "ABC"."STANDARD_LETTERS"
ORA-12899: value too large for column NAME (actual: 51, maximum: 50)
ORA-02372: data for row: NAME : 0X'4D65746572205374726174656779202D204D6574657220696E'

Use script to identify row data (run for all the offending rows)….

SQL> select name FROM ABC.STANDARD_LETTERS WHERE NAME like '%' || utl_raw.cast_to_varchar2 ('4D65746572205374726174656779202D204D6574657220696E') || '%'

Strategy -  installaltion  £600 letter …etc

However, we still need to generate the insert statement to insert the problem row to the table.

The easy option is to use Toad and select the table from the schema browser

From the export dataset button use the drop-down option and select “create insert statement”

 

You need to include the where clause for each of the offending rows –

This generates -

SET DEFINE OFF;
Insert into STANDARD_LETTERS
(STAN_ID, NAME, SHORT_NAME, FILENAME, EMAIL,
STAN_BODY, VALID_DATE, CURRENT_VALID, PREVIEW, DOC_SOURCE,
STAN_COVLET_STAN_ID)
Values
(100, 'Strategy -  installaltion > £600 letter', 'MS8', 'MS8.doc', 'N',
'', TO_DATE('02/04/2009 12:06:33', 'DD/MM/YYYY HH24:MI:SS'), 'Y', NULL, NULL,
NULL);
COMMIT;

However :

SQL> Insert into STANDARD_LETTERS;

(STAN_ID, NAME, SHORT_NAME, FILENAME, EMAIL,
STAN_BODY, VALID_DATE, CURRENT_VALID, PREVIEW, DOC_SOURCE,
STAN_COVLET_STAN_ID)
Values
(100, 'Strategy -  installaltion > £600 letter', 'MS8', 'MS8.doc', 'N',
'', TO_DATE('02/04/2009 12:06:33', 'DD/MM/YYYY HH24:MI:SS'), 'Y', NULL, NULL,
NULL);
(1001077, 'Strategy -  installaltion > £600 letter', 'MS8', 'MS8.doc', 'N',
*

ERROR at line 6:
ORA-12899: value too large for column "ABC"."STANDARD_LETTERS"."NAME" (actual:
51, maximum: 50)

Which is the error we are seeing in datapump – not surprising.

Removing the offending “£” (tempted to correct the spelling of installation!)

We could have changed the datatype on the column to insert the pound but left “as is” to demonstrate the process.

SQL> Insert into STANDARD_LETTERS
(STAN_ID, NAME, SHORT_NAME, FILENAME, EMAIL,
STAN_BODY, VALID_DATE, CURRENT_VALID, PREVIEW, DOC_SOURCE,
STAN_COVLET_STAN_ID)
Values
(100, 'Strategy - installaltion > 600 letter', 'MS8', 'MS8.doc', 'N',
'', TO_DATE('02/04/2009 12:06:33', 'DD/MM/YYYY HH24:MI:SS'), 'Y', NULL, NULL,
NULL);

1 row created.

After running the above for all the offending rows - on checking the row counts (as expected).

SQL> select count(*) from ABC.AFFECTED_ADDRESSES;

COUNT(*)
----------
10799944

SQL> select count(*) from ABC.STANDARD_LETTERS;

COUNT(*)
----------
1146

Ensure to test on the test environment prior to updating any prod database,

This is quite a simple example and worth further reading - any comments please let me know.

I may update this with a scripted solution and provide further detail going forward.