Debug Oracle stored procedures in SQL PLUS

In general, we use visual tools such as pl SQL DEVELOPER and TOAD to develop and debug Oracle stored procedures. No one is stupid enough to use vim for development and SQL plus for debugging. But in some cases, we know how to debug the stored procedure in SQL plus is still profitable.

For some of my clients, I can only connect to the database through ssh. The developer told me that a stored procedure will die after debugging with pl SQL developer.

I cannot see any errors in the database system management view. What should I do?

No way. You can only use SQL plus to debug the stored procedure!

After several layers of network settings are bypassed, connect to the database server operating system and open the sqlplus tool window.

The stored procedure to be debugged is like this:

Create or replace procedure "PROC_OBJECT_GET_ID" (p_id_name varchar2,
Result out integer,
P_message out varchar2)

One input variable and two output variables are of the varchar, integer, and varchar types respectively.

In SQL plus, use var to declare variables, use exec to assign values and execute stored procedures

SQL> VAR P_ID_NAME VARCHAR2 (100)
SQL> VAR RESULT NUMBER
SQL> VAR P_MESSAGE VARCHAR2 (100)

SQL> EXEC: P_ID_NAME: = 'com.css.cms.doc ument'
SQL> execute PROC_OBJECT_GET_ID1 (: P_ID_NAME,: result,: p_message );
 
The PL/SQL process is successfully completed.
 
SQL> PRINT RESULT
 
RESULT
----------
-1
 
SQL> PRINT P_MESSAGE
 
P_MESSAGE
--------------------------------------------------------------------------------
ORA-01422: the number of rows actually returned exceeds the number of requested rows
 

 

The variable type is only listed in help.

SQL> variable result integer;
Usage: VAR [IABLE] [<variable> [NUMBER | CHAR (n [CHAR | BYTE]) |
VARCHAR2 (n [CHAR | BYTE]) | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
BINARY_FLOAT | BINARY_DOUBLE]

The sqlplus tool also has some functions that can be learned using the help method.

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12