Table as Parameter

[neonomaly]

Hi,

I have this package in Oracle:

create or replace
PACKAGE UPTSTP AS
type ids is table of number;
procedure load_ac(i in ids);
END UPTSTP;

create or replace
PACKAGE BODY uptstp AS
procedure load_ac(i in ids) is
BEGIN
FORALL indx IN 1..cardinality(i)
INSERT INTO test (val) VALUES (i (indx));
END;
END uptstp;

In CBuilder:
sql->SQL:
begin
uptstp.load_ac(:x);
end;

How should I set parameter "x" correctly?

[AlexP]

Hello,

You cannot work with the type declared into a package from outside (it's an Oracle restriction).
You must create a “global” type and work with it in the following way:

Code: Select all

CREATE OR REPLACE TYPE IDS IS TABLE OF NUMBER;

create or replace 
PACKAGE UPTSTP AS 
procedure load_ac(i in ids); 
END UPTSTP; 

create or replace 
PACKAGE BODY uptstp AS 
procedure load_ac(i in ids) is 
BEGIN 
FORALL indx IN 1..cardinality(i) 
INSERT INTO test (val) VALUES (i (indx)); 
END; 
END uptstp; 

Code: Select all

TOraSQL *OraSQL = new TOraSQL(NULL);
 OraSQL->Session = OraSession1;
 OraSQL->SQL->Text = "begin uptstp.load_ac(:param); end;";
 OraSQL->ParamByName("param")->AsArray->AllocObject(OraSession1->OCISvcCtx, "ids");
 OraSQL->ParamByName("param")->AsArray->ItemAsInteger[0] = 1;
 OraSQL->ParamByName("param")->AsArray->ItemAsInteger[1] = 2;
 OraSQL->ParamByName("param")->AsArray->ItemAsInteger[2] = 3;
 OraSQL->ParamByName("param")->AsArray->ItemAsInteger[3] = 4;
 OraSQL->Execute();