Re: OCI and PL\SQL: Binding an input record as a parameter for a stored procedure

From: Mark Tomlinson <marktoml_at_gdi.net>
Date: Tue, 15 Sep 1998 14:03:15 GMT
Message-ID: <360272fe.61815686@newshost.us.oracle.com>

Here is an Oracle 7 OCI version, IN or OUT the same procedure applies, only the direction would be different:

----------------------------------------------------------------------------------:

Name: OCI Basic Array Binding to PL/SQL Table Sample Code Category: OCI
Port: WIN32
Description: A great simplification of the Oracle supplied CDEMO4. Uses obndra to bind an array of chars to a PL/SQL table. Note that it is not possible to bind PL/SQL tables of records, on PL/SQL tables of simple types like numbers or varchars.

---

/*

create or replace package test_pkg as
  type array is table of varchar2(20) index by binary_integer;   procedure test_proc(v_array out array); end;
/

create or replace package body test_pkg as   procedure test_proc(v_array out array) is     cursor c1 is select loc from dept;
  begin
    open c1;
    for i in 1..4 loop
      fetch c1 into v_array(i);
    end loop;
  end;
end;
/

*/

#include <stdio.h>
#include <memory.h>
#include <ociapr.h>

#pragma comment(lib, "d:\\orant\\oci73\\lib\\old\\ora73.lib")

#define VARCHAR2_TYPE 1
#define NUMBER_TYPE 2
#define INT_TYPE 3
#define FLOAT_TYPE 4
#define STRING_TYPE 5
#define ROWID_TYPE 11
#define DATE_TYPE 12

Lda_Def lda;
Cda_Def cda;
ub1 hda[256];

#define ELEMENTS 4
#define ELEMENT_LEN 20

text v_str[ELEMENTS][ELEMENT_LEN];
int a_size = ELEMENTS;

void main()
{

        memset(hda,0,255);

        if (olog(&lda,hda,(text*)
"scott/tiger_at_exampledb",-1,0,-1,0,-1,OCI_LM_DEF))

        printf("Logon failed: %i\n", lda.rc);

	if (oopen(&cda,&lda,0,-1,-1,0,-1))
	printf("Open failed: %i\n", lda.rc);

	if (oparse(&cda,(text*) "begin test_pkg.test_proc(:str);
end;",-1,1,2))
	printf("Parse failed: %i\n", cda.rc);

	if (obndra(&cda,(text*) ":str",-1,(text*)
v_str,20,STRING_TYPE,-1,
	0,0,0,(ELEMENTS * ELEMENT_LEN),&a_size,
	0,-1,-1))
	printf("Binding failed: %i\n", cda.rc);

	if (oexec(&cda))
	printf("Execute failed: %i\n", cda.rc);

	printf("%s\n", v_str[0]);
	printf("%s\n", v_str[1]);
	printf("%s\n", v_str[2]);
	printf("%s\n", v_str[3]);

	if (ocan(&cda))
	printf("Cancel failed: %i\n", cda.rc);

	if (oclose(&cda))
	printf("Close failed: %i\n", lda.rc);

	if (ologof(&lda))
	printf("Logoff failed: %i\n", lda.rc);

}

---

Received on Tue Sep 15 1998 - 09:03:15 CDT