Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

output plsql procedure output

RobeenJul 25 2022

Oracle DB 12.1.0.2
Hello Team,

kindly advise how to get the output of the procedure below on sqldeveloper?

create or replace PROCEDURE     SPIO_UTS_BILL_INFO1 (
 VI_Tel_Num        IN Varchar2,
 V_ACCOUNT_CODE OUT Number,
 VO_Outstanding_Amt  OUT Number,
 VO_Waived_Amt     OUT Number,
 VO_Purchase_Amt    OUT Number)
 IS

/******************************************************************************
  NAME:    SPIO_UTS_BILL_INFO
  PURPOSE:

  REVISIONS:
  Ver    Date    Author      Description
  --------- ---------- --------------- ------------------------------------
  1.0    04/02/2019  khoosunee    1. Created this procedure.

  NOTES:

   Object Name:   SPIO_UTS_BILL_INFO
   Sysdate:     04/02/2019
   Description:   Stored Procedure to retrieve Waived Amount, Service Outstanding Amount and Purchase (Devices & Accessories) Outstanding Amount based on a phone number
   New version updated and optimised on 12/02/2019 -- Breaking the Main Select Statement into various 3 Select Statements avoid joins with more than 2 tables

******************************************************************************/
 V_ACC_LINK_CODE NUMBER;


BEGIN



/* Retrieve ACCOUNT_LINK_CODE_N from CB_ACCOUNT_SERVICE_LIST using Phone Number */

    SELECT ASL.ACCOUNT_CODE_N
     INTO V_ACCOUNT_CODE
     FROM CB_ACCOUNT_SERVICE_LIST@cbsstandby ASL
    WHERE NVL(ASL.TO_DATE_D,SYSDATE) >= SYSDATE
      AND ASL.SERVICE_INFO_V = VI_Tel_Num
      AND ASL.TO_DATE_D = (select max(to_date_d) from cb_account_service_list@cbsstandby asl1
      where asl1.service_info_v = asl.service_info_v);
       
      
/* Waived Amount */     

    SELECT SUM(NVL(SAP.OVERALL_AMT_N,0))/100
     INTO VO_Waived_Amt
     FROM CB_ACCOUNT_RECEIVE_PAY@cbsstandby ARP, 
       CB_SUB_AR_AP@cbsstandby SAP, CB_ACCOUNT_MASTER@cbsstandby CAM
     WHERE SAP.MAIN_ACCOUNT_LINK_CODE_N = CAM.ACCOUNT_LINK_CODE_N
      AND ARP.ACCOUNT_LINK_CODE_N = CAM.ACCOUNT_LINK_CODE_N
      AND ARP.TRANS_NUM_V = SUBSTR(SAP.TRANS_NUM_V,2,14)
      AND SUBSTR(SAP.TRANS_NUM_V,1,3) = '5CR'
      AND ARP.USER_CODE_N NOT IN (1,2)
      AND CAM.ACCOUNT_CODE_N = V_ACCOUNT_CODE;

       
/* Outstanding Amount */

    SELECT SUM(NVL(SAA.OVERALL_AMT_N,0) - NVL(SAA.CLEARED_AMT_N,0))/100
    INTO VO_Outstanding_Amt
    FROM CB_SUB_AR_AP@cbsstandby SAA , cb_account_master@cbsstandby CAM
    WHERE SAA.MAIN_ACCOUNT_LINK_CODE_N = CAM.ACCOUNT_LINK_CODE_N
    AND CAM.ACCOUNT_CODE_N = V_ACCOUNT_CODE;

      
/* Purchase Outstanding Amount */

     SELECT SUM(NVL(SAA.OVERALL_AMT_N,0) - NVL(SAA.CLEARED_AMT_N,0))/100
      INTO VO_Purchase_Amt
     FROM CB_POS_TRANSACTIONS@cbsstandby CPT ,CB_SUB_AR_AP@cbsstandby SAA, CB_ACCOUNT_MASTER@cbsstandby CAM
     WHERE CPT.TRANSACTION_NUM_V = SAA.TRANS_NUM_V
       AND SAA.MAIN_ACCOUNT_LINK_CODE_N = CAM.ACCOUNT_LINK_CODE_N
       AND CPT.TYPE_OF_SALES_V = 'C'
       AND CAM.ACCOUNT_CODE_N = V_ACCOUNT_CODE;

   EXCEPTION
   WHEN NO_DATA_FOUND THEN
    NULL;
   WHEN OTHERS THEN
    --Consider logging the error and then re-raise
    RAISE;

END SPIO_UTS_BILL_INFO1;

expected output should be recordset but only console below is displayed.

image.png
Thanks,

Roshan
This post has been answered by SachinP-Oracle on Jul 25 2022
Jump to Answer

Comments

BluShadow

What makes you think the output should be a record set?
PL/SQL isn't T-SQL. The procedures execute on the database, so anything you want out of the procedure will be whatever you put into your OUT variable(s).
You could have a function also, but the same applies.
What you're probably looking for is some sort of pipelined table function so you can select from that function and it's results act like a table?

SachinP-Oracle

Please create the function instead of Procedure and restore the output of SQL SELECT into OUT variable. Then that OUT variable will contain the output of SQL SELECT. That would be easy way. You can achieve same thing in Procedure also but for that you need IN and OUT variables.

BluShadow

OUT variables are not ideal unless you're passing out values within PL/SQL, as functions with OUT parameters can't be used in SQL statements, so very limiting. Also, you can't just pass out the results of a select statement easily, especially if there are multiple rows.
A pipelined table function would be more appropriate so that the data can be used inside other SQL statements like a table of data, and provide the "record set" kind of logic.

1 person found this helpful
SachinP-Oracle

Yes, you are right but here he is looking for the SELECT output which is the SUM so it is single output only and Multiple. So for this case, function with OUT variable could be the ideal solution

1 person found this helpful
Robeen

In the Output Variables section I see the output.

SachinP-Oracle

So once you are able to get the output in OUT variable then your case is solved ?

SachinP-Oracle
Answer

create or replace function test (INpar in varchar2,OUTPar out varchar2, INOUTPar in out varchar2) return varchar2 is
begin
OUTPar:='I am OUT PARAM';
INOUTPar:='I am INOUT PARAM';
return 'Hello Done';
end;
/

declare
x VARCHAR2:='Hi';
y VARCHAR2:='ME';
z VARCHAR2;
begin
z:=test(x,z,y);
DBMS_OUTPUT.PUT_LINE(x||':'||x);
DBMS_OUTPUT.PUT_LINE(y||':'||y);
DBMS_OUTPUT.PUT_LINE(z||':'||z);
end;
/

Marked as Answer by Robeen · Jul 26 2022
1 - 7

Post Details

Added on Jul 25 2022
7 comments
125 views