PRO*C & PL/SQL Stored procedure

[David BARATTE]

Hello,

Is it possible to pass a host array to a PL/SQL stored procedure as a
parameter ?
Remark : the call to the stored procedure is made from a PRO*C function.

Of what type should be the IN parameters of the stored procedure ?

Thanks in
advance.

[Karl R.]

Hello David
Here an example, but you can see that too in the Oracle-Doc
Bye!

EXEC SQL BEGIN DECLARE SECTION;
int HostDimId [SQL_ROW_SET_SIZE];
int HostZeitId [SQL_ROW_SET_SIZE];
int HostModellId[SQL_ROW_SET_SIZE];
int HostAgentId[SQL_ROW_SET_SIZE];

int HostExistingDimId [SQL_ROW_SET_SIZE];

int dimension;
EXEC SQL END DECLARE SECTION;

dimension = HowMany;

// Das ist klasse! Auch weniger Sätze als
SQL_ROW_SET_SIZE können bearbeitet werden
EXEC SQL ARRAYLEN HostDimId (dimension);
EXEC SQL ARRAYLEN HostZeitId (dimension);
EXEC SQL ARRAYLEN HostModellId (dimension);
EXEC SQL ARRAYLEN HostAgentId (dimension);
EXEC SQL ARRAYLEN HostExistingDimId (dimension);

try {

//KRE.MOD.TUNING : HostVar's füllen
for ( iRow = 0; iRow < dimension; iRow ++ ) {
HostDimId[iRow] = DimId[iRow];
HostZeitId[iRow] = ZeitId[iRow];
HostModellId[iRow] = ModellId[iRow];
HostAgentId[iRow] = AgentId[iRow];
}

/** KRE.TUNING Mit PLSQL-BLOCK
um mehr Logik reinstecken zu
können, die notwendig ist um revd korrekt zu verarbeiten
Tritt in REVD ein Dup-key auf
dann muss die REVID selektiert werden
und im Array HostExistingDimId
zurückgegeben werden
für den ArrayInsert auf REVV u.
REVF
*/
EXEC SQL EXECUTE
DECLARE
TYPE HostDimIdTab IS TABLE OF INTEGER
INDEX BY BINARY_INTEGER;
TYPE HostZeitIdTab IS TABLE OF INTEGER
INDEX BY BINARY_INTEGER;
TYPE HostModellIdTab IS TABLE OF INTEGER
INDEX BY BINARY_INTEGER;
TYPE HostExistingDimId IS TABLE OF
INTEGER INDEX BY BINARY_INTEGER;
TYPE HostAgentIdTab IS TABLE OF INTEGER
INDEX BY BINARY_INTEGER;

-- Procedure zum Insert in die REVD-
Table, ist das TRIPEL (FK_REVM,FK_REVZ,FK_AGEN)
-- schon vorhanden dann wird die
zugehörige REVID über das ARRAY HostNewDimId zurückgegeben
PROCEDURE PINSERT_REVD (
itHostDimIdTab IN
HostDimIdTab,
itHostZeitIdTab IN
HostZeitIdTab,
itHostModellIdTab IN
HostModellIdTab,
itHostAgentIdTab IN
HostAgentIdTab,
itHostExistingDimId OUT
HostExistingDimId,
MaxRow IN BINARY_INTEGER
) IS
nRow BINARY_INTEGER;
LReturnExistingID BOOLEAN;
BEGIN

FOR nRow IN 1..MaxRow LOOP
BEGIN

LReturnExistingID := FALSE;
INSERT INTO revd
(
id,
fk_revm,
fk_revz,
fk_agen

)
VALUES (

itHostDimIdTab(nRow),

itHostModellIdTab(nRow),

itHostZeitIdTab(nRow),

itHostAgentIdTab(nRow)
);

EXCEPTION
WHEN
DUP_VAL_ON_INDEX THEN

LReturnExistingID := TRUE;
END;

IF LReturnExistingID THEN
SELECT
id
INTO

itHostExistingDimId( nRow )
FROM
REVD
WHERE
fk_revz
= itHostZeitIdTab(nRow) AND
fk_revm
= itHostModellIdTab(nRow) AND
fk_agen
= itHostAgentIdTab(nRow)
;
ELSE

itHostExistingDimId( nRow ) := 0;
END IF;

END LOOP;
END;
BEGIN -- Jetzt gehts endlich los!!

PINSERT_REVD(
:HostDimId,
:HostZeitId,
:HostModellId,
:HostAgentId,
:HostExistingDimId,
:dimension
);

END; -- Ende der Action
END-EXEC;

Karl Reitschuster
Senior Consultant CSC Ploenzke AG
Oracle Databases, Implementation, Performance-Tuning
<!Jesus is Lord!>
* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!

[Deepak Kumar]

David BARATTE wrote:

Hi David,

It is possible to pass host array to a PL/SQL stored procedure from a
Pro *C
program. The procedure must be accepting parameter as PL/SQL Tables.
If you are passingMulti Dimensional Array, the PL/SQL table should be
created
based on the RECORD data type to accept this Array.

Thanks
Deepak

[asok...@my-deja.com]

I was wondering if someone noticed a strange behaviour of Pro*C apps
that pass a number of PL/SQL table types as parameters for stored
procedures in Oracle 8i R2 (8.1.6)?
The app was running just fine on Oracle 7, but developers tried to
migrate to Oracle 8.1.6 , compiled successfully their packages in 8.1.6,
but get an ORA error at run time - parameter too long (don't remember
the error number). App passes in excess of 50 table types, but it was
not a problem pre-8.1.6.

Any ideas?

Thanks, Alex Sokol

Sent via Deja.com http://www.deja.com/
Before you buy.