How to get the content written for the packages in Oracle DB.

To extract specific ranges or large blocks (e.g., 2,000 lines) of context from an Oracle Package or Package Body, the most effective methods rely on querying the data dictionary views (USER_SOURCE / ALL_SOURCE) or using Oracle's built-in package DBMS_METADATA.

Option 1: Query USER_SOURCE for a Specific Line Range

If you know the starting line (for example, reading 2,000 lines starting from line 1000 to line 3000), query USER_SOURCE directly:

SQL

SELECT line, text
FROM user_source
WHERE name = 'YOUR_PACKAGE_NAME'       -- Replace with package name (UPPERCASE)
  AND type = 'PACKAGE BODY'            -- 'PACKAGE' or 'PACKAGE BODY'
  AND line BETWEEN 1000 AND 3000       -- 2,000-line range
ORDER BY line;

Option 2: Extract Context Around a Specific Line or Keyword

If an error or stack trace points to a specific line number (e.g., Line 2500), you can extract 1,000 lines before and 1,000 lines after:

SQL

SELECT line, text
FROM user_source
WHERE name = 'YOUR_PACKAGE_NAME'
  AND type = 'PACKAGE BODY'
  AND line BETWEEN (2500 - 1000) AND (2500 + 1000)
ORDER BY line;

Option 3: Pull Entire Source Code (No Line Limits)

If you want to pull the entire source code for packages exceeding 2,000 lines into your client tool or SQL*Plus session:

Using DBMS_METADATA:

SQL

SELECT dbms_metadata.get_ddl('PACKAGE_BODY', 'YOUR_PACKAGE_NAME', 'YOUR_SCHEMA') 
FROM dual;

Using SQL*Plus (Avoids line clipping issues):

SQL

SET LINESIZE 32767
SET PAGESIZE 0
SET LONG 20000000
SET LONGCHUNKSIZE 20000000
SET TRIMSPOOL ON
SPOOL package_output.txt

SELECT text
FROM user_source
WHERE name = 'YOUR_PACKAGE_NAME'
  AND type = 'PACKAGE BODY'
ORDER BY line;

SPOOL OFF;