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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
deepakgaurConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
All Courses

From novice to tech pro — start learning today.