Skip to Main Content
  • Questions
  • How to pass an array to a stored procedure

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Prem .

Asked: May 02, 2000 - 1:21 pm UTC

Answered by: Tom Kyte - Last updated: November 30, 2011 - 7:01 am UTC

Category: - Version:

Viewed 100K+ times! This question is

You Asked

I want to know if I can have one of the parameter in
stored procedure as an array.
Ex : I have a procedure
procedure employee_report
(emp_no number
,emp_dept varchar2
,emp_salary number
,emp_title varchar2)

Instead can I define an object/array emp_property of structure
(emp_no number
,emp_dept varchar2
,emp_salary number
,emp_title varchar2)
and use it a parameter in stored proc like
procedure employee_report(emp_prop emp_property ).
If this is possible than can I pass multiple records in this procedure.




and we said...


Sure, you just need to declare new types. You can have types that are tables of a RECORD or types that are tables of SCALARS. I'll show both below. See the plsql guide for how to declare record types -- you can do them based on an existing table structure, a cursor structure or by explicity defining your own record type.

Note that if you are clling emp_report from a 3gl such as Pro*C, OCI, VB, etc (eg: anything OTHER then plsql) you will need to use tables of scalars -- NOT a table of records as tables of records cannot be bound to by a 3gl. Also, I suggest only use charArray's if you are going to be calling this from a 3gl -- if you made a table of NUMBER or a table of DATE -- we would expect you to send the INTERNAL format of a number (22 byte field in an obscure format) or a date (7 byte, equally obscure format). Best to send strings and let the conversions take place.

ops$tkyte@8i> create or replace package demo_pkg
2 as
3 type empArray is table of emp%rowtype index by binary_integer;
4
5 type charArray is table of varchar2(255) index by binary_integer;
6
7
8 procedure emp_report( p_inputs in empArray );
9
10 procedure emp_report( p_empno in charArray,
11 p_deptno in charArray,
12 p_sal in charArray,
13 p_job in charArray );
14
15 end;
16 /

Package created.

ops$tkyte@8i>
ops$tkyte@8i>
ops$tkyte@8i>
ops$tkyte@8i> create or replace package body demo_pkg
2 as
3 procedure emp_report( p_inputs in empArray )
4 is
5 begin
6 for i in 1 .. p_inputs.count loop
7 dbms_output.put_line( 'Empno = ' || p_inputs(i).empno ||
8 ' Deptno = ' || p_inputs(i).deptno );
9 end loop;
10 end;
11
12
13 procedure emp_report( p_empno in charArray,
14 p_deptno in charArray,
15 p_sal in charArray,
16 p_job in charArray )
17 is
18 begin
19 for i in 1 .. p_empno.count loop
20 dbms_output.put_line( 'Empno = ' || p_empno(i) ||
21 ' Deptno = ' || p_deptno(i) );
22 end loop;
23 end;
24
25
26
27 end;
28 /

Package body created.

ops$tkyte@8i>
ops$tkyte@8i> set serveroutput on
ops$tkyte@8i> declare
2 my_data demo_pkg.empArray;
3 begin
4 my_data(1).empno := 1234;
5 my_data(1).deptno := 10;
6
7 my_data(2).empno := 4567;
8 my_data(2).deptno := 20;
9
10 demo_pkg.emp_report( my_data );
11 end;
12 /
Empno = 1234 Deptno = 10
Empno = 4567 Deptno = 20

PL/SQL procedure successfully completed.

ops$tkyte@8i>
ops$tkyte@8i> declare
2 my_empnos demo_pkg.charArray;
3 my_deptno demo_pkg.charArray;
4 empty demo_pkg.charArray;
5 begin
6 my_empnos(1) := 1234;
7 my_deptno(1) := 10;
8
9 my_empnos(2) := 4567;
10 my_deptno(2) := 20;
11
12 demo_pkg.emp_report( my_empnos, my_deptno, empty, empty );
13 end;
14 /
Empno = 1234 Deptno = 10
Empno = 4567 Deptno = 20

PL/SQL procedure successfully completed.






and you rated our response

  (54 ratings)

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

Reviews

March 23, 2001 - 7:08 am UTC

Reviewer: Helena Markova from Bratislava, Slovakia


October 17, 2001 - 12:17 pm UTC

Reviewer: A reader

3 type empArray is table of emp%rowtype index by
binary_integer;


Can pl/sql tables contain more than one column.

can I select all the rows in emp into emp array, and then insert emparray into another table

Tom Kyte

Followup  

October 17, 2001 - 12:48 pm UTC

The example was of a PLSQL table type that is of more then one column. empArray is a table of records -- each record is the shape of a row in the emp table.

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1583402705463 <code>
for howto bulk collect into a SQL Table type (not a plsql type) and insert using it.

Pass array to stored proc from VB?

February 06, 2002 - 4:12 am UTC

Reviewer: Sasa Petkovic from Belgrade , Yugoslavia

Your examaple is very usefull and it works excellent in PL/SQL, but the problem occurs when trying to call the sam procedure from VB. Parameter of a command object is defined as adArray + adVarChar, and assigning a VB array of strings to it goes fine, but .Execute crashes with "Type mismatch" error. Same thing happens when using either ODBC or Oracle OLEDB drivers. Is this feature currently supported, and if it is - then what is the lowest version of drivers that support it?


Tom Kyte

Followup  

February 06, 2002 - 8:07 am UTC

I don't know much about VB personally. I know we can do this with OO4O (Oracle Objects for OLE). Sorry I cannot provide an example as I've never touched VB personally.

Great responses

March 07, 2002 - 4:13 pm UTC

Reviewer: Teodora Jevtic from Livonia, MI USA

Tom,

Do you know if there are such limitations in ODBC that can prevent from calling this kind of stored procedures - i.e in Crystal Reports. Some of the reports are to complex to be written in one SQL statement.
I am thinking if the call to this kind of stored procedure is not possible(which seems to be the case) to make a call to stored procedure (prior running the report - from the GUI application) which will store the resultset into a Oracle temporary table - and the report will just pull the data from it (or use permanent table with a unqiue identifier for each calling session).
Do you have any other ideas? Thanks in advance.

Tom Kyte

Followup  

March 07, 2002 - 4:39 pm UTC

I've no idea what limits crystal reports might impose, sorry -- i've never used that tool.

You can use global temporary tables for this purpose handily. Very very little redo log generated during insertion and automatic cleanup on commit or session termination.

RE: Great responses

March 07, 2002 - 6:04 pm UTC

Reviewer: Kim from Boise, ID USA

As far as crystal reports go...
I suggest you look at the following (you have to set up a couple things in Crystal Reports as well):

</code> http://www.seagatesoftware.com/docs/ <code>
and search for
SCR_Oracle_Stored_Procedures.pdf
down where it says or search for the filename below.

but how to delete the element

June 28, 2002 - 11:56 am UTC

Reviewer: Raj from Peoria, IL

Tom
Nice example. Could you please tell me how to remove an element from the array. Once removed is the row empty or deleted completely?

Thanks for your help

Tom Kyte

Followup  

June 28, 2002 - 12:51 pm UTC

see
</code> http://docs.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/04_colls.htm#14165 <code>

you'll use the .DELETE method.

The row is deleted from the collection. It'll still "be there", it just won't "exist" anymore. Read the above link for all of the details

June 28, 2002 - 2:47 pm UTC

Reviewer: Alex from Chicago, IL

I would like to comment on calling procedures from VB apps. If VB works the same way as ASP, then you cannot use PL/SQL tables of rows - ASP (and possibly VB) doesn't recognize it, so you must declare each field that will be used as a separate PL/SQL table and pass it as a parameter.

June 28, 2002 - 3:00 pm UTC

Reviewer: Robert from Memphis, TN USA


Thanks

June 28, 2002 - 3:29 pm UTC

Reviewer: Raj from Peoria, IL

Thanks Tom for your reply.

How to pass arrya of number from oci to PL/sql

November 12, 2002 - 4:29 am UTC

Reviewer: sam

Dear Tom

This article is very useful to PL/SQL, but when i write a oci(C++) project, it said "Type mismatch".

Can you give me a example in C++ of oci?

Tom Kyte

Followup  

November 12, 2002 - 10:21 am UTC

Nope -- cuase only PLSQL can see PLSQL types-

You need to use OBJECT TYPES

create or replace type myObject as object .....
create or repalce type myArray as table of ....

See the OCI guides for that -- they have examples and PLSQL can use the object types as freely as its own types.

About OCI Object for PL/SQL Parameter

November 12, 2002 - 8:00 pm UTC

Reviewer: sam

Tom

I have searched OCI Progammer Guide, But i cann't find and OCI number Array for PL/SQL,

The following link is OCI Programmer Guide, page 154 is the example of number array, is it for sql, not for pl/sql?

</code> http://docs.oracle.com/cd_a87860/doc/appdev.817/a76975.pdf <code>

How to pass number array to pl/sql?

thanks again

Tom Kyte

Followup  

November 13, 2002 - 12:51 pm UTC

it is an array bind
see

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76975/oci05bnd.htm#420907 <code>


continued for above

November 12, 2002 - 8:23 pm UTC

Reviewer: sam

The following is my package code:

package PKG_SCOTT2
as
type NumberArray is table of Number index by binary_integer;
type CharArray is table of varchar2(255) index by binary_integer;

procedure Dept_Insert2( p_DeptNo in NumberArray,
p_DName in CharArray,
p_Loc in CharArray );

end;

package body PKG_SCOTT2
as
procedure Dept_Insert( p_DeptNo in NumberArray,
p_DName in CharArray,
p_Loc in CharArray )
is
begin
for i in 1 .. p_DeptNo.count loop
insert into dept
values(p_DeptNo(i), p_DName(i), p_Loc(i));
end loop;
end;
end;


pass array to procedure

February 20, 2004 - 6:37 am UTC

Reviewer: A reader

Hi

Is it possible to pass arrays this way?

exec proc_a(array);

proc_a needs to process this way

proc_a (p_array chararray)
as
begin
for i in p_array.first .. array.last loop
dbms_output.put_line(p_array(i));
end loop;
end;
/


Tom Kyte

Followup  

February 20, 2004 - 9:55 am UTC

yes

exec declare array; begin array(1) := 'x'; proc_a(array); end;

pass array then insert

March 11, 2005 - 6:24 am UTC

Reviewer: A reader

Hi

I am beginner C pogrammer doing some PL/SQL tasks, i have this procedure to perform some bulk inserts for test purposes and learning,

CREATE OR REPLACE PROCEDURE INSERT_BULK
IS
iArraySize NUMBER := 100;
CURSOR cMov IS ( SELECT ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO);

TYPE movArray IS TABLE OF cMov%ROWTYPE INDEX BY BINARY_INTEGER;
RH_MOV movArray;

PROCEDURE INSERT_EMP_DEPT(rh_mov in movArray)
IS
BEGIN
INSERT INTO EMP_DEPT (ENAME, DNAME) VALUES (rh_mov);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE ('ERROR DUPLICATE');
WHEN OTHERS THEN
RAISE;
END INSERT_EMP_DEPT;
BEGIN
OPEN cMov;
LOOP
FETCH cMov BULK COLLECT INTO RH_MOV LIMIT iArraySize;
INSERT_EMP_DEPT(RH_MOV);
END LOOP;
EXCEPTION
WHEN others THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE ('ERROR ' || SQLERRM);
END;
/


LINE/COL ERROR
-------- -----------------------------------------------------------------
14/3 PL/SQL: SQL Statement ignored
14/47 PLS-00382: expression is of wrong type
24/12 PL/SQL: SQL Statement ignored
24/41 PLS-00597: expression 'RH_MOV' in the INTO list is of wrong type

it does not compile and I cant see what is the error can you throw some lights?

also I am not sure if we can simply insert like this

INSERT INTO EMP_DEPT (ENAME, DNAME) VALUES (rh_mov);

where rh_mov is an structure containing arrays of data (like HOST ARRAYS in PRO*C), in PRO*C we can use simply insert ... values (structure) something like

INSERT INTO EMP_DEPT (ENAME, DNAME) VALUES (:rh_mov);

is this possible in PL/SQL?

regards

Tom Kyte

Followup  

March 11, 2005 - 6:43 am UTC

well, why don't you just

insert into emp_dept
select * from .....


but if you really feel you need to do it slow by slow - see:

</code> http://asktom.oracle.com/pls/ask/search?p_string=%22save+exceptions%22 <code>

for examples of bulk inserts (using forall) with row level exception handling.


pass array then insert

March 11, 2005 - 6:55 am UTC

Reviewer: A reader

Hi

I need to do it that way because I have to catch the exceptions, with insert select I cant do it, it´s all or nothing

From your code

open c;
loop
fetch c BULK COLLECT INTO data LIMIT 100;
begin
FORALL i IN 1 .. data.count SAVE EXCEPTIONS
insert into t2 values data(i);
nd loop;

is it possible to do

FORALL i IN 1 .. data.count SAVE EXCEPTIONS
insert into t2 values data(i);

in another procedure? so it becomes


procedure insert_tab()
begin
FORALL i IN 1 .. data.count SAVE EXCEPTIONS
insert into t2 values data(i);
... end;
open c;
loop
fetch c BULK COLLECT INTO data LIMIT 100;
begin
insert_tab
end loop;

I am quite used in PRO*C to make the code modular so I tend to isolate SQL statments in functions/procedures

Also from your code I understand that the insert takes place in batch of 100 rows correct?

Thank you

Tom Kyte

Followup  

March 11, 2005 - 7:28 am UTC

yes, you just pass the array as a parameter.



pass array then insert

March 11, 2005 - 7:31 am UTC

Reviewer: A reader

Hi

Thank you very much for your responses. I am stillstruggling with the procedure I posted, i dont understand why I am getting this error

24/41 PLS-00597: expression 'RH_MOV' in the INTO list is of wrong type

do you know why?

thank you very much

pass an array then insert

March 11, 2005 - 8:10 am UTC

Reviewer: A reader

Hi

I know why itñs not working, its because 8.1.7.4 does not support BULK COLLECT into records....

I tested the code in 10g I resolved the problem but getting this error now.

CREATE OR REPLACE PROCEDURE INSERT_BULK
IS
iArraySize NUMBER := 100;
CURSOR cMov IS ( SELECT ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO);

TYPE movArray IS TABLE OF cMov%ROWTYPE INDEX BY BINARY_INTEGER;
RH_MOV movArray;

PROCEDURE INSERT_EMP_DEPT(rh_mov in movArray)
IS
BEGIN
FOR i IN 1..RH_MOV.COUNT
LOOP
INSERT INTO EMP_DEPT (ENAME, DNAME) VALUES (rh_mov(i));
END LOOP;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE ('ERROR DUPLICATE');
WHEN OTHERS THEN
RAISE;
END INSERT_EMP_DEPT;
BEGIN
OPEN cMov;
LOOP
FETCH cMov BULK COLLECT INTO RH_MOV LIMIT iArraySize;
EXIT WHEN cMov%NOTFOUND;
INSERT_EMP_DEPT(RH_MOV);
END LOOP;
EXCEPTION
WHEN others THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE ('ERROR ' || SQLERRM);
END;
/

Errors for PROCEDURE INSERT_BULK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
16/4 PL/SQL: SQL Statement ignored
16/40 PL/SQL: ORA-00947: not enough values

If I change

INSERT INTO EMP_DEPT (ENAME, DNAME) VALUES (rh_mov(i));

to

INSERT INTO EMP_DEPT (ENAME, DNAME) VALUES (rh_mov(i).ename, rh_mov(i).dname);

then it works, how so?!

after testing pass array to procedure

March 11, 2005 - 8:38 am UTC

Reviewer: A reader

Hi

after struggling I got it sort of working in 9.2.0.4 however it does not insert rows

As you can see the curso returns 14 rows

SELECT ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING

however using the procedure it inserts 0 rows

CREATE OR REPLACE PROCEDURE INSERT_BULK
IS
iArraySize NUMBER := 5;
CURSOR cMov IS ( SELECT ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO);
TYPE movArray IS TABLE OF cMov%ROWTYPE INDEX BY BINARY_INTEGER;
RH_MOV movArray;
BEGIN
OPEN cMov;
LOOP
FETCH cMov BULK COLLECT INTO RH_MOV;
EXIT WHEN cMov%NOTFOUND;
FORALL i IN 1..RH_MOV.COUNT
INSERT INTO EMP_DEPT VALUES rh_mov(i);
END LOOP;
END;
/

exec insert_bulk;

PL/SQL procedure successfully completed.

select * from emp_dept;

no rows selected

If I limit FETCH cMov BULK COLLECT INTO RH_MOV to 5 rows then it inserts 10 rows (two fetches). Since I have been coding PRO*C for a while I understand that the internal control of size of ARRAY in PL/SQL must be bugged

I tested in 9.2.0.4 and 10.1.0.2 and both gives same result, no rows.



Works if you move your exit

March 11, 2005 - 10:31 am UTC

Reviewer: Robert from St. Louis Mo.

Try moving your EXIT WHEN cMov%NOTFOUND; to after your insert.

test9.2.0.6> l
1 CREATE OR REPLACE PROCEDURE INSERT_BULK
2 IS
3 iArraySize NUMBER := 5;
4 CURSOR cMov IS ( SELECT ENAME, DNAME
5 FROM EMP, DEPT
6 WHERE EMP.DEPTNO = DEPT.DEPTNO);
7 TYPE movArray IS TABLE OF cMov%ROWTYPE INDEX BY BINARY_INTEGER;
8 RH_MOV movArray;
9 BEGIN
10 OPEN cMov;
11 LOOP
12 FETCH cMov BULK COLLECT INTO RH_MOV;
13 FORALL i IN 1..RH_MOV.COUNT
14 INSERT INTO EMP_DEPT VALUES rh_mov(i);
15 EXIT WHEN cMov%NOTFOUND;
16 END LOOP;
17* END;
test9.2.0.6> /

Procedure created.

Elapsed: 00:00:00.06
test9.2.0.6> exec insert_bulk;


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
test9.2.0.6> select * from emp_dept;

Press <RETURN> To Continue...
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING

14 rows selected.



EXIT WHEN cMov%NOTFOUND;

March 13, 2005 - 4:20 pm UTC

Reviewer: A reader

I have a question about where to put the EXIT in the code.

Why EXIT WHEN cMov%NOTFOUND has to go after insert...? Dont see the point. Can anyone give some hint?

Tom Kyte

Followup  

March 13, 2005 - 5:55 pm UTC

if you have 14 rows in the table

and you fetch bulk collect 100 at a time

then - the very very very first fetch returns "c%notfound" -- for you fetched all 14, nothing there for 15 so the cursor has to return "notfound"


loop
fetch c bulk collect
PROCESS the records fetched
exit when c%notfound
end loop

is the only way to process a bulk collect

June 17, 2005 - 10:31 am UTC

Reviewer: A reader


passing procedure as a parameter in another procedure

June 24, 2005 - 5:19 am UTC

Reviewer: Narasimha Rao A from INDIA

Hai sir,
My name is Narasimha Rao and iam a fresh post graduate completed student..
My doubt is..
Can we pass the procedure as a parameter in another procedure Please clarify the doubt with example ..
Thank you...

Tom Kyte

Followup  

June 24, 2005 - 6:53 am UTC

you can.
but you should not.

"dynamic code" like that is "cool" but not super efficient.

you would use dynamic sql,

execute immediate 'begin ' || p_proc_name || '; end;';


the plsql guide covers dynamic sql in depth, but again, you would have a hard time convincing me this would be a "good idea"

Type of inparameter of Procedure if it is dynamic

August 05, 2005 - 10:08 am UTC

Reviewer: T. Srinivasan from India

I need to pass tablename and the tables record array to
a generic procedure which would accept the above parameters and do bulk insert 0f the array to the table(in parameter) . Please explain

Tom Kyte

Followup  

August 05, 2005 - 11:28 am UTC

a procedure cannot take a "generic record type"

you know, if a client has

a) a tablename
b) an array it understands


then the client should

c) bulk insert it. it is the only thing that knows how

PL/SQL package as parameter

November 08, 2005 - 9:22 am UTC

Reviewer: Pran from India

Hi Tom,

I have a package as given below ....

CREATE OR REPLACE PACKAGE WR_tst_pack IS
TYPE tst_rec IS RECORD
( col1 NUMBER
,col2 VARCHAR2(150));
TYPE tst_tab IS TABLE OF tst_rec INDEX BY BINARY_INTEGER;
procedure wr_tst(p_tab tst_tab) ;
end WR_tst_pack;


CREATE OR REPLACE PACKAGE BODY WR_tst_pack IS
procedure wr_tst(p_tab tst_tab) is
pltab tst_tab;
begin
pltab(1).col1 := p_tab(1).col1;
pltab(1).col2 := p_tab(1).col2;
end ;
end WR_tst_pack;

In the above given package i have Pl/SQL table pltab which i popluate based on the parameter p_tab which again a PL/SQL table ..Now i execute the following Pl/SQL block ...

declare
TYPE tst_rec IS RECORD
( col1 NUMBER
,col2 VARCHAR2(150));
TYPE tst_tab IS TABLE OF tst_rec INDEX BY BINARY_INTEGER;
v_tab tst_tab;
begin
v_tab(1).col1 := 1;
v_tab(1).col2 := 'ABC';
WR_tst_pack.wr_test(v_tab);
end;

This raises me an error
PLS-00306: wrong number or types of arguments in call to 'WR_TST'

Is passing of PL/SQL table as parameter wrong.If not what should i do to sort this out.

Kindly execuse me if i sound stupid :-) I am just new to PL/SQL ..

Anybody to answer my above given question ?????????

November 15, 2005 - 5:21 am UTC

Reviewer: Pran


Tom Kyte

Followup  

November 15, 2005 - 8:41 am UTC

I do not see every single review, I try to - but don't see them all. It is purely catch as catch can.


Look at your code and ask yourself "why am I defining two totally different records"

sure, they might have the same attributes - but you have two definitions.

Your procedure takes an input type of

wr_tst_pack.tst_tab;


not a brand new type defined locally and only visible to the anonymous plsql block called 'tst_type'

declare
v_tab wr_tst_pack.tst_tab;
begin
v_tab(1).col1 := 1;
v_tab(1).col2 := 'ABC';
WR_tst_pack.wr_test(v_tab);
end;

Using array types with dynamic SQL

March 15, 2007 - 4:39 pm UTC

Reviewer: Brian Gregg from Tampa, FL

Tom,

I have read in the documentation that Oracle Dynamic SQL method 4 does *not* support binding PL/SQL arrays, however the documentation (Chapter 14 of the Pro*C documentation) states that ANSI Dynamic SQL supports all types, presumably array types as well.

I have been trying, with no success, to call a PL/SQL procedure with both a number and varchar array type (similar to your demo_pkg.emp_report procedure) in the parameter list through ANSI Dynamic SQL. It seems no matter what I do I encounter both a compiler warning and runtime error. I can call the procedure just fine without dynamic SQL in Pro*C (using EXEC SQL EXECUTE; BEGIN proc(:arr) END; END-EXEC;), but I need the dynamic flexibility.

If I do an "EXEC SQL SET DESCRIPTOR 'in' VALUE 1 REF DATA :arr" where arr is of an integer array C type (int arr[MAX_ARR]) then it will compile and then die during runtime with an ORA-06550 error. I have followed the guide for all the other steps.

The minute I attempt to assign any other attributes to the bind via the SET DESCRIPTOR command I get the following compiler warning: PCC-W-02344, Host variable array size mismatch. Using minimum: 1. The compilation still succeeds but the oracle runtime error still appears. This same things happens for the VARCHAR array, which is of C-type char arr[MAX_ARR][MAX_STR_LEN+1]. Both can be bound successfully without dynamic SQL.

If this is truely supported by ANSI Dynamic SQL then can you please recommend the appropriate SET commands for the number and varchar arrays. I have tried running a loop and setting type to numbers from -255..255 to see if there was an undocumented type that would work to no avail, the app either cores or stops with an ORA error; I can't get rid of the type mismatch problem! I don't know if the compiler warning is the root of the problem or what, but I haven't been able to fix it.

I could really use your help on this one as neither Google nor the Oracle documentation has provided examples showing ANSI Dynamic SQL binding to anything other than basic types and my experiments to get it working have all come up short. I am beginning to think the bug is in the documentation, lol.
Tom Kyte

Followup  

March 16, 2007 - 3:07 pm UTC

if the documentation says "we do not support this type", well......

it doesn't.

plsql table types - are not sql types. dynamic sql method 4 in pro*c supports SQL types.

You could

create type mytable as table of number;
/

in SQL and use that, but a plsql table type:

create package my_pkg
as
type array is table of number;
end;
/

that is not known to SQL.

you can use OCI (mixed with pro*c) to bind plsql table types.

Still no go :-(

March 19, 2007 - 11:49 am UTC

Reviewer: A reader

Tom,

The documentation states that it doesn't support the types for Oracle Dynamic SQL, it doesn't have that same caveat for ANSI Dynamic SQL.

However, I will take you at your word that it means SQL types; I see Oracle Dynamic SQL doesn't support certain SQL types either and maybe that's the extentions they are refering to.

So, I took your suggestion and created an array type, however I am still having issues making it work with ANSI Dynamic SQL; it core dumps.

The ANSI dynamic SQL documentation (Pro*C Programmer's Guide - 14.3.4) says to use a describe statement and set the type to 108 for object types, did that. Here's what I have:

The create type statement is:
CREATE OR REPLACE
TYPE number_tbl_type AS TABLE OF NUMBER;
/


The procedure (in a package test_pkg) is:
 PROCEDURE test_type(
       no_array       OUT number_tbl_type
      ) IS
 BEGIN
    no_array(1) := 1;
 END;


and the Pro*C snip is:
#define MAX_ARR_SIZE 5

const static char *procSQL =
 "BEGIN\ntest_pkg.test_type(:iArr);\nEND;";
  
const static int oraUserDefinedType = 108;
  
struct sqlca sqlca;
 
int  iArr[MAX_ARR_SIZE];
 
EXEC SQL ALLOCATE DESCRIPTOR 'in' WITH MAX 1;

EXEC SQL PREPARE procCallStmt FROM :procSQL;

EXEC SQL DESCRIBE INPUT procCallStmt USING DESCRIPTOR 'in';

EXEC SQL WHENEVER SQLERROR CONTINUE;
 
EXEC SQL SET DESCRIPTOR 'in' VALUE 1 REF DATA = :iArr, TYPE = :oraUserDefinedType;

EXEC SQL EXECUTE procCallStmt USING DESCRIPTOR 'in';


I still get a compile time warning regarding the "SET DESCRIPTOR" line "PCC-W-02344, Host variable array size mismatch. Using minimum: 1". However, even if it used the minimum of 1 for the array size, that's all I'm trying to stick in it (intentionally for this test) in the PL/SQL code.

Maybe I just can't solve it this way.... I am open to mixing in the OCI here if I must, so if you could point me to a good set of references for pulling all the required OCI handles out of a Pro*C context I would appreciate it. I can read the OCI documentation for the rest, unless you want to give me a short example which I would appreciate!

The worse part is, it runs just fine like this (for the PL/SQL array types):
EXEC SQL EXECUTE
BEGIN
  test_pkg.test_type(:iArr);
END;
END-EXEC;

However, the reason I can't use this is because of other Pro*C limitations (can't pass pointers into the call, can't access C structure variables inside BEGIN/END, etc). So, I thought that dynamic SQL would allow me more flexibility to set up my copy buffers. Seems the OCI will allow me the most flexibility and speed, so maybe that's the best way at this point.

THANKS Tom!
Tom Kyte

Followup  

March 19, 2007 - 12:16 pm UTC

it is not an array, there should be no array size, it is a single variable - a collection.

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14407/pc_05adv.htm#i431046

March 19, 2007 - 1:13 pm UTC

Reviewer: A reader

Tom,

Interesting, wonder why the integer array works just fine with the anonymous PL/SQL block and straight EXEC. Maybe some insight into the under-hood operations here would be useful, since I am looking to recreate them anyway.

Thanks for the OCI information.

Failed in OCI...still no go!

April 12, 2007 - 12:24 pm UTC

Reviewer: Brian Gregg from Tampa, FL

Tom,

I took your suggestions to use OCI and the application is still not working. When I run the code I get "ORA-03113: end-of-file on communication channel." I checked with the DBA and on their side they are getting a generic ORA-07445 on their side, which is obviously causing my disconnection. So either there is a problem with my code that is causing the DB to blow up, or a problem with the DB. However, I know that if I remove the array bind and call a procedure with just a single numeric bind all goes well. I inserted debug statements into the PL/SQL procedure and I know it executes fully, so the death must be occurring somewhere after the procedure executes; I'm not sure why.

Here is the code, maybe you can see something glaringly wrong...but I'm at a loss with what to do. I read through the OCI documentation and as far as I can tell I'm not doing anything wrong.

PL/SQL code that I'm calling:

CREATE OR REPLACE PACKAGE test_pkg IS

 TYPE p_char_array  IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
    
 
 PROCEDURE test_type(
       c_array       IN OUT p_char_array,
    num     OUT NUMBER
 );
   
END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg IS

 
 PROCEDURE test_type(
       c_array       IN OUT p_char_array,
    num     OUT NUMBER
 ) IS
 BEGIN
    insert into tst_dbg_log values (sysdate, 'test_type', 'enter');
    
    c_array(1) := 1;
    num := 1;
    
    insert into tst_dbg_log values (sysdate, 'test_type', 'exit');    
    commit;
 END;
   
END test_pkg;
/


Then the relevant OCI function that is causing me the headache:

dbmFailureCodes_t dbmTestProc(void *cxt)
{
 #define MAX_ARR_SIZE 5
 #define MAX_STR_SIZE 11
 #define NUM_BINDS  2
 
 const static char *procSQL =
  "BEGIN\ntest_pkg.test_type(:arr, :num);\nEND;";

  /* - OCI variables */
 
 OCIEnv  *ociEnv;
 OCISvcCtx *ociSvcCxt;
 
 OCIError *ocihndlErr;
 OCIStmt  *ocihndlStmt;
 OCIBind  *ocihndlBind[NUM_BINDS];
 
 sword   ociStatus;
 
  /* - statement data buffers */
 
 char   cArr[MAX_ARR_SIZE][MAX_STR_SIZE+1];
 ub4    maxArrSz = MAX_ARR_SIZE;
 
 int    cnt;
 
  /* - misc. */
 
 int    i;
 
 /* get the environment and service context for OCI */
 
 if (SQLEnvGet(cxt, &ociEnv) != SQL_SUCCESS)
 {  
  return DBM_FC_OP_FAILED;
 }
 
 if (SQLSvcCtxGet(cxt, NULL, 0, &ociSvcCxt) != SQL_SUCCESS)
 {
  return DBM_FC_OP_FAILED;
 }
 
 /* create an error and statement handles for OCI */
 
 if (OCIHandleAlloc(ociEnv, (void **) &ocihndlErr, OCI_HTYPE_ERROR, 0, 0) != OCI_SUCCESS)
 {
  return DBM_FC_OP_FAILED;
 }
 
 if (OCIHandleAlloc(ociEnv, (void **) &ocihndlStmt, OCI_HTYPE_STMT, 0, 0) != OCI_SUCCESS)
 {
  return DBM_FC_OP_FAILED;
 }
 
 /* prepare statement */
 
 if (OCIStmtPrepare(ocihndlStmt, ocihndlErr, procSQL, strlen(procSQL),
                       OCI_NTV_SYNTAX, OCI_DEFAULT) != OCI_SUCCESS)
 {
  return DBM_FC_OP_FAILED;
 }
 
 /* bind statement */
 
  /* - initialize binds to NULL */
  
 for (i = 0; i < NUM_BINDS; i++)
 {
  ocihndlBind[i] = NULL;
 }

 if (OCIBindByPos(ocihndlStmt, &ocihndlBind[0], ocihndlErr, 1,
  cArr, sizeof(cArr[0]), SQLT_STR, NULL, NULL, NULL,
  maxArrSz, NULL, OCI_DEFAULT) != OCI_SUCCESS)
 {
  return DBM_FC_OP_FAILED;
 }
 
 if (OCIBindArrayOfStruct(ocihndlBind[0], ocihndlErr, sizeof(cArr[0]), 0, 0, 0) != OCI_SUCCESS)
 {
  return DBM_FC_OP_FAILED;
 }
 
 if ((ociStatus = OCIBindByPos(ocihndlStmt, &ocihndlBind[1], ocihndlErr, 2,
  &cnt, sizeof(cnt), SQLT_INT, NULL, NULL, NULL,
  NULL, NULL, OCI_DEFAULT)) != OCI_SUCCESS)
 {
  return DBM_FC_OP_FAILED;
 }
 
 /* initialize output buffers */
 
 for (i = 0; i < MAX_ARR_SIZE; i++)
 {
  cArr[i][0] = '\0';
 }
 
 /* execute statement */

 printf("Testing execution...\n");
 
 if ((ociStatus = OCIStmtExecute(ociSvcCxt, ocihndlStmt, ocihndlErr, (ub4) 1, 0,
  NULL, NULL, OCI_DEFAULT)) != OCI_SUCCESS)
 {
  checkerr(ocihndlErr, ociStatus);
  
  return DBM_FC_OP_FAILED;
 }
  
 return DBM_FC_SUCCESS;
}


I have tried the following experiments:

1) In the first OCIBindByPos statement I remove the reference to maxArrSz and replace it with 0; result "ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'TEST_TYPE'" Obviously, I need a number here to signify that an array bind is being performed and digging in the OCI shows that to be true.

2) Change the PL/SQL function to remove the array and just return the number; change the code to take a single bind; result - runs successfully.

3) Change maxArrSz to be the sizeof the entire array; result - no difference, still causes ORA-03113.

I have also perused the header files looking for a different SQLT_* type that I could use and nothing strikes me as being correct. At this point I'm just at a loss, I don't know why this code is bombing due to the array bind.

Anything you can suggest, any additional reading, or a fix, would be highly appreciated.
Tom Kyte

Followup  

April 13, 2007 - 12:14 pm UTC

ora-3113, ora-7445, ora-600 => please utilize support.

May 30, 2007 - 2:02 pm UTC

Reviewer: Ramchandar

Tom,
You said from 3gl we can send only table of scalars and not as record.I am in a situation where i need to send a array of structure to a procedure from pro*c.I have created an object my_rec and with help of OTT i got a header which i have included in my pro*c source file

here is object creation database
create or replace type my_rec as object(
name varchar(50),
age number(3)
);

create or replace type my_tab as table of my_rec;


Here is the pro*c code
#include <sqlca.h>
#include <string.h>
#include <oci.h>

EXEC SQL BEGIN DECLARE SECTION;
/* This is extracted from the header generated by ott */
struct my_rec
{
   OCIString * name;
   OCINumber age;
};
typedef struct my_rec my_rec;
EXEC SQL END DECLARE SECTION;

int main()
{
EXEC SQL BEGIN DECLARE SECTION;
  varchar log1[20];
  my_rec my_rec2[3];
EXEC SQL END DECLARE SECTION;

  strcpy((char *)log1.arr,"ouser1/ouser1");
  log1.len=strlen((char *)log1.arr);
   log1.arr[log1.len]='\0';

  EXEC SQL CONNECT :log1;

  my_rec2[0].name="john";
  my_rec2[0].age=27;

  my_rec2[1].name="Dennis";
  my_rec2[1].age=23;

  my_rec2[2].name="Todd";
  my_rec2[2].age=25;


  EXEC SQL EXECUTE
  DECLARE
   myrec my_tab;
  BEGIN
     myrec := :my_rec2;
  END;
  END-EXEC;


}


While compiling it says


Semantic error at line 9, column 14, file obj.pc:
   OCINumber age;
.............1
PCC-S-02400, This host variable must be declared as a pointer type
     myrec := :my_rec2;
...............1
PLS-S-00382, expression is of wrong type
     myrec := :my_rec2;
...........1


The reason for not including header generated by ott is it is not recognizing my_rec type as it is not in BEGIN and END declare section.

My questions
1) Whether is it possible to pass array of structure from pro*c to PL/SQL
2) Why i am getting this error?Do i need to declare my_rec2 as pointer and do ALLOCATE ?



Thanks
Ramchandar



Tom Kyte

Followup  

May 30, 2007 - 4:08 pm UTC

this chapter:
http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14407/pc_18col.htm#g1023189

shows how to do collections in pro*c, you will not directly bind your C struct, you'll do it as demonstrated there....


June 02, 2007 - 7:59 am UTC

Reviewer: Ramchandar

Thanks Tom for your help .Is it possible to do the same with PL/SQL tables than using collections..? and if possible then which one would be the best ..?Could you please suggest?
Tom Kyte

Followup  

June 03, 2007 - 5:38 pm UTC

collections would be suggested, plsql table types are not easily bound to from other languages.

multi-dimensional dynamic array and pointer application

July 24, 2007 - 8:14 am UTC

Reviewer: Piyush from India

Hi Tom,

I love to read your blog where I found solutions to the most complex problems as well as ways to find efficient and best possible way for a given problem statement with benchmark.

Now I have received 2 questions from the developers currently first one I fumbled to answer them and about 2nd I asked them to call the stuff in Java application.

Unfortunately, I have only the case study but no codes for it.

Well 1st Case:-
They want to pass multi-dimensional array to the procedure where the dimensions are known at runtime only. This means an array could have dimentions (5,10) or (1,2). Is there anything possible in PL/SQL (VERsion 9 and 10) ? If yes, then how. Could you please give some examples of it.

2nd Case:-
We have one C / C++ DLL which has used some pointers and complex algorithm and the logic is to return no of notes based on the weightage applied and availability. It will take one input paramter amount. It has nothing to do with the database (ATM like logic). Can we convert the same in PL/SQL ? Should we call the same directly from PL/SQL using OCI calls or it would be efficient if the call is made from Java application directly. I think we should not try to convert the same if it has nothing to do with DB and that OCI call should be from Java application instead from Oracle procedure.

Please give me your valuable comments and suggestions.

Thank you,
Piyush
Tom Kyte

Followup  

July 24, 2007 - 10:17 am UTC

what do you mean by dimensions (5,10) or (1,2)?

that looks like a two dimensional array? PLSQL supports collections of collections - yes.


plsql is a programming language, if you put into a specification the logic necessary to process your data, I'm sure you can implement it in ANY language.

I'm not saying that you should port it to plsql, I'm just saying that of course - anything is possible.

DLL to Procedure

July 26, 2007 - 1:27 am UTC

Reviewer: Piyush from India

Hi Tom!

Where would you recommend. I think PL/SQL is not the best option. Java could be or any other procedural language would be. Do you agree with me ?

If somehow we implement it in PL/SQL, what would be the impact on performance and on DB Server IO.

Thanks.
Tom Kyte

Followup  

July 27, 2007 - 8:23 am UTC

tell you what - you tell us why you think a language like plsql is not the best option.

it is a LANGUAGE for goodness sake, why would some OTHER procedural language (the set of procedural languages INCLUDES PLSQL after all) be better

of course I do not agree with you - in fact, I believe quite the opposite of you and this mentality of "plsql isn't good, we need java" just is annoying - for you have no technical basis for saying this.


if you implement it in plsql, likely you will decrease IO on the server since you'll actually be able to have a person that knows the database and how it works look at the code and suggest more efficient ways to do it.

I'm at a loss as to how you associate "using plsql" with "database server IO" - what connection are you trying to make?????

apologies

July 29, 2007 - 12:11 pm UTC

Reviewer: Piyush from India

Sorry Tom,

I have implemented in PL/SQL efficiently with some different legible algorithm given to me. What I meant was we should re-design the stuff and should not code as it is like dlls in any other language. If someone require that then pl/sql is not the best choice.

I did not meant that pl/sql is not correct. I love it myself. I just wanted to know the best practice. Of course, something related to database should be kept in the database objects and I adore this practice and have adopted it already.

Thanks and Regards.
Tom Kyte

Followup  

July 29, 2007 - 1:28 pm UTC

sorry, that first paragraph is not saying anything to me - not making sense.

my mantra:

o do it in sql, as much as you can - pack it into a single sql statement
o if you cannot possibly do it in just sql, code as LITTLE plsql as possible to accomplish it
o if you cannot do it in plsql (because there is something the language doesn't support) consider a tiny bit of java
o if you cannot do it in java (speed, low level access needed, whatever) write as little C as you can...

thanks

August 02, 2007 - 6:05 am UTC

Reviewer: Piyush from India

Thanks Tom,

I will remember this mantra for all the future and currently assigned tasks.

Your tips are always useful to me.

how to pass empty Associative Array into procedure

August 14, 2007 - 11:28 am UTC

Reviewer: Jen from TX

A stored procedure with an associative array as in parameter is called by a .net application. It all works good when passing an array that has values, but it get less encouraging results when attempting to pass an "empty" list.
The error message says "OracleParameter.Value is invalid".

How can I get around with this problem? Thanks ahead.

Tom Kyte

Followup  

August 20, 2007 - 12:02 pm UTC

are you binding "null"? I'm not sure what you are doing - hence it is hard to comment.

Getting back an array of records from a package

August 15, 2007 - 10:10 am UTC

Reviewer: veera from Gaithersburg, MD

I created a procedure in a package which gives
all the roles in the database in an array of
records.

When Iam calling this procedure from another program
Iam not getting the array back.
When I changed the procedure into a function and return
the array I could get back the array elements.

Is it a restriction or am I doing anything wrong.

This is the package
-------------------

CREATE OR REPLACE PACKAGE dbaroles AS
TYPE r_dba_roles_typ IS RECORD
(rolename VARCHAR2(30),
role_assigned VARCHAR2(1));
TYPE t_dba_roles_typ IS TABLE OF r_dba_roles_typ
INDEX BY BINARY_INTEGER;
v_dba_roles t_dba_roles_typ;
v_role_index BINARY_INTEGER := 0;

PROCEDURE get_dba_roles (p_db_user_name IN VARCHAR2,
p_roles OUT t_dba_roles_typ,
p_error_message OUT VARCHAR2,
p_status OUT BOOLEAN
);


END;

CREATE OR REPLACE PACKAGE BODY dbaroles AS
PROCEDURE get_dba_roles (p_db_user_name IN VARCHAR2,
p_roles OUT t_dba_roles_typ,
p_error_message OUT VARCHAR2,
p_status OUT BOOLEAN
) IS
BEGIN
FOR c1 IN
(SELECT DECODE(NVL(grantee, 'N'), 'N', 'N', 'Y') role_assigned, role
FROM dba_roles a,
dba_role_privs b
WHERE a.role = b.granted_role (+)
AND NVL(grantee(+), p_db_user_name) = p_db_user_name
AND (role like '%xyz%'))
LOOP
v_message := 'dba roles for : '||c1.role;
v_role_index := v_role_index + 1;
v_dba_roles(v_role_index).rolename := c1.role;
v_dba_roles(v_role_index).role_assigned := c1.role_assigned;
v_message := 'dba roles : '||v_dba_roles(v_role_index).rolename||' - '||v_dba_roles(v_role_index).role_assigned;
insert_log_line(v_message);
END LOOP;
p_status := TRUE;
EXCEPTION
WHEN OTHERS THEN
p_status := FALSE;
p_error_message := 'Get dba roles error: '||v_message||' - '||SQLERRM;
insert_log_line(p_error_message);
END;
END;


Then Iam calling from another program
-------------------------------------

DECLARE
v_dba_roles dbaroles.t_dba_roles_typ;
v_role_index BINARY_INTEGER := 0;
v_error_message VARCHAR2(300);
v_status BOOLEAN;
BEGIN
v_dba_roles := dbaroles.get_dba_roles('JHON') ,
v_dba_roles ,
v_error_message ,
v_status
);
IF v_status THEN
dbms_output.put_line('success ');
ELSE
dbms_output.put_line('failure ');
END IF;
dbms_output.put_line(v_dba_roles.count||' - ');
dbms_output.put_line(v_dba_roles(1).rolename||' - '||v_dba_roles(1).role_assigned);
FOR i in v_dba_roles.first..v_dba_roles.last
LOOP
dbms_output.put_line('inside loop - ');
dbms_output.put_line(v_dba_roles.first||' - '||v_dba_roles.last);
dbms_output.put_line(v_dba_roles(i).rolename||' - '||v_dba_roles(i).role_assigned);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error '||SQLERRM);
END;

Tom Kyte

Followup  

August 20, 2007 - 7:14 pm UTC

man - oh - man.

Please forever and for always - LOSE THE WHEN OTHERS THEN NULL; code you have.

when others - not followed by RAISE is a bug, a bug in your code, just STOP DOING IT.

Your code does not compile - v_message for example...

Getting array from stored procedure

August 21, 2007 - 8:58 am UTC

Reviewer: veera from Gaithersburg, MD

I only sent a small part of the code to test the package. So I did not put all the error handling in this. And the anonymous procedure is only for debugging and not used in the actual code.

Here is the code without any compile errors.

This is the package
-------------------

CREATE OR REPLACE PACKAGE dbaroles AS
TYPE r_dba_roles_typ IS RECORD
(rolename VARCHAR2(30),
role_assigned VARCHAR2(1));
TYPE t_dba_roles_typ IS TABLE OF r_dba_roles_typ
INDEX BY BINARY_INTEGER;
v_dba_roles t_dba_roles_typ;
v_role_index BINARY_INTEGER := 0;

PROCEDURE get_dba_roles (p_db_user_name IN VARCHAR2,
p_roles OUT t_dba_roles_typ,
p_error_message OUT VARCHAR2,
p_status OUT BOOLEAN
);


END;
/
CREATE OR REPLACE PACKAGE BODY dbaroles AS
PROCEDURE get_dba_roles (p_db_user_name IN VARCHAR2,
p_roles OUT t_dba_roles_typ,
p_error_message OUT VARCHAR2,
p_status OUT BOOLEAN
) IS
BEGIN
FOR c1 IN
(SELECT DECODE(NVL(grantee, 'N'), 'N', 'N', 'Y') role_assigned, role
FROM dba_roles a,
dba_role_privs b
WHERE a.role = b.granted_role (+)
AND NVL(grantee(+), p_db_user_name) = p_db_user_name
AND (role like '%xyz%'))
LOOP
--v_message := 'dba roles for : '||c1.role;
v_role_index := v_role_index + 1;
v_dba_roles(v_role_index).rolename := c1.role;
v_dba_roles(v_role_index).role_assigned := c1.role_assigned;
--v_message := 'dba roles : '||v_dba_roles(v_role_index).rolename||' -
--'||v_dba_roles(v_role_index).role_assigned;
--insert_log_line(v_message);
END LOOP;
p_status := TRUE;
EXCEPTION
WHEN OTHERS THEN
p_status := FALSE;
--p_error_message := 'Get dba roles error: '||v_message||' - '||SQLERRM;
--insert_log_line(p_error_message);
END;
END;
/

Then Iam calling from another program
-------------------------------------

DECLARE
v_dba_roles dbaroles.t_dba_roles_typ;
v_role_index BINARY_INTEGER := 0;
v_error_message VARCHAR2(300);
v_status BOOLEAN;
BEGIN
dbaroles.get_dba_roles('John' ,
v_dba_roles ,
v_error_message ,
v_status
);
IF v_status THEN
dbms_output.put_line('success ');
ELSE
dbms_output.put_line('failure ');
END IF;
dbms_output.put_line(v_dba_roles.count||' - ');
dbms_output.put_line(v_dba_roles(1).rolename||' - '||v_dba_roles(1).role_assigned);
FOR i in v_dba_roles.first..v_dba_roles.last
LOOP
dbms_output.put_line('inside loop - ');
dbms_output.put_line(v_dba_roles.first||' - '||v_dba_roles.last);
dbms_output.put_line(v_dba_roles(i).rolename||' - '||v_dba_roles(i).role_assigned);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error '||SQLERRM);
END;




Tom Kyte

Followup  

August 22, 2007 - 11:46 am UTC

well, there is the fact you assign to a package global variable v_dba_roles, but you passed in p_roles

you are putting the output into a package global variable, you are reading a parameter you never set.


Here is how to write this code, some notes:

a) lose the when others, useless bits of code. If you use it, use ONLY to log the error and then immediately follow by a RAISE.

b) lose the status and error message bits, they are useless in the year 2007. use exceptions. Status is OBVIOUS (you get exception, it did not work). Error Message is EASILY retrieved.

c) less code = less bugs, strive to write as little code as possible


ops$tkyte%ORA10GR2> CREATE OR REPLACE PACKAGE dbaroles
  2  AS
  3          cursor get_roles( p_username varchar2 )
  4          is
  5      SELECT DECODE(NVL(grantee, 'N'), 'N', 'N', 'Y') role_assigned, role  rolename
  6        FROM  dba_roles a, dba_role_privs b
  7       WHERE  a.role = b.granted_role (+)
  8         AND  NVL(grantee(+), p_username) = p_username
  9         AND  (role like '%');
 10
 11          type t_dba_roles_typ is table of get_roles%rowtype;
 12
 13          PROCEDURE get_dba_roles
 14          (p_db_user_name  IN VARCHAR2,
 15           p_roles         OUT t_dba_roles_typ);
 16  END;
 17  /

Package created.

ops$tkyte%ORA10GR2> CREATE OR REPLACE PACKAGE BODY dbaroles
  2  AS
  3  PROCEDURE get_dba_roles
  4  (p_db_user_name    IN VARCHAR2,
  5   p_roles          OUT t_dba_roles_typ)
  6  is
  7  BEGIN
  8          open get_roles( p_db_user_name );
  9          fetch get_roles BULK COLLECT into p_roles;
 10          close get_roles;
 11  end;
 12
 13  END;
 14  /

Package body created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> DECLARE
  2     v_dba_roles          dbaroles.t_dba_roles_typ;
  3  BEGIN
  4     dbaroles.get_dba_roles(user  , v_dba_roles    );
  5
  6     FOR i in v_dba_roles.first..v_dba_roles.last
  7     LOOP
  8        dbms_output.put_line('inside loop - ');
  9        dbms_output.put_line(v_dba_roles.first||' - '||v_dba_roles.last);
 10        dbms_output.put_line(v_dba_roles(i).rolename||' - '||v_dba_roles(i).role_assigned);
 11     END LOOP;
 12  END;
 13  /
inside loop -
1 - 38
CONNECT - Y
inside loop -
....

May 14, 2008 - 8:59 am UTC

Reviewer: Searcher from India

Thanks for the solution. Example given is simply great.

How to pass pl/sql table to stored procedure in dynamic sql

June 19, 2008 - 2:08 am UTC

Reviewer: Omer from Dubai

I am trying to pass pl/sql table to dynamic sql.
Here is my scenario:

CREATE OR REPLACE PROCEDURE p_start_batch
(p_header IN NUMBER,
p_code IN VARCHAR2,
p_action IN p.t_actioncharge)
IS
l_proc_name VARCHAR2(30000);
BEGIN
--
l_proc_name := 'mypkg.p_update_many_details('||p_action||')';
l_proc_name := 'BEGIN '||l_proc_name|| '; END;';

pkg_schedule.p_sce(l_proc_name);

EXCEPTION
WHEN OTHERS THEN
RAISE;
END;


Definition of qp.t_actioncharge is:
TYPE t_actioncharge IS TABLE OF emp%ROWTYPE;

I have to pass p_action (pl/sql table) parameter to mypkg.p_update_many_details in string. Then this string/job will be scheduled using pkg_schedule.p_sce procedure and will be run under different session.
The problem is, when I pass p_action in string, it gives error.

l_proc_name := 'mypkg.p_update_many_details('||p_action||')';

is there any way to pass pl/sql table to dynamic sql?
Thanks for your great help.
Tom Kyte

Followup  

June 19, 2008 - 10:12 am UTC

can anyone, anyone explain the 'logic' behind this construct? why do so many people do this? what is the point? do you know that all it accomplishes is to make debugging the code incredibly HARD, it moves the line number of the error from the actual line to the end of the routine?

EXCEPTION
  WHEN OTHERS THEN
  RAISE;
END; 


it ranks up there in the top of the worst practices possible - not just in plsql but in EVERY language.

You must be using dbms_job or the like, in order to use dbms_job in a bind friendly fashion, there is only one "right way" to do it as far as I'm concerned.

You schedule a job and pass only the job id to it.
You pass ALL parameters via a table.
Your job queries this information out by job id.

eg:

ops$tkyte%ORA10GR2> create table job_parms
  2  ( job_id number primary key,
  3    data varchar2(20)
  4  ) organization index;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table job_parms_child
  2  ( job_id references job_parms on delete cascade,
  3    seq number,
  4    more_data varchar2(20),
  5    constraint job_parms_child_pk primary key(job_id,seq)
  6  ) organization index;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t ( msg varchar2(4000) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure the_job( p_job in number )
  2  as
  3      type job_parms_child_arr is table of job_parms_child%rowtype;
  4      l_parms     job_parms%rowtype;
  5      l_parms_arr job_parms_child_arr;
  6
  7      l_data      long;
  8  begin
  9      select * into l_parms from job_parms where job_id = p_job;
 10      select * bulk collect into l_parms_arr from job_parms_child where job_id = p_job order by seq;
 11
 12      l_data := l_parms.data;
 13      for i in 1 .. l_parms_arr.count
 14      loop
 15          l_data := l_data || ' array('||i||') = "' || l_parms_arr(i).more_data || '"';
 16      end loop;
 17      insert into t values ( l_data );
 18
 19      delete from job_parms where job_id = p_job;
 20  end;
 21  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t;

no rows selected

ops$tkyte%ORA10GR2> declare
  2      l_job       number;
  3  begin
  4      dbms_job.submit( l_job, 'the_job(JOB);' );
  5
  6      insert into job_parms (job_id,data) values (l_job,'hello world');
  7      insert into job_parms_child(job_id,seq,more_data) values ( l_job, 1, 'xxxxxx' );
  8      insert into job_parms_child(job_id,seq,more_data) values ( l_job, 2, 'yyyyyy' );
  9      insert into job_parms_child(job_id,seq,more_data) values ( l_job, 3, 'zzzzzz' );
 10
 11      commit; -- job runs....
 12      dbms_lock.sleep(5);
 13  end;
 14  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t;

MSG
-------------------------------------------------------------------------------
hello world array(1) = "xxxxxx" array(2) = "yyyyyy" array(3) = "zzzzzz"


Can I pass OUT parameters as an array?

September 12, 2008 - 1:02 pm UTC

Reviewer: Reader from Chicago from Chicago,IL USA

Hi Tom,
Excellant web blog! :) You have solved some of my issues here.
Question:
Can I pass an array which has one IN parameter and 2 Out paramters to a stored procedure.
Say for example
My record set
(emp_no number /* IN paramter */
,emp_dept varchar2 /* IN paramter */
,emp_salary number /* IN paramter */
,emp_title varchar2 /* IN paramter */
,emp_start_date date /* OUT paramter */
,emp_job_hisotry varchar2 /* OUT paramter */)
how do you handle such a situation? the start_date and job_history values need to be extraced from different table in my proc2 and these values must be passed to the proc1 for further processing.
Reasong for taking this approach:
1. I will be passing it as an array to reduce the no. of calls from one proc1 to another.
2. And my client prefers modular coding so I can't include proc2 in proc1 :)
Your suggestions will be highly valued.
Thanks,
Reader from Chicago

Tom Kyte

Followup  

September 16, 2008 - 1:49 pm UTC

yes, you can.

just do it, it works

ORA-00947

December 15, 2008 - 6:08 am UTC

Reviewer: Ananth from INDIA

Hi Tom,

Can i use WITH clause inside a Cursor statement.

I created a query using WITH Clause.
i want to replicate the same in PLSQL program.
so i used CURSOR Statement..

while compiling this procedure, i encounter ORA-00947 error message ...

Could you pls help me out...
Tom Kyte

Followup  

December 29, 2008 - 10:04 am UTC

you'll need an example to demonstrate your issue please

ops$tkyte%ORA9IR2> declare
  2          cursor c
  3          is
  4          with data as (select * from dual)
  5          select * from data;
  6
  7          l_rec c%rowtype;
  8  begin
  9          open c;
 10          fetch c into l_rec;
 11          close c;
 12  end;
 13  /

PL/SQL procedure successfully completed.

pass associative arry to stored procedure

May 13, 2009 - 6:26 pm UTC

Reviewer: srp from madison, WI, USA

Thanks Tom,
This is really really helpful. I am trying to write a stored procedure with associative arrays passed to it and return from it. Can anyone show me a small example how to do that and especially how to call this stored procedure from java code.

Thanks

Tom Kyte

Followup  

May 13, 2009 - 7:49 pm UTC

you cannot do that, only plsql index by binary integer tables and sql collections can be passed from a 3gl to plsql.

plsql index by <some other datatype> are not supported from a 3gl to plsql

passing multilevel collections as parameter to procedure and handle records inside the procedure

January 13, 2011 - 6:25 am UTC

Reviewer: G.S.Reddy from Hyd, India

hi tom,

how can we pass a nested records to a procedure and how can we handle those records inside that procedure.

ex:
create type cust_detail_type 
is 
object(itemno   number,
       itemname varchar2(30),
       qty      number,
       price    number);

create type cust_header_type
is 
object(ssn    number,
       fname  varchar2(20),
       lname  varchar2(20)
       items  cust_detail_type);

create type customer_tab_type is table of cust_header_type;

procedure ins_cust_proc(p_cust_tab_type_i IN customer_tab_type)
Is
Begin
   -- how can we handle p_cust_tab_type_i here....
End;



can you please give us an example on this
Tom Kyte

Followup  

January 13, 2011 - 10:53 am UTC

those are not multi-level collections, nor is it nested records.


It is a collection of an object type, that object type has a scalar attribute (not a collection) called items that is an object type as well.



ops$tkyte%ORA11GR2> create or replace procedure ins_cust_proc(p_cust_tab_type_i IN customer_tab_type)
  2  Is
  3  Begin
  4          dbms_output.put_line( 'there are ' || p_cust_tab_type_i.count || ' elements in the collection' );
  5          for i in 1 .. p_cust_tab_type_i.count
  6          loop
  7                  dbms_output.put_line( 'ssn('||i||') = ' || p_cust_tab_type_i(i).ssn );
  8                  dbms_output.put_line( 'fname('||i||') = ' || p_cust_tab_type_i(i).fname );
  9                  dbms_output.put_line( 'lname('||i||') = ' || p_cust_tab_type_i(i).lname );
 10                  dbms_output.put_line( 'items('||i||') = (' ||
 11                                         p_cust_tab_type_i(i).items.itemno || ', '||
 12                                         p_cust_tab_type_i(i).items.itemname || ', '||
 13                                         p_cust_tab_type_i(i).items.qty || ', '||
 14                                         p_cust_tab_type_i(i).items.price || ')' );
 15          end loop;
 16  End;
 17  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          array customer_tab_type := customer_tab_type();
  3  begin
  4          for i in 1 .. 3
  5          loop
  6                  array.extend;
  7                  array(i) := cust_header_type( i, 'name ' || i, 'lname ' || i, cust_detail_type( 100+i, 'item ' || i, 1000+i, 42*i ) );
  8          end loop;
  9          ins_cust_proc( array );
 10  end;
 11  /
there are 3 elements in the collection
ssn(1) = 1
fname(1) = name 1
lname(1) = lname 1
items(1) = (101, item 1, 1001, 42)
ssn(2) = 2
fname(2) = name 2
lname(2) = lname 2
items(2) = (102, item 2, 1002, 84)
ssn(3) = 3
fname(3) = name 3
lname(3) = lname 3
items(3) = (103, item 3, 1003, 126)

PL/SQL procedure successfully completed.

thanks a lot

January 14, 2011 - 5:41 am UTC

Reviewer: G.S.Reddy from Hyd, India

great tom, thanks a lot for that reply

multilevel collections

May 17, 2011 - 8:33 am UTC

Reviewer: G.S.Reddy

I have a new requirement, can we have collection like this?

SQL>CREATE OR REPLACE TYPE Emp_Type AS OBJECT(emp_no NUMBER ,
                                          mgr_id NUMBER ,
                                          Employees Emp_Type );
/

SQL>show err
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/1      PLS-00318: type "EMP_TYPE" is malformed because it is a non-REF
         mutually recursive type

  
CREATE type dept( dept_no NUMBER, dName VARCHAR2, Emps Emp_Type);

if possible could you please help.

Thanks

Tom Kyte

Followup  

May 18, 2011 - 9:40 am UTC

of what use could this requirement be. think about this for a minute please. Imagine your statement worked - what exactly would you have (besides an INFINITE data structure????)


How can an emp_type have in it - an emp_type (which implies the emp type in the emp type would have an emp type and so on and so on forever)

tell us the goal, not how you are trying to achieve it (since we don't know what IT is yet)

multilevel collections

May 19, 2011 - 1:53 am UTC

Reviewer: G.S.Reddy

I got this requirement for front-end team as they want data in this format, they gave me an example for this and hierarchy level limit is 10

Employee
----------------------
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER


Tom Kyte

Followup  

May 19, 2011 - 3:16 am UTC

still not getting your point. if they want data in that format, just give them a ref cursor with an associated sql statement that uses connect by. why would there be a "data structure" at all? You just need a result set.

To: G.S. Reddy. Re: Recursive object types

May 19, 2011 - 5:41 am UTC

Reviewer: Kim Berg Hansen from Middelfart, Denmark

Hello, Mr. Reddy

Just a quick pointer that your example here has another flaw:

SQL>CREATE OR REPLACE TYPE Emp_Type AS OBJECT(emp_no NUMBER ,
                                          mgr_id NUMBER ,
                                          Employees Emp_Type );
/


If this had worked, each employee could only have one subordinate employee. You define Employees to be Emp_Type which is just one person.

If it should work, then you would at least have to do something like this:

create or replace type emp_type 
as object(
   emp_no         number ,
   subordinates   emp_tab_type
);

create or replace type emp_tab_type
as table of emp_type;


Your employee object type should have an element that is a collection type. The collection type could then be a collection of employees.

But this example also fails to compile since it also creates recursion in the objects. To allow objects to recurse, you need to use REFs.

Example:

create or replace type emp_type 
as object(
   emp_no         number ,
   subordinates   emp_tab_type
);

create or replace type emp_tab_type
as table of REF emp_type;


When you first create emp_type it will be created invalid, since emp_tab_type does not exist. After creating emp_tab_type, you can compile emp_type again to make it valid.

Or you can do an object type referencing itself like this:

create or replace type emp_type
as object(
   emp_no         number ,
   manager        REF emp_type
);


Theoretically you might even do something like this:

create or replace type emp_type 
as object(
   emp_no         number ,
   manager        REF emp_type,
   subordinates   emp_tab_type
);

create or replace type emp_tab_type
as table of REF emp_type;


An employee object with a REF to a manager object and a table of REFs to subordinate objects.

Personally though I would feel this possibly could get hairy and not necessarily efficient to work. I would prefer Toms suggestion simply to return a cursor using CONNECT BY - very efficient :-)

May 20, 2011 - 4:11 pm UTC

Reviewer: A reader


arrays in procedure

November 03, 2011 - 1:38 am UTC

Reviewer: jyoshna from india

create or replace
TYPE "ARRAY_OF_OUTBOUND_REP_PARAM"
as table of varchar2(4000);
create or replace
PACKAGE "PRPK_OUTBOUND_POPULATION" AUTHID CURRENT_USER
AS
PROCEDURE generic_outbound_report (
p_err_array IN array_of_outbound_rep_param
);
END Prpk_Outbound_Population;

create or replace
PACKAGE BODY "PRPK_OUTBOUND_POPULATION"
AS
PROCEDURE generic_outbound_report (
p_err_array IN array_of_outbound_rep_param
)
AS
array_of_outbound array_of_outbound_rep_param
:= array_of_outbound_rep_param
();
paramvalue param_arr := param_arr ('1356');
lv_querys_c VARCHAR2 (32000) := '';
-- Vikas Mantis id # 468 starts for yearend
lv_pay_grp VARCHAR2 (10) := '';
lv_param5 varchar2(100) ;
lv_param9 varchar2(12);
-- Vikas Mantis id # 468 ends for yearend
BEGIN

array_of_outbound := p_err_array;
paramvalue.EXTEND (22);
paramvalue (1) := array_of_outbound (1); --iv_CompCode_n; --PR1
paramvalue (2) := array_of_outbound (2); -- iv_DataRefNo_n; --PR2
paramvalue (3) := array_of_outbound (3); --iv_PinNo_n;
paramvalue (4) := array_of_outbound (4); --iv_DataRunNo_n;
paramvalue (5) := array_of_outbound (5); --iv_PayGrpCode_c;
paramvalue (6) := array_of_outbound (6); --iv_ProcessId_c;
paramvalue (7) := array_of_outbound (7); --iv_PeriodNo_n;
paramvalue (8) := array_of_outbound (8); -- iv_ProcessType_c;
paramvalue (9) := array_of_outbound (9); --iv_YearCode_c;
paramvalue (10) := array_of_outbound (10); --iv_emp_id_from_c;
paramvalue (11) := array_of_outbound (11); --iv_emp_id_to_c;
paramvalue (12) := array_of_outbound (12); --iv_cost_center_code_c;
paramvalue (13) := array_of_outbound (13); --iv_emp_status_c;
paramvalue (14) := array_of_outbound (14); -- Start_date;
paramvalue (15) := array_of_outbound (15); -- End Date;
paramvalue (16) := array_of_outbound (16); --User Message;
paramvalue (17) := array_of_outbound (17); --uen_id_c;
paramvalue (18) := array_of_outbound (18); --Tax year;
paramvalue (19) := array_of_outbound (19); --emp_id_c;
paramvalue (20) := array_of_outbound (20); --PayElement;
paramvalue (21) := array_of_outbound (21); --IR21_Status;
-- paramvalue (22) := '''Y''';

IF replace(array_of_outbound (22),'''','') = 'A' THEN
paramvalue (22) := 'NULL';
ELSIF replace(array_of_outbound (22),'''','') = 'O' THEN
paramvalue (22) := '''Y''';
ELSIF replace(array_of_outbound (22),'''','')= 'E' THEN
paramvalue (22) := '''N''';
END IF;

/*for i in 1..paramvalue.count
loop

insert into test_ext values ( paramvalue (i) ) ;
end loop;*/
Put_Lines ('Overseas flag is :'||paramvalue (22));
-- Vikas Mantis id # 468 starts for yearend
lv_param5 := paramvalue (5);
lv_param9 := paramvalue (9);
lv_param9 := replace(lv_param9,'''','');

IF paramvalue (2) = '71' then
lv_param5 :=lv_param5 ||',';
Put_Lines ('calling PRSP lv_param5:'||lv_param5);
for i in 1..(length(lv_param5) - length(replace(lv_param5,',')))
loop

lv_pay_grp := substr(lv_param5,1,instr(lv_param5,',',1)-1);
lv_pay_grp := replace(lv_pay_grp,'''','');

PRSP_STP_YEARLY_REPOPULATION(lv_param9 ,lv_pay_grp );

lv_param5 :=replace(lv_param5,substr(lv_param5,1,instr(lv_param5,',',1)),'');
--insert into test_ext values ( lv_param5);
end loop;
ELSIF paramvalue (2) = '72' then

PRSP_IR8S(replace(paramvalue (19),'''',''),replace(paramvalue (5),'''',''),paramvalue(22));
Put_Lines ('after PRSP_IR8S');
end if;
Put_Lines ('after PRSP_STP_YEARLY_REPOPULATION');
COMMIT;
-- Vikas Mantis id # 468 ends for yearend
prsp_outbound (paramvalue);
END;
END prpk_outbound_population;

can u please send me the script for this
Tom Kyte

Followup  

November 03, 2011 - 2:18 am UTC

U gave us the script already.

Seriously - think about this - what the heck do you want anyone to do with this????

If you have a question - it would be best to actually ask it don't you think?

tell me honestly, If I wrote to you:

arrays in procedure

code
code code
code

<no explanation of anything, just a bunch of code>


what could you say to me? could you even begin to understand what it was I was asking?

multi-dimensional array

November 22, 2011 - 8:45 pm UTC

Reviewer: A reader

Hi Tom,

I have used single dimensional array till today.

Could you please give me an example where we can pass multi-dimension array as parameter to a stored procedure and that procedure returns two arrays, ERROR_ARRAY and RESULT_ARRAY.

Lets say, I pass some values to a procedure which has to insert rows in the emp table. In case it gets the row is already present in emp table, then it records the error in ERROR_ARRAY and in case it is successful, it gets inserted into RESULT_ARRAY...

I hope I am able to clarify my conditions.

Tom Kyte

Followup  

November 23, 2011 - 8:23 am UTC

I wouldn't be using a two dimensional array for that, simply use arrays of records.

ops$tkyte%ORA11GR2> create table emp as select * from scott.emp;

Table created.

ops$tkyte%ORA11GR2> alter table emp add constraint emp_pk primary key(empno);

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package my_pkg
  2  as
  3      type array is table of emp%rowtype index by binary_integer;
  4  
  5      procedure p( p_in in array, p_out out array );
  6  end;
  7  /

Package created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package body my_pkg
  2  as
  3  
  4  procedure p( p_in in array, p_out out array )
  5  is
  6  begin
  7      null;
  8      for i in 1 .. p_in.count
  9      loop
 10      begin
 11          insert into emp values p_in(i);
 12      exception
 13          when dup_val_on_index
 14          then
 15              p_out(p_out.count+1) := p_in(i);
 16      end;
 17      end loop;
 18  end;
 19  
 20  end;
 21  /

Package body created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      l_data my_pkg.array;
  3      l_new_data my_pkg.array;
  4  begin
  5      select case when mod(rownum,2)=0 then empno else -empno end,
  6             ename, job, mgr, hiredate, sal, comm, deptno
  7        bulk collect
  8        into l_data
  9        from emp;
 10  
 11      my_pkg.p( l_data, l_new_data );
 12  
 13      for i in 1 .. l_new_data.count
 14      loop
 15          dbms_output.put_line( 'dup empno = ' || l_new_data(i).empno );
 16      end loop;
 17  
 18  end;
 19  /
dup empno = 7499
dup empno = 7566
dup empno = 7698
dup empno = 7788
dup empno = 7844
dup empno = 7900
dup empno = 7934

PL/SQL procedure successfully completed.

One more clarification for the Insert through Array

November 28, 2011 - 4:44 pm UTC

Reviewer: A reader

Hi Tom,

Thanks for the help. It works for table which has primary key on table defined, but I would require this scenario:

Lets say:

create table plan_test (plan_name varchar2(100), effective_dt date, expiry_date date, notes varchar2(1000));

insert into plan_test values('Plan A','20-JAN-2000','31-DEC-2011', 'Plan A');
insert into plan_test values('Plan B','20-JAN-2011','31-DEC-2012', 'Plan B');
insert into plan_test values('Plan C','01-JAN-2001','31-DEC-2010', 'Plan C');
insert into plan_test values('Plan D','25-FEB-1998','31-OCT-2009', 'Plan D');

create table plan_test_lookup (plan_name varchar2(100), effective_dt date, expiry_date date);

insert into plan_test_lookup values('Plan A','20-JAN-2000','31-DEC-2011');
insert into plan_test_lookup values('Plan B','20-JAN-2011','31-DEC-2012');
insert into plan_test_lookup values('Plan C','01-JAN-2001','31-DEC-2010');
insert into plan_test_lookup values('Plan D','25-FEB-1998','31-OCT-2009');
insert into plan_test_lookup values('Plan E','25-FEB-2010','31-OCT-2012');


Now the scenario is : We don't have any primary key on both tables. Primary keys are on different columns which is an sequence number, I didn't include in this example.

Scenario is: Pass all the records of table plan_test_lookup to plan_test. For each row in plan_test_lookup table, check for the existence of plan_name in the table, plan_test. In case the row exists, we check if the new effective date(from table plan_test_lookup) is not between the effective date and expiry date of table plan_test. In case, it exists we put this in an ERROR array. if it does not exist then we insert the row in the table.

Also the collection that we made as ARRAY type of emp may not hold true as the column structure of plan_test_lookup and plan_test differ.

Estimated size of tables:
plan_test_lookup - 1000 Rows
plan_test - 1 Million Rows

Expected result:
PLAN_NAME    EFFECTIVE_DT              EXPIRY_DATE               NOTES 
------------ ------------------------- ------------------------- ------
Plan A       20-JAN-2000 12.00.00      31-DEC-2011 12.00.00      Plan A
Plan B       01-JAN-2013 12.00.00      31-DEC-2015 12.00.00      Plan B
Plan C       01-JAN-2001 12.00.00      31-DEC-2010 12.00.00      Plan C
Plan D       25-FEB-1998 12.00.00      31-OCT-2009 12.00.00      Plan D
Plan E       25-FEB-2010 12.00.00      31-OCT-2012 12.00.00      Plan E


ERROR Array Results:
Already Exists: Plan A : 20-JAN-2001 : 31-DEC-2011
Already Exists: Plan C : 01-JAN-2005 : 31-DEC-2011
Already Exists: Plan D : 25-FEB-1998 : 31-OCT-2009

Hope I am clear now.


Tom Kyte

Followup  

November 29, 2011 - 7:56 am UTC

simple, add the MISSING UNIQUE CONSTRAINT to this

otherwise, you would have to LOCK the table before every single insert in order to prevent any other session from inserting into this table.



so, do the RIGHT thing, the only RIGHT thing, which is

add the MISSING unique constraint.


that is not only the right thing, it is the simpliest approach....

Oracle version : 11gR2 for above question

November 28, 2011 - 4:45 pm UTC

Reviewer: A reader


Followup

November 29, 2011 - 11:51 am UTC

Reviewer: A reader

Hi Tom,

I will implement the same approach as you suggested. I really appreciate your time. Just require a suggestion from you. What happens if we have both rows in the table, active as well as expired, like:

create table plan_test (plan_name varchar2(100), effective_dt date, expiry_date date, notes 
varchar2(1000));

insert into plan_test values('Plan A','20-JAN-2000','31-DEC-2011', 'Plan A');
insert into plan_test values('Plan B','20-JAN-2011','31-DEC-2012', 'Plan B');
insert into plan_test values('Plan C','01-JAN-2001','31-DEC-2010', 'Plan C');
insert into plan_test values('Plan D','25-FEB-1998','31-OCT-2009', 'Plan D');

create table plan_test_lookup (plan_name varchar2(100), effective_dt date, expiry_date date);

insert into plan_test_lookup values('Plan A','20-JAN-2000','31-DEC-2011');
insert into plan_test_lookup values('Plan B','01-JAN-2013','31-DEC-2015');
insert into plan_test_lookup values('Plan C','01-JAN-2001','31-DEC-2010');
insert into plan_test_lookup values('Plan D','25-FEB-1998','31-OCT-2009');
insert into plan_test_lookup values('Plan E','25-FEB-2010','31-OCT-2012');

PLAN_NAME    EFFECTIVE_DT              EXPIRY_DATE               NOTES 
------------ ------------------------- ------------------------- ------
Plan A       20-JAN-2000 12.00.00      31-DEC-2011 12.00.00      Plan A
Plan B       20-JAN-2011 12.00.00      31-DEC-2012 12.00.00      Plan B
Plan B       01-JAN-2013 12.00.00      31-DEC-2015 12.00.00      Plan B
Plan C       01-JAN-2001 12.00.00      31-DEC-2010 12.00.00      Plan C
Plan D       25-FEB-1998 12.00.00      31-OCT-2009 12.00.00      Plan D
Plan E       25-FEB-2010 12.00.00      31-OCT-2012 12.00.00      Plan E



I can do
INSERT INTO plan_test(plan_name, effective_dt, expiry_date) select plan_name, effective_dt, expiry_date from plan_test_lookup x where not exists(select 1 from plan_test t where t.plan_name=x.plan_name and x.effective_dt between t.effective_dt and t.expiry_date);

It may solve the issue of inserting the rows in table but I am not sure how can I return the rows in ERROR array which were not inserted with this logic. Is there really any possible way to return in array with this kind of logic, with this kind of scenario. Could you please suggest.
Tom Kyte

Followup  

November 30, 2011 - 7:01 am UTC

just run the query with "where exists" and you'll get the rows NOT inserted.

so, insert what you want, select out what you didn't want.

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here