Oracle Database SQL*Unloader
Date: 3 Jul 92 00:52:20 GMT
Message-ID: <1992Jul3.005220.257_at_oracle.us.oracle.com>
This note contains files necessary for building a simple Oracle database unloader. The program prompts for username and password, and builds an unloaded data file based on any given SQL statement. The program can also constuct a SQL*Loader control file for reloading the data. This allows Oracle data to be flexibly unloaded from the database, where it can easily be loaded by other Oracle's, or archived etc...
The unloader is based on one of the sample pro*C programs, with a few enhancements. It requires PRO*C to be precompiled, compiled, and linked. Please note this is an undocumented, unsupported utility I have provided solely for your convenience. I have tested it on semi-large tables and found no problems. Please take care before using this utility in production applications.
I encourage you hacker types out there to enhance and repost this. I'd love to see a version that unloads object definitions, constraints, user grants, etc into .sql scripts as well.
This note should be split into three files called:
unloader.pc (The C precompiler source for the unloader program.)
proc.mk (The make file to build the unloader program on
unix systems. Type make -f proc.mk unloader
to build the unloader utility. )
make_unloader.com (A VMS com file to build the unloader utility.
Type _at_make_unloader to invoke it. )
On both VMS and unix, you must first be pointing to an Oracle database, and have the Oracle Pro*C precompiler installed for this to work. Note there is an IFDEF for MS-DOS if you happen to be running 16 bit.
- cut here for unloader.pc ---------------------------
/* Copyright (c) 1990 by Oracle Corporation */
/* NAME
unloader.pc
FUNCTION
Oracle C Precompiler unload program to spool a table to a flat file
RETURNS
NOTES
MODIFIED
Dahbour 07/22/91 - add enhancement for longs and promt users for input
instead of command line parameters. Dahbour 07/10/91 - add modifications from sample9.pc to unloader.pc Osborne 12/21/90 - Replacing with new version */
/***********************************************************************Sample Program 9: Dynamic SQL Method 4
This program shows the basic steps required to use dynamic SQL with Method 4. After logging on to ORACLE, the program allocates memory for the descriptors using sqlald(), prompts the user for a SQL statement, PREPAREs the statement, DECLAREs a cursor, checks for any bind variables using DESCRIBE BIND, OPENs the cursor, and DESCRIBEs any select-list items. If the input SQL statement is a query, the program FETCHes each row of data, then CLOSEs the cursor.
Limitations :
With DATE columns, only day,month and year will be selected. Minutes, seconds and hours will not be saved.
LONG RAW and RAW column are not handled properly in sqlloader 1.0.
***********************************************************************/
#include <stdio.h>
#include <ctype.h>
#ifndef MSDOS
extern char *malloc();
#else
extern unsigned char *malloc();
#endif
char *nullstring=" ";
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[21];
VARCHAR password[21];
VARCHAR sql_statement[240];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca.h;
EXEC SQL INCLUDE sqlda.h;
char ldr; /* y/n to generate loader control file */ unsigned short recsiz; /* the record size */
int max_col_num; long len_c; long max_long=80;
char error_file_name[50];
char out_file_name[50];
char ctl_file_name[50];
char table_name[50];
int ind_num; /* the index key */ char *prev_index_col; /* previous key */short *indicator;
FILE *err_fp; FILE *out_fp; FILE *ctl_fp;
int i;
#define SQLCODE (sqlca.sqlcode)
/* Declare the bind and select descriptors. */ SQLDA *select_dp;
extern SQLDA *sqlald();
extern void sqlprc();
int precision, scale;
extern void sqlnul();
int null_ok;
int sl;
char *buf,*xbuf;
main ()
{
prompts();
/* Log on to ORACLE. */
EXEC SQL CONNECT :username ;
if (SQLCODE) sqlerror();
puts("\nConnected to ORACLE.");
select_dp = sqlald (max_col_num, 30, 30); select_dp->N = max_col_num; /* Initialize count of array elements. */
/* Prepare the statement and declare a cursor. */
EXEC SQL PREPARE S FROM :sql_statement;
if (SQLCODE) sqlerror();
EXEC SQL DECLARE C CURSOR FOR S;
printf (" spooling to file %s. Working ...",out_file_name);
EXEC SQL OPEN C ;
if (SQLCODE) sqlerror();
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
if (SQLCODE) sqlerror();
if (select_dp->F < 0)
{
printf ("\nToo many select-list items for: %d",
-(select_dp->F));
sqlerror();
}
/* Set the maximum number of array elements in the
descriptor to the number found. */
select_dp->N = select_dp->F;
/* Allocate storage for each select-list item. */
for (i=0; i<select_dp->F; i++)
{
/* Turn off high-order bit of datatype (in this example,
it does not matter if the column is NOT NULL). */
sqlnul (&(select_dp->T[i]), &(select_dp->T[i]), &null_ok);
switch (select_dp->T[i])
{
case 1 : /* CHAR datatype: no change in length
needed, except possibly for TO_CHAR
conversions. */
break;
case 2 : /* NUMBER datatype: use sqlprc() to
extract precision and scale. */
sqlprc (&(select_dp->L[i]), &precision,
&scale);
/* Allow for maximum size of NUMBER. */
if (precision == 0) precision = 40;
/* Also allow for decimal point and
possible sign. */
select_dp->L[i] = precision + 2;
/* Allow for a negative scale. */
if (scale < 0)
select_dp->L[i] += -scale;
break;
case 8 : /* LONG datatype */
select_dp->L[i] = max_long;
break;
case 11 : /* ROWID datatype */
select_dp->L[i] = 18;
break;
case 12 : /* DATE datatype */
select_dp->L[i] = 9;
break;
case 23 : /* RAW datatype */
break;
case 24 : /* LONG RAW datatype */
select_dp->L[i] = 240;
break;
}
/* Allocate space for the select-list data values. */
select_dp->V[i] = malloc(select_dp->L[i]);
/* Allocate space for any indicator variables. */
#ifndef MSDOS
select_dp->I[i] = (short *)malloc(sizeof(short));
#else
select_dp->I[i] = (unsigned short *)malloc(sizeof(short));
#endif
/* Coerce ALL datatypes except for LONG RAW to
character. */
if (select_dp->T[i] != 24)
select_dp->T[i] = 1;
}
/* Allocate space for previous record if needed. */
if (ind_num>=0)
{
len_c = (select_dp->L[ind_num]);
prev_index_col=(char *)malloc (len_c);
}
/* get teh record size and adjust for the LF and NULL char */
getrsz(&recsiz);
recsiz +=2;
/* generate the sqlloader control file of necessary */
if (ldr =='y' || ldr =='Y')
gen_ctl_file();
/* allocate space for the buffer which holds each record for I/O */
buf = (char *) malloc (recsiz + 5 );
/* FETCH each row selected and print the column values. */
EXEC SQL WHENEVER NOT FOUND GOTO end_main;
for (;;)
{
EXEC SQL FETCH C USING DESCRIPTOR select_dp;
if (SQLCODE) { report_err();goto end_main;}
else
{
xbuf=buf;
*buf='\0';
/* Since each variable returned has been coerced to a
character string, very little processing is required
here. This routine just prints out the values on
the terminal. */
for (i=0; i<select_dp->F; i++)
{
/* save the key */
if (ind_num>=0) /*save prev record when needed */
strncpy(prev_index_col,select_dp->V[ind_num],len_c);
indicator = (short *)select_dp->I[i];
if (*indicator > 0) report_err();
strncat (buf,select_dp->V[i],select_dp->L[i]);
}
fprintf (out_fp,"%s\n",buf);
if ( !(sqlca.sqlerrd[2] % 1000) )
{
fprintf (err_fp,"\n now processing %d \n",sqlca.sqlerrd[2]);
fflush(err_fp);
}
} /* else */
} /* for */
end_main:
if (sqlca.sqlcode == 1403)
printf ("\n spooling is completed successfully \n");
fclose(out_fp);
fclose(err_fp);
/* Free space used by descriptor (graceful exit). */
/* 1. Free the malloc()'ed space for select-list items. */
for (i=0; i<select_dp->F; i++)
{
free (select_dp->V[i]);
free (select_dp->I[i]);
}
/* 2. Free space used by the descriptors themselves. */
sqlclu (select_dp);
EXEC SQL CLOSE C;
EXEC SQL COMMIT WORK RELEASE;
return;
} /* main*/
/* ORACLE error handler */
sqlerror ()
{
fclose(out_fp);
fclose(err_fp);
printf ("\n\n%.70s\n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
report_err()
{
if (ind_num>=0)
fprintf (err_fp,"error occured after fetching %-*.*s ",
len_c,len_c,prev_index_col);
fprintf (err_fp,"%s\n",sqlca.sqlerrm.sqlerrmc);
fprintf (err_fp,"when processing record # %d \n",sqlca.sqlerrd[2]);
fprintf (err_fp,"\n indicator =%d \n",*indicator);
return;
}
validate_files(file_name,fp)
char *file_name;
FILE **fp;
{
if ( ( (*fp) =fopen(file_name,"r")) != NULL)
{
printf ("Sorry, this file already exist. Specify another file name \n");
fclose(fp);
exit(1);
}
if ( ((*fp)=fopen(file_name,"w")) == NULL)
{
printf ("Couldn't open file %s \n",file_name);
perror("fopen");
exit(1);
}
else printf (" opened file %s for output \n",file_name); }
prompts()
{
fflush(stdout);fflush(stdin);
printf("\nplease enter the userid/password \n>> ");
gets(username.arr);
username.len = strlen(username.arr);
printf("\nplease enter the output file name \n>> ");
gets(out_file_name);
validate_files(out_file_name,&out_fp);
printf("\nplease enter the error file name \n>> ");
gets(error_file_name);
validate_files(error_file_name,&err_fp);
fflush(stdin);fflush (stdout);
printf("\nwould you like to generate sqlloader controle file [Y/N] \n>> ");
scanf("%c",&ldr);
if (ldr=='y' || ldr=='Y')
{
fflush(stdin);fflush (stdout);
printf("\nplease enter the sqlloader control file name \n>> ");
gets(ctl_file_name);
validate_files(ctl_file_name,&ctl_fp);
printf("\nplease enter the table name for loader's control file\n>> ");
gets(table_name);
}
fflush(stdin);fflush (stdout);
printf("\nplease enter the select statement << without ';'>>\n>>"); gets(sql_statement.arr);
sql_statement.len=strlen(sql_statement.arr);
fflush(stdout);fflush(stdin);
printf("\nplease enter the max length for long \n>>"); scanf("%d",&max_long);
printf("\nplease enter the column number to be printed when an - \n "); printf(" error occures (0..n-1) << -1 to ignore >> \n>>"); scanf("%d",&ind_num);
printf("\nplease enter the max number of columns in the table \n>>"); scanf("%d",&max_col_num);
}
gen_ctl_file()
{
int i;
int bpos=1;
int epos=1;
fprintf(ctl_fp," LOAD DATA \n"); fprintf(ctl_fp," INFILE %s \n",out_file_name); fprintf(ctl_fp," FIXED %d \n",recsiz); fprintf(ctl_fp," INTO TABLE %s \n",table_name); fprintf(ctl_fp," APPEND \n"); fprintf(ctl_fp," ( \n");
for (i=0;i< select_dp->F;i++)
{
epos= bpos + (select_dp->L[i]) -1;
fprintf (ctl_fp,"%-*.*s\t POSITION (%d:%d)\t ",
select_dp->M[i],select_dp->M[i],
select_dp->S[i],
bpos,epos);
bpos=epos+1;
if ( ( (select_dp->F) -1 ) == i)fprintf (ctl_fp," -- col# %d\n",i);
else
fprintf (ctl_fp,", -- col# %d \n",i);
}
fprintf(ctl_fp," ) \n");
fclose ( ctl_fp);
}
getrsz(recsiz)
short *recsiz;
{
int i;
int bpos=1;
int epos=1;
for (i=0;i< select_dp->F;i++)
{
epos= bpos + (select_dp->L[i]) -1;
bpos=epos+1;
}
(*recsiz)= (short) epos;
}
- Cut here for proc.mk --------------------------------
#
# $Header: proc.mk.pp,v 6.54 90/04/01 14:50:54 pgreenwa Exp $ proc.mk.pp
#
#
# proc.mk - Command file for "make" to compile and load OCI and Pro*C programs.
#
# Pro*C programs are assumed to have the extension ".pc"
#
#
# Usage for sample OCI program:
# make -f proc.mk sample
# Usage for sample Pro*C program:
# make -f proc.mk samplec USERID=name/pass
#
#
# NOTE: ORACLE_HOME must be either:
# . set in the user's environment
# . passed in on the command line
# . defined in a modified version of this makefile
#
.SUFFIXES: .exe .c .pc
CC = cc
CC=/usr/5bin/cc
OTHERLIBS=`cat $(ORACLE_HOME)/rdbms/lib/sysliblist`
CLIBS= $(OTHERLIBS)
ECHO=$(ORACLE_HOME)/bin/echodo
CFLAGS = -O
PCC = pcc
PCCINC = $(ORACLE_HOME)/c/lib
PCCFLAGS = include=$(PCCINC) ireclen=132 oreclen=132 select_error=no sqlcheck=li
mited userid=scott/tiger
SQLLIB = $(ORACLE_HOME)/rdbms/lib/libsql.a OCILIB = $(ORACLE_HOME)/rdbms/lib/libocic.a PCCLIBS = $(ORACLE_HOME)/rdbms/lib/libpcc.a $(SQLLIB) $(OCILIB) \
$(ORACLE_HOME)/rdbms/lib/libpro.a $(ORACLE_HOME)/rdbms/lib/libpcc.a NETLIBS= $(ORACLE_HOME)/rdbms/lib/osntab.o \
$(ORACLE_HOME)/rdbms/lib/libsqlnet.a
STLIBS= $(ORACLE_HOME)/rdbms/lib/osntabst.o \
$(ORACLE_HOME)/rdbms/lib/config.o
ORALIBS = $(ORACLE_HOME)/rdbms/lib/libora.a
CONFIG=$(ORACLE_HOME)/rdbms/lib/config.o LIBPLS= $(ORACLE_HOME)/rdbms/lib/libpls.a LIBPSD=$(ORACLE_HOME)/c/lib/libpsd.a
LIBKNL= $(ORACLE_HOME)/rdbms/lib/libknl.a
all: sample samplec
sample: sample.o
_at_$(ECHO) $(CC) $(CFLAGS) -o $@ $@.o $(OCILIB) $(NETLIBS) $(ORALIBS) \ $(CLIBS) samplest: sample.o _at_$(ECHO) $(CC) $(CFLAGS) -o $@ $? $(OCILIB) $(STLIBS) $(ORALIBS) \ $(LIBKNL) $(ORALIBS) $(LIBPLS) $(ORALIBS) $(LIBKNL) $(ORALIBS) \ $(LIBPLS) $(CLIBS) samplec: samplec.c samplec.pc _at_$(ECHO) $(CC) $(CFLAGS) samplec.c -o $@ $(SQLLIB) $(NETLIBS) \ $(ORALIBS) $(CLIBS) samplecst: samplec.c samplec.pc _at_$(ECHO) $(CC) $(CFLAGS) samplec.c -o $@ $(SQLLIB) $(STLIBS) \ $(ORALIBS) $(LIBKNL) $(ORALIBS) $(LIBPLS) $(ORALIBS) \ $(LIBKNL) $(ORALIBS) $(LIBPLS) $(CLIBS) .pc.c: $(PCC) $(PCCFLAGS) iname=$*.pc install: clean pcc -chmod 755 $(ORACLE_HOME)/bin/pcc -mv pcc $(ORACLE_HOME)/bin/pcc -chmod 755 $(ORACLE_HOME)/bin/pcc clean: -rm -f pcc pcc: $(PCCLIBS) $(ORALIBS) $(NETLIBS) $(CONFIG) $(LIBPLS) $(LIBPSD) _at_$(ECHO) $(CC) $(CFLAGS) $(LDFLAGS) -o $@ $(PCCLIBS) $(NETLIBS) $(LIBPSD) \ $(ORALIBS) $(LIBPLS) $(LIBPSD) $(ORALIBS) $(LIBPLS) $(CONFIG) $(CLIBS)
#
# General suffix rule to build executables from .pc and .c files.
#
# Usage :
# make -f proc.mk USERID=<user/pass> <prog>.exe
#
# For example to build an executable from a Pro*C source file named 'abc.pc'
# using scott/tiger for the ORACLE account name. The make command line will
# be:
# make -f proc.mk USERID=scott/tiger abc.exe
#
# The executable will be named 'abc', not 'abc.exe'.
#
.pc.exe:
-$(PCC) iname=$*.pc $(PCCFLAGS) userid=$(USERID) _at_$(ECHO) $(CC) $(CFLAGS) -o $* $*.c $(SQLLIB) $(NETLIBS) $(ORALIBS) \ $(CLIBS) .c.exe: _at_$(ECHO) $(CC) $(CFLAGS) -o $* $*.c $(SQLLIB) $(OCILIB) $(NETLIBS) \ $(ORALIBS) $(CLIBS)
#
# A Pro*C demo that that uses dynamic SQL to execute arbitray
# interactive SQL commands.
#
unloader: unloader.c unloader.pc
_at_$(ECHO) $(CC) $(CFLAGS) unloader.c -o $@ $(SQLLIB) $(NETLIBS) \ $(ORALIBS) $(CLIBS) -------------------------cut here for make_unloader.com -------------- $ ! Created August 29, 1991 by DDRUKER $ ! This COM file will precompile and link unloader. $ ! TYPE _at_MAKE_UNLOADER.COM to build the unloader $ ! Type RUN UNLOADER to execute $ set ver $ $ proc iname=unloader.pc include=ora_progint: $ cc unloader.c
$ lnproc unloader unloader
$ set nover Received on Fri Jul 03 1992 - 02:52:20 CEST