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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
ASKER
What I needed is that 'userid' option. I work with non-default databse.
Thank you very much!
Thank you very much!
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.