0

Please forgive me, I am very green with PL/SQL stored procedures. I am writing a stored procedure that will take in an input parameter (OFF_Set) and run a SQL statement using OFFSET and FETCH functions.

This procedure is called from an Oracle Integration program (OIC) and will loop this procedure and increment the Off_set parameter each time, in order to avoid exceeding file size thresholds in the OIC program.

I have the below procedure written, however I am not sure how to output all the selected columns back to the calling OIC program. I saw some things about using the SYS_REFCURSOR to return all the rows output from the Select statement, but I'm not sure if how I have this written now will do that...I need to output each column individually.

create or replace PACKAGE BODY "DEPLETION_ERRORS_MONTHLY_PKG" 
AS

PROCEDURE RUN_SQL (OFF_Set IN NUMBER, p_errors out SYS_REFCURSOR)
IS BEGIN
OPEN p_errors for

  SELECT subinventory, source_code||'-'||trx_reference source_casenum_line,item_number,
item_description, error_msg, filename, creation_date, quantity_used, quantity_wasted, INV_ITEM_FLAG,
INV_CATEGORY_NAME 
from TGC_INT016_INV_DEPL_FBDI
where 1=1 
 and status='ERROR' 
 and creation_date >= add_months(trunc(sysdate,'mm'),-1) 
 and creation_date < trunc(sysdate, 'mm')
ORDER BY CREATION_DATE
OFFSET OFF_Set ROWS
FETCH NEXT 10000 ROWS ONLY;

END RUN_SQL;
END DEPLETION_ERRORS_MONTHLY_PKG;

EDIT:

enter image description here

Nick
  • 268
  • 8
  • 33
  • I'm not sure what question you're asking here. You seem to be asking whether your code does what you want your code to do. But you're presumably in the best position to tell us that. Does your code compile? If not, what error do you get. Does it run? Does it return the results you want? If not, in what way? – Justin Cave Apr 07 '23 at 13:43
  • @JustinCave I'm not asking whether the code does what I want it to, I'm asking how I can output all the data from the SELECT statement back to the calling program? Do I need to specify OUT variables for each select column, etc..? – Nick Apr 07 '23 at 13:55
  • @Nick - The calling application can fetch data from the `sys_refcursor` into separate local variables or collections. So it sounds like this is what you want. – Justin Cave Apr 07 '23 at 13:59
  • @JustinCave is there another way to output the data other than `sys_refcursor`? I basically want to return all the data as if I am just executing the SQL statement on it's own... – Nick Apr 07 '23 at 14:15
  • You could return a collection. You could write a pipelined table function rather than a procedure. But functionally, a `sys_refcursor` is returning the data just as if the caller ran the SQL statement. Potentially @OldProgrammer is right and you're really trying to ask how to test a stored proc that returns a `sys_refcursor`. – Justin Cave Apr 07 '23 at 14:46
  • @JustinCave I added a screenshot above, but essentially the issue is that the only data being returned by calling program shows just the sys_refcursor variable (P_ERRORS), whereas I would like it to be broken out individually by column, so that I can map it accordingly when ouputting a file – Nick Apr 07 '23 at 14:57
  • @Nick did you read the link I posted, because it shows you exactly how to do what you want. – OldProgrammer Apr 07 '23 at 15:00

0 Answers0