Re: -> Write table data to a text file? How? <-

From: Govindhan Padmavijayam <govind_at_smtskit.attmail.com>
Date: 1996/10/28
Message-ID: <552q9u$qqe@newsa.netnews.att.com>

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;

EXEC SQL END DECLARE SECTION; FILE *fp;

        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

SELECT
decode(column_id,:maxcolid,column_name||'||''|''',column_name||'||''|''||' )
from all_Tab_columns
where table_name = :tabbuff
and OWNER = :schema_owner
order by column_id;

        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