Fetching multi line DBMS_OUTPUT.PUT_LINE statments
628524Jun 12 2008 — edited Jun 13 2008I have one procedure with the following code.I had lot of DBMS_OUTPUT statments in my code and most of these statments goes to multi line also.I want to search out all the DBMS_OUTPUT.PUT_LINE statments including those goes into multi line also from the souce code.
PROCEDURE REM_DBMS(VAR_EMPNO NUMBER)
AS
VAR_ENAME VARCHAR2(10);
VAR_JOB VARCHAR2(10);
VAR_DEPT NUMBER;
VAR_SAL NUMBER;
BEGIN
SELECT ENAME,JOB,DEPTNO,SAL INTO VAR_ENAME,VAR_JOB,VAR_DEPT,VAR_SAL
FROM EMP
WHERE EMPNO=VAR_EMPNO;
IF VAR_ENAME LIKE 'A%' THEN
<some_processing>
DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE NAME IS '||VAR_ENAME);
ELSE
<some_processing>
DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE NAME IS '||VAR_ENAME);
END IF;
IF VAR_JOB='CLERK' THEN
<some_processing>
DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE
JOB IS '||VAR_JOB);
ELSE
<some_processing>
DBMS_OUTPUT.PUT_LINE('THE EMP
LOYEE JOB IS '||VAR_JOB);
END IF;
IF VAR_DEPT=10 THEN
DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE WORKS FOR
DEPARTMENT NUMBER '||VAR_DEPT);
<some_processing>
ELSE
DBMS_OUTPUT.PUT_LINE('THE EMP
LOYEE WORKS FOR '||VAR_DEPT);
<some_processing>
END IF;
END REM_DBMS;
For the same i have tried the following query:
SELECT REGEXP_SUBSTR(TEXT,'DBMS_OUTPUT.PUT_LINE\(.*\)',1,1,'n')DBMS_RES FROM USER_SOURCE
WHERE NAME='REM_DBMS';
But i am getting only those DBMS STATEMENTS which ends on a single line only.I need to get all the DBMS statements even on single or multi line.
DBMS_RES
---------------------------------------------------------------------------------------------------------
DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE NAME IS '||VAR_ENAME)
DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE NAME IS '||VAR_ENAME)
Please suggest some ways to sort out the things as i have lot such procedures and i want to get rid off all such DBMS_OUTPUT.PUT_LINE statements.
-MS