How to get a view on packages larger than 30K in Oracle Portal

For a project with Oracle Portal, I had to do some lookups in packages and procedures on the database to get some information about the project.   Since I wasn’t working full-time for this project I had no access to the DB, except via the portal itself.

Several times I had the problem that the client wanted a quick answer for his question, but therefor I needed to dig into the code that was developed in the past. The problem was that the size for editing/viewing a package or procedure was limited to 30K and since there where several packages bigger than 30K, I couldn’t give them a quick answer.

If you only want to read the packages(not edit), there is a very easy portal solution for this problem:

You just have to create a new ‘Portal Report’ in a locally built provider.

In this report choose the option Reports from SQL Query and then add the following query:

SELECT text
FROM USER_SOURCE
WHERE TYPE = ‘PACKAGE BODY’ –(or PROCEDURE OR PACKAGE)
AND NAME = ‘<name of the package>’
ORDER BY line

Change the Maximum Rows Per Page to the amount that you want to see on your screen(let’s say 10000)
When you run this report you can see the code that you where looking for.

If you would like to use this for several procedures/packages you can use some bind variables
SELECT text
FROM USER_SOURCE
WHERE TYPE = :type /*(or PROCEDURE OR PACKAGE)*/
AND NAME = :name
ORDER BY line

A very easy solution and it made my life much easier and I was able to respond much faster to questions of the client.
(Don’t forget to set the security for this portlet only to administrators)

About these ads

2 thoughts on “How to get a view on packages larger than 30K in Oracle Portal

  1. If you have access to the PORTAL or ORCLADMIN user or any other lightweight user with DBA rights, you can also click on “Show Properties” in Navigator to see the contents of packages larger than 30k.

    Rgds/Mark M.

    • Mark thank you for your comment. Yes this is correct, only as developer you don’t always get DBA privileges on portal (this would mean that you have the same rights as the sys user).
      Another nice thing about this is that you could publish this as portlet on your personal page(or maybe better, on a tab of your personal page).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s