Oracle SQL REGEXP to find specific pattern-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Here's one option, which combines substr instr and regular expressions (just for column size).

As of your sample data: I don't think that it actually contains leading double quotes (because - if it does - then you're missing closing ones, and parameters within instr should be modified):

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

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

SQL>

CodePudding user response：

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.