use strict;
my $str = 'DECLARE
COUNTER NUMBER(10) := 0;
pTABLENAME VARCHAR2(100) := \'ALM_AL1\';
pCOLUMNNAME VARCHAR2(100) := \'FLOATACCRUALDCBASISDAYC\';
pDATATYPE VARCHAR2(100) := \'NVARCHAR2(50) NULL\';
pSQL VARCHAR2(32000) := \'
ALTER TABLE \' || pTABLENAME || \' ADD \' || pCOLUMNNAME || \' \' || pDATATYPE;
BEGIN
SELECT COUNT(0) INTO COUNTER FROM USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER(pTABLENAME) AND COLUMN_NAME=UPPER(pCOLUMNNAME);
IF COUNTER>0 THEN
dbms_output.put_line(pTABLENAME || \' tablosunda \' || pCOLUMNNAME || \' kolonu var.\');
ELSE
EXECUTE IMMEDIATE pSQL;
dbms_output.put_line(pTABLENAME || \' tablosuna\' || pCOLUMNNAME || \' kolonu oluÅ?turuldu.\');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(\'HATA: \' || pSQL || \' -> SQLERRM: \' || SQLERRM);
END;
/
DECLARE
COUNTER NUMBER(10) := 0;
pTABLENAME VARCHAR2(100) := \'ALM_AL2\';
pCOLUMNNAME VARCHAR2(100) := \'RATETYPETYPE\';
pDATATYPE VARCHAR2(100) := \'NVARCHAR2(40) NULL\';
pSQL VARCHAR2(32000) := \'
ALTER TABLE \' || pTABLENAME || \' ADD \' || pCOLUMNNAME || \' \' || pDATATYPE;
BEGIN
SELECT COUNT(0) INTO COUNTER FROM USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER(pTABLENAME) AND COLUMN_NAME=UPPER(pCOLUMNNAME);
IF COUNTER>0 THEN
dbms_output.put_line(pTABLENAME || \' tablosunda \' || pCOLUMNNAME || \' kolonu var.\');
ELSE
EXECUTE IMMEDIATE pSQL;
dbms_output.put_line(pTABLENAME || \' tablosuna\' || pCOLUMNNAME || \' kolonu oluÅ?turuldu.\');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(\'HATA: \' || pSQL || \' -> SQLERRM: \' || SQLERRM);
END;
/
DECLARE
COUNTER NUMBER(10) := 0;
pTABLENAME VARCHAR2(100) := \'ALM_AL3\';
pCOLUMNNAME VARCHAR2(100) := \'PAYCURRENCYUNIT\';
pDATATYPE VARCHAR2(100) := \'NVARCHAR2(3) NULL\';
pSQL VARCHAR2(32000) := \'
ALTER TABLE \' || pTABLENAME || \' ADD \' || pCOLUMNNAME || \' \' || pDATATYPE;
BEGIN
SELECT COUNT(0) INTO COUNTER FROM USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER(pTABLENAME) AND COLUMN_NAME=UPPER(pCOLUMNNAME);
IF COUNTER>0 THEN
dbms_output.put_line(pTABLENAME || \' tablosunda \' || pCOLUMNNAME || \' kolonu var.\');
ELSE
EXECUTE IMMEDIATE pSQL;
dbms_output.put_line(pTABLENAME || \' tablosuna\' || pCOLUMNNAME || \' kolonu oluÅ?turuldu.\');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(\'HATA: \' || pSQL || \' -> SQLERRM: \' || SQLERRM);
END;
/';
my $regex = qr/(?<prm>\w+)\s+.*:\s*=\s*'(?<tbl>\w+)'\s*;(?:.|\n)*(?:alter|create)\s+table\s+'\s*\|\|\s*(\k<prm>\s+)/ip;
if ( $str =~ /$regex/g ) {
print "Whole match is ${^MATCH} and its start/end positions can be obtained via \$-[0] and \$+[0]\n";
# print "Capture Group 1 is $1 and its start/end positions can be obtained via \$-[1] and \$+[1]\n";
# print "Capture Group 2 is $2 ... and so on\n";
}
# ${^POSTMATCH} and ${^PREMATCH} are also available with the use of '/p'
# Named capture groups can be called via $+{name}
Please keep in mind that these code samples are automatically generated and are not guaranteed to work. If you find any syntax errors, feel free to submit a bug report. For a full regex reference for Perl, please visit: http://perldoc.perl.org/perlre.html