How to use a PIPELINE in plsql

[Srivathsava]

Hi Experts,

We have a requirement where the ‘select person_id from TABLE(abc_pkg.abc_fun(:transactionId)) ORDER BY emp_level’ should be used where it returns more than one person (we are using this query in Oracle Apps AME to fetch the list of approvers. So we need to use in this format only).

We have XX_EMP_APPRVR_TBL table where it has list of all employees. So, we need to fetch all person_ids from XX_EMP_APPRVR_TBL using abc_pkg.abc_fun. Here, we heared that we need to use PIPELINE command to achieve the result.

Can anyone please help me on how to create function using PIPELINE

Thanks and Regards,

Sri

[NormTeamT]

Hi Srivathsava,

Hopefully, the following will help.

– Create a test table first and populate it with some data.
create table xx_emp_apprvr_tbl(
Person_id number(6),
Transaction_id number(8)
);

insert into xx_emp_apprvr_tbl values (1,1);
insert into xx_emp_apprvr_tbl values (2,1);
insert into xx_emp_apprvr_tbl values (3,1);
insert into xx_emp_apprvr_tbl values (616,1);
insert into xx_emp_apprvr_tbl values (1,2);
insert into xx_emp_apprvr_tbl values (777,2);
insert into xx_emp_apprvr_tbl values (666,300);
insert into xx_emp_apprvr_tbl values (1002, 450);
commit;

CREATE OR REPLACE PACKAGE abc_pkg AS
– Create a record type for the data we want to return. Doing it as
– a record in this manner allows the column_names to be explicitly
– specified. In this case, we will see PERSON_ID rather than the
– default COLUMN_NAME.
TYPE Person_id_rec is record (person_id xx_emp_apprvr_tbl.Person_id%type);

-- Now we must create a type of a table of the above records, as this
-- is what the pipelined functions return, tables.
TYPE Person_id_table IS TABLE OF Person_id_rec;

-- Finally, our pipelined function to return all the person_id_recs for
-- a given transaction_id passed in.
FUNCTION abc_fun(TID NUMBER) RETURN Person_id_table PIPELINED;

END abc_pkg;
/

CREATE OR REPLACE PACKAGE BODY abc_pkg AS

FUNCTION abc_fun(TID NUMBER) RETURN Person_id_table PIPELINED IS
BEGIN
– Loop around each person_id in the table, who has a given
– transaction_id. For each record found, pipe it back to
– the caller.
FOR rec IN (select person_id
from xx_emp_apprvr_tbl
where transaction_id = TID)
LOOP
PIPE ROW(rec);
END LOOP;

-- This is a function, so we must return something, but as this
-- is a pipelined function, RETURN on its own will do.
RETURN;

END abc_fun;

END abc_pkg;
/

select * from table(abc_pkg.abc_fun(1)) order by 1;

PERSON_ID
1
2
3
616

Ok, an explanation.

You must return a table of some kind from a pipelined function. As you wish to return a single column, the person_id, we first create a TYPE of person_id_rec in the package itself. Doing this as a record is useful because it allows you to name the column(s) that your function will return. Here I’ve simply named it PERSON_ID. You will note that I have “anchored” the PERSON_ID column in the record type, to the table’s PERSON_ID column by the way I’ve used table_name.column_name%type in the creation of the type. That way, if PERSON_ID in the table is ever changed, you won’t have to search all your code to make changes, Oracle will simply recompile it on the fly for you! You should almost never hard code a data type when it relates to something in a table or view.

Next we have to create a table of the record type. This is what the pipelined function will return. In this case, I call it person_id_table and it is simply a table of the above record type.

Finally, we have your abc_fun function, which takes in a single parameter of the correct type for a transaction_id in the table (you see that I have anchored that as well) and returns a value which is a table type, the one we have just created, person_id_table.

In the package body, the function is pretty simple, it enters a loop for each row in the table which has the supplied transaction_id. Each row is referred to as “rec” and each row is simply PIPEd out to the caller. Functions must return something, but pipelined functions just need to RETURN.

When you execute the SQL statement:

select * from table(abc_pkg.abc_fun(1));

You will see that the column name in the output (as shown above) is named PERSON_ID as we created in our record.

HTH

Cheers,

Norm. [TeamT]

[NormTeamT]

I wrote:

Finally, we have your abc_fun function, which takes in a single parameter of the correct type for a transaction_id in the table (you see that I have anchored that as well) and returns a value which is a table type, the one we have just created, person_id_table.

Obviously, I DIDN’T anchor the trasaction_id, but I SHOULD have! Sorry about that.

[Srivathsava]

Hi Norm,

It is showing no data

Below is package specification and body i created

CREATE OR REPLACE PACKAGE xx_po_req_workflow_pkg
AS
** TYPE person_id_rec2 IS RECORD (**
** manager_id VARCHAR2(100)**
** );**

TYPE person_id_tbl2 IS TABLE OF person_id_rec2;

FUNCTION xx_get_approvers (p_req_hdr_id IN NUMBER)
** RETURN NUMBER;**

FUNCTION xx_po_req_appvr_mgr_fnc (p_req_hdr_id IN NUMBER)
** RETURN person_id_tbl2 PIPELINED;**

END xx_po_req_workflow_pkg;

CREATE OR REPLACE PACKAGE BODY xx_po_req_workflow_pkg
AS
** FUNCTION xx_get_approvers (p_req_hdr_id IN NUMBER)**
** RETURN NUMBER**
** IS**
** v_job NUMBER := 0;**
** v_po_req per_all_people_f.person_id%TYPE;**
** —**
** v_emp_num per_all_people_f.employee_number%TYPE;**
** v_emp_name per_all_people_f.full_name%TYPE;**
** v_emp_job per_jobs_tl.NAME%TYPE := NULL;**
** v_mgr_num per_all_people_f.employee_number%TYPE;**
** v_mgr_name per_all_people_f.full_name%TYPE;**
** v_mgr_job per_jobs_tl.NAME%TYPE;**
** v_emp_id per_all_people_f.person_id%TYPE;**
** v_mgr_id per_all_people_f.person_id%TYPE;**
** —**
** v_level NUMBER := 0;**
** v_loop_emp_id per_all_people_f.person_id%TYPE;**
** —**
** v_seq_id NUMBER;**
** BEGIN**
** SELECT xx_poreq_apprv_seq.NEXTVAL**
** INTO v_seq_id**
** FROM DUAL;**

BEGIN
** SELECT ppf.person_id**
** INTO v_po_req**
** FROM po_requisition_headers_all prh, per_all_people_f ppf**
** WHERE 1 = 1**
** AND prh.requisition_header_id = p_req_hdr_id**
** AND prh.preparer_id = ppf.person_id;**
** EXCEPTION**
** WHEN OTHERS**
** THEN**
** DBMS_OUTPUT.put_line**
** ('Exception in fetching the Requester. Error: ');**
** RETURN 0;**
** END;**

v_loop_emp_id := v_po_req;

WHILE v_job < 1
** LOOP**
** BEGIN**
** SELECT DISTINCT ppf.employee_number, ppf.full_name, pj.NAME,**
** sup_ppf.employee_number, sup_ppf.full_name,**
** sup_pj.NAME, ppf.person_id, sup_ppf.person_id**
** INTO v_emp_num, v_emp_name, v_emp_job,**
** v_mgr_num, v_mgr_name,**
** v_mgr_job, v_emp_id, v_mgr_id**
** FROM per_all_people_f ppf,**
** per_all_assignments_f paf,**
** per_jobs_tl pj,**
** per_all_people_f sup_ppf,**
** per_jobs_tl sup_pj,**
** per_all_assignments_f sup_paaf**
** WHERE 1 = 1**
** AND ppf.person_id = v_loop_emp_id**
** AND ppf.person_id = paf.person_id**
** AND ppf.person_type_id = 3**
** AND NVL (ppf.effective_end_date, SYSDATE + 1) >=**
** SYSDATE**
** AND NVL (paf.effective_end_date, SYSDATE + 1) >=**
** SYSDATE**
** AND paf.job_id = pj.job_id(+)**
** AND paf.supervisor_id = sup_ppf.person_id(+)**
** AND sup_ppf.person_id = sup_paaf.person_id(+)**
** AND sup_paaf.job_id = sup_pj.job_id(+);**
** EXCEPTION**
** WHEN OTHERS**
** THEN**
** DBMS_OUTPUT.put_line ('When Others Exception in LOOP. Error: ');**
** RETURN 0;**
** END;**

v_level := v_level + 1;

INSERT INTO xx_po_req_apprvr_tbl
** (employee_num, employee_full_name, employee_job,**
** manager_employee_num, manager_full_name,**
** supervisor_job, requisition_header_id, employee_id,**
** manager_id, job_level, seq_id**
** )**
** VALUES (v_emp_num, v_emp_name, v_emp_job,**
** v_mgr_num, v_mgr_name,**
** v_mgr_job, p_req_hdr_id, v_emp_id,**
** v_mgr_id, v_level, v_seq_id**
** );**

IF v_emp_job = 'Manager’
** THEN**
** v_job := 1;**
** ELSE**
** v_job := 0;**
** END IF;**

v_loop_emp_id := v_mgr_id;
** END LOOP;**

RETURN 1;
** EXCEPTION**
** WHEN OTHERS**
** THEN**
** RETURN 0;**
** END xx_get_approvers;**

FUNCTION xx_po_req_appvr_mgr_fnc (p_req_hdr_id IN NUMBER)
** RETURN person_id_tbl2 PIPELINED**
** IS**
** v_func_return NUMBER;**
** v_job NUMBER := 0;**
** v_po_req per_all_people_f.person_id%TYPE;**
** —**
** v_emp_num per_all_people_f.employee_number%TYPE;**
** v_emp_name per_all_people_f.full_name%TYPE;**
** v_emp_job per_jobs_tl.NAME%TYPE := NULL;**
** v_mgr_num per_all_people_f.employee_number%TYPE;**
** v_mgr_name per_all_people_f.full_name%TYPE;**
** v_mgr_job per_jobs_tl.NAME%TYPE;**
** v_emp_id per_all_people_f.person_id%TYPE;**
** v_mgr_id per_all_people_f.person_id%TYPE;**
** —**
** v_level NUMBER := 0;**
** v_loop_emp_id per_all_people_f.person_id%TYPE;**
** —**
** v_seq_id NUMBER;**
** BEGIN**
** v_func_return := xx_get_approvers (p_req_hdr_id);**

IF v_func_return <> 0
** THEN**
** FOR c_poreqapprv IN (SELECT ‘person_id:’||manager_id manager_id**
** FROM xx_po_req_apprvr_tbl**
** WHERE 1 = 1**
** AND requisition_header_id = p_req_hdr_id**
** AND supervisor_job <> ‘Director’**
** ORDER BY job_level)**
** LOOP**
** PIPE ROW (c_poreqapprv);**
** END LOOP;**
** RETURN;**
** ELSE**
** RETURN;**
** END IF;**
** EXCEPTION**
** WHEN OTHERS**
** THEN**
** RETURN;**
** END xx_po_req_appvr_mgr_fnc;**

END xx_po_req_workflow_pkg;

Below is query I used. This is returning no data

SELECT manager_id FROM TABLE(XX_PO_REQ_WORKFLOW_PKG.xx_po_req_appvr_mgr_fnc(:transactionId))

Could you please help on this.

Thanks in Advance,

Sri

[Srivathsava]

Also, below is approval level example.

Created Requisition with amount $100. If amount is less that $1000, then need at least Manager approval.

Here, for our Requisition, the Requester and hierarchy is as below

Employee
Employee Job
Employee Supervisor
Supervisor Job
Employee Email
Supervisor Email
AME_ADMIN
Requester
Srivathsava
Supervisor
zzzz
zzzz
Srivathsava
Supervisor
ABC
Supervisor
zzzz
zzzz
ABC
Supervisor
DEF
Supervisor
zzzz
zzzz
DEF
Manager
XYZ
Director
zzzz
zzzz

For my requisition, the Requester is AME_ADMIN. So, once initiated, it should go for approval to Srivathsava, then ABC and then DEF and then complete (as DEF is manager)

[NormTeamT]

Hi Srivathsava,

obviously, I don’t have your tables and data, so the above is a tad “unhelpful” to me in helping, however, what I would do is to remove all your EXCEPTION sections. Let Oracle find any possible problems and report them directly (you are running this on a test database aren’t you?) That way, you will see what might be causing the no rows returned problem. If, that is, an exception is indeed being raised. Your current exception handlers do nothing to report errors, they just quietly exit the called function(s). Don’t do that! You will encounter the Wrath of Norm! An exception handler should give some information that a problem has occurred. An exception handler that doesn’t, or does nothing (when others then null) is totally useless and, worse, potentially harmful to your data, your users and your sanity.

However,note the following:

1. You define the function as:

FUNCTION xx_po_req_appvr_mgr_fnc (p_req_hdr_id IN NUMBER)
** RETURN person_id_tbl2 PIPELINED**

2. You define person_id_tbl2 as:

TYPE person_id_tbl2 IS TABLE OF person_id_rec2;

3. In the function, you PIPE the following:

’person_id:’||manager_id manager_id

So, you are not piping a person_id_tbl2 type, you are piping a string consisting on a manager id prefixed by some text. This might be causing the problem, maybe.

Failing this, try to set up a small example, as minimal as possible, that will take in a literal value - which you can work out for yourself based on your data - and returns the piped rows that you know are applicable to that value. Similar to what I set up to demonstrate the pipelined function based on your original posting here. Then call the pipelined function, directly, with the passed parameter. Your problem could be coming from any of the support functions in the above package, and perhaps the pipelined function is ok - have you tested the support code in isolation to ensure that it works according to what you expect to happen? Test and validate all the code to be sure that each part works as expected. At least then you can say quite accurately that, the problem in in the pipelined function, for example.

HTH

[damir.vadas_250]

FUNCTION xx_po_req_appvr_mgr_fnc (p_req_hdr_id IN NUMBER)
RETURN person_id_tbl2 PIPELINED

so you should always have to have in your code “return person_id_tbl2 (type)”. you must return variable of that type.

But if you say "return"m what means “return null” then normally you get null as result.

[Srivathsava]

Hi Norm,

It is working now. I just missed to define the function ***xx_get_approvers ***as PRAGMA AUTONOMOUS TRANSACTION as it contains DML statements in it.

[NormTeamT]

On 27/10/17 14:38, Srivathsava wrote:

*RE: How to use a PIPELINE in plsql

Reply by Srivathsava

Hi Norm,

It is working now. I just missed to define the function

*/xx_get_approvers /*as PRAGMA AUTONOMOUS TRANSACTION as it contains DML

statements in it.

Excellent!

--

Cheers,

Norm. [TeamT]

[Srivathsava]

Hi,

Today I observed that duplicate records are getting inserted into my custom table after I added PRAGMA AUTONOMOUS_TRANSACTION. If I didn’t make that as autonomous transaction data is not getting inserted.

Now, this duplication is not creating any issues as I am using DISTINCT in cursor loop.

But, I want to know what I missed here. Why duplication is coming?

[NormTeamT]

Sounds like you are missing a unique contraint on your table which will prevent the duplicates. Also, I suspect a coding error perhaps, and an exception handler that is ignoring errors maybe?

If a check by eye doesn’t show up the cause, disable, by commenting out, the exception handlers and try to recreate the problem. I suspect some exception or other will direct you to the cause.