Generate a data window using the oracle Stored Procedure returned record set as a data source in PB
First, oracle Stored Procedure writing is different from MSSQL, with a big difference. For the stored procedure of the returned dataset, you must use the oracle package to define the cursor.
However, if you write the stored procedure in the package according to the regular oracle statement, if so, PB cannot find the Stored Procedure (if you use direct connection) (If ODBC is used, the parameters of stored procedures may not be properly identified)
Therefore, we need to write the stored procedure separately.
First, let's take a look at the writing of the stored procedure of oracle's regular returned result set.
Method 1:
Create or replace package pkg_Sp_Changebill
-- Package based on change ticket
-- Create a package (because the stored procedure of oracle needs to use a cursor to return the record set)
As
Type Cur_myRecord Is ref Cursor RETURN item % RowType;
Procedure sp_mx3_wgggcp (stritemid varchar2, cur_List out cur_MyRecord );
End pkg_Sp_Changebill;
/
Create or replace package body pkg_Sp_Changebill
Procedure sp_mx3_wgggcp (stritemid varchar2, cur_List out cur_MyRecord)
Begin
-- Execution body of the stored procedure
Open cur_List
Select * from item where itemid = stritemid;
End sp_mx3_wgggcp;
End pkg_Sp_Changebill;
If you use the preceding method, the stored procedure cannot be found if you connect to the database directly in PB, if you connect to the database in odbc mode, you can find some stored procedures but cannot correctly identify the parameters.
Method 2: The following method does not include the stored procedure in the package.
Create or replace package pkg_Sp_Changebill3
As
Type Cur_myRecord Is ref Cursor RETURN item % RowType; -- indicates that this Cursor Is returned in the form of an item Table Record
End pkg_Sp_Changebill3;
/
Create or replace procedure sp_mx3_wgggcp3 (stritemid varchar2,
Cur_List out pkg_Sp_Changebill3.cur_MyRecord)-You can use the package name directly for the cursor.
As
Begin
-- Execution body of the stored procedure
Open cur_List
Select * from item
Where itemid = stritemid;
End sp_mx3_wgggcp3;
The stored procedure can be found in direct connection mode and the parameters can be identified normally. The stored procedure can be found in odbc mode, but the parameter cannot be correctly identified.