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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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,194 views