Debug the Oracle stored procedure in SQLPLUS

Visual tools are used to develop and debug Oracle stored procedures, such as PLSQLDEVELOPER and TOAD. No one is stupid enough to use vim for development and sqlplus for debugging.

Visual tools are used to develop and debug Oracle stored procedures, such as pl SQL DEVELOPER and TOAD. No one is stupid enough to use vim for development and SQL plus for debugging.

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 being debugged by 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] [ [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 the Oracle topic page? Tid = 12