|
|
第二篇， 使用c:
Doc ID: Note:1007848.6
Subject: HOW TO SELECT THE FULL 2147483647 BYTES IN A LONG OR LONG RAW COLUMN IN PLSQL?
Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 24-FEB-1995
Last Revision Date: 02-MAR-1995
Problem Description:
====================
How can I select and insert the full 2147483647 bytes in a long or long raw
column in PL/SQL?
Solution: PL/SQL IS LIMITED TO 32760 BYTES - USE THE ORACLE PRECOMPILERS INSTEAD
Solution Description:
====================
Although you can insert a long or long raw value into a long or long raw
database column respectively, you cannot select a value longer than
32760 bytes from a long or long raw column into a long or long raw variable.
You can manipulate the full 2147483647 bytes in a precompiler program
with regular embedded SQL by using datatype equivalencing to declare
a host variable of type long varchar, long varraw, or long raw.
Solution Explanation:
====================
For users of Pro*C V2.0 or higher, see sample4.pc. It is available in the
manual and on-line. For users of the Oracle Precompilers V1.x, the
following is an example of how to do this in Pro*C.
[php]
/* manip.pc
This program inserts and retrieves binary executables into the database
using the long varraw structure.
Usage:
manip -c: creates the table
manip -i <key> <file>: inserts <file> into the table with key <key>
manip -r <key> <file>: writes the contents of the table at key <key> to
<file>
manip -R <key> <file>: same as -r, but using dynamic method 4.
manip -I <key> <file>: same as -i, but using dynamic method 4.
*/
#define _HPUX_SOURCE /* This is necessary for HP platforms */
#define _POSIX_SOURCE /* This is necessary for AIX platforms */
#include <stdio.h>
#include <sys/file.h>
#include <string.h>
#include <fcntl.h>
EXEC SQL include sqlca;
EXEC SQL include sqlda;
#define TOTALSIZE 5000000
typedef struct {
long len;
char buf[TOTALSIZE];
} longvarraw;
typedef struct {
long len;
char *buf;
} lvr_type;
EXEC SQL begin declare section;
EXEC SQL type longvarraw is long varraw (5000000);
EXEC SQL end declare section;
void sqlerror();
/* Reads file 'filename' into buf of size bufsize,
returns total length or -1 if error */
#define LOCALSIZE 512
int read_file(filename, buf, bufsize)
char *filename, *buf;
long bufsize; {
char local_buffer[LOCALSIZE];
int n;
int total_size = 0;
int in_fd;
in_fd = open(filename, O_RDONLY, 0);
if (in_fd == -1)
return(-1);
while ((n = read(in_fd, local_buffer, LOCALSIZE)) > 0) {
if (total_size + n > bufsize) {
/* Out of space */
close(in_fd);
return(-1);
}
memcpy(buf+total_size, local_buffer, n);
total_size += n;
}
close(in_fd);
return(total_size);
}
/* Writes contents of buf, size bufsize, to filename.
Returns -1 if error. */
int write_file(filename, buf, bufsize)
char *filename, *buf;
long bufsize; {
int out_fd;
int num_written;
printf("write_file: filename '%s'\n", filename);
printf("bufsize: %d\n", bufsize);
printf("buf: %d\n", buf);
out_fd = creat(filename, 0755);
if (out_fd == -1) {
printf("creat error.\n" ;
return(-1);
}
num_written = write(out_fd, buf, bufsize);
close(out_fd);
return(num_written);
}
/* Connect to the database */
void connect() {
EXEC SQL begin declare section;
char *uid = "scott/tiger";
EXEC SQL end declare section;
EXEC SQL whenever sqlerror do sqlerror();
EXEC SQL connect :uid;
printf("Connected.\n";
}
void create_table() {
connect();
/* We don't care about errors on this statement */
EXEC SQL whenever sqlerror continue;
EXEC SQL drop table executables;
/* Reset error handler */
EXEC SQL whenever sqlerror do sqlerror();
EXEC SQL create table executables
(name varchar2(20),
binary long raw);
printf("Table created.\n";
}
void sqlerror() {
EXEC SQL whenever sqlerror continue;
printf("\nOracle Error!\n";
printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
EXEC SQL rollback work release;
exit(1);
}
/* Inserts the binary file identified by file into the executables table
identified by key. */
int insert(key, file)
EXEC SQL begin declare section;
char *key, *file;
EXEC SQL end declare section; {
EXEC SQL begin declare section;
longvarraw lvr;
EXEC SQL end declare section;
lvr.len = read_file(file, lvr.buf, TOTALSIZE);
printf("length: %d\n", lvr.len);
if (lvr.len == -1)
exit(fprintf(stderr, "error while reading\n");
connect();
EXEC SQL whenever sqlerror do sqlerror();
EXEC SQL insert into executables values (:key, :lvr);
EXEC SQL commit;
printf("Inserted.\n";
}
int insert4(key, file)
EXEC SQL begin declare section;
char *key, *file;
EXEC SQL end declare section; {
EXEC SQL begin declare section;
char *sqlstatement = "insert into executables values (:key, :ll)";
EXEC SQL end declare section;
SQLDA *bind_dp;
short key_ind = 0, executable_ind = 0;
char *ll;
long length;
char *buf_ptr;
ll = (char *)malloc(TOTALSIZE+4);
buf_ptr = ll + 4;
length = read_file(file, buf_ptr, TOTALSIZE);
printf("length: %d\n", length);
if (length == -1)
exit(fprintf(stderr, "error while reading\n");
memcpy(ll, &length, 4);
connect();
printf("Connected.\n";
bind_dp = (SQLDA *)sqlald(1, 20, 20);
bind_dp->N = 2;
EXEC SQL prepare S from :sqlstatement;
EXEC SQL describe bind variables for S into bind_dp;
bind_dp->L[0] = strlen(key);
bind_dp->T[0] = 1;
bind_dp->V[0] = key;
bind_dp->I[0] = &key_ind;
bind_dp->L[1] = TOTALSIZE + 4;
bind_dp->T[1] = 95;
bind_dp->V[1] = ll;
bind_dp->I[1] = &executable_ind;
printf("bound.\n";
EXEC SQL execute S using descriptor bind_dp;
printf("Executed.\n";
sqlclu(bind_dp);
EXEC SQL commit;
printf("Inserted.\n");
}
/* Retrieves the executable identified by key into file, using
dynamic method 4 */
int retrieve4(key, file)
EXEC SQL begin declare section;
char *key, *file;
EXEC SQL end declare section; {
EXEC SQL begin declare section;
char *sqlstatement = "select binary from executables where name = :key";
EXEC SQL end declare section;
SQLDA *select_dp;
SQLDA *bind_dp;
int num_written;
short key_ind = 0, executable_ind;
char *ll;
long length;
char *buf_ptr;
ll = (char *)malloc(TOTALSIZE+4);
connect();
printf("Selecting...\n");
select_dp = (SQLDA *)sqlald(1, 20, 20);
bind_dp = (SQLDA *)sqlald(1, 20, 20);
select_dp->N = 1;
bind_dp->N = 1;
EXEC SQL prepare S from :sqlstatement;
EXEC SQL declare C cursor for S;
EXEC SQL describe bind variables for S into bind_dp;
bind_dp->L[0] = strlen(key);
bind_dp->T[0] = 1;
bind_dp->V[0] = key;
bind_dp->I[0] = &key_ind;
printf("bound.\n");
EXEC SQL open C using descriptor bind_dp;
printf("opened\n");
EXEC SQL describe select list for S into select_dp;
select_dp->L[0] = TOTALSIZE + 4;
select_dp->V[0] = (char *)ll;
select_dp->T[0] = 95;
select_dp->I[0] = &executable_ind;
printf("described select list.\n");
EXEC SQL fetch C using descriptor select_dp;
printf("selected.\n");
memcpy(&length, ll, 4);
buf_ptr = ll + 4;
printf("length: %d\n", length);
num_written = write_file(file, buf_ptr, length);
printf("num_written: %d\n", num_written);
if (num_written != length)
exit(fprintf(stderr, "error while writing\n"));
EXEC SQL close C;
sqlclu(select_dp);
sqlclu(bind_dp);
}
/* Retrieves the executable identified by key into file */
int retrieve(key, file)
EXEC SQL begin declare section;
char *key, *file;
EXEC SQL end declare section; {
EXEC SQL begin declare section;
longvarraw lvr;
EXEC SQL end declare section;
int num_written;
connect();
printf("Selecting...\n");
EXEC SQL select binary
into :lvr
from executables
where name = :key;
printf("selected.\n");
printf("length: %d\n", lvr.len);
num_written = write_file(file, lvr.buf, lvr.len);
if (num_written != lvr.len)
exit(fprintf(stderr, "error while writing\n"));
}
main(argc, argv)
int argc;
char **argv; {
char all[TOTALSIZE];
int num_written;
if (--argc == 1 && !strcmp(argv[1], "-c"))
create_table();
else if (argc == 3) {
if (!strcmp(argv[1], "-i"))
insert(argv[2], argv[3]);
else if (!strcmp(argv[1], "-I"))
insert4(argv[2], argv[3]);
else if (!strcmp(argv[1], "-r"))
retrieve(argv[2], argv[3]);
else if (!strcmp(argv[1], "-R"))
retrieve4(argv[2], argv[3]);
else
printf("Usage error.\n");
}
else
printf("Usage error.\n");
}
.
[/php]
--------------------------------------------------------------------------------
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use. |
|
楼主: xiao
IP卡
狗仔卡
发表于 2002-4-26 16:34
显身卡
楼主
发表于 2002-4-28 14:50
racle:thin:@msltstsv.us.oracle.com:1522:ORCL", "scott", "tiger"