Link to home
Start Free TrialLog in
Avatar of yashik
yashik

asked on

Passing host array to stored procedure from Pro*C

Hello,


   How can I pass an array of records to stored procedure? Is it possible?

   I'm trying to call stored procedure from PRO*C, passing array of integers.

I create a package and load it, using SQL*Plus.

CREATE OR REPLACE PACKAGE My_Test IS
TYPE NumTabTyp IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
PROCEDURE TestArray( data IN NumTabTyp );
END My_Test;

Package body:
...
PROCEDURE TestArray(data IN NumTabTyp )
IS
BEGIN
   UPDATE TEST_TABLE
   SET
   TEST_COL = data(1)
   WHERE
   ID = 6;
END;
...


In Pro*C program:
=================

EXEC SQL BEGIN DECLARE SECTION;
int      iArray[3];
...
EXEC SQL END DECLARE SECTION;
EXEC SQL CALL My_Test.TestArray( :iArray );


I get ORA-06553: PLS-306: wrong number or types of arguments in call


What I am doing wrong?
Thank you
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

I think it can be done, but I never have and Oracle certainly does not make it easy to do.  There have been other questions almost exactly like yours in the last few months.  If you browse the previously answered questions, you may find an answer.

It looks to me like your procedure attempts to set one column value of only one row (where ID=6) to the first value in the array.  If that is what you want, then using an array is complicating the issue.  If you really want to set the values for multiple rows to different values from an array, then you won't be able to have a where clause limited to one row.
Avatar of yashik
yashik

ASKER

It's just a test to see what value I get in  the first cell of the array. Of course, real procedure will use the whole array, not just the first value
ASKER CERTIFIED SOLUTION
Avatar of deepakgaur
deepakgaur

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of yashik

ASKER

Thank you for your help,
but unfortunatelly I'm doing smth wrong ( I'm a beginner, really )
After I set SQLCHECK=FULL
I get the following error
( My stored procedure is not embeded, but in the database. So, when I set SQLCHECK=FULL option I probably should declare it somehow in my Pro*c program, but I have no idea how to do that )
I realize, that it is probably very simple question, but after I spent 3.5 working days in order to get it working, without any result,I'd really appretiate your help.

Error at line 115, column 4 in file
\Work\Tests\test.pc                        My_Test.TestArray(:iArray);
....1
PLS-S-00201, identifier 'MY_TEST.TESTARRAY' must be declared
Error at line 115, column 4 in file \Work\Tests\test.pc                     My_Test.TestArray(:iArray);
....1
PLS-S-00000, Statement ignored
Semantic error at line 114, column 3, file \Work\Tests\test.pc                    
BEGIN
...1
PCC-S-02346, PL/SQL found semantic errors



You need not declare sqlcheck=full in your Pro *C program, but you should specify it in your Makefile
which compiles your Pro *C Program. That's a Pro *C compiler option wjhich allows compiler to let it know that it wants to excute a PL/SQL block.

Also include userid in your Makefile( or proc.mk file)
to get connected to the desired database.

Deepak

Avatar of yashik

ASKER

What I needed is that 'userid' option. I work with non-default databse.
Thank you very much!