Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Fetching multi line DBMS_OUTPUT.PUT_LINE statments

628524Jun 12 2008 — edited Jun 13 2008
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 11 2008
Added on Jun 12 2008
15 comments
1,214 views