Re: How do I know the line number in a stored procedure?

From: Mike Daniell <mike_at_helix.com.au>
Date: Wed, 24 Nov 1999 14:26:51 +1000
Message-ID: <383B690B.A310D239@helix.com.au>

Geoffrey Stewart wrote:

<<snipped previous message>>

> Use this little Sql*Plus script...
>
> -------------------------
> SET PAGESIZE 2000
> COLUMN text FORMAT A72 trunc
> COLUMN line FORMAT 9999
> SET LINESIZE 80
>
> accept xobj char prompt 'Please enter procedure to list :'
>
> ttitle '** SOURCE FOR PROCEDURE: &xobj **'
>
> SELECT line,text
> FROM user_source
> WHERE name = upper('&xobj')
> and type like 'PROCEDURE%'
> ORDER BY type,line
> /
>
> ttitle off
> -----------------------------
>
> cheers
> Geoffrey Stewart

Thanks for this suggestion, Geoff. It sure beats what I was doing which was to load up the source file in a windows version of the Unix vi editor, and then to delete all the blank lines. As my main use for this is identifying errors reported by the compilation of a procedure or package body, I have added the facility to nominate a range of line numbers:

SET PAGESIZE 2000
SET LINESIZE 96
COLUMN text FORMAT A90 TRUNC
COLUMN line FORMAT 9999

ACCEPT xobj CHAR PROMPT 'Please enter procedure or package to list: ' ACCEPT xstart NUMBER DEFAULT 1 PROMPT 'Start line number [1]: ' ACCEPT xend NUMBER DEFAULT 9999 PROMPT 'End line number [9999]: '

TTITLE '** SOURCE FOR &xobj **'

SELECT          line,text
FROM            user_source
WHERE           name = upper('&xobj')
AND             type IN ('PROCEDURE', 'PACKAGE BODY')
AND             line BETWEEN &xstart AND &xend
ORDER BY        type,line

/
COLUMN text CLEAR
COLUMN line CLEAR
TTITLE OFF Regards,
Mike Received on Tue Nov 23 1999 - 22:26:51 CST