0

I have a select statement which is as follows

 SELECT 
    PLN.PLAN_NAME,
    HON.IVC_CODE,
    HON.FORENAME,
    HON.SURNAME,
    HON.SALUTATION
    FROM PLANS  PLN
    INNER JOIN COMPANIES COMP ON
    COMP.COMP_CODE = PLN.COMP_CODE
    INNER JOIN TRADING_STOCK_EXCHANGES TSE ON
    TSE.COMP_CODE = COMP.COMP_CODE
    INNER JOIN FINANCE_ORGANISATIONS FORG ON
    FORG.FINANCE_ORG_ID = TSE.FINANCE_ORG_ID
    INNER JOIN HOLDER_NAMES HON ON
    HON.COMP_CODE = PLN.COMP_CODE
    WHERE FORG.FINANCE_ORG_TYPE_CODE = 'SE' AND 
    COMP.COMP_CODE = 'B598' AND
    --HON.FORENAME LIKE '%g%' AND
    FORG.ORG_NAME IN ('NASDAQ','NEW YORK STOCK EXCHANGE');

This works and I can see the results.

I have a package function rr400_generate_sip_movement.get_sip_movement which accepts one In Parameter and tree output parametes. I have put something like this so far

declare        
          po_plan_name VARCHAR2(10 CHAR);
          po_client_comp_name VARCHAR2(50 CHAR);
          po_comp_code VARCHAR2(10 CHAR);  
          rc sys_refcursor;
          ab sys_refcursor;
        begin
            rc := rr400_generate_sip_movement.get_sip_movement(137610, po_plan_name, po_client_comp_name, po_comp_code);
             
       end;

When I run this I see a message PL/SQL statement completed successfully. I am assuming the PL/SQL block is good.

What I would like to do is join the package functions results with my select statement results on IVC_CODE(Column Name as per select statement). I am only interested in the IVC's which are in the package function.

Any help is much appreciated. sorry I am new to PL/SQL. I have tried a few routes for this and as per the suggestion from @littlefoot in my other question

PL/SQL Function result to a table

This approach might be a cleaner and tidier approach. Thanks in advance.

Thom A
  • 88,727
  • 11
  • 45
  • 75
abeuwe
  • 159
  • 2
  • 15

3 Answers3

1

If I understood you correctly, function returns refcursor (by default) and 3 OUT parameters (you can store into variables). As you want to use them (variables) in another query, it is irrelevant what refcursor contains.

Here's an example; this is function that simulates your rr400_generate_sip_movement.get_sip_movement (I'm returning only one OUT value, though):

SQL> CREATE OR REPLACE FUNCTION f_test (par_deptno  IN     NUMBER,
  2                                     par_job        OUT VARCHAR2)
  3     RETURN SYS_REFCURSOR
  4  AS
  5     rc  SYS_REFCURSOR;
  6  BEGIN
  7     par_job := 'CLERK';
  8
  9     OPEN rc FOR SELECT empno, ename, job
 10                   FROM emp
 11                  WHERE deptno = par_deptno;
 12
 13     RETURN rc;
 14  END;
 15  /

Function created.

Let's use it; read comments within code:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     l_deptno  NUMBER := 10;   --> IN parameter to the function
  3     rc        SYS_REFCURSOR;  --> that's what function returns
  4     l_job     VARCHAR2 (10);  --> OUT parameter
  5     l_cnt     NUMBER;         --> local variable (for joining / filtering purposes)
  6  BEGIN
  7     rc := f_test (l_deptno, l_job);
  8
  9     DBMS_OUTPUT.put_line ('refcursor contents (along with PAR_JOB): ');
 10
 11     FOR cur_r IN (SELECT ename, job, sal
 12                     FROM emp
 13                    WHERE job = l_job)
 14     LOOP
 15        DBMS_OUTPUT.put_line (cur_r.ename || ' ' || cur_r.job);
 16     END LOOP;
 17
 18     -- join variable returned by function in another query
 19     SELECT COUNT (*)
 20       INTO l_cnt
 21       FROM emp e
 22      WHERE e.job = l_job;      --> L_JOB is consumed here
 23
 24     DBMS_OUTPUT.put_line ('Number of ' || l_job || ' employees: ' || l_cnt);
 25  END;
 26  /
refcursor contents (along with PAR_JOB):
SMITH CLERK
ADAMS CLERK
JAMES CLERK
MILLER CLERK
Number of CLERK employees: 4

PL/SQL procedure successfully completed.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

It's been a while since I worked with Oracle, but it should be doable by returning a type from a function and using the table keyword. Something like this Join to an oracle table valued function

Lauris
  • 1
  • 1
0

From how i understand the question, i think instead of returning a cursor, working with pipelined functions might work out better in your case. Oracle Doc: https://blogs.oracle.com/connect/post/pipelined-table-functions#:~:text=Pipelined%20table%20functions%20are%20something,clause%20of%20a%20SELECT%20statement

--written in pseudo-code it would be something along the lines of:
--in your package:
create function xyz(<params>) return <table_type> pipelined
is
--<fill data> multiple examples given in the docs
  pipe row('abc');
  return;
end;
--

--select statement
So in your case it would look sth along the lines of
select <query#1>
<join> table(package.xyz(<params>)
where <condition>;

But be wary: the table()-Function likes to swallow no_data_found exceptions without raising them! This will then lead to just an empty data-set/table!

Grubchri
  • 1
  • 2