Home » Developer & Programmer » Precompilers, OCI & OCCI » Passing array of structure to oracle procedure and returning back. (Oracle 10.2.0.3)
Passing array of structure to oracle procedure and returning back. [message #453591] Thu, 29 April 2010 15:29 Go to next message
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??
  • Attachment: sample.pc.txt
    (Size: 0.72KB, Downloaded 1589 times)
Re: Passing array of structure to oracle procedure and returning back. [message #454033 is a reply to message #453591] Mon, 03 May 2010 11:50 Go to previous messageGo to next message
mayurpuli
Messages: 1
Registered: May 2010
Junior Member
Hi, I have a similar requirement where i need to pass host arrays to a stored function and get a single host array as a return parameter.

I am also getting more or less the same errors you have mentioned in the post. Sad

Please let me know once you are able to crack it.

Thanks
--Mayur
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 Go to previous message
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
Previous Topic: Array bind - Strings
Next Topic: Using Cursor in ProCobol
Goto Forum:
  


Current Time: Thu Mar 28 13:46:34 CDT 2024