Helena Markova, March 23, 2001 - 7:08 am UTC
A reader, October 17, 2001 - 12:17 pm UTC
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
Pass array to stored proc from VB?
Sasa Petkovic, February 06, 2002 - 4:12 am UTC
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?
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
Teodora Jevtic, March 07, 2002 - 4:13 pm UTC
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.
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
Kim, March 07, 2002 - 6:04 pm UTC
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
Raj, June 28, 2002 - 11:56 am UTC
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
Alex, June 28, 2002 - 2:47 pm UTC
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.
Robert, June 28, 2002 - 3:00 pm UTC
Thanks
Raj, June 28, 2002 - 3:29 pm UTC
Thanks Tom for your reply.
How to pass arrya of number from oci to PL/sql
sam, November 12, 2002 - 4:29 am UTC
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?
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
sam, November 12, 2002 - 8:00 pm UTC
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
November 13, 2002 - 12:51 pm UTC
continued for above
sam, November 12, 2002 - 8:23 pm UTC
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
A reader, February 20, 2004 - 6:37 am UTC
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;
/
February 20, 2004 - 9:55 am UTC
yes
exec declare array; begin array(1) := 'x'; proc_a(array); end;
pass array then insert
A reader, March 11, 2005 - 6:24 am UTC
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
pass array then insert
A reader, March 11, 2005 - 6:55 am UTC
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
March 11, 2005 - 7:28 am UTC
yes, you just pass the array as a parameter.
pass array then insert
A reader, March 11, 2005 - 7:31 am UTC
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
A reader, March 11, 2005 - 8:10 am UTC
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
A reader, March 11, 2005 - 8:38 am UTC
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
Robert, March 11, 2005 - 10:31 am UTC
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;
A reader, March 13, 2005 - 4:20 pm UTC
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?
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
A reader, June 17, 2005 - 10:31 am UTC
passing procedure as a parameter in another procedure
Narasimha Rao A, June 24, 2005 - 5:19 am UTC
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...
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
T. Srinivasan, August 05, 2005 - 10:08 am UTC
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
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
Pran, November 08, 2005 - 9:22 am UTC
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 ?????????
Pran, November 15, 2005 - 5:21 am UTC
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
Brian Gregg, March 15, 2007 - 4:39 pm UTC
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.
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 :-(
A reader, March 19, 2007 - 11:49 am UTC
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!
A reader, March 19, 2007 - 1:13 pm UTC
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!
Brian Gregg, April 12, 2007 - 12:24 pm UTC
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.
April 13, 2007 - 12:14 pm UTC
ora-3113, ora-7445, ora-600 => please utilize support.
Ramchandar, May 30, 2007 - 2:02 pm UTC
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
Ramchandar, June 02, 2007 - 7:59 am UTC
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?
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
Piyush, July 24, 2007 - 8:14 am UTC
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
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
Piyush, July 26, 2007 - 1:27 am UTC
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.
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
Piyush, July 29, 2007 - 12:11 pm UTC
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.
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
Piyush, August 02, 2007 - 6:05 am UTC
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
Jen, August 14, 2007 - 11:28 am UTC
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.
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
veera, August 15, 2007 - 10:10 am UTC
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;
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
veera, August 21, 2007 - 8:58 am UTC
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;
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 -
....
Searcher, May 14, 2008 - 8:59 am UTC
Thanks for the solution. Example given is simply great.
How to pass pl/sql table to stored procedure in dynamic sql
Omer, June 19, 2008 - 2:08 am UTC
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.
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?
Reader from Chicago, September 12, 2008 - 1:02 pm UTC
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
September 16, 2008 - 1:49 pm UTC
yes, you can.
just do it, it works
ORA-00947
Ananth, December 15, 2008 - 6:08 am UTC
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...
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
srp, May 13, 2009 - 6:26 pm UTC
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
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
G.S.Reddy, January 13, 2011 - 6:25 am UTC
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
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
G.S.Reddy, January 14, 2011 - 5:41 am UTC
great tom, thanks a lot for that reply
multilevel collections
G.S.Reddy, May 17, 2011 - 8:33 am UTC
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
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
G.S.Reddy, May 19, 2011 - 1:53 am UTC
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
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
Kim Berg Hansen, May 19, 2011 - 5:41 am UTC
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 :-)
A reader, May 20, 2011 - 4:11 pm UTC
arrays in procedure
jyoshna, November 03, 2011 - 1:38 am UTC
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
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
A reader, November 22, 2011 - 8:45 pm UTC
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.
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
A reader, November 28, 2011 - 4:44 pm UTC
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.
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
A reader, November 28, 2011 - 4:45 pm UTC
Followup
A reader, November 29, 2011 - 11:51 am UTC
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.
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.