Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: -> Write table data to a text file? How? <-
Les Gainous <lesgainous_at_earthlink.net> wrote:
>To all,
>What's the best way to write data out to a text file: comma-delimited,
>tab-separated, or fixed length? I basically need to write out a table
>into a format usable by other RDMSs/applications. I'm looking for
>something similar to the Bulk-Copy Program from Sybase or Microsoft SQL
>Server.
>Thanks in advance!
>--
> Les Gainous, lesgainous_at_earthlink.net
> Visit my web page at
> http://home.earthlink.net/~lesgainous
> Looking for a Client-Server job in California?
> http://home.earthlink.net/~lesgainous/jobs.html
Here's a Pro*C script which will do what you need.
/* Table Unload script */
/* This script unloads data from each table for a given user into
a flat ascii file - each column, delimited by a pipe. */ /* The following environment variables need to be set :
ORAUSR .. Oracle User
ORAPWD .. Oracle Password
TABLE_OWNER .. Owner of the Objects [ Can be different from ORAUSR ] DBEXTRACT .. Directory to which output is to written to. for example : /db_dumps/ NOTE : the last "/" is important !*/
/* If the concatinated string is more than 2000 characters, then
the script fails. */
/* Haven't found a work around for this problem yet. */
/*
#define ORAUSR (char *) getenv("ORAUSR") #define ORAPWD (char *) getenv("ORAPWD") #define SCHEMA_OWNER (char *) getenv("SCHEMA_OWNER") #define DBEXTRACT (char *) getenv("DBEXTRACT")
#include <stdio.h>
EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION;
char *pUserID; char *pPassWd; char *schema_owner; char *command = "compress -f "; varchar sqlstmt[3072]; int cnt = 0; long count= 0; varchar tabbuff[40]; varchar colbuff[1024]; varchar outline[1000][3072]; short maxcolid; short ind; char *p;
char filename[80];
int i;
int nrec = 0; long totrec = 0;
main()
{
schema_owner = SCHEMA_OWNER;
pUserID = ORAUSR; pPassWd = ORAPWD; if(!pUserID || !pPassWd) { printf("DbConnect: FAILED TO CONNECT TO THE LIVE DATABASE \n"); exit (-1); } EXEC SQL WHENEVER SQLERROR GOTO error; EXEC SQL CONNECT :pUserID IDENTIFIED BY :pPassWd; output(); exit(0);
error:
EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\nORACLE error detected:\n"); printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
output()
{
/* int iCount=0; */ /* USER_NOTE included 'cos note_text is > 2000 characters -- results in ORA-1489 if length(note_text) > approx. 1920 bytes */ EXEC SQL DECLARE get_tables CURSOR FOR SELECT TABLE_NAME FROM ALL_TABLES A WHERE OWNER = :schema_owner AND TABLE_NAME NOT LIKE '%TEMP%' AND TABLE_NAME != 'USER_NOTE' AND NOT EXISTS ( SELECT '1' FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = A.TABLE_NAME AND OWNER = :schema_owner AND DATA_TYPE = 'LONG' ); EXEC SQL DECLARE GET_DATA CURSOR FOR
EXEC SQL OPEN get_tables;
/* ========================================================== */ /* Placing of the EXE SQL OPEN statements is very important ! */ /* ==========================================================*/
/* EXEC SQL OPEN GET_DATA; */ /* this is wrong ! */ while(1) { /* EXEC SQL WHENEVER NOT FOUND do break; */ EXEC SQL FETCH get_tables into :tabbuff; if ( sqlca.sqlcode == 1403 ) break; tabbuff.arr[tabbuff.len] = '\0'; tabbuff.len = strlen(tabbuff.arr); EXEC SQL SELECT MAX(COLUMN_ID) INTO :maxcolid FROM all_tab_columns WHERE TABLE_NAME = :tabbuff AND OWNER = :schema_owner; EXEC SQL OPEN GET_DATA; /* make p = starting position of sqlstmt.arr */ p = (char *) sqlstmt.arr; p += sprintf(p,"%s", "SELECT "); /* this is correct ! */ while(1) { EXEC SQL FETCH get_data into :colbuff; if ( sqlca.sqlcode == 1403 ) break; colbuff.arr[colbuff.len] = '\0'; colbuff.len = strlen(colbuff.arr); p += sprintf(p,"%s", colbuff.arr); } p += sprintf(p," FROM %s", tabbuff.arr); sqlstmt.len = strlen(sqlstmt.arr); strcpy(filename,DBEXTRACT); strcat(filename,tabbuff.arr); strcat(filename,".txt"); if ( ( fp=fopen(filename,"w") ) == NULL ) { printf("Error in opening File !"); exit (-1); } EXEC SQL PREPARE S FROM :sqlstmt; EXEC SQL DECLARE exec_sql CURSOR FOR S; /* initialize value of totrec to zero ! */ totrec = 0; EXEC SQL OPEN exec_sql; while(1) { /* printf(" iCount = [%d] \n", iCount); */ EXEC SQL FETCH exec_sql into :outline; if ( sqlca.sqlcode != 0 && sqlca.sqlcode != -1002 && sqlca.sqlcode != -1405 && sqlca.sqlcode != 1403 ) error(); nrec = sqlca.sqlerrd[2] - totrec; totrec = sqlca.sqlerrd[2]; if ( nrec <= 0 ) break; /* printf(" iCount = [%d] \n", iCount++); */ for (i=0; i<nrec; i++ ) { outline[i].arr[outline[i].len] = '\0'; fprintf(fp,"%s\n",outline[i].arr); } } fclose(fp); strcpy(command,"compress -f "); strcat(command,filename); printf("%s", command ); system(command); EXEC SQL CLOSE exec_sql; } EXEC SQL close get_data; EXEC SQL close get_tables; return;
}
nothing()
{
exit(0);
}
error()
{
EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\nORACLE error detected:\n"); printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
Received on Mon Oct 28 1996 - 00:00:00 CST