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
LVL 2
yashikAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
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.
0
yashikAuthor Commented:
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
0
deepakgaurCommented:
Hi yashik,

  It's 100% possible  to get Host arrays passed to
PL/SQL stored Procedure.
   I just tried your Scenario and it worked the same
way you want, the only think I used   in my Pro *C
code I just call the procedure as follows.

EXEC SQL EXECUTE
    BEGIN
         My_Test.TestArray(:iarray);
    END;
END-EXEC;

And it's working fine. Also make sure that your
Pro *C Compiler option is set to SQLCHECK=FULL to compile PL/SQL blocks.

This is Tested solution.

Rgds
Deepak



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

yashikAuthor Commented:
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



0
deepakgaurCommented:
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

0
yashikAuthor Commented:
What I needed is that 'userid' option. I work with non-default databse.
Thank you very much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.