CODEWITHSUNDEEP
sqlregexoracle-database
Jayachandran Jayaseelan
Jayachandran Jayaseelan

Reputation: 1

Oracle SQL REGEXP to find specific pattern

I am building a mini project in SQL to automate the load failures occurring due to ORA-12899 - Value too large for the column.

From an error repository table, I can get the error message like the following,

"ORA-12899:value too large for column "SCOTT"."TABLE_EMPLOYEE"."NAME" ( actual 15, maximum:10 )

I would like to use a regular expression such that I can extract the following DDL from the above error message,

ALTER TABLE TABLE_EMPLOYEE MODIFY NAME VARCHAR2(15);

Below is my current code. With this I was able to extract only the schema name i.e "SCOTT"

SELECT REGEXP_SUBSTR('ORA-12899: value too large for column "SCOTT"."TABLE_EMPLOYEE"."NAME" (actual:15 , maximum: 10)','"([^"]+)"',1,1,NULL,1) AS RESULT from DUAL;

Appreciate it if someone can help me with the right regular expression or any other ways of extracting this information

Upvotes: 0

Views: 150

Answers (3)

MT0
MT0

Reputation: 167962

You can use the regular expression:

SELECT REGEXP_REPLACE(
         error,
         '^.*(".*?"\.".*?")\.(".*?")\s+\(\s+actual:\s+(\d+).*$',
         'ALTER TABLE \1 MODIFY \2 VARCHAR2(\3)'
       ) AS query
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (error) AS
SELECT 'ORA-12899:value too large for column "SCOTT"."TABLE_EMPLOYEE"."NAME" ( actual: 15, maximum: 10 )' FROM DUAL UNION ALL
-- Handle quoted identifiers with numbers
SELECT 'ORA-12899:value too large for column "test1"."actual 20"."VALUE20" ( actual: 25, maximum: 7 )' FROM DUAL;

Outputs:

QUERY
ALTER TABLE "SCOTT"."TABLE_EMPLOYEE" MODIFY "NAME" VARCHAR2(15)
ALTER TABLE "test1"."actual 20" MODIFY "VALUE20" VARCHAR2(25)

fiddle

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142705

Here's one option, which uses only substr + instr combination.

As of your sample data: that's most probably fabricated. "Real" error message looks like this:

SQL> create table table_employee (name varchar2(10));

Table created.

SQL> insert into table_employee (name) values ('Littlefoot Wiki');
insert into table_employee (name) values ('Littlefoot Wiki')
                                          *
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."TABLE_EMPLOYEE"."NAME" (actual: 15, maximum: 10)


SQL>

When dealing with such strings, you just need to be precise, otherwise you'll get unexpected (wrong) result.

You   : ORA-12899:value too large for column "SCOTT"."TABLE_EMPLOYEE"."NAME" ( actual 15, maximum:10 )
Really: ORA-12899: value too large for column "SCOTT"."TABLE_EMPLOYEE"."NAME" (actual: 15, maximum: 10)

Yet another example (for @MT0's - correct - objection to my previous code) with table and column containing digits:

SQL> create table test15 (col12umn varchar2(2));

Table created.

SQL> insert into test15 values ('abc');
insert into test15 values ('abc')
                           *
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."TEST15"."COL12UMN" (actual: 3, maximum: 2)


SQL>

So:

SQL> with test (col) as
  2    (select 'ORA-12899: value too large for column "SCOTT"."TEST15"."COL12UMN" (actual: 3, maximum: 2)'       from dual union all
  3     select 'ORA-12899: value too large for column "SCOTT"."TABLE_EMPLOYEE"."NAME" (actual: 15, maximum: 10)' from dual
  4    )
  5  select
  6    'alter table ' ||
  7    substr(col, instr(col, '"', 1, 1),
  8                instr(col, '"', 1, 4) - instr(col, '"', 1, 1) + 1
  9          ) ||
 10    ' modify ' ||
 11    substr(col, instr(col, '"', 1, 5),
 12                instr(col, '"', 1, 6) - instr(col, '"', 1, 5) + 1
 13          ) ||
 14    ' varchar2(' ||
 15    substr(col, instr(col, ': ', -1, 2) + 2,
 16                instr(col, ',', -1, 1) - instr(col, ': ', -1, 2) - 2
 17          ) || ')' as result
 18  from test;

RESULT
----------------------------------------------------------------------------------------------------
alter table "SCOTT"."TEST15" modify "COL12UMN" varchar2(3)
alter table "SCOTT"."TABLE_EMPLOYEE" modify "NAME" varchar2(15)

SQL>

Upvotes: 0

Reza Davoudian
Reza Davoudian

Reputation: 155

You need to write a PL/SQL block like this:

cl scr
set SERVEROUTPUT ON

declare
err varchar2(3000);
cmnd varchar2(3000);
scma varchar2(100);
tbl varchar2(500);
clm varchar2(200);
N varchar2(100);

begin

err:='ORA-12899:value too large for column "SCOTT"."TABLE_EMPLOYEE"."NAME" ( actual 15, maximum:10 )';

select regexp_substr(err, '\w+', 1, 8) into scma from dual; -- Extract 8th word
select regexp_substr(err, '\w+', 1, 9) into tbl from dual;  -- Extract 9th word
select regexp_substr(err, '\w+', 1, 10) into clm from dual;  -- Extract 10th word
select regexp_substr(err, '\w+', 1, 12) into N from dual; -- Extract 12th word

cmnd:='ALTER TABLE "'||scma||'"."'||tbl||'" modify "'||clm||'" varchar2('||N||');';
dbms_output.put_line(cmnd);

end;

the result:

ALTER TABLE "SCOTT"."TABLE_EMPLOYEE" modify "NAME" varchar2(15);

Indeed you should generate a dynamic sql as you saw. by this, you can generate any command from information you extract from errors.

Upvotes: 0

Related Questions