Re: Raw data/Stored Procedures

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1995/07/04
Message-ID: <3tblts$4p1_at_inet-nntp-gw-1.us.oracle.com>

aminabdu_at_paul.rutgers.edu (Amin Abdulghani) wrote:

>Hi,
>
>I am trying to write a small stored procedure which inserts binary
>data of type long raw into a table. The procedure is called by a
>PRO-C application. As I understand it one can insert raw data
>of size at most 32k using PL/SQL. My binary data is around 4k
>. My question is how should I declare my host variable(which
> contains the binary data) and how should I be passing the host
>variable to the stored proc (PL/SQL DECLARATION).
 

>Thanks..
>Amin

The following is a sqlplus session showing my database objects (table and stored procedure).

---

SQL> desc test_blob;

 Name                            Null?    Type
 ------------------------------- -------- ----
 X                                        LONG RAW

SQL> select text from user_source
  2 where name = 'TEST_BLOB_PROC'
  3 order by line;

TEXT

---

procedure test_blob_proc( xx in long raw ) as
begin
insert into test_blob values ( xx );
end;

SQL> exit

---

Next is some pro*c code that shows ONE WAY of declaring a host variable, using the stored procedure to insert it. It also commits the work in the database and retrieves the row it just inserted. It validates that the row retrieved matches (in a binary fashion) the row inserted (to prove it works).

Hope this helps.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government

• blob.pc =====================================

#include <stdio.h>
#include <string.h>
#include <ctype.h>
#include <assert.h>

#define vstrcpy( a, b ) \
(strcpy( a.arr, b ), a.len = strlen( a.arr ), a.arr)

static char * USERID = NULL;

#define SQLCA_INIT
EXEC SQL INCLUDE sqlca;

EXEC SQL BEGIN DECLARE SECTION;
int SQLCODE;
EXEC SQL END DECLARE SECTION; static void process_parms( argc, argv )

int	argc;
char *	argv[];
{
int	i;

	for( i = 1; i < argc; i++ )
	{
		if ( !strncmp( argv[i], "userid=", 7 ) ) USERID = argv[i]+7;
		else
		{
			printf( "usage: %s userid=xxx/xxx\n", argv[0] );
			exit(1);
		}
	}
	if ( USERID == NULL ) 
	{
		printf( "usage: %s userid=xxx/xxx\n", argv[0] );
		exit(1);
	}

}

static void sqlerror_hard()
{

    EXEC SQL WHENEVER SQLERROR CONTINUE;     printf("\nORACLE error detected:");
    printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

    EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);
}

static void process()
{

/*
 * This is the data structure we will use with our LONG RAWs.  We will
 * always use pointers to this structure as we don't know how big we

need

• until runtime. Will use malloc to allocate storage on the fly. */

typedef struct TAGmy_raw
{

	long			len;
	unsigned char	arr[1];
}
	my_raw;

/*
 * Use type equivalencing to tell Oracle that the C type "my_raw" is
 * equivalent to the Oracle type LONG VARRAW and can hold upto 5k
 * bytes of data (we will never allocate that much here, just an upper

• bound, could be MUCH higher */ EXEC SQL TYPE my_raw IS LONG VARRAW(5120) REFERENCE;

my_raw	* buffer;
long	size = 4096;
int		i;

	EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

	/*
	 * we will allocate a little over 4k of space
	 */
	buffer = (my_raw *)malloc( size+sizeof(my_raw) );

	/*
	 * fill it up with every possible byte value from 0..254
	 */
	for( i = 0; i < size; i++ )
		buffer->arr[i] = i % 255;

	/*
	 * Just like a varchar, set the length field
	 */
	buffer->len = size;

	printf( "TEST_BLOB_PROC\n" );
	EXEC SQL EXECUTE
	BEGIN
		TEST_BLOB_PROC( xx=>:buffer );
	END;
	END-EXEC;

	/*
	 * commit the changes
	 */
	printf("EXEC SQL COMMIT WORK;\n" );
	EXEC SQL COMMIT WORK;

	/*
	 * Now, ZERO out the buffer so when we fetch, we know that the buffer
	 * was NOT equal to what it was on the insert.  Just to *prove* that
	 * the fetch got back the data we inserted
	 */
	memset( buffer->arr, 0, size );

	/*
	 * Select out the data
	 */
	printf( " SELECT X INTO :buffer FROM TEST_BLOB;\n" );
	EXEC SQL SELECT X
			 INTO :buffer
			 FROM TEST_BLOB;

	/* 
	 * show what size we got 
	 */
	printf( "The Length = %ld\n", buffer->len );

	/*
	 * Check each character to make sure it is what we inserted
	 */
	for( i = 0; i < size; i++ )
		if ( buffer->arr[i] != i % 255 ) 
		{
			printf( "Error! %d != %d\n", buffer->arr[i], i%255 );
			break;
		}

	/*
	 * delete that table
	 */
	printf( "EXEC SQL DELETE FROM TEST_BLOB;\n" );
	EXEC SQL DELETE FROM TEST_BLOB;
	EXEC SQL COMMIT WORK;

}  

main( argc, argv )

int	argc;
char *	argv[];

{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;         process_parms( argc, argv );

	/* Connect to ORACLE. */
	vstrcpy( oracleid, USERID );

	EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

	EXEC SQL CONNECT :oracleid;
	printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);


	process();

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

}

• blob.pc =====================================

Received on Tue Jul 04 1995 - 00:00:00 CEST