Analyze troubleshooting of faults encountered during Oracle storage creation based on the package name

Oh, when you create a stored procedure, you can directly use the ldquo; createorreplaceprocedure stored procedure name rdquo.

Oh, when you create a stored procedure, you can directly use the ldquo; create or replace procedure stored procedure name rdquo.

Oh, when you create a stored procedure, you can directly create it in the form of "create or replace procedure stored procedure name" without any problems, in the past two days, I tried the inventory package in the project. During the storage creation process, I encountered an actual problem. After running commands in PL/SQL, there was no compilation error .. After running, when you look at pl/SQL, there will be a red x on the package, dizzy .. The error message above indicates that an error is reported on create or replace package body display_users_package is created below. At first glance, there is no mistake in writing Guan jianzi .. In fact, the above prompt is very correct after finding the cause and thinking back. (This means that the command for building the package body cannot be run together during package creation. In other words, delete all the statements starting from create or replace package body display_users_package is to the end)

Here, I would like to thank his department colleague "Mu Sheng" for his help in finding the cause of the error.
The code is put together in an online article, including creating packages, package subjects, and stored procedures. There is basically no description, most people think that these commands are running together ..

The complete code is as follows:
Create or replace package xinfei_ims.display_users_package is
Type search_results is ref cursor;
Procedure display_users_proc (results_out search_results, userId in test_users.user_id % type );
End display_users_package;

Create or replace package body display_users_package is
Procedure display_users_proc (results_out search_results, userId in test_users.user_id % type) is
Begin
If userId is not null then
Open results_out for select * from test_users where user_id like userId | '% ';
Else
Open results_out for select * from test_users;
End if;
End display_users_proc;
End display_users_package;

In fact, the commands for "Running build package" and "Running build package subject" must be separated .. In this case, the left menu in PL/SQL shows that the PACKAGE and PACKAGE BOYIES in Oracle are separated.

Create a PACKAGE
Create or replace package display_users_package is
Type search_results is ref cursor;
Procedure display_users_proc (results_out search_results,
UserId in test_users.user_id % type );
End display_users_package;

Create PACKAGE BODY
Create or replace package body display_users_package is
Procedure display_users_proc (results_out search_results, userId in test_users.user_id % type) is
Begin
If userId is not null then
Open results_out for select * from test_users where user_id like userId | '% ';
Else
Open results_out for select * from test_users;
End if;
End display_users_proc;
End display_users_package;

The following format is used to create a stored procedure containing packages in JAVA:
Package name. Stored Procedure name
CallableStatement cs = null;
Cs = conn. prepareCall ("call display_users_package.display_users_proc (?,?) ");
Cs. registerOutParameter (1, OracleTypes. CURSOR );
Cs. setObject (2, params );