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

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,

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.

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.

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

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.

Thanks Tom for your reply.

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

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

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;

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;
/

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

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

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

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?!

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.

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.

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?

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...

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

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 ..

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,

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,

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.

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,
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

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?

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

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.

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.

Thanks Tom,

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

Your tips are always useful to me.

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.

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;

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;

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 -
....

Thanks for the solution. Example given is simply great.

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.

EXCEPTION
  WHEN OTHERS THEN
  RAISE;
END; 

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"

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

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...

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.

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

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

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.

great tom, thanks a lot for that reply

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

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

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 :-)

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

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.

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.

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.

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.