Re: A couple of questions

From: Ken Johnson <kjohnson_at_ryback.com>
Date: 1998/01/30
Message-ID: <01bd2db4$48c25360$79110fcf@kjohnson.ryback.com>#1/1

songmuh <songmuh_at_MCI2000.com> wrote in article <01bd2d9f$be791c20$b24337a6_at_g2k486>...
> > Two more questions for all you Oracle gurus:
> 1. How can I list all the stored procedures?
SELECT DISTINCT

	OWNER,
	NAME,
	TYPE
FROM	DBA_SOURCE;

This will list stored procedures, functions, packages, and package body's.

If you want just procedures you could use: SELECT DISTINCT

	OWNER,
	NAME,
	TYPE
FROM	DBA_SOURCE

WHERE TYPE='PROCEDURE'; You can also replace DBA_SOURCE with ALL_SOURCE (for just objects you have access to under your current login), or USER_SOURCE (for just object owned by you).

> 2. I want to get the table definition and format it
> to a text file so that I can import the file into a structure.
> I know that I can describe the table at SQL*Plus prompt,
> but is there a way to catch the output of the DESC
> command and save it to a file?

If you mean just capture the output as it is on screen, then this will work:

spool yourfile.txt
DESC table_name
spool off

If you mean formatting it as a SQL statement, then Oracle doesn't do that itself.
A company called RevealNet does have a program that does this (among numerous other things). A version of the software comes with the book "Advanced Oracle with PL/SQL" from O'Riley (which is how I got it). I thing it is also on RevealNet's web page ( http://www.revealnet.com ).

Hope this helped,
Ken Received on Fri Jan 30 1998 - 00:00:00 CST