PRO*C에서 PLSQL PROCEDURE에 HOST ARRAY BINDING하는 방법

PRO*C에서 PLSQL PROCEDURE에 HOST ARRAY BINDING하는 방법
=======================================================

proc*c안에서 PL/SQL stored procedures로 host arrays를 넘기는
방법을 알아봅니다.

sample program 실행하는 방법.

1. 먼저 pl/sql package를 만듭니다.
sqlplus scott/tiger @pkg.sql

2. 만든 package를 test해 봅니다.
sqlplus scott/tiger @harness.sql

3. testit.pc를 compile합니다.
make -f $ORACLE_HOME/precomp/demo/proc/demo_proc.mk build EXE=testit OBJS=testit.o PROCFLAGS="SQLCHECK=full USERID=scott/tiger"

Program
-------

pkg.sql - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -
rem pkg.sql follows...
create or replace package my_package as
type charTabTyp is table of char(15) index by binary_integer;
type numTabTyp is table of number index by binary_integer;
procedure test_plsql_table_parameters (t1 in out charTabTyp,
t2 in out numTabTyp);
end my_package;
/
show errors
create or replace package body my_package as
procedure test_plsql_table_parameters (t1 in out charTabTyp,
t2 in out numTabTyp) is
begin
for i in 1..t1.count loop
t1(i):= 'NEW '||i;
end loop;
for i in 1..t2.count loop
t2(i):= i*100;
end loop;
end;
end my_package;
/
show errors
quit

pkg.sql- - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -

harness.sql - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -
rem harness.sql follows...
set serverout on
declare
v1 my_package.charTabTyp;
v2 my_package.numTabTyp;
begin
for i in 1..5 loop
v1(i):=i||' '||sysdate;
v2(i):=i*10;
end loop;
dbms_output.put_line('Before...');
dbms_output.put_line('Num rows in v1 is '||v1.count);
for i in 1..v1.count loop
dbms_output.put_line(v1(i));
end loop;
dbms_output.put_line('Num rows in v2 is '||v2.count);
for i in 1..v2.count loop
dbms_output.put_line(v2(i));
end loop;
my_package.test_plsql_table_parameters(v1, v2);
dbms_output.put_line('AFTER...');
dbms_output.put_line('Num rows in v1 is '||v1.count);
for i in 1..v1.count loop
dbms_output.put_line(v1(i));
end loop;
dbms_output.put_line('Num rows in v2 is '||v2.count);
for i in 1..v2.count loop
dbms_output.put_line(v2(i));
end loop;
end;
/
quit
harness.sql - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -

testit.pc - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -
/* testit.pc starts here... */

#include <stdio.h>
#include <string.h>
#include <sqlca.h>

void sql_error();

/* as the parameter is a PL/SQL in out we need to reserve a space for the
terminator. */
typedef char mystring[16];
EXEC SQL TYPE mystring is CHARZ(16);

#define SIZE 20
main()
{
mystring char_array[SIZE];
int num_array[SIZE];
int i,j;
char *connstr = "scott/tiger";

/* Register sql_error() as the error handler. */
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");

/* Connect to ORACLE. */
EXEC SQL CONNECT :connstr;

printf("\nConnected to ORACLE as user: %s\n", connstr);

printf("Populating the arrays to be inserted...\n");
for (i=0; i<SIZE; i++)
{
num_array = i*10;
strcpy(char_array," Hello");
printf("\tElement %d of num_array is %d\n", i, num_array);
printf("\tElement %d of char_array is %s\n", i, char_array);
}
EXEC SQL EXECUTE
begin
my_package.test_plsql_table_parameters(:char_array, :num_array);
end;
END-EXEC;

printf("After call to stored procedure the contents looks like...\n");
for (i=0; i<SIZE; i++)
{
printf("\tElement %d of num_array is %d\n", i, num_array);
printf("\tElement %d of char_array is %s\n", i, char_array);
}
printf("\nG'day.\n\n\n");

/* Disconnect from ORACLE. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}

void
sql_error(msg)
char *msg;
{
char err_msg[128];
int buf_len, msg_len;

EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("\n%s\n", msg);
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);

EXEC SQL ROLLBACK RELEASE;
exit(1);
}

testit.pc - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -

Sample Output
-------------

Connected to ORACLE as user: scott/tiger
Populating the arrays to be inserted...
Element 0 of num_array is 0
Element 0 of char_array is Hello
Element 1 of num_array is 10
Element 1 of char_array is Hello
Element 2 of num_array is 20
Element 2 of char_array is Hello
Element 3 of num_array is 30
Element 3 of char_array is Hello
Element 4 of num_array is 40
Element 4 of char_array is Hello
Element 5 of num_array is 50
Element 5 of char_array is Hello
Element 6 of num_array is 60
Element 6 of char_array is Hello
Element 7 of num_array is 70
Element 7 of char_array is Hello
Element 8 of num_array is 80
Element 8 of char_array is Hello
Element 9 of num_array is 90
Element 9 of char_array is Hello
Element 10 of num_array is 100
Element 10 of char_array is Hello
Element 11 of num_array is 110
Element 11 of char_array is Hello
Element 12 of num_array is 120
Element 12 of char_array is Hello
Element 13 of num_array is 130
Element 13 of char_array is Hello
Element 14 of num_array is 140
Element 14 of char_array is Hello
Element 15 of num_array is 150
Element 15 of char_array is Hello
Element 16 of num_array is 160
Element 16 of char_array is Hello
Element 17 of num_array is 170
Element 17 of char_array is Hello
Element 18 of num_array is 180
Element 18 of char_array is Hello
Element 19 of num_array is 190
Element 19 of char_array is Hello
After call to stored procedure the contents looks like...
Element 0 of num_array is 100
Element 0 of char_array is NEW 1
Element 1 of num_array is 200
Element 1 of char_array is NEW 2
Element 2 of num_array is 300
Element 2 of char_array is NEW 3
Element 3 of num_array is 400
Element 3 of char_array is NEW 4
Element 4 of num_array is 500
Element 4 of char_array is NEW 5
Element 5 of num_array is 600
Element 5 of char_array is NEW 6
Element 6 of num_array is 700
Element 6 of char_array is NEW 7
Element 7 of num_array is 800
Element 7 of char_array is NEW 8
Element 8 of num_array is 900
Element 8 of char_array is NEW 9
Element 9 of num_array is 1000
Element 9 of char_array is NEW 10
Element 10 of num_array is 1100
Element 10 of char_array is NEW 11
Element 11 of num_array is 1200
Element 11 of char_array is NEW 12
Element 12 of num_array is 1300
Element 12 of char_array is NEW 13
Element 13 of num_array is 1400
Element 13 of char_array is NEW 14
Element 14 of num_array is 1500
Element 14 of char_array is NEW 15
Element 15 of num_array is 1600
Element 15 of char_array is NEW 16
Element 16 of num_array is 1700
Element 16 of char_array is NEW 17
Element 17 of num_array is 1800
Element 17 of char_array is NEW 18
Element 18 of num_array is 1900
Element 18 of char_array is NEW 19
Element 19 of num_array is 2000
Element 19 of char_array is NEW 20

G'day.