Commonly Used Oracle Commands

On-line Oracle Documentation (Current as of Dec. 15, 2004)
List of Books | Oracle FormsPL/SQL | SQL*Loader | SQL*Plus | Messages and Codes

Other Topics:   EnvironmentIntegrity Constraints | Stored ProceduresTable Descriptions/CommentsView Definitions



Working with Stored Procedures

To view the source code for a stored procedure, use the following commands in SQL*Plus.

set pagesize 500
set linesize 200
SELECT DISTINCT OWNER,NAME FROM ALL_SOURCE WHERE OWNER != 'SYS'

The above command will list all of the stored procedures that are not System procedures

SELECT SUBSTR(TEXT,1,80) FROM ALL_SOURCE WHERE NAME = <Procedure Name> ORDER BY LINE;

The above command will list the actual source code for a particular stored procedure.

Note: You must be logged into the correct instance and user to see procedures that belong to that user. 

When trying to update a procedure you may receive the message "Procedure created with compilation errors".  In order to see the actual errors, use the following commands.

SET VERIFY OFF;
SET HEADING OFF;
SET PAGESIZE 2000;
SELECT 'At line '||LINE||', col '||POSITION||': '||SUBSTR(TEXT,1,100) FROM USER_ERRORS WHERE NAME=<Procedure Name> ORDER BY SEQUENCE;
SELECT 'LineSUBSTR(TEXT,1,80) from USER_ERRORS WHERE NAME= <Procedure Name> ORDER BY LINE;

Stored procedures are kept in the instance in which they are created.  This can cause complications when testing because some database links, etc. may not exist in a test version of the database.

It is always possible to test a stored procedure by running it in stand-alone mode.  The only change required is to remove the keywords CREATE OR REPLACE from the procedure definition.  You can then start the procedure directly from SQL*Plus. 

Note:  It is highly recommended that you ALWAYS use the ROLLBACK command after testing procedures in stand-alone mode so that you have not committed any changes to the database.

You can allow other users to use a particular stored procedure using the GRANT command, as shown below;
GRANT EXECUTE ON <procedure_name> TO <some_user>;

You can then set up a SYNONYM for the stored procedure by logging on as the other user, in this case <some_user>, and using the following command;
CREATE SYNONYM <procedure_name> FOR <creating_user>.<procedure_name>;


Descriptions for Oracle Tables

To find all the descriptions for tables owned by a particular user (SCHEMA), log into SQL*Plus as that user and type the command

SELECT TABLE_NAME,SUBSTR(COMMENTS,1,255) FROM ALL_TAB_COMMENTS WHERE OWNER = '<table_owner>';

To add a description for an Oracle table, use the command COMMENT ON TABLE <tablename> IS 'This is a comment'


Displaying View Definitions

From within SQL*Plus use the commands
SET LONG 400
SELECT TEXT FROM ALL_VIEWS WHERE VIEW_NAME= <view_name>



Integrity Constraints and Index Columns

During the course of some operation or procedure you may encounter a message such as the following:

ORA-00001: unique constraint (USER.TAPES_U2) violated.

To find out what is wrong, you will have to look at the definition for the constraint or index that is having the problem.  Note that, in many cases, constraint errors are actually generated by Index problems rather than true database constraints.

To find out if the constraint is actually a constraint or an index, use the following two queries while logged in as the constraint/index owner (i.e. USER if the constraint is USER.TAPES_U2):

SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME=<constraint_name>;
SELECT * FROM USER_INDEXES WHERE INDEX_NAME=<constraint_name>;

To find the columns used in a constraint, use the following command, logged in as the owner of the constraint.

SELECT * FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME=<constraint_name>;

To find the columns in an index, use the following command;

SELECT TABLE_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE INDEX_NAME=<indexname>


 
 
 

1