Host Arrays and Structs in Stored Procedures

#include
#include
#include
#include
#include
#include

#DEFINE SQL_LEN 300
#DEFINE REC_LEN 30
#DEFINE MAX(A,B) ((A) > (B) ? (A) : (B))

EXEC SQL BEGIN DECLARE SECTION;
struct record {
VARCHAR npa[3];
VARCHAR npa[3];
...
}

typedef struct record recorddata;
VARCHAR host_string[SQL_LEN];
EXEC SQL END DECLARE SECTION;

strcpy((char*)host_string.arr, "BEGIN ICVC.SP_LOAD_TTC (:npa, :npx, ...); END;");
host_string.len = strlen((char*)host_string.arr);

void main()
{
insertdata();
}
void insertdata()
{
int i, cnt;
char *str;
recorddata rec_in[REC_LEN];

str = (char *)malloc (25 * sizeof(char));
for (i = 0; i < REC_LEN; i++)
{
sprintf(str, "001");
strcpy (rec_in[i].npx.arr, str);
rec_in[i].npx.len = strlen (rec_in[i].npx.arr);
rec_in[i].npx.arr[rec_in[i].npx.len] = '\0';

sprintf(str, "001");
strcpy (rec_in[i].npa.arr, str);
rec_in[i].npa.len = strlen (rec_in[i].npa.arr);
rec_in[i].npa.arr[rec_in[i].npa.len] = '\0';

...

}
free (str);
/*
EXEC SQL EXECUTE
BEGIN
ICVC.SP_LOAD_TTC (:rec_in,, :ret_code);
END;
END-EXEC;

OR

EXEC SQL ARRAYLEN int_tab (REC_LEN) EXECUTE;
char *stmt3 = "begin pkg.proc1(:v1, :v2, :v3); end; ";
EXEC SQL PREPARE s3 FROM :host_string;
EXEC SQL EXECUTE s3 USING :rec_in, :REC_LEN, :rec_in;

OR
*/




EXEC SQL CALL ICVC.SP_LOAD_TTC (:rec_in, :status :ret_code);
if(status==0){

}



==============================================================
Default Value
char v_location[12] = {'\0'};

CREATE OR REPLACE PACKAGE pkg AS
TYPE tab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;
PROCEDURE proc1 (parm1 tab, parm2 NUMBER, parm3 tab);
END;

The following Pro*C/C++ function demonstrates how host arrays can be used to determine how many times a given PL/SQL block is executed. In this case, the PL/SQL block will be executed 3 times resulting in 3 new rows in the emp table.

func1()
{
int empno_arr[5] = {1111, 2222, 3333, 4444, 5555};
char *ename_arr[3] = {"MICKEY", "MINNIE", "GOOFY"};
char *stmt1 = "BEGIN INSERT INTO emp(empno, ename) VALUES :b1, :b2; END;";

EXEC SQL PREPARE s1 FROM :stmt1;
EXEC SQL EXECUTE s1 USING :empno_arr, :ename_arr;
}

The following Pro*C/C++ function demonstrates how to bind a host array to a PL/SQL index table through dynamic method 2. Note the presence of the ARRAYLEN...EXECUTE statement for all host arrays specified in the EXEC SQL EXECUTE statement.

func2()
{
int ii = 2;
int int_tab[3] = {1,2,3};
int dim = 3;
EXEC SQL ARRAYLEN int_tab (dim) EXECUTE;

char *stmt2 = "begin pkg.proc1(:v1, :v2, :v3); end; ";

EXEC SQL PREPARE s2 FROM :stmt2;
EXEC SQL EXECUTE s2 USING :int_tab, :ii, :int_tab;
}

However the following Pro*C/C++ function will result in a precompile-time warning because there is no ARRAYLEN...EXECUTE statement for int_arr.

func3()
{
int int_arr[3];
int int_tab[3] = {1,2,3};
int dim = 3;
EXEC SQL ARRAYLEN int_tab (dim) EXECUTE;

char *stmt3 = "begin pkg.proc1(:v1, :v2, :v3); end; ";

EXEC SQL PREPARE s3 FROM :stmt3;
EXEC SQL EXECUTE s3 USING :int_tab, :int_arr, :int_tab;
}