Passing array of structure to oracle procedure and returning back. [message #453591] |
Thu, 29 April 2010 15:29 |
aabhapandey
Messages: 6 Registered: April 2010 Location: New York
|
Junior Member |
|
|
I created a table and some type definitions in oracle 10.2.0.3 DB as follows. There is also a procedure that I defined and is shown below.
SQL> create table test_proc
(
test_name varchar2(10),
test_ver number (3,3),
active_flag number(2)
);
Table created.
SQL> select * from test_proc;
TEST_NAME TEST_VER ACTIVE_FLAG
---------- ---------- -----------
AFP 1.5 2
PSA 11.89 0
EHIV 99.5 3
aHAVM 1.45 9
HbcT 1.567 10
Create or replace type obj_tdefs as object
(
test_name varchar2(10),
test_ver number (3,3),
active_flag number(2)
);
Create or replace type tab_obj_tdefs is table of obj_tdefs;
Create or replace procedure proc_modify_tdefs
(mytdefs in tab_obj_tdefs, O_tdefs out tab_obj_tdefs)
As
Begin
For cnt in mytdefs.first .. mytdefs.last
Loop
O_tdefs(cnt).test_name := mytdefs(cnt).test_name;
O_tdefs(cnt).test_ver := mytdefs(cnt).test_ver;
O_tdefs(cnt).active_flag := mytdefs(cnt).active_flag;
End loop;
Exception
When others then
Dbms_output.put_line( 'Error code: ' || SQLCODE || ' Error msg' ||SQLERRM);
End;
Now from Pro*C function I want to pass a similar array of structure to this procedure and return it via the out parameter of the procedure back to Pro*C. How do I do it?
I am using the attached program but its giving me compiler error as follows..
Error at line 31, column 1 in file sample.pc
proc_modify_tdefs (:in_tdefs,:out_tdefs);
1
PLS-S-00306, wrong number or types of arguments in call to 'PROC_MODIFY_TDEFS'
Error at line 31, column 1 in file sample.pc
proc_modify_tdefs (:in_tdefs,:out_tdefs);
1
PLS-S-00306, wrong number or types of arguments in call to 'PROC_MODIFY_TDEFS'
Error at line 31, column 1 in file sample.pc
proc_modify_tdefs (:in_tdefs,:out_tdefs);
1
PLS-S-00000, Statement ignored
Semantic error at line 30, column 1, file sample.pc:
BEGIN
1
PCC-S-02346, PL/SQL found semantic errors
*** Error code 1
make: Fatal error: Command failed for target `sample.c'
Any advise??
|
|
|
|
Re: Passing array of structure to oracle procedure and returning back. [message #477975 is a reply to message #454033] |
Tue, 05 October 2010 08:02 |
clancypc
Messages: 36 Registered: December 2006
|
Member |
|
|
From what I am reading you cannot pass in an array of structures. You can pass in arrays in structures so if you had a structure definition of:
typedef struct st_tdef_r
{
char test_name[10][10];
float test_ver[10];
short active_flag[10];
} st_tdef;
and then had the declarations as:
st_tdef in_tdefs;
st_tdef out_tdefs;
This might work. But dont quote me on that because I am just learning this stuff myself.
Hope it helps.
Peter
|
|
|