Thanks for the question, Kalyana.

Asked: May 19, 2016 - 11:15 am UTC

Last updated: June 01, 2016 - 12:46 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

Greetings !!!
Could you help me in the resolution of the error

The steps are given below:-

1. Step 1:-

Create the types as below

create or replace TYPE TEST_INPUT_REC AS OBJECT
(
PO_NO VARCHAR2(100),
PO_LINE_NO NUMBER,
ITEM_NO VARCHAR2(240)
);
/

create or replace TYPE TEST_INPUT_TBL IS TABLE OF TEST_INPUT_REC;
/

create or replace TYPE TEST_OUTPUT_REC AS OBJECT
(
PO_NO VARCHAR2(100),
PO_LINE_NO NUMBER,
ITEM_NO VARCHAR2(240),
ERROR_FLAG VARCHAR2(5),
ERROR_DESC VARCHAR2(1000)
);
/

create or replace TYPE TEST_OUTPUT_TBL IS TABLE OF TEST_OUTPUT_REC;
/

CREATE TABLE TEST_PO_ITEM
(PO_NO VARCHAR2(100),
PO_LINE_NO NUMBER,
ITEM_NO VARCHAR2(240));

2. create the procedure as below:-

--DECLARE

CREATE OR REPLACE PROCEDURE PTEST_PROC (l_inputs IN test_input_tbl,
l_outputs OUT test_output_tbl,
x_error_code OUT VARCHAR2,
x_error_desc OUT VARCHAR2 )
AS
--BEGIN
l_po_number VARCHAR2(100);
l_po_line_no NUMBER;
l_item_no VARCHAR2(240);
l_err_code VARCHAR2(5);
l_err_msg VARCHAR2(240);
l_po_head_id NUMBER;
l_po_line_id NUMBER;
l_inventory_id NUMBER;
val1_flag VARCHAR2(1) := NULL;
val2_flag VARCHAR2(1) := NULL;
val3_flag VARCHAR2(1) := NULL;

-- i_inputs test_input_tbl;
-- l_outputs test_output_tbl;
i_inputs test_input_tbl :=test_input_tbl();
o_outputs test_output_tbl :=test_output_tbl();

BEGIN

l_po_number := NULL;

FOR i IN 1.. l_inputs.COUNT LOOP
o_outputs.extend;
--- check for PO----
l_po_number:=l_inputs(i).po_no;
l_po_line_no:=l_inputs(i).po_line_no;
l_item_no := l_inputs(i).item_no;

----------check po header_id ---------------------
BEGIN
SELECT po_header_id
INTO l_po_head_id
FROM PO_HEADERS_ALL
WHERE SEGMENT1= l_po_number;

EXCEPTION
WHEN NO_DATA_FOUND THEN
--val1_flag:=NULL:
l_err_code:='E';
l_err_msg:='No PO found';
dbms_output.put_line(' Error code is '|| l_err_code);
dbms_output.put_line(' Error code is '|| l_err_msg);

END;
IF l_po_head_id IS NOT NULL
THEN
val1_flag:='Y';
o_outputs(i).po_no:=l_po_number;
-- l_outputs.po_line_no:=l_po_line_no;
-- val
END IF;

----------------check po line number--------------------------------------
BEGIN
SELECT pol.po_line_id
INTO l_po_line_id
FROM PO_LINES_ALL pol,
PO_HEADERS_ALL poh
WHERE pol.po_header_id=pol.po_header_id
AND pol.line_num=l_po_line_no;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_err_code:='E';
l_err_msg:='No PO line found';
dbms_output.put_line(' Error code is '|| l_err_code);
dbms_output.put_line(' Error code is '|| l_err_msg);

END;

IF l_po_line_id IS NOT NULL THEN
val2_flag:='Y';
-- l_outputs.po_no:=l_po_number;
o_outputs(i).po_line_no:=l_po_line_no;

END IF;

----------------check inventory item------------------------------

BEGIN
SELECT inventory_item_id
INTO l_inventory_id
FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1||'.'||SEGMENT3= l_po_number
AND organization_id=83;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_err_code:='E';
l_err_msg:='No Item found';
dbms_output.put_line(' Error code is '|| l_err_code);
dbms_output.put_line(' Error code is '|| l_err_msg);

END;
IF l_inventory_id IS NOT NULL THEN
val3_flag:='Y';
o_outputs(i).item_no:=l_item_no;
END IF;

IF val1_flag='Y' AND val2_flag='Y' and val3_flag='Y' THEN
INSERT INTO TEST_PO_ITEM values
--(l_po_number,l_po_line_no,l_item_no);
( l_outputs(i).po_no,l_outputs(i).po_line_no, l_outputs(i).item_no);
COMMIT;

END IF;

END LOOP;

EXCEPTION
WHEN OTHERS THEN
x_error_code :='E';
x_error_desc:=substr(1,500,sqlerrm);
END PTEST_PROC;
/
show err;

3. Execute the procedure as below:

SET SERVEROUTPUT ON

DECLARE

i_inputs test_input_tbl := test_input_tbl();
o_outputs test_output_tbl := test_output_tbl();
l_error_code varchar2(5) := NULL;
l_err_desc varchar2(1000):=NULL ;

v_po_no VARCHAR2(100);
v_po_line_no NUMBER;
v_item_no VARCHAR2(240);

BEGIN
--BEGIN
i_inputs.extend;
o_outputs.extend;
i_inputs(1):=test_input_rec(
po_no=>'1549263',
po_line_no=> 1,
item_no=> 'SVC.50001');

PTEST_PROC(i_inputs,o_outputs,l_error_code,l_err_desc);

END;

I am getting the error in the third step - while executing the procedure

I need help in the two areas:-

1. Get the output successfully as in step 3
2. In case we are passing large values as out put, how do we handle the below:

1. there are validations for three parameters in the procedure

1.Po_no, po_line_no and item_no.
if po_no validation fails, the program should go to the exception and from there its should go the
next set of records in the for loop.
if po_no succeeds, go the po_line_no, if po_line_no fails, go to the exception and go to the next set of records
in the for loop.

Likewise for item_no

if all the three parameters validation succeed, the record has to be inserted into the test_po_item table
and then go the next set of records in for loop

Thanks in advance!!!
Kalyan

and Chris said...

1. What error do you get exactly? Your test case doesn't include the tables, which makes it tricky for us to validate the code...

2. I'm not sure what you mean? What do you mean by "large values"?

In any case, the code looks overly complex to me. If I've understood correctly, you want to:

- Pass an array of records
- Check whether these values exist in your tables
- Insert into test_po_item if they do

Correct?

If so, you could replace most of the code with a single insert!

Just use bulk processing (forall) to "loop" through the input array. Insert the results of a query joining all the tables together:

  forall i in i_inputs.first .. i_inputs.last
    insert into TEST_PO_ITEM
      select pol.po_line_id, pol.line_num, m.inventory_item_id
      from   po_lines_all pol, po_headers_all poh, mtl_system_items_b m
      where  pol.po_header_id = pol.po_header_id
      and    pol.line_num     = i_inputs(i).po_line_no
      and    m.segment1 || '.' || segment3 = i_inputs(i).item_no
      and    pol.po_header_id = i_inputs(i).po_no;

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Kalyana Chakravarthy, May 19, 2016 - 3:18 pm UTC

Looks like you are not using the Oracle EBS Instance.
Am I right ?
the validation is done againts the PO_HEADERS_ALL,
PO_LINES_ALL and MTL_SYSTEM_ITEMS_B , they are all Oracle EBs tables.

If you don't have , pl let me know , I"ll rephrase the question appropriately.
Please let mw know any of the tables you have in your instance.
like EMP, DEPt etc.

Tha tables will be different, but the approach towards the validation will be the same.

Thank you
Regards
Kalyana

May 19, 2016 - 3:35 pm UTC

Nope, I'm not using EBS!

Modified query

Kalyana Chakravarthy, May 20, 2016 - 10:50 am UTC

Hi,
I have modified my question as below

create or replace TYPE TEST_INPUT_REC AS OBJECT
(
EMPNO NUMBER,
DEPTNO NUMBER,
ENAME VARCHAR2(10)
);
/

create or replace TYPE TEST_INPUT_TBL IS TABLE OF TEST_INPUT_REC;
/

create or replace TYPE TEST_OUTPUT_REC AS OBJECT
(
EMPNO NUMBER,
DEPTNO NUMBER,
ENAME VARCHAR2(10),
ERROR_FLAG VARCHAR2(5),
ERROR_DESC VARCHAR2(1000)
);
/

create or replace TYPE TEST_OUTPUT_TBL IS TABLE OF TEST_OUTPUT_REC;
/

CREATE TABLE TEST_EMP_DEPT
(EMPNO NUMBER,
DEPTNO NUMBER,
ENAME VARCHAR2(10));

create table emp_dept71 as select e.*,d.dname,d.loc
from emp e,(select dname,loc,deptno from dept) d where e.deptno=e.deptno;

create the procedure as below:-

CREATE OR REPLACE PROCEDURE PTEST_PROC (l_inputs IN test_input_tbl,
l_outputs OUT test_output_tbl,
x_error_code OUT VARCHAR2,
x_error_desc OUT VARCHAR2 )
AS
--BEGIN
l_empno NUMBER :=NULL;
l_deptno NUMBER :=NULL;
l_ename VARCHAR2(20):=NULL;

ln_empno NUMBER;
ln_deptno NUMBER;
ln_ename VARCHAR2(20):=NULL;

l_err_code VARCHAR2(5):=NULL;
l_err_msg VARCHAR2(240):=NULL;

val1_flag VARCHAR2(1) := NULL;
val2_flag VARCHAR2(1) := NULL;
val3_flag VARCHAR2(1) := NULL;

-- i_inputs test_input_tbl;
-- l_outputs test_output_tbl;
i_inputs test_input_tbl :=test_input_tbl();
o_outputs test_output_tbl :=test_output_tbl();

BEGIN

l_empno := NULL;

FOR i IN 1.. i_inputs.COUNT LOOP
o_outputs.extend;
--- check for empno----
l_empno:=i_inputs(i).empno;
l_deptno:=i_inputs(i).deptno;
l_ename := i_inputs(i).ename;

----------check empno ---------------------
BEGIN
SELECT empno
INTO ln_empno
FROM EMP
WHERE empno= l_empno ;

EXCEPTION
WHEN NO_DATA_FOUND THEN
--val1_flag:=NULL:
l_err_code:='E';
l_err_msg:='No Emp found';
dbms_output.put_line(' Error code is '|| l_err_code);
dbms_output.put_line(' Error code is '|| l_err_msg);

END;
IF ln_empno IS NOT NULL
THEN
val1_flag:='Y';
o_outputs(i).empno:=ln_empno;
-- l_outputs.po_line_no:=l_po_line_no;
-- val
END IF;

----------------check dept--------------------------------------
BEGIN
SELECT deptno
INTO ln_deptno
FROM DEPT
WHERE deptno=l_deptno;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_err_code:='E';
l_err_msg:='No Deptno found';
dbms_output.put_line(' Error code is '|| l_err_code);
dbms_output.put_line(' Error code is '|| l_err_msg);

END;

IF ln_deptno IS NOT NULL THEN
val2_flag:='Y';
-- l_outputs.po_no:=l_po_number;
o_outputs(i).deptno:=ln_deptno;

END IF;

----------------check ename ------------------------------

BEGIN
SELECT ename
INTO ln_ename
FROM EMP
WHERE ename=l_ename;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_err_code:='E';
l_err_msg:='No Ename found';
dbms_output.put_line(' Error code is '|| l_err_code);
dbms_output.put_line(' Error code is '|| l_err_msg);

END;
IF ln_ename IS NOT NULL THEN
val3_flag:='Y';
o_outputs(i).ename:=ln_ename;
END IF;

IF val1_flag='Y' AND val2_flag='Y' and val3_flag='Y' THEN
INSERT INTO TEST_EMP_DEPT values
--(l_po_number,l_po_line_no,l_item_no);
( o_outputs(i).empno,o_outputs(i).deptno, o_outputs(i).ename);
COMMIT;

END IF;

END LOOP;

EXCEPTION
WHEN OTHERS THEN
x_error_code :='E';
x_error_desc:=substr(1,500,sqlerrm);
END PTEST_PROC;
/
show err;

-------------------------------------------------------------------------------
Run the procedure as below:-
SET SERVEROUTPUT ON

DECLARE

i_inputs test_input_tbl := test_input_tbl();
o_outputs test_output_tbl := test_output_tbl();
l_error_code varchar2(5) := NULL;
l_err_desc varchar2(1000):=NULL ;

v_empno NUMBER;
v_deptno NUMBER;
v_ename VARCHAR2(20);

BEGIN
--BEGIN
i_inputs.extend;
o_outputs.extend;
i_inputs(1):=test_input_rec(
v_empno=>7369,
v_deptno=> 20,
v_ename=> 'SMITH');

PTEST_PROC(i_inputs,o_outputs,l_error_code,l_err_desc);

END;
--------------------------------------------------------------
After the running the procedure as below, Iam getting the error as below

Error starting at line : 3 in command -
DECLARE

i_inputs test_input_tbl := test_input_tbl();
o_outputs test_output_tbl := test_output_tbl();
l_error_code varchar2(5) := NULL;
l_err_desc varchar2(1000):=NULL ;

v_empno NUMBER;
v_deptno NUMBER;
v_ename VARCHAR2(20);

BEGIN
--BEGIN
i_inputs.extend;
o_outputs.extend;
i_inputs(1):=test_input_rec(
v_empno=>7369,
v_deptno=> 20,
v_ename=> 'SMITH');

PTEST_PROC(i_inputs,o_outputs,l_error_code,l_err_desc);

END;
Error report -
ORA-06550: line 16, column 14:
PLS-00306: wrong number or types of arguments in call to 'TEST_INPUT_REC'
ORA-06550: line 16, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
----------------------------------------------------------

I am getting the error in the third step - while executing the procedure

I need help in the two areas:-

1. Get the output successfully as in step 3

1. there are validations for three parameters in the procedure

empno, deptno and ename.
the requirement is that validate each of the parameters
If empno parameter is present in emp, - validation is successful

if deptno is present in dept - deptno validation is successful

if ename is present in emp table - ename validation is successful

the above conditions are taken care in the procedure

2. How do we pass values from emp_dept71 table and do the validations as below

a. empno vaidation fails, go to the exception section
skip the parameter set and go to the next record
b. empno success, deptno fails
skip the parameter set and go to the next record
c. empno, deptno - success and ename fails
skip the parameter set and go to the next record

If all the three -->success insert into test_emp_dept table

Thank you
Kalyan

May 20, 2016 - 2:29 pm UTC

You have an error because the field names are wrong for the type:

v_empno=>7369, 
v_deptno=> 20, 
v_ename=> 'SMITH');

Should be:

empno=>7369, 
deptno=> 20, 
ename=> 'SMITH');

When I do that, the code works fine.

To get the values from emp_dept71, you need to do something like:

declare
  type emp_dept_arr is table of emp_dept71%rowtype 
    index by binary_integer;
  emps_and_depts emp_dept_arr;
begin

  select * 
  bulk   collect into emps_and_depts
  from   emp_dept71;

end;
/

I still think you'll be better off rewriting the code. Instead of checking each column separately, just join all the tables together and insert the results. If the values exist, you'll get a row in your table. If they don't, you won't!

requesting a better answer

Kalyana Chakravarthy, May 21, 2016 - 10:49 am UTC

Hi Chris,
Thanks for your mail.
I had modified the type/procedure definition keeping in the mind that you were not using the EBS tables.

We would therefor like to have the validation steps against each column and have the scenario in which the next set of records would be fetched if one column validation fails.

Could you help us,

Thanks
Kalyana

May 31, 2016 - 1:22 pm UTC

I'm not really sure what you're looking for here.

What precisely is the issue?

A reader, May 24, 2016 - 10:26 am UTC

Dear Team,
Could you get back on my query, pl

Regards
Kalyan

Follow up

A reader, May 31, 2016 - 7:28 pm UTC

Hi Team .,
Thanks for rverting back.
My requirement is as follows-
create the types and procedure as below and execute
create or replace TYPE TEST_INPUT_REC AS OBJECT
(
EMPNO NUMBER,
DEPTNO NUMBER,
ENAME VARCHAR2(10)
);
/

create or replace TYPE TEST_INPUT_TBL IS TABLE OF TEST_INPUT_REC;
/

create or replace TYPE TEST_OUTPUT_REC AS OBJECT
(
EMPNO NUMBER,
DEPTNO NUMBER,
ENAME VARCHAR2(10),
ERROR_FLAG VARCHAR2(5),
ERROR_DESC VARCHAR2(1000)
);
/

create or replace TYPE TEST_OUTPUT_TBL IS TABLE OF TEST_OUTPUT_REC;
/

CREATE TABLE TEST_EMP_DEPT
(EMPNO NUMBER,
DEPTNO NUMBER,
ENAME VARCHAR2(10));

create table emp_dept71 as select e.*,d.dname,d.loc
from emp e,(select dname,loc,deptno from dept) d where e.deptno=e.deptno;

create the procedure as below:-

CREATE OR REPLACE PROCEDURE PTEST_PROC (l_inputs IN test_input_tbl,
l_outputs OUT test_output_tbl,
x_error_code OUT VARCHAR2,
x_error_desc OUT VARCHAR2 )
AS
--BEGIN
l_empno NUMBER :=NULL;
l_deptno NUMBER :=NULL;
l_ename VARCHAR2(20):=NULL;

ln_empno NUMBER;
ln_deptno NUMBER;
ln_ename VARCHAR2(20):=NULL;

l_err_code VARCHAR2(5):=NULL;
l_err_msg VARCHAR2(240):=NULL;

val1_flag VARCHAR2(1) := NULL;
val2_flag VARCHAR2(1) := NULL;
val3_flag VARCHAR2(1) := NULL;

-- i_inputs test_input_tbl;
-- l_outputs test_output_tbl;
i_inputs test_input_tbl :=test_input_tbl();
o_outputs test_output_tbl :=test_output_tbl();

BEGIN

l_empno := NULL;

FOR i IN 1.. i_inputs.COUNT LOOP
o_outputs.extend;
--- check for empno----
l_empno:=i_inputs(i).empno;
l_deptno:=i_inputs(i).deptno;
l_ename := i_inputs(i).ename;

----------check empno ---------------------
BEGIN
SELECT empno
INTO ln_empno
FROM EMP
WHERE empno= l_empno ;

EXCEPTION
WHEN NO_DATA_FOUND THEN
--val1_flag:=NULL:
l_err_code:='E';
l_err_msg:='No Emp found';
dbms_output.put_line(' Error code is '|| l_err_code);
dbms_output.put_line(' Error code is '|| l_err_msg);

END;
IF ln_empno IS NOT NULL
THEN
val1_flag:='Y';
o_outputs(i).empno:=ln_empno;
-- l_outputs.po_line_no:=l_po_line_no;
-- val
END IF;

----------------check dept--------------------------------------
BEGIN
SELECT deptno
INTO ln_deptno
FROM DEPT
WHERE deptno=l_deptno;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_err_code:='E';
l_err_msg:='No Deptno found';
dbms_output.put_line(' Error code is '|| l_err_code);
dbms_output.put_line(' Error code is '|| l_err_msg);

END;

IF ln_deptno IS NOT NULL THEN
val2_flag:='Y';
-- l_outputs.po_no:=l_po_number;
o_outputs(i).deptno:=ln_deptno;

END IF;

----------------check ename ------------------------------

BEGIN
SELECT ename
INTO ln_ename
FROM EMP
WHERE ename=l_ename;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_err_code:='E';
l_err_msg:='No Ename found';
dbms_output.put_line(' Error code is '|| l_err_code);
dbms_output.put_line(' Error code is '|| l_err_msg);

END;
IF ln_ename IS NOT NULL THEN
val3_flag:='Y';
o_outputs(i).ename:=ln_ename;
END IF;

IF val1_flag='Y' AND val2_flag='Y' and val3_flag='Y' THEN
INSERT INTO TEST_EMP_DEPT values
--(l_po_number,l_po_line_no,l_item_no);
( o_outputs(i).empno,o_outputs(i).deptno, o_outputs(i).ename);
COMMIT;

END IF;

END LOOP;

EXCEPTION
WHEN OTHERS THEN
x_error_code :='E';
x_error_desc:=substr(1,500,sqlerrm);
END PTEST_PROC;
/
show err;

-------------------------------------------------------------------------------
Run the procedure as below:-
SET SERVEROUTPUT ON

DECLARE

i_inputs test_input_tbl := test_input_tbl();
o_outputs test_output_tbl := test_output_tbl();
l_error_code varchar2(5) := NULL;
l_err_desc varchar2(1000):=NULL ;

v_empno NUMBER;
v_deptno NUMBER;
v_ename VARCHAR2(20);

BEGIN
--BEGIN
i_inputs.extend;
o_outputs.extend;
i_inputs(1):=test_input_rec(
empno=>7369,
deptno=> 20,
ename=> 'SMITH');

PTEST_PROC(i_inputs,o_outputs,l_error_code,l_err_desc);

END;

When we pass values into the input parameters of the procedure,
during the course of execution if either of the three elements error out , i.e., empno, deptno and ename, the program should skip the current record in the loop, display the error and continue with the next iteration in the loop.
Could you help ?

Regards
Kalyana

June 01, 2016 - 7:56 am UTC

If you have three input records and the first and third are invalid, second is valid what should happen?

Should it insert the second, then stop? Or keep going and try to process the third too?

You only have one error code/string in your output parameters. If two or more input records are bad, how will you know?

Do you need to know precisely why it errored (empno, empname or deptno), or just that it has errored?

Review follow up

A reader, June 01, 2016 - 10:25 am UTC

Dear Team,
Thank you for the response.

My requirement is as follows:
In the FOR LOOP

lets say that we have the values as below
FOR i IN 1.. l_inputs.COUNT LOOP
o_outputs.extend;

First record
l_inputs(1).empno:=100 -- invalid
l_inputs(1).deptno:= 2- -- valid
l_inputs(1).ename :='JANE' -- invalid

As per my requirement
, if empno is invalid,
the control should flow to the exception section
error message should be propped
and then the next iteration should start
For e.g if we have records as below
l_inputs(2).empno -- valid
l_inputs(2).deptno -- invalid
l_inputs(2).ename -- valid

the program should go to the exception section at l_inputs(2).deptno
and the iteration of the FOR loop should start
i.e; l_inputs(3).empno

If only all the three element for l_inputs(i) are valid, then the message success should be displayed.

the process should go till the end of the loop.

Regards
Kalyan

June 01, 2016 - 12:46 pm UTC

The process I've used is similar to the one I first described:

- Use forall to try and insert the rows into the test table
- Then loop through the inputs again. Assign the values to the outputs and check the sql%bulk_rowcount attribute. If this is zero, then those inputs didn't add a row. So set the error message on the output. You could add further validation regarding what the problem is at this point if you want.

I've removed the error out parameters from the procedure. I'm not clear what purpose they serve.

create or replace procedure ptest_proc (
 l_inputs  in test_input_tbl, 
 l_outputs out test_output_tbl ) 
as 

begin 
  l_outputs := test_output_tbl();
  l_outputs.extend(l_inputs.count);

  forall i in l_inputs.first .. l_inputs.last 
    insert into test_emp_dept 
      select distinct e.empno, d.deptno, e.ename
      from   scott.emp e, scott.dept d
      where  e.empno = l_inputs(i).empno
      and    e.ename = l_inputs(i).ename
      and    d.deptno = l_inputs(i).deptno;
      
  for i in l_inputs.first .. l_inputs.last loop
    l_outputs(i) := test_output_rec (
      l_inputs(i).empno, l_inputs(i).deptno, l_inputs(i).ename, null, null
    );
    if sql%bulk_rowcount(i) = 0 then 
      l_outputs(i).error_desc := 'Missing!';
    end if;
  end loop;
  
end ptest_proc; 
/

Calling with three inputs records, where the 1st and 3rd are invalid gives:

SQL> DECLARE
  2
  3  i_inputs test_input_tbl := test_input_tbl();
  4  o_outputs test_output_tbl := test_output_tbl();
  5
  6  BEGIN
  7  --BEGIN
  8  i_inputs.extend(3);
  9  o_outputs.extend(3);
 10  i_inputs(1):=test_input_rec(
 11  empno=>7369,
 12  deptno=> 20,
 13  ename=> 'JONES');
 14
 15  i_inputs(2):=test_input_rec(
 16  empno=>7369,
 17  deptno=> 20,
 18  ename=> 'SMITH');
 19
 20  i_inputs(3):=test_input_rec(
 21  empno=>-1,
 22  deptno=> 20,
 23  ename=> 'SMITH');
 24
 25
 26  PTEST_PROC(i_inputs,o_outputs);
 27
 28  for i in o_outputs.first .. o_outputs.last loop
 29     if o_outputs(i).error_desc is not null then
 30       dbms_output.put_line('Record ' || i || ' failed!');
 31     end if;
 32  end loop;
 33
 34  END;
 35  /
Record 1 failed!
Record 3 failed!

PL/SQL procedure successfully completed.

SQL>
SQL> select * from TEST_EMP_DEPT;

     EMPNO     DEPTNO ENAME
---------- ---------- ----------
      7369         20 SMITH

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Modified query

requesting a better answer

Follow up

Review follow up

More to Explore

PL/SQL demos

PL/SQL docs

PL/SQL Table types Collections and procedures

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Modified query

requesting a better answer

Follow up

Review follow up

More to Explore

PL/SQL demos

PL/SQL docs