Thanks for the question, Kevin.

Asked: July 20, 2000 - 12:02 pm UTC

Last updated: November 24, 2015 - 1:09 am UTC

Version: 7.3

Viewed 10K+ times! This question is

You Asked

Tom,

I've written Pro/C to generically dump any table to delimited ASCII. It dynamically builds a select statement with a single column containing all the table's columns concatenated together with a delimiter, then fetches this into a HOSTARRAY for output.

The problem: I get ORA-01489 when a member of the rowset exceeds 2000 characters.

What would you suggest here? Any tricks in Pro/C to overcome this limitation?

I plan to turn this into a system that can very quickly dump large tables to compressed ASCII via the use of home-grown parallel processing. It's not really worth my time to do so unless I can get past this issue ( and a few others ).

Thanks,

-Kevin

and Tom said...

Thats the limit of a character column in 7.3 (it would be 4000 bytes in 8.0 and up).

Not to worry, what we need is a slighty more complex pro*c app is all -- one that fetches any number of columns from any arbitrary query. I happen to have one. Currently if you run it with a command line like:

$./array_flat userid=scott/tiger 'sqlstmt=select * from emp' arraysize=100

(thats on 1 line) it will produce:

Connected to ORACLE as user: scott/tiger

Unloading 'select * from emp'
Array size = 100
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,17-DEC-1980 00:00:00,800,(null),20
7499,ALLEN,SALESMAN,7698,20-FEB-1981 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-1981 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,02-APR-1981 00:00:00,2975,(null),20
7654,MARTIN,SALESMAN,7698,28-SEP-1981 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-1981 00:00:00,2850,(null),30
7782,CLARK,MANAGER,7839,09-JUN-1981 00:00:00,2450,(null),10
7788,SCOTT,ANALYST,7566,09-DEC-1982 00:00:00,3000,(null),20
7839,KING,PRESIDENT,(null),17-NOV-1981 00:00:00,5000,(null),10
7844,TURNER,SALESMAN,7698,08-SEP-1981 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,12-JAN-1983 00:00:00,1100,(null),20
7900,JAMES,CLERK,7698,03-DEC-1981 00:00:00,950,(null),30
7902,FORD,ANALYST,7566,03-DEC-1981 00:00:00,3000,(null),20
7934,MILLER,CLERK,7782,23-JAN-1982 00:00:00,1300,(null),10
14 rows extracted

If you run it as:

$ ./array_flat userid=scott/tiger 'sqlstmt=select * from emp' arraysize=100 > test.dat

You'll see:

Connected to ORACLE as user: scott/tiger

Unloading 'select * from emp'
Array size = 100
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
14 rows extracted

and test.dat will just have the data.

The pro*c program (works in 7.x - 8.x as is) is simply:

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

#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30

static char * USERID = NULL;
static char * SQLSTMT = NULL;
static char * ARRAY_SIZE = "10";

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

EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;

extern SQLDA *sqlald();
extern void sqlclu();

static void die( char * msg )
{
fprintf( stderr, "%s\n", msg );
exit(1);
}

/*
this array contains a default mapping
I am using to constrain the
lengths of returned columns. It is mapping,
for example, the Oracle
NUMBER type (type code = 2) to be 45 characters
long in a string.
*/

static int lengths[] =
{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,
18, 25, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 512, 2000 };

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
if ( !strncmp( argv[i], "sqlstmt=", 8 ) )
SQLSTMT = argv[i]+8;
else
if ( !strncmp( argv[i], "arraysize=", 10 ) )
ARRAY_SIZE = argv[i]+10;
else
{
fprintf( stderr,
"usage: %s %s %s\n",
argv[0],
"userid=xxx/xxx sqlstmt=query ",
"arraysize=<NN>\n" );
exit(1);
}
}
if ( USERID == NULL || SQLSTMT == NULL )
{
fprintf( stderr,
"usage: %s %s %s\n",
argv[0],
"userid=xxx/xxx sqlstmt=query ",
"arraysize=<NN>\n" );
exit(1);
}
}

static void sqlerror_hard()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;

fprintf(stderr,"\nORACLE error detected:");
fprintf(stderr,"\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

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

static SQLDA * process_1(char * sqlstmt, int array_size )
{
SQLDA * select_dp;
int i;
int j;
int null_ok;
int precision;
int scale;
int size = 10;

fprintf( stderr, "Unloading '%s'\n", sqlstmt );
fprintf( stderr, "Array size = %d\n", array_size );

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :sqlstmt;
EXEC SQL DECLARE C CURSOR FOR S;

if ((select_dp = sqlald(size,MAX_VNAME_LEN,MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for select descriptor." );

select_dp->N = size;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
if ( !select_dp->F ) return NULL;

if (select_dp->F < 0)
{
size = -select_dp->F;
sqlclu( select_dp );
if ((select_dp =
sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for descriptor." );
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
}
select_dp->N = select_dp->F;

for (i = 0; i < select_dp->N; i++)
select_dp->I[i] = (short *) malloc(sizeof(short) *
array_size );

for (i = 0; i < select_dp->F; i++)
{
sqlnul (&(select_dp->T[i]),
&(select_dp->T[i]), &null_ok);
if ( select_dp->T[i] <
sizeof(lengths)/sizeof(lengths[0]) )
{
if ( lengths[select_dp->T[i]] )
select_dp->L[i] = lengths[select_dp->T[i]];
else select_dp->L[i] += 5;
}
else select_dp->L[i] += 5;

select_dp->T[i] = 5;
select_dp->V[i] = (char *)malloc( select_dp->L[i] *
array_size );

for( j = MAX_VNAME_LEN-1;
j > 0 && select_dp->S[i][j] == ' ';
j--);
fprintf (stderr,
"%s%.*s", i?",":"", j+1, select_dp->S[i]);
}
fprintf( stderr, "\n" );

EXEC SQL OPEN C;
return select_dp;
}

static void process_2( SQLDA * select_dp, int array_size )
{
int last_fetch_count;
int row_count = 0;
short ind_value;
char * char_ptr;
int i,
j;

for ( last_fetch_count = 0;
;
last_fetch_count = sqlca.sqlerrd[2] )
{
EXEC SQL FOR :array_size FETCH C
USING DESCRIPTOR select_dp;

for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
{
for (i = 0; i < select_dp->F; i++)
{
ind_value = *(select_dp->I[i]+j);
char_ptr = select_dp->V[i] +
(j*select_dp->L[i]);

printf( "%s%s", i?",":"",
ind_value?"(null)":char_ptr );
}
row_count++;
printf( "\n" );
}
if ( sqlca.sqlcode > 0 ) break;
}

sqlclu(select_dp);

EXEC SQL CLOSE C;

EXEC SQL COMMIT WORK;
fprintf( stderr, "%d rows extracted\n", row_count );
}

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;
SQLDA * select_dp;

process_parms( argc, argv );

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

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

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

EXEC SQL ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

select_dp = process_1( SQLSTMT, atoi(ARRAY_SIZE) );
process_2( select_dp , atoi(ARRAY_SIZE));

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

Rating

(155 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

PRO*C Compilation

A reader, August 24, 2001 - 11:09 am UTC

Tom,
Thanks for your prompt response. However, I still couldn't compile the pro*.
This is what I did.
1) created file flatten.pc with a copy of your source code
2) create Makefile from the contents you provided
3) set up the env. variables as follows:
% setenv SOURCE flatten.pc
% setenv TARGET flatten
% setenv ORACLE_PROC_MAKEFILE $ORACLE_HOME/precomp/demo/proc/demo_proc.mk
% make
Make: /disk01/app/oracle/product/8.1.7/precomp/lib/env_precomp.mk: Must be a separator on line 7. Stop.

I have installed pro*c on the server on Tru64. Database version is 8172.

Your help in resolving this would be much appreciated as I would like to try this out asap. (I have a very urgent need to copy tables regularly).

Rgds, Chris

prepare problem

Kapil, June 22, 2002 - 10:34 am UTC

Hi Tom
This is with reference to 8.1.7
My code is similar to Kevins. I go a little further than that and dump data of multiple tables. The problem I am facing is that when I prepare the SQL statement for the second table my executable crashes and generates a core file. I am doing something like this :

delare cursor listTables
open listTables
start loop
fetch from listTables
Form dynamic statement for the table
prepare
declare
open
....
close
end loop

The program works fine for the table fetched first but core dumps at the prepare statement for the table fetched second. Is there anything wrong in preparing a dynamic statement using the same statement id and host string the second time in the same transaction or session?

June 22, 2002 - 1:34 pm UTC

Well, my first guess is -- you have a bug in your code. C is notorious for having mysterious "crashes" like that unless you are really good. I have a feeling that given any non-trivial source code file written in C, it would be possible to find a bug.

Lets see some of your code.

bug in code

Kapil, July 21, 2002 - 11:15 am UTC

Hi Tom
Yes, you are right
There is a bug in the code
The size of a variable was too less to accomodate the data stored in it
Thus the data stored in it was probably corrupting the memory
Thanks for your help

Slow

A reader, October 07, 2002 - 10:17 am UTC

Hi, TOm,

Did you test this code with large table(I mean 100 columns
with million rows)? The speed is very slow?

Any ideas?

Thanks

October 07, 2002 - 12:42 pm UTC

is this a question?

The code works with any number of columns and any number of rows.

Define "slow" for me -- do you have something "faster"? I just ran on RH Linux 7.3 with 9iR2 and a 1,000,000 row table. big_wide_table has over 100 columns, big_table has 14 (1/8 the data). Ran against all_objects as well (1/34th the data of big_table)

Timings were:

[tkyte@dhcp-reston-gen-3-west-120-222 array_flat]$ ./test.sh

Connected to ORACLE as user: big_table/big_table

Unloading 'select * from big_wide_table'
Array size = 100
ID_1,ID_2,ID_3,ID_4,ID_5,ID_6,ID_7,ID_8,OWNER_1,OWNER_2,OWNER_3,OWNER_4,OWNER_5,OWNER_6,OWNER_7,OWNER_8,OBJECT_NAME_1,OBJECT_NAME_2,OBJECT_NAME_3,OBJECT_NAME_4,OBJECT_NAME_5,OBJECT_NAME_6,OBJECT_NAME_7,OBJECT_NAME_8,SUBOBJECT_NAME_1,SUBOBJECT_NAME_2,SUBOBJECT_NAME_3,SUBOBJECT_NAME_4,SUBOBJECT_NAME_5,SUBOBJECT_NAME_6,SUBOBJECT_NAME_7,SUBOBJECT_NAME_8,OBJECT_ID_1,OBJECT_ID_2,OBJECT_ID_3,OBJECT_ID_4,OBJECT_ID_5,OBJECT_ID_6,OBJECT_ID_7,OBJECT_ID_8,DATA_OBJECT_ID_1,DATA_OBJECT_ID_2,DATA_OBJECT_ID_3,DATA_OBJECT_ID_4,DATA_OBJECT_ID_5,DATA_OBJECT_ID_6,DATA_OBJECT_ID_7,DATA_OBJECT_ID_8,OBJECT_TYPE_1,OBJECT_TYPE_2,OBJECT_TYPE_3,OBJECT_TYPE_4,OBJECT_TYPE_5,OBJECT_TYPE_6,OBJECT_TYPE_7,OBJECT_TYPE_8,CREATED_1,CREATED_2,CREATED_3,CREATED_4,CREATED_5,CREATED_6,CREATED_7,CREATED_8,LAST_DDL_TIME_1,LAST_DDL_TIME_2,LAST_DDL_TIME_3,LAST_DDL_TIME_4,LAST_DDL_TIME_5,LAST_DDL_TIME_6,LAST_DDL_TIME_7,LAST_DDL_TIME_8,TIMESTAMP_1,TIMESTAMP_2,TIMESTAMP_3,TIMESTAMP_4,TIMESTAMP_5,TIMESTAMP_6,TIMESTAMP_7,TIMESTAMP_8,STATUS_1,STATUS_2,STATUS_3,STATUS_4,STATUS_5,STATUS_6,STATUS_7,STATUS_8,TEMPORARY_1,TEMPORARY_2,TEMPORARY_3,TEMPORARY_4,TEMPORARY_5,TEMPORARY_6,TEMPORARY_7,TEMPORARY_8,GENERATED_1,GENERATED_2,GENERATED_3,GENERATED_4,GENERATED_5,GENERATED_6,GENERATED_7,GENERATED_8,SECONDARY_1,SECONDARY_2,SECONDARY_3,SECONDARY_4,SECONDARY_5,SECONDARY_6,SECONDARY_7,SECONDARY_8
1000000 rows extracted

real 5m9.647s
user 4m40.720s
sys 0m12.440s

5 minutes, 9seconds

Connected to ORACLE as user: big_table/big_table

Unloading 'select * from big_table'
Array size = 100
ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY
1000000 rows extracted

real 0m41.229s
user 0m38.370s
sys 0m1.530s

Hmmm, 1/8th the data, just about EXACTLY 1/8th the time - seems to not be affected by the WIDTH of the table, how about the height?

Connected to ORACLE as user: big_table/big_table

Unloading 'select * from all_objects'
Array size = 100
OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY
29272 rows extracted

real 0m6.781s
user 0m0.940s
sys 0m0.030s

Ok, given that there is 1/34th the data in all_objects as opposed to big_table and this ran in 1/6th the time -- we can see it is not the HEIGHT of the table either

So, it scales up with the number of rows, it scales across with the number of columns. In short -- it is a pure function of the AMOUNT of data.

So, what is slow to you, perhaps you just have unreasonable expectations of how fast your server can give you the data and how fast you can write it out.

Excel Format

A reader, October 07, 2002 - 10:23 am UTC

Hi, Tom,

The code is great. But my problem is to write an "excel" file(truely excel file, specify format of each column, not only excel readable file) with fast speed. Is it possible by this PROC style code?

Thanks

October 07, 2002 - 12:52 pm UTC

you'll need to goto www.askbillgates.com to see if he can tell you the file format for excel.

Good luck keeping up with the file formats.

Maybe if you search for

OWA_SYLK

on this site, you'll find a utility that whilst it doesn't write EXCEL proprietary formats -- it does write SYLK which is an open file format that is readable by many spreadsheet programs and allows you to use formatting and formula's and such.

Excellent Pro*C "table dump" program!!

Geoff Yaworski, December 03, 2002 - 10:55 am UTC

Tom, I grabbed you Pro*C source, proc'd it, then gcc'd it with the appropriate libraries and ran it against our 2,000,000+ row, 27 column table. It dumped the table in 8 minutes and I did this over a network! I was impressed. I plan to port your code to dump a direct path load flat file. This will be an excellent replacement (I think) for our current method of doing a SQL*Plus copy from source to destination databases to get the data. Thanks again.

Balasubramanian.S, March 18, 2003 - 5:41 am UTC

Very nice article

pro*c unload

Marcel Rosa, March 28, 2003 - 7:49 am UTC

I made the unload using the pro*c , how do I the load ?
Can you give me a example ?

March 28, 2003 - 7:55 am UTC

sqlldr ....

look in $ORACLE_HOME/rdbms/demo/ulcase*.ctl

for example control files.

Kamal Kishore, April 19, 2003 - 9:25 pm UTC

I saved the Pro*C program that you posted and tried to compile it, but got following errors:

proc iname=flat_file.pc

Pro*C/C++: Release 9.2.0.1.0 - Production on Sat Apr 19 21:17:47 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

System default option values taken from: D:\Oracle\Ora_92\precomp\admin\pcscfg.cfg

Semantic error at line 110, column 33, file flat_file.pc:
EXEC SQL PREPARE S FROM :sqlstmt;
................................1
PCC-S-02322, found undefined identifier
Semantic error at line 110, column 27, file flat_file.pc:
EXEC SQL PREPARE S FROM :sqlstmt;
..........................1
PCC-S-02319, expression type does not match usage
Semantic error at line 181, column 23, file flat_file.pc:
EXEC SQL FOR :array_size FETCH C
......................1
PCC-S-02322, found undefined identifier
Semantic error at line 181, column 23, file flat_file.pc:
EXEC SQL FOR :array_size FETCH C
......................1
PCC-S-02330, expecting an expression of integer type

April 20, 2003 - 8:51 am UTC

what is in your 

D:\Oracle\Ora_92\precomp\admin\pcscfg.cfg

file.  As I recall, on windoze, the default settings are different then normal operating systems like unix.

Here are my default settings:

[tkyte@tkyte-pc-isdn Desktop]$ proc

Pro*C/C++: Release 9.2.0.3.0 - Production on Sun Apr 20 08:50:21 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

System default option values taken from: /usr/oracle/ora920/OraHome1/precomp/admin/pcscfg.cfg

Option Name    Current Value  Description
-------------------------------------------------------------------------------
auto_connect   no             Allow automatic connection to ops$ account
char_map       charz          Mapping of character arrays and strings
close_on_commitno             Close all cursors on COMMIT
cmax           100            CMAX Value for connection pool
cmin           2              CMIN Value for connection pool
cincr          1              CINCR Value for connection pool
ctimeout       0              CTIMEOUT Value for connection pool
cnowait        0              CNOWAIT Value for connection pool
code           kr_c           The type of code to be generated
comp_charset   multi_byte     The character set type the C compiler supports
config         default        Override system configuration file with another
cpool          no             Support connection pooling
cpp_suffix     *none*         Override the default C++ filename suffix
dbms           native         v6/v7/v8 compatibility mode
def_sqlcode    no             Generate '#define SQLCODE sqlca.sqlcode' macro
define         *none*         Define a preprocessor symbol
duration       transaction    Set pin duration for objects in the cache
dynamic        oracle         Specify Oracle or ANSI Dynamic SQL Semantics
errors         yes            Whether error messages are sent to the terminal
errtype        *none*         Name of the list file for intype file errors
fips           none           FIPS flagging of ANSI noncompliant usage
header         *none*         Specify file extension for Precompiled Headers
hold_cursor    no             Control holding of cursors in the cursor cache
iname          *none*         The name of the input file
include        *none*         Directory paths for included files
intype         *none*         The name of the input file for type information
lines          no             Add #line directives to the generated code
lname          *none*         Override default list file name
ltype          short          The amount of data generated in the list file
maxliteral     1024           Maximum length of a generated string literal
maxopencursors 10             Maximum number of cached open cursors
mode           oracle         Code conformance to Oracle or ANSI rules
nls_char       *none*         Specify National Language character variables
nls_local      no             Control how NLS character semantics are done
objects        yes            Support object types
oname          *none*         The name of the output file
oraca          no             Control the use of the ORACA
pagelen        80             The page length of the list file
parse          full           Control which non-SQL code is parsed
prefetch       1              Number of rows pre-fetched at cursor OPEN time
release_cursor no             Control release of cursors from cursor cache
select_error   yes            Control flagging of select errors
sqlcheck       syntax         Amount of compile-time SQL checking
sys_include    /usr/lib/gcc-liDirectory where system header files are found
               /usr/lib/gcc-lib/i486-suse-linux/2.95.3/include
               /usr/include
threads        no             Indicates a multi-threaded application
type_code      oracle         Use Oracle or ANSI type codes for Dynamic SQL
unsafe_null    no             Allow a NULL fetch without indicator variable
userid         *none*         A username/password [@dbname] connect string
utf16_charset  nchar_charset  The character set form used by UTF16 variables
varchar        no             Allow the use of implicit varchar structures
version        recent         Which version of an object is to be returned
PCC-F-02135, CMD-LINE:  User asked for help


make sure the options match

Work Around...

Kamal Kishore, April 19, 2003 - 10:26 pm UTC

When I changed this:
=========================================================
static SQLDA * process_1(char * sqlstmt, int array_size )
=========================================================

To the following:
=========================================================
static SQLDA * process_1(sqlstmt, array_size )
EXEC SQL BEGIN DECLARE SECTION ;
char * sqlstmt ;
int array_size ;
EXEC SQL END DECLARE SECTION ;
=========================================================

The program compiled and was working.

Is this someting new to Oracle9i that all host variables appearing in the EXEC SQL statements must be enclosed within the DECLARE SECTION?

April 20, 2003 - 8:55 am UTC

no, it is something old with windoze -- one of the default options is different.

I had the same problem

David Sansom, April 20, 2003 - 1:24 pm UTC

Tom: Sorry, but I've just asked the same question in a separate thread (I did search your site for answers to the problem last week, but this response is new).

Setting these parameters:
code=KR_C parse=full
resolves the "found undefined identifier" error.

However, I'm now getting this error:
Error at line 1, column 10 in file C:\Temp\unload.pc
#include <stdio.h>
.........1
PCC-S-02015, unable to open include file

I presume this is because my sys_include parameter is incorrect. Its currently set to:
sys_include=c:\Oracle\Ora9iR2\precomp\lib
Do you know what it should be on a Windows XP machine?

Thanks

David

April 20, 2003 - 2:57 pm UTC

stdio.h is a standard C include file, it'll be whereever your c compiler has it (eg: /usr/include on unix, probably c:\program files\something\really\long\with spaces\in it

I've managed to fix that problem, but still having compile problems

David Sansom, April 20, 2003 - 2:01 pm UTC

I downloaded Borland C++ Compiler. and set my sys_include paramter to point at a folder where I'd uncompressed the files to. It now finds stdio.h, etc, but I'm getting other errors now:

++: Release 9.2.0.1.0 - Production on Sun Apr 20 18:54:39 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

System default option values taken from: c:\Oracle\Ora9iR2\precomp\admin\pcscfg.cfg

Syntax error at line 139, column 29, file c:\Oracle\Ora9iR2\precomp\borland\mem.h:
Error at line 139, column 29 in file c:\Oracle\Ora9iR2\precomp\borland\mem.h
#endif /* __USING_CNAME__ */
............................1
PCC-S-02014, Encountered the symbol "<eof>" when expecting one of the following:

; { } , = : ( ) [ ] * ? | & < > + - / % ~ ! . # @ ^ *= /= %=
+= -= <<= >>= &&= ||= ^= ~= := | & == != <= >= << >> ++ -- ->
... .. <> ** => an identifier, a string, a numeric constant,
newline, a sql string, misc. punctuation, newline, define,
elif, else, endif, error, if, ifdef, ifndef, include, line,
pragma, undef, exec, sql, begin, end, var, type, oracle,
an immediate preprocessor command, a C token, exec sql,
exec sql include, exec sql var, exec sql begin, exec sql end,
end-exec, exec sql type, exec oracle, exec oracle else,
exec oracle endif, exec oracle begin, a sql hint, create,
function, package, procedure, trigger, or, replace,
a C++ token,
The symbol "newline," was substituted for "<eof>" to continue.

Syntax error at line 18, column 14, file C:\Temp\unload.pc:
Error at line 18, column 14 in file C:\Temp\unload.pc
extern SQLDA *sqlald();
.............1
PCC-S-02201, Encountered the symbol "*" when expecting one of the following:

; , = ( [
The symbol ";" was substituted for "*" to continue.

Syntax error at line 95, column 14, file C:\Temp\unload.pc:
Error at line 95, column 14 in file C:\Temp\unload.pc
static SQLDA * process_1(char * sqlstmt, int array_size )
.............1
PCC-S-02201, Encountered the symbol "*" when expecting one of the following:

; , = ( [
The symbol ";" was substituted for "*" to continue.

Syntax error at line 168, column 30, file C:\Temp\unload.pc:
Error at line 168, column 30 in file C:\Temp\unload.pc
static void process_2( SQLDA * select_dp, int array_size )
.............................1
PCC-S-02201, Encountered the symbol "*" when expecting one of the following:

, )

Error at line 0, column 0 in file C:\Temp\unload.pc
PCC-F-02102, Fatal error while doing C preprocessing

Any ideas?

Thanks

David

April 20, 2003 - 2:57 pm UTC

the borland c compiler is not supported at all. ms visual c/c++ is.

Unloading to a flat file when running on Windows

David Sansom, April 21, 2003 - 5:43 pm UTC

Thanks Tom - I managed to compile it using Microsoft C++.

On our server, exporting a 150MB Text file, took 1hour20mins using UTL_FILE, whereas it now only takes 3mins15secs using Pro*C !

Cheers

David

great program

michel moureaux, May 26, 2003 - 4:09 am UTC

This is the best way i found to download data from huge table (6 Gb) to flat file with such good performances.
Thanks a lot !

A reader, June 29, 2003 - 12:28 pm UTC

Tom,

OS:R.H 7.3
Database: 9.0.1

i saved your code as t1.pc and when i give this command i get this error

$ proc iname=t1.pc

Pro*C/C++: Release 9.0.1.0.0 - Production on Sun Jun 29 09:19:10 2003

(c) Copyright 2001 Oracle Corporation. All rights reserved.

System default option values taken from: /u01/oracle/product/9.0.1/precomp/admin/pcscfg.cfg

Error at line 34, column 11 in file /usr/include/stdio.h
# include <stddef.h>
..........1
PCC-S-02015, unable to open include file
Error at line 29, column 10 in file /usr/include/bits/types.h
#include <stddef.h>
.........1
PCC-S-02015, unable to open include file
Syntax error at line 48, column 3, file /usr/include/bits/pthreadtypes.h:
Error at line 48, column 3 in file /usr/include/bits/pthreadtypes.h
size_t __guardsize;
..1
PCC-S-02201, Encountered the symbol "size_t" when expecting one of the following
:

} char, const, double, enum, float, int, long, ulong_varchar,
OCIBFileLocator OCIBlobLocator, OCIClobLocator, OCIDateTime,
OCIExtProcContext, OCIInterval, OCIRowid, OCIDate, OCINumber,
OCIRaw, OCIString, short, signed, sql_context, sql_cursor,
struct, union, unsigned, utext, uvarchar, varchar, void,
volatile, a typedef name,
The symbol "enum," was substituted for "size_t" to continue.

Syntax error at line 51, column 3, file /usr/include/bits/pthreadtypes.h:
Error at line 51, column 3 in file /usr/include/bits/pthreadtypes.h
size_t __stacksize;
..1
PCC-S-02201, Encountered the symbol "size_t" when expecting one of the following
:

} char, const, double, enum, float, int, long, ulong_varchar,
OCIBFileLocator OCIBlobLocator, OCIClobLocator, OCIDateTime,
OCIExtProcContext, OCIInterval, OCIRowid, OCIDate, OCINumber,
OCIRaw, OCIString, short, signed, sql_context, sql_cursor,
struct, union, unsigned, utext, uvarchar, varchar, void,
volatile, a typedef name,
The symbol "enum," was substituted for "size_t" to continue.

Syntax error at line 51, column 3, file /usr/include/bits/pthreadtypes.h:
Error at line 51, column 3 in file /usr/include/bits/pthreadtypes.h
size_t __stacksize;
..1
PCC-S-02201, Encountered the symbol "size_t" when expecting one of the following
:

} char, const, double, enum, float, int, long, ulong_varchar,
OCIBFileLocator OCIBlobLocator, OCIClobLocator, OCIDateTime,
OCIExtProcContext, OCIInterval, OCIRowid, OCIDate, OCINumber,
OCIRaw, OCIString, short, signed, sql_context, sql_cursor,
struct, union, unsigned, utext, uvarchar, varchar, void,
volatile, a typedef name,
The symbol "enum," was substituted for "size_t" to continue.

Error at line 14, column 10 in file /usr/include/_G_config.h
#include <stddef.h>
.........1
PCC-S-02015, unable to open include file
Error at line 48, column 10 in file /usr/include/wchar.h
#include <stddef.h>
.........1
PCC-S-02015, unable to open include file
Syntax error at line 72, column 5, file /usr/include/wchar.h:
Error at line 72, column 5 in file /usr/include/wchar.h
wint_t __wch;
....1
PCC-S-02201, Encountered the symbol "wint_t" when expecting one of the following
:

There is alot like this..and my proc is
$proc

Pro*C/C++: Release 9.0.1.0.0 - Production on Sun Jun 29 09:27:32 2003

(c) Copyright 2001 Oracle Corporation. All rights reserved.

System default option values taken from: /u01/oracle/product/9.0.1/precomp/admin/pcscfg.cfg

Option Name Current Value Description
-------------------------------------------------------------------------------
auto_connect no Allow automatic connection to ops$ account
char_map charz Mapping of character arrays and strings
close_on_commitno Close all cursors on COMMIT
code kr_c The type of code to be generated
comp_charset multi_byte The character set type the C compiler supports
config default Override system configuration file with another
cpp_suffix *none* Override the default C++ filename suffix
dbms native v6/v7/v8 compatibility mode
def_sqlcode no Generate '#define SQLCODE sqlca.sqlcode' macro
define *none* Define a preprocessor symbol
duration transaction Set pin duration for objects in the cache
dynamic oracle Specify Oracle or ANSI Dynamic SQL Semantics
errors yes Whether error messages are sent to the terminal
errtype *none* Name of the list file for intype file errors
fips none FIPS flagging of ANSI noncompliant usage
header *none* Specify file extension for Precompiled Headers
hold_cursor no Control holding of cursors in the cursor cache
iname *none* The name of the input file
include *none* Directory paths for included files
intype *none* The name of the input file for type information
lines no Add #line directives to the generated code
lname *none* Override default list file name
ltype none The amount of data generated in the list file
maxliteral 1024 Maximum length of a generated string literal
maxopencursors 10 Maximum number of cached open cursors
mode oracle Code conformance to Oracle or ANSI rules
nls_char *none* Specify National Language character variables
nls_local no Control how NLS character semantics are done
objects yes Support object types
oname *none* The name of the output file
oraca no Control the use of the ORACA
pagelen 80 The page length of the list file
parse full Control which non-SQL code is parsed
prefetch 1 Number of rows pre-fetched at cursor OPEN time
release_cursor no Control release of cursors from cursor cache
select_error yes Control flagging of select errors
sqlcheck syntax Amount of compile-time SQL checking
sys_include /usr/lib/gcc-liDirectory where system header files are found
/usr/include
threads no Indicates a multi-threaded application
type_code oracle Use Oracle or ANSI type codes for Dynamic SQL
unsafe_null no Allow a NULL fetch without indicator variable
userid *none* A username/password [@dbname] connect string
utf16_charset nchar_charset The character set form used by UTF16 variables
varchar no Allow the use of implicit varchar structures
version recent Which version of an object is to be returned
PCC-F-02135, CMD-LINE: User asked for help

June 29, 2003 - 1:40 pm UTC

what is the "genealogy" of that machine? undergo an upgrade?

you might have to peek in your
/u01/oracle/product/9.0.1/precomp/admin/pcscfg.cfg

and see that the path is set right. check out the sys_include and make sure its pointing to the right place. I've noticed when I upgrade the OS, this gets "stale"

Yet another ASCII dump issue

Peter, July 02, 2003 - 4:29 pm UTC

I was wondering what is required to use this same function, except getting this from a DMP file instead of a database table itself

July 03, 2003 - 8:38 am UTC

easy -- all you would need to do is run imp ;)

DMP file formats are not documented, can -- do -- and will change from release to release (thats why a dmp from 9iR2 cannot be read by 8i for example)

I don't read dmp's

pro*cobol

A READER, August 05, 2003 - 2:17 pm UTC

Tom,

Our project plans to extract Oracle table to flat file using POR*COBOL.

This will be the same logic here ? Can you give me some sample code ?

Thanks!

Gre

August 05, 2003 - 2:24 pm UTC

</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

has all of my unloaders.

COBOL is not a language I can actually code in anymore ;) It was the first I learned (punched cards no less) but the least liked...

You know, if you just need to unload to a flat file, you might find one of the tools linked to above "more then suffcient" and avoid writing code of your own.

Yet another ASCII dump issue

Suvamoy Sen, August 21, 2003 - 6:56 am UTC

This article was just great. It was exactly the kind of code I was looking for. In his Expert One-on-One book in the chapter on data loading Tom has provided alternate codes to do the same using PL/SQL and SQL*PLUS. The best part is that he has even provided a PL/SQL package to generate the control file to load the flat file data generated using the PL/SQL version of his script. While generating the flat file, the PL/SQL version of the script encloses all the fields in quotes '"' even the null ones. This I find very useful. The SQL*LOADER control file generation procedure also creates a control file which expects that all fields would be enclosed in quotes. The current Pro*C program however does not enclose the fields in quotes and additionally does not replace every occurrence of the quote symbol in the fields with two quotes as the PL/SQL version of his program does.
I have added a few lines to Tom's Pro*C program to do just this:
1. It would enclose every field within quotes, even the NULL ones.
2. It would separate each field using ','
and finally each line would be terminated using the charcters "~^@#|\n" (quotes not included).

I am not a professional C programmer and my routine may be inefficient. If you have better ways of achieving this please share it with us all.

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

#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30

static char * USERID = NULL;
static char * SQLSTMT = NULL;
static char * ARRAY_SIZE = "10";

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

EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;

extern SQLDA *sqlald();
extern void sqlclu();

static void die( char * msg )
{
fprintf( stderr, "%s\n", msg );
exit(1);
}

/*
this array contains a default mapping
I am using to constrain the
lengths of returned columns. It is mapping,
for example, the Oracle
NUMBER type (type code = 2) to be 45 characters
long in a string.
*/

static int lengths[] =
{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,
18, 25, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 512, 2000 };

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
if ( !strncmp( argv[i], "sqlstmt=", 8 ) )
SQLSTMT = argv[i]+8;
else
if ( !strncmp( argv[i], "arraysize=", 10 ) )
ARRAY_SIZE = argv[i]+10;
else
{
fprintf( stderr,
"usage: %s %s %s\n",
argv[0],
"userid=xxx/xxx sqlstmt=query ",
"arraysize=<NN>\n" );
exit(1);
}
}
if ( USERID == NULL || SQLSTMT == NULL )
{
fprintf( stderr,
"usage: %s %s %s\n",
argv[0],
"userid=xxx/xxx sqlstmt=query ",
"arraysize=<NN>\n" );
exit(1);
}
}

static void sqlerror_hard()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;

fprintf(stderr,"\nORACLE error detected:");
fprintf(stderr,"\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

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

static SQLDA * process_1(char * sqlstmt, int array_size )
{
SQLDA * select_dp;
int i;
int j;
int null_ok;
int precision;
int scale;
int size = 10;

fprintf( stderr, "Unloading '%s'\n", sqlstmt );
fprintf( stderr, "Array size = %d\n", array_size );

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :sqlstmt;
EXEC SQL DECLARE C CURSOR FOR S;

if ((select_dp = sqlald(size,MAX_VNAME_LEN,MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for select descriptor." );

select_dp->N = size;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
if ( !select_dp->F ) return NULL;

if (select_dp->F < 0)
{
size = -select_dp->F;
sqlclu( select_dp );
if ((select_dp =
sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for descriptor." );
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
}
select_dp->N = select_dp->F;

for (i = 0; i < select_dp->N; i++)
select_dp->I[i] = (short *) malloc(sizeof(short) *
array_size );

for (i = 0; i < select_dp->F; i++)
{
sqlnul (&(select_dp->T[i]),
&(select_dp->T[i]), &null_ok);
if ( select_dp->T[i] <
sizeof(lengths)/sizeof(lengths[0]) )
{
if ( lengths[select_dp->T[i]] )
select_dp->L[i] = lengths[select_dp->T[i]];
else select_dp->L[i] += 5;
}
else select_dp->L[i] += 5;

select_dp->T[i] = 5;
select_dp->V[i] = (char *)malloc( select_dp->L[i] *
array_size );

for( j = MAX_VNAME_LEN-1;
j > 0 && select_dp->S[i][j] == ' ';
j--);
fprintf (stderr,
"%s%.*s", i?",":"", j+1, select_dp->S[i]);
}
fprintf( stderr, "\n" );

EXEC SQL OPEN C;
return select_dp;
}

static void process_2( SQLDA * select_dp, int array_size )
{
int last_fetch_count;
int row_count = 0;
short ind_value;
char * char_ptr, s2[8002];
int i, j, k, l = 0;

for ( last_fetch_count = 0;
;
last_fetch_count = sqlca.sqlerrd[2] )
{
EXEC SQL FOR :array_size FETCH C
USING DESCRIPTOR select_dp;

for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
{
for (i = 0; i < select_dp->F; i++)
{
ind_value = *(select_dp->I[i]+j);
char_ptr = select_dp->V[i] +
(j*select_dp->L[i]);
s2[l = 0] = '"';
for(k = 0; char_ptr[k] != '\0'; k++) {
s2[++l] = char_ptr[k];
if (char_ptr[k] == '"') s2[++l] = '"';
}
s2[++l] = '"';
s2[++l] = '\0';
printf( "%s%s", i?",":"", s2 );
}
row_count++;
printf( "~^@#|\n" );
}
if ( sqlca.sqlcode > 0 ) break;
}

sqlclu(select_dp);

EXEC SQL CLOSE C;

EXEC SQL COMMIT WORK;
fprintf( stderr, "%d rows extracted\n", row_count );
}

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;
SQLDA * select_dp;

process_parms( argc, argv );

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

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

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

EXEC SQL ALTER SESSION
SET NLS_DATE_FORMAT = 'ddmmyyyyhh24miss';

select_dp = process_1( SQLSTMT, atoi(ARRAY_SIZE) );
process_2( select_dp , atoi(ARRAY_SIZE));

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

Make error

Gerald Koerkenmeier, September 22, 2003 - 5:05 pm UTC

Received the following errors when doing a make on the source code. Any ideas?

cc -I. -I/usr/local/opt/oracle/product/8.1.7/precomp/public -I/usr/local/opt/oracle/product/8.1.7/rdbms/public -I/usr/local/opt/oracle/product/8.1.7/rdbms/demo -I/usr/local/opt/oracle/product/8.1.7/plsql/public -I/usr/local/opt/oracle/product/8.1.7/network/public flat_file.c -Wl,+s -Wl,+n -o flat_file -L/usr/local/opt/oracle/product/8.1.7/precomp/lib/ -L/usr/local/opt/oracle/product/8.1.7/lib/ -o flat_file
(Bundled) cc: "flat_file.pc", line 23: error 1705: Function prototypes are an ANSI feature.
(Bundled) cc: "flat_file.pc", line 95: error 1705: Function prototypes are an ANSI feature.
(Bundled) cc: "flat_file.pc", line 168: error 1000: Unexpected symbol: "*".
(Bundled) cc: "flat_file.pc", line 168: error 1705: Function prototypes are an ANSI feature.
(Bundled) cc: "flat_file.pc", line 168: error 1573: Type of "select_dp" is undefined due to an illegal declaration.
(Bundled) cc: "flat_file.pc", line 181: error 1527: Incompatible types in cast: Must cast from scalar to scalar or to void type.
(Bundled) cc: "flat_file.pc", line 186: error 1532: Reference through a non-pointer.
(Bundled) cc: "flat_file.pc", line 186: error 1566: Test expression in for must be scalar.
(Bundled) cc: "flat_file.pc", line 188: error 1532: Reference through a non-pointer.
(Bundled) cc: "flat_file.pc", line 188: error 1528: Subscript expression must combine pointer and integer.
(Bundled) cc: "flat_file.pc", line 188: error 1554: Indirection must be through a pointer.
(Bundled) cc: "flat_file.pc", line 189: error 1532: Reference through a non-pointer.
(Bundled) cc: "flat_file.pc", line 189: error 1528: Subscript expression must combine pointer and integer.
(Bundled) cc: "flat_file.pc", line 190: error 1532: Reference through a non-pointer.
(Bundled) cc: "flat_file.pc", line 190: error 1528: Subscript expression must combine pointer and integer.
(Bundled) cc: "flat_file.pc", line 236: warning 563: Argument #1 is not the correct type.
*** Error exit code 1

Stop.

September 22, 2003 - 8:11 pm UTC

you are not using an ANSI c compiler.

maybe this is HP/UX where the default (for free) compiler is not the ansi c one, which the code requires.

gcc works on many platforms
the system ANSI c compiler (eg: sparcworks on solaris) works.

Its realy v.good

Srikanth, September 29, 2003 - 11:57 pm UTC

Hi tom,

I have gone through the code and its good and I have a question to you that, can I print the same result like 'to shi' format in my report.

each field with some spaces...

could you please help me to know where can I change the format in the code.

Regards
Sri.

September 30, 2003 - 7:05 am UTC

'to shi' ???

but anyway, the code is right there -- you can modify anything at all you want.

One more question

Gerald Koerkenmeier, September 30, 2003 - 1:25 pm UTC

Thanks for the ANSI C tip. When I compile now I get:

cc: "flat_file.c", line 773: warning 604: Pointers are not assignment-compatible.
cc: "flat_file.c", line 773: warning 563: Argument #1 is not the correct type.
cc: "flat_file.c", line 773: warning 604: Pointers are not assignment-compatible.
cc: "flat_file.c", line 773: warning 563: Argument #1 is not the correct type.
/usr/ccs/bin/ld: Unsatisfied symbols:
sqlnul (first referenced in flat_file.o) (code)
sqlclu (first referenced in flat_file.o) (code)
sqlald (first referenced in flat_file.o) (code)
sqlcxt (first referenced in flat_file.o) (code)

Line 773 is:
vstrcpy( oracleid, USERID );

Am I doing something wrong?

September 30, 2003 - 1:51 pm UTC

warnings are OK.

you need to link in our libraries though. see
</code> http://asktom.oracle.com/~tkyte/proc_makefile/ <code>

for an example

create CSV using PRO*C and stored procedure

Manish, October 16, 2003 - 10:31 am UTC

Hi Tom,
I am new to Oracle and Pro*C. I liked your pro*C program to create CSV file but how do I get it running if I have a Stored Procedure ( in a package ) which takes parameters and has a Ref Cursor out parameter.We have a lot of stored procedures like this.
I assume that by passing SQL('sqlstmt=select * from emp') as a parameter will be slower than static stored procedure.
>>>./array_flat userid=scott/tiger 'sqlstmt=select * from emp' arraysize=100

October 16, 2003 - 11:15 am UTC

a ref cursor is just an opened cursor, a cursor opened by plsql.

it'll perform no differently at all then pro*c opening the cursor itself. they are after all just cursors at the end of the day and they'll both have to do the same amount of work.

you can either recode the code to do plsql calls or use it as is, they'll perform pretty much the same as they both just use cursors.

Please help PRO*C CSV using Stored Procedure

Manish, October 16, 2003 - 12:54 pm UTC

The Stored procedures which we have return 15 million rows by 25 columns. I tested your both solutions a) sqlplus ( using dos prompt) b) Pro*C ( above program ). As you mentioned correctly the PRO*C works great!! for huge data coversion to CSV.
you said following to run above program:
>> $ ./array_flat userid=scott/tiger 'sqlstmt=select * from emp' arraysize=100 >
test.dat
How to modify the above PRO*C program so that it can except package.procedure(agr1,arg2..) returning refcursor
and convert the procedure output to CSV.

Please advice.

October 16, 2003 - 5:25 pm UTC

You would read the pro*c docs and go for it. I don't have any examples at hand with dynamic sql and all.

specify date format in SQL

Wor, October 21, 2003 - 2:56 pm UTC

Hi Tom,
I am using PRO*C program and it is very efficient.
I notice that you have specified date format in the PRO*C code. However I tried to specify the format in SQL
e.g. select to_char(DATE_COL,'MM/DD/YYYY') from ...
I modified the following line in pro*c instead of providing date format. sqlstm.stmt = alter SESSION SET NLS_DATE_FORMAT..
to sqlstm.stmt = ""

I get the following error.
ORA-00900: invalid SQL statement

Since this is a generic routine for all queries I would like to specify the date format in the SQL.

Please help.

October 21, 2003 - 5:40 pm UTC

are you saying you would like to pass in the NLS_DATE_FORMAT?

not sure what you are trying to do?

Date format

Wor, October 23, 2003 - 9:52 am UTC

I have many queries and some require different dates formats in the output e.g. mm/dd/yyyy or mm/dd/yyyy hh:mm:ss or DD-MON-YYYY HH24:MI:SS.
What I notice is you have specified the date format in the main () as EXEC SQL ALTER SESSION ..SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
If I have this in pro*c than all the queries will return the same format instead what I want is if I can specify the date format in the query e.g. to_char(date_col,'MM/DD/YYYY')
so that based on the query the date format will be different in the output.

If I use the existing program as is than when I run the command as follows
dos prompt> exe_name userid=usr1/pass1 "sqlstmt=SELECT to_char(DATE_COL,'MM/DD/YYYY') FROM TABLE1" arraysize=100 > test.csv

The program crashes.
When I debugged I found out that the program crashes in
pc file>>
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
exact line in .c file is>>
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);

Can you please help to avoid the program crash and let me know how to specify the date format in the query.
I have windows 2000 setup with oracle 8.1.6

For everthing else the Pro*c works fine...

Please help.

October 23, 2003 - 12:56 pm UTC

> ./array_flat userid=/ arraysize=100 sqlstmt="select to_char( sysdate, 'mm/dd/yyyy hh24:mi:ss month' ) from dual"

Connected to ORACLE as user: /

Unloading 'select to_char( sysdate, 'mm/dd/yyyy hh24:mi:ss month' ) from dual'
Array size = 100
TO_CHAR(SYSDATE,'MM/DD/YYYYHH2
10/23/2003 12:54:12 october
1 rows extracted

could be the length of the column name - try aliasing that with a short name

Thank you very much

A reader, October 23, 2003 - 4:00 pm UTC

It works great!!!!

Array size = 100 or 10000

wor, October 29, 2003 - 5:28 pm UTC

Hi Tom,
I was testing with a very long SQL query ( it returned only 1000 records ) with lots of joins and unions.
approx 4000 characters including spaces.

with array size = 1000 it worked fine but with array size = 100 I got a sqlnet.log with following data

How does array size affect the sql execution can I just increase the size to 10000 or is there any limit or performance hit ?

with array size = 100 following is the sqlnet.log
***********************************************************************
Fatal NI connect error 12560, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=D:\reportsdll\Project1.exe)(HOST=NY-WORLIKAR2K)(USER=WorliMa))))

VERSION INFORMATION:
TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 8.1.6.0.0 - Production
Time: 29-OCT-2003 16:59:44
Tracing not turned on.
Tns error struct:
nr err code: 0
ns main err code: 12560
TNS-12560: TNS:protocol adapter error
ns secondary err code: 0
nt main err code: 530
TNS-00530: Protocol adapter error
nt secondary err code: 126
nt OS err code: 0

October 30, 2003 - 6:44 am UTC

the array size cannot possibly affect a connection.

Not even remotely.

the arraysize is a setting for a query -- doesn't even come into play with connecting. Something else was happening here.

SHARED MEMORY

kom, October 30, 2003 - 10:30 am UTC

Hi Tom,
I am using this pro*C program for running batch queries( same queries with input variables value change ) plus different queries. Each query creates its own .csv file.

The problem is since the proc* excepts dynamic queries
after certain time it completely fills the shared memory. I found out that using Stored procedures this problem does not occur since only one instance of procedure remains in the shared memory even though the input parameter value changes.

But I am extremely impressed by your pro*c program and I would like to definitely use it.
How do I overcome the shared memory problem.
( Ideally only one instance of the query should remain in the shared memory if only the input values change or oracle should auto clean the shared memory )

I checked the shared memory usgae by using
SELECT * FROM sys.V_$SQL_SHARED_MEMORY
WHERE sql_text LIKE 'select OFFICENUMBER%'
..
SELECT * FROM sys.V_$SQL_SHARED_MEMORY
WHERE sql_text LIKE 'select DEPTNUMBER%'

Please help.

October 30, 2003 - 11:24 am UTC

add

exec sql alter session set cursor_sharing=force;

to the proc code after the connect, before the parse.

in addition

kom, October 30, 2003 - 10:58 am UTC

To add to previous question
I am using oracle 8.1.6 on windows 2000.

kom, October 30, 2003 - 2:54 pm UTC

Hi Tom,

>>> exec sql alter session set cursor_sharing=force;

this feature is very good for any dynamic query without bind varables.

why doesn't oracle 8i have this feature set in basic installation . Why should I call this explicitly.

Ideally what oracle should do is:
for any dynamic query withour bind variables it should cursor_sharing=force automatically. Because if not set than that query will get chache unnecessarly.

is there any disadvantage by using this ?
why did you say just session why not at system level ?

October 30, 2003 - 9:59 pm UTC

do you have my book "expert one on one Oracle", if so, read chapter 10.

there are lots of reasons (besides backwards compatibility) that this is not the default.

consider what happens to:

select substr( ename, 1, 10 ) from t;

right now, that is not using binds. but, the parser knows "ahh, 10 characters, describe this query and I'll tell you 10"

using cursor sharing

select substr( ename, :bv1, :bv2 ) from t;

uses binds (un-necessarily!). the parser now knows NOTHING. describe this query and it'll "make up a length". consider what happens to report generators.

or

select * from t where x = 5;

will, x has 2 values. 1,000,000 of them are 6. 2 are 5. that query should use and index, but

select * from t where x = :bv1;

won't (only two values, could be 5 or 6, bummer)

basically, cursor sharing is a CRUTCH to be used to temporarily get you over a BUGGY application that the developers MUST FIX.

Or, as in the case of this generic utility, can be used for this session.

To Kom

Menon, October 30, 2003 - 4:20 pm UTC

Search for cursor_sharing on this site.
In particular read
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5180609822543, <code>

or get Expert one on one by Tom where he explains
the disadvantages of using cursor_sharing.

kom, October 30, 2003 - 6:15 pm UTC

>>>> I read your article saying
in short, cursor sharing is a crutch that MIGHT be useful in some cases to help a poorly written program survive for the period of time the developers are
hard at work CORRECTING their bug and putting bind variable support into their application
>>>>>>
After reading this now I am afraid to use cursor_sharing=force since I am developing new robust application.

I really love the speed of your pro*c program.
How do I modify your Pro*c program to support bind variables.

Please help.

October 30, 2003 - 10:03 pm UTC

this pro*c program is just a data dumper, very simple, very straightforward, not really designed to dump 1,000 different queries.

do you need to call this "from the command line" -- if so, cursor_sharing=force is correct.

would you be calling this as an API from a c program? if so, we can do better.

but as a single command line program, it would be OK with cursor sharing

kom, October 31, 2003 - 9:54 am UTC

>> would you be calling this as an API from a c program? if so, we can do better

yes you are correct. I liked your pro*c code so much that I created a "C" dll to have more control over execution. I created a new func CreateCSV and exported that func using .def file. I changed the main func to mainnew and instead of excepting argv argc and am passing parameters to this func from the exported funtion CreateCSV.
This CreateCSV( char* strDSN, char* strSQL, char* strArrSize, char* strFileName is called from VB dll as an API call.

I need to make this more efficient to handle many dynamic queries with bind variable support.

also I have alot of stored procedures (in packages ) how do I handle them ( if possible ) .

November 01, 2003 - 11:51 am UTC

I'd change the parameter list to include:

char * bindValues[]

and then some code like:

set_bind_variables(char * bindValues[] )
{
int size = 10;
int i;
char bvname[255];
SQLDA * bind_dp;

if ((bind_dp = sqlald(size, 255, 255)) == NULL)
die( "Cannot allocate memory for bind descriptor" );

bind_dp->N = size;
EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;
if ( sqlca.sqlcode ) return sqlerror_soft(__LINE__);

if (bind_dp->F < 0)
{
size = -bind_dp->F;
sqlclu(bind_dp);
if ((bind_dp = sqlald(size, 255, 255)) == NULL)
die( "Cannot allocate memory for bind descriptor" );

bind_dp->N = size; /* Initialize count of array elements. */
EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;
if ( sqlca.sqlcode ) return sqlerror_soft(__LINE__);
}

bind_dp->N = bind_dp->F;
for (i = 0; i < bind_dp->N; i++)
bind_dp->I[i] = (short *) malloc(sizeof (short));

for (i = 0; i < bind_dp->F; i++)
{
bind_dp->V[i] = bindValues[i];
bind_dp->L[i] = strlen(bind_dp->V[i]);
*bind_dp->I[i] = (*bind_dp->V[i]) ? 0 : -1;
if ( *bind_dp->I[i] )
{
bind_dp->V[i] = (char *)malloc(5);
bind_dp->L[i] = 4;
}
bind_dp->T[i] = 1;
}
EXEC SQL OPEN C USING DESCRIPTOR bind_dp;
if ( sqlca.sqlcode ) return sqlerror_soft(__LINE__);

for( i = 0; i < bind_dp->N; i++ ) free( bind_dp->I[i] );
sqlclu( bind_dp );
}

and then you can pass queries like:

select * from t where vc = :x and num = to_number(:y)
and dt = to_date(:z,'dd-mon-yyyy hh24:mi:ss' )

(the use of TO_NUMBER and TO_DATE being very very very important since we are generically binding only strings!!!!)

have you glanced through the pro*c docs? Not sure what you want to do with the plsql stored procedures, but you basically just parse and execute them, you wouldn't fetch from them.... but not much different then the existing code.

set_bind_variables(char * bindValues[] )

kom, November 03, 2003 - 12:26 pm UTC

Hi Tom,
I am really very sorry to trouble you. I am completely new to Pro*C and my project deadline has arrived. I am confused about where to call set_bind_variables(char * bindValues[] ). should I call in main (..) before
select_dp = process_1( SQLSTMT, atoi(ARRAY_SIZE) );
process_2( select_dp , atoi(ARRAY_SIZE));
or should I not call process_1 and process2 at all.
How do I integrate this new bind function into existing code.

November 03, 2003 - 6:05 pm UTC

sorry -- don't have time to code it right now, on the road.

use the alter session set cursor_sharing=force, it'll work just dandy for now.

Bind Variables IN clause

kom, November 04, 2003 - 5:26 pm UTC

Hi Tom,
I could get the bind variables working. ( Thanx alot for that function )

Is there a way I can bind the IN clause.
e..g SELECT * FROM table1 where col1 IN ('abc','pqr') to something SELECT * FROM table1 where col1 IN (:x) for both VARCHAR and INT.
is that possible using pro*c ?

November 05, 2003 - 7:50 am UTC

search this site for

variable in list

Great!! thanks

kom, November 05, 2003 - 11:10 am UTC

I just love your web-site. It just gives me a feeling that nothing is Impossible and there is always a room for improving efficiency.

Strored Procedures

kom, November 06, 2003 - 9:46 am UTC

Hi Tom,
I am successfully running the PRO*C program ( I created a C dll ) via VB as an API call for Dynamic queries including bind variable support.

I also have a lot of Stored procedures in packages.
I wrote another Pro*c program to handle stored procedures.
the main parts of the program are as follows..
e.g.
I declared 2 variables like this...
EXEC SQL BEGIN DECLARE SECTION;
SQL_CURSOR chk_access; ( this is ref cursor returned from SP )
char firstn [15];
char lastn [15];
long inputVar; ( input variable for SP )
EXEC SQL END DECLARE SECTION;
then ...
/* Execute stored functio to open cursor for recordset */
EXEC SQL ALLOCATE :chk_access;
EXEC SQL EXECUTE
BEGIN
:inputVar := 12;
:chk_access := TEST_MAN.TEST(:inputVar);
END;
END-EXEC;
for ( ;; )
{
EXEC SQL fetch :chk_access
into :firstn,:lastn
;
if ( sqlca.sqlcode)
break;

printf ("%s,%s\n",firstn,lastn);
}

problem:
1) This cannot be generic because I have to declare array variables in advance. This program can call any procedure which will return refcursor. e.g. firstn [15] and lastn [15];. I do not know the size of variables returned by ref cursor in advance.
2) It takes alot of time to generate a csv file. around 10 times plus more slower than if I run the same sql using your pro*c program.
Is there any way that I can just modify your exisiting pro*c code to execute stored procedures. with lightning fast speed.

Please help

kom, November 10, 2003 - 11:04 am UTC

Hi Tom,
I read several pro*c documents and I find similar code for packages and stored procedures.
As I mentioned earlier ( my previous question ) that with this approach I noticed 2 problems:
1) Speed is extremely slow compared to your pro*c program.
2) Cannot be generic

Please let me know whether and how it is possible to overcome the above problems.

Thanks in advance,
Kom

November 10, 2003 - 12:13 pm UTC

for dumping data to disk like this, C is going to naturally be faster and more efficient the plsql.

It can however be generic. See
</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

Clob

Pal, November 10, 2003 - 6:03 pm UTC

Hello Tom,

Will this pro*c program work if I have a CLOB column in the select statement.
If possible than how do I modify the program.

Thanks
Pal

November 10, 2003 - 7:40 pm UTC

you would have to process the lob specially (pro*c docs describe how)

but most clobs have NEWLINES in them, makeing them not very suitable for a generic "dump into csv"

Add SQLLDR control information

Gerald Koerkenmeier, November 12, 2003 - 4:45 pm UTC

November 13, 2003 - 6:30 am UTC

you'd have to modify this code:

...
for( j = MAX_VNAME_LEN-1;
j > 0 && select_dp->S[i][j] == ' ';
j--);
fprintf (stderr,
"%s%.*s", i?",":"", j+1, select_dp->S[i]);
}
fprintf( stderr, "\n" );
....

which shows how to access the column names -- and print it out.

getting the tablename -- that is upto you (since array_flat here works on arbitrary QUERIES -- there can and often is more then one table referenced!). So you might:

printf( "load data infile * into table XXXXX replace fields ... (\n" );
print out column names
printf( ")\nbegindata\n" );

and just edit the file to replace the XXXXX or add an additional parameter to the program -- the name of the table you want to put there.

Multi-user

Wor, December 02, 2003 - 10:11 pm UTC

Hi Tom,
I have converted the above pro*C code into a C DLL which I call from VB component which is called from ASP.
The VB component is in COM+. I am calling this C dll using standard C API calling conventions from VB. I have exported a C funtion which wraps all the above Pro*c funtions.

Win2000 and oracle 8.1.6

C dll generates a .csv file with the name and path specified by VB dll, basically a online report.

When this whole process is called sequetially say 2000 times or more one after another in a loop it works great!!.
say e.g. user1 click on the browser button "generate report"
ASP->calls->VB->calls->C DLL and the report is ready.
again user1 clicks button "generate report" like this 2000 times or more... works great!!

Problem:
When 2 or more users click the button at the same time then
2 or more instances of VB component is created in COM+
and both try to call C DLL using API call together and the PRO*C crashes...
Is this a problem of multi-threading ?
I read your article on multi-threading but in my case the userid/pass will always be same only and the simultaneous calls are made from VB.
note: same userid / pass are passed by VB to C DLL.

do you want me to post my pro*C code..
how do I change the above code for multi-user access.
Please help..

December 03, 2003 - 6:47 am UTC

depending on how big the "report" is, i would use the PLSQL implementation, see

</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

but, each extproc runs in its own address space (you should see 2 extproc.exe's out there). they are not "threads" in the oracle process.

so, i would put in copious debug (if you have my book expert one on one Oracle -- I demonstrate that heavily in the chapter on external procedures) and try to find out WHERE it is crashing.

Concurrent Users, Data Unloading using Pro*C

Wor, January 07, 2004 - 10:53 am UTC

Hi Tom,
To continue with my previous post.
My reports can run from 1 record 1 colum to 10 Million and 25 columns. Since I need faster speed I want to implement using Pro*C.
As I previously said my workflow
User action on IE browser->VB component in comp+ ( calls C dll using declare API provided by VB )-> C.dll ( pro*C code )-> generates the .csv file.
Everything works fine with one user at a time ( similar to what you mentioned using command prompt, one at a time )
But incase multi users try to create .csv files concurrently than data retrieved in select_dp gets courrupted in between the csv file creation.
I have your book and in chapter "Data Loading->topic Unload Data" you mentioned that the code for pro*C is on Wrox web-site. can you give me exact URL.
Since the select_dp gets corrupted can you please show me example how to modify the existing pro*C code to handle concurrent request for csv files ( e.g if contexts have to be used, how to declare select_dp like structures etc )
As always you are the BEST....
Thanks in advance,
Wor

January 07, 2004 - 6:23 pm UTC

you are in windoze dll hell.

dll's are NOT re-entrant. they are not nice like shared object code on unix and such...

You need a C programmer there to help you out. it is bigger then a bread box.

</code> http://asktom.oracle.com/~tkyte/flat.html <code>

has pointers to the code.

in addition...

Wor, January 07, 2004 - 2:15 pm UTC

In addition to my previous question
I see a line
static unsigned long sqlctx = 33013571;
in my .C file generated by Pro*C compiler ( this is not there in .pc file )
Is this one more cause of problem ? If so how do I solve it?
Regards,
Wor

January 08, 2004 - 11:05 am UTC

you would have to code using "threads=y" and use contexts (instead of a single global context) and other things to make the code RE-ENTRANT. Windows is not a friendly platform for this. I would suggest OCI, much easier to remove the re-entrancy issue.

Each user would need their own connection to the database, their own context. You would have to be thread safe, re-entrant.

Either that or just serialize the entire thing -- use a mutex to make sure one person at a time is dumping to disk.

Wor

wor, January 08, 2004 - 8:10 am UTC

Hi Tom,
1) The link does not work. It says page cannot be found.
Please send me the good URL.
2) Also what I observe is there are lot of global variables generated in .c file, is this the cause too ?

You said >>
You need a C programmer there to help you out. it is bigger then a bread box.
</code> http://asktom.oracle.com/~tkyte/flat.html <code>
has pointers to the code.

A reader, January 08, 2004 - 9:08 am UTC

</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

MP, January 08, 2004 - 12:42 pm UTC

Useful, and as is often the case, entertaining. The only jarring note -- and it's frequent -- is the use of the word "then" when you mean "than." Maybe it's a keyboard problem.

January 08, 2004 - 3:24 pm UTC

tis a pebkac problem actually. i have a really hard time with those two for some reason. i'll just try to stop using them.

some info for Pro*C options

Andrey Prokopenko, January 14, 2004 - 12:58 pm UTC

key option that user must set for this code is
parse=full
by some unknown reason in windoze this parameter is set
to parse=none

Coredump

Gerald Koerkenmeier, August 18, 2004 - 3:45 pm UTC

Well I have the program running but I am getting a coredump. I guess I need to get some help from a C developer, eh?

rrs4sfd:/home/gk7692/test>flat_file userid=rrs/piasa4 sqlstmt='select * from acna_ref' arraysize=100 > acna_ref.dat

Connected to ORACLE as user: rrs/xxxxxxx

Unloading 'select * from acna_ref'
Array size = 100
COMPANY,ACNA_SEGMENT_CODE
Memory fault(coredump)

August 18, 2004 - 11:36 pm UTC

did you change the code?

was does acna_ref look like?

what OS?

what compiler?

Coredump

Gerald Koerkenmeier, August 23, 2004 - 12:37 pm UTC

Sorry about the lack of info. No code change. HPUX 11. ANSI C compiler.

ACNA_REF:

Name Null? Type
----------------- -------- ------------
COMPANY NOT NULL VARCHAR2(18)
ACNA_SEGMENT_CODE NOT NULL VARCHAR2(3)

August 23, 2004 - 1:05 pm UTC

ok, looks like process_1 was "ok", but somewhere in process_2 it is bombing.

I don't see anything obvious -- so, suggest you liter process_2 with:

printf( "%d\n", __LINE__ );

calls

maybe like this:

static void process_2( SQLDA * select_dp, int array_size )
{
int last_fetch_count;
int row_count = 0;
short ind_value;
char * char_ptr;
int i,
j;

printf( "%d\n", __LINE__ );
for ( last_fetch_count = 0;
;
last_fetch_count = sqlca.sqlerrd[2] )
{
printf( "%d\n", __LINE__ );
EXEC SQL FOR :array_size FETCH C
USING DESCRIPTOR select_dp;
printf( "%d\n", __LINE__ );

for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
{
printf( "%d\n", __LINE__ );
for (i = 0; i < select_dp->F; i++)
{
printf( "%d\n", __LINE__ );
ind_value = *(select_dp->I[i]+j);
printf( "%d\n", __LINE__ );
char_ptr = select_dp->V[i] +
(j*select_dp->L[i]);
printf( "%d\n", __LINE__ );

printf( "%s%s", i?",":"",
ind_value?"":char_ptr );
printf( "%d\n", __LINE__ );
}
printf( "%d\n", __LINE__ );
row_count++;
printf( "%d\n", __LINE__ );
printf( "\n" );
printf( "%d\n", __LINE__ );
}
printf( "%d\n", __LINE__ );
if ( sqlca.sqlcode > 0 ) break;
printf( "%d\n", __LINE__ );
}

printf( "%d\n", __LINE__ );
sqlclu(select_dp);
printf( "%d\n", __LINE__ );

EXEC SQL CLOSE C;
printf( "%d\n", __LINE__ );

EXEC SQL COMMIT WORK;
printf( "%d\n", __LINE__ );
fprintf( stderr, "%d rows extracted\n", row_count );
printf( "%d\n", __LINE__ );
}

see what was the last line printed out -- and post that snippet of source code.

Coredump

Gerald Koerkenmeier, August 23, 2004 - 2:07 pm UTC

Tom - Now I am getting the following error when I run make on the old or the new Pro*C code:

rrs4sfd:/home/gk7692>make
proc ireclen=255 lines=yes include=/usr/local/opt/oracle/product/rrst/proc/lib iname=flat_file

Pro*C/C++: Release 9.2.0.5.0 - Production on Mon Aug 23 13:54:01 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

System default option values taken from: /usr/local/opt/oracle/product/rrst/precomp/admin/pcscfg.cfg

/opt/ansic/bin/cc -I. -g -c flat_file.c
cc: "flat_file.pc", line 224: warning 604: Pointers are not assignment-compatible.
cc: "flat_file.pc", line 224: warning 563: Argument #1 is not the correct type.
cc: "flat_file.pc", line 224: warning 604: Pointers are not assignment-compatible.
cc: "flat_file.pc", line 224: warning 563: Argument #1 is not the correct type.
/opt/ansic/bin/cc -Wl,+s -Wl,+n +DA2.0W +DS2.0 -o flat_file -L/usr/local/opt/oracle/product/rrst/precomp/lib/ -L/usr/local/opt/oracle/product/rrst/lib/ -t -o flat_file \
flat_file.o -L/usr/local/opt/oracle/product/rrst/lib -lclntsh `cat /usr/local/opt/oracle/product/rrst/lib/ldflags` `cat /usr/local/opt/oracle/product/rrst/lib/sysliblist` -lm -lpthread -lpthread
cc: error 1400: Option t usage: -t c,name where c may be 1 or more of pc0al.
ld: Cannot specify input file (flat_file) that is the same as the output file.
Fatal error.
*** Error exit code 1

Stop.

Here is my makefile:

CC=/opt/ansic/bin/cc
TARGET=flat_file
SOURCE=flat_file.pc

$(TARGET): $(SOURCE) $(SOURCE:.pc=.c) $(SOURCE:.pc=.o)
$(CC) $(LDFLAGS) -t -o $(TARGET) \
$(SOURCE:.pc=.o) -L$(ORACLE_HOME)/lib $(PROLDLIBS)

include proc.mk

PROCFLAGS= ireclen=255 lines=yes $(PROC_ENV_FLAGS) \
include=$(ORACLE_HOME)/proc/lib
PROFLAGS=$(PROCFLAGS)

CFLAGS=-I. -g $(CC_ENV_FLAGS)

Any ideas? Sorry to trouble you

August 23, 2004 - 2:10 pm UTC

try to make the sample proc apps and see what CC flags they use on your platform.

$ORACLE_HOME/precomp/demo/proc

$ make -f demo_proc.mk sample1

Coredump

Gerald Koerkenmeier, August 23, 2004 - 2:23 pm UTC

Here are the results:

/usr/ccs/bin/make -f /usr/local/opt/oracle/product/rrst/precomp/demo/proc/demo_proc.mk OBJS=sample1.o EXE=sample1 build
/usr/ccs/bin/make -f /usr/local/opt/oracle/product/rrst/precomp/demo/proc/demo_proc.mk PROCFLAGS="" PCCSRC=sample1 I_SYM=include= pc1
proc iname=sample1 include=. include=/usr/local/opt/oracle/product/rrst/precomp/public include=/usr/local/opt/oracle/product/rrst/rdbms/public include=/usr/local/opt/oracle/product/rrst/rdbms/demo include=/usr/local/opt/oracle/product/rrst/plsql/public include=/usr/local/opt/oracle/product/rrst/network/public

Pro*C/C++: Release 9.2.0.5.0 - Production on Mon Aug 23 14:11:51 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

System default option values taken from: /usr/local/opt/oracle/product/rrst/precomp/admin/pcscfg.cfg

cc +DA2.0W +DS2.0 -DSS_64BIT_SERVER -I. -I/usr/local/opt/oracle/product/rrst/precomp/public -I/usr/local/opt/oracle/product/rrst/rdbms/public -I/usr/local/opt/oracle/product/rrst/rdbms/demo -I/usr/local/opt/oracle/product/rrst/plsql/public -I/usr/local/opt/oracle/product/rrst/network/public -c sample1.c
cc +DA2.0W -o sample1 sample1.o -L/usr/local/opt/oracle/product/rrst/lib/ -lclntsh `cat /usr/local/opt/oracle/product/rrst/lib/ldflags` `cat /usr/local/opt/oracle/product/rrst/lib/sysliblist` -lm -lpthread -lpthread

August 23, 2004 - 3:08 pm UTC

so, use those options to the compiler -- just use cc, and +DA2.0W +DS2.0 -DSS_64BIT_SERVER -I.

Coredump

Gerald Koerkenmeier, August 23, 2004 - 4:20 pm UTC

Ok - I compiled the new code with the printf statements throughout process_2, and got the same results and no debugging output:

rrs4sfd:/home/gk7692>flat_file userid=rrs/piasa4 sqlstmt='select * from
> acna_ref' arraysize=100 > acna_ref.dat

Connected to ORACLE as user: rrs/piasa4

Unloading 'select * from
acna_ref'
Array size = 100
COMPANY,ACNA_SEGMENT_CODE
Memory fault(coredump)

August 23, 2004 - 4:42 pm UTC

ok, can you instrument process_1 in a similar fashion?

need to narrow down where the crash is happening to see if that helps me figure out what I did wrong!

Coredump

Gerald Koerkenmeier, August 23, 2004 - 4:44 pm UTC

I take that back - I was directing output to a file.

The last line listed in 181, which begins here:

NOTES
**************************************************************
*** ***
*** This file is SOSD. Porters must change the data types ***
*** appropriately on their platform. See notes/pcport.doc ***
*** for more information. ***
*** ***
**************************************************************

If the symbol SQLCA_STORAGE_CLASS is defined, then the SQLCA
will be defined to have this storage class. For example:

#define SQLCA_STORAGE_CLASS extern

will define the SQLCA as an extern.

If the symbol SQLCA_INIT is defined, then the SQLCA will be
statically initialized. Although this is not necessary in order
to use the SQLCA, it is a good pgming practice not to have
unitialized variables. However, some C compilers/OS's don't
allow automatic variables to be init'd in this manner. Therefore,
if you are INCLUDE'ing the SQLCA in a place where it would be
an automatic AND your C compiler/OS doesn't allow this style
of initialization, then SQLCA_INIT should be left undefined --
all others can define SQLCA_INIT if they wish.

If the symbol SQLCA_NONE is defined, then the SQLCA variable will
not be defined at all. The symbol SQLCA_NONE should not be defined
in source modules that have embedded SQL. However, source modules
that have no embedded SQL, but need to manipulate a sqlca struct
passed in as a parameter, can set the SQLCA_NONE symbol to avoid
creation of an extraneous sqlca variable.

MODIFIED
lvbcheng 07/31/98 - long to int
jbasu 12/12/94 - Bug 217878: note this is an SOSD file
losborne 08/11/92 - No sqlca var if SQLCA_NONE macro set
Clare 12/06/84 - Ch SQLCA to not be an extern.
Clare 10/21/85 - Add initialization.
Bradbury 01/05/86 - Only initialize when SQLCA_INIT set
Clare 06/12/86 - Add SQLCA_STORAGE_CLASS option.
*/

#ifndef SQLCA
#define SQLCA 1

struct sqlca
{
/* ub1 */ char sqlcaid[8];
/* b4 */ int sqlabc;
/* b4 */ int sqlcode;
struct
{
/* ub2 */ unsigned short sqlerrml;
/* ub1 */ char sqlerrmc[70];
} sqlerrm;
/* ub1 */ char sqlerrp[8];
/* b4 */ int sqlerrd[6];
/* ub1 */ char sqlwarn[8];
/* ub1 */ char sqlext[8];
};

#ifndef SQLCA_NONE
#ifdef SQLCA_STORAGE_CLASS
SQLCA_STORAGE_CLASS struct sqlca sqlca
#else
struct sqlca sqlca
#endif

#ifdef SQLCA_INIT
= {
{'S', 'Q', 'L', 'C', 'A', ' ', ' ', ' '},
sizeof(struct sqlca),
0,
{ 0, {0}},
{'N', 'O', 'T', ' ', 'S', 'E', 'T', ' '},
{0, 0, 0, 0, 0, 0},
{0, 0, 0, 0, 0, 0, 0, 0},

August 23, 2004 - 4:48 pm UTC

get it from the .pc file please......

Coredump

Gerald Koerkenmeier, August 23, 2004 - 4:52 pm UTC

Sorry - Line 181 is the second printf:

static void process_2( SQLDA * select_dp, int array_size )
{
int last_fetch_count;
int row_count = 0;
short ind_value;
char * char_ptr;
int i,
j;
printf( "%d\n", __LINE__ );
for ( last_fetch_count = 0;
;
last_fetch_count = sqlca.sqlerrd[2] )
{
printf( "%d\n", __LINE__ );
EXEC SQL FOR :array_size FETCH C
USING DESCRIPTOR select_dp;
printf( "%d\n", __LINE__ );
for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
{

August 23, 2004 - 7:42 pm UTC

what does this output?

static SQLDA * process_1(char * sqlstmt, int array_size )
{
SQLDA * select_dp;
int i;
int j;
int null_ok;
int precision;
int scale;
int size = 10;

fprintf( stderr, "Unloading '%s'\n", sqlstmt );
fprintf( stderr, "Array size = %d\n", array_size );

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :sqlstmt;
EXEC SQL DECLARE C CURSOR FOR S;

if ((select_dp = sqlald(size,MAX_VNAME_LEN,MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for select descriptor." );

select_dp->N = size;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
if ( !select_dp->F ) return NULL;

if (select_dp->F < 0)
{
size = -select_dp->F;
sqlclu( select_dp );
if ((select_dp =
sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for descriptor." );
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
}
select_dp->N = select_dp->F;

fprintf( stderr, "this has %d columns\n", select_dp->N );

for (i = 0; i < select_dp->N; i++)
select_dp->I[i] = (short *) malloc(sizeof(short) *
array_size );

for (i = 0; i < select_dp->F; i++)
{
fprintf( stderr, "length[%d] was %d\n",i, select_dp->L[i] );
sqlnul (&(select_dp->T[i]),
&(select_dp->T[i]), &null_ok);
if ( select_dp->T[i] <
sizeof(lengths)/sizeof(lengths[0]) )
{
if ( lengths[select_dp->T[i]] )
select_dp->L[i] = lengths[select_dp->T[i]];
else select_dp->L[i] += 5;
}
else select_dp->L[i] += 5;
fprintf( stderr, "setting length[%d] = %d\n", i, select_dp->L[i] );

select_dp->T[i] = 5;
select_dp->V[i] = (char *)malloc( select_dp->L[i] *
array_size );

for( j = MAX_VNAME_LEN-1;
j > 0 && select_dp->S[i][j] == ' ';
j--);
fprintf (stderr,
"%s%.*s", i?",":"", j+1, select_dp->S[i]);
}
fprintf( stderr, "\n" );

EXEC SQL OPEN C;
return select_dp;
}

static void process_2( SQLDA * select_dp, int array_size )
{
int last_fetch_count;
int row_count = 0;
short ind_value;
char * char_ptr;
int i,
j;

for ( last_fetch_count = 0;
;
last_fetch_count = sqlca.sqlerrd[2] )
{
fprintf( stderr, "going to fetch %d rows...\n", array_size );
EXEC SQL FOR :array_size FETCH C
USING DESCRIPTOR select_dp;
fprintf( stderr, "fetched %d rows...\n", sqlca.sqlerrd[2]-last_fetch_count);

for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
{
for (i = 0; i < select_dp->F; i++)
{
ind_value = *(select_dp->I[i]+j);
char_ptr = select_dp->V[i] +
(j*select_dp->L[i]);

printf( "%s%s", i?",":"",
ind_value?"":char_ptr );
}
row_count++;
printf( "\n" );
}
if ( sqlca.sqlcode > 0 ) break;
}

sqlclu(select_dp);

EXEC SQL CLOSE C;

EXEC SQL COMMIT WORK;
fprintf( stderr, "%d rows extracted\n", row_count );
}

Coredump

Gerald Koerkenmeier, August 24, 2004 - 10:15 am UTC

rrs4sfd:/home/gk7692>flat_file userid=rrs/piasa3 sqlstmt='select * from acna_ref' arraysize=100

Connected to ORACLE as user: rrs/piasa4

273
Unloading 'select * from acna_ref'
Array size = 100
this has 2 columns
length[0] was 18
setting length[0] = 23
COMPANYlength[1] was 3
setting length[1] = 8
,ACNA_SEGMENT_CODE
275
going to fetch 100 rows...
Memory fault(coredump)

August 24, 2004 - 10:42 am UTC

beat me with a stick, no clue. I cannot reproduce, haven't had an issue -- haven't heard of anyone having an issue.

everything looks AOK, that was what I was expecting to see. I don't see anything "wrong" in the code.

what does your SQLDA look like? (cut and paste from the .c file -- search for struct SQLDA to find it.) only thing i can think is it might be looking different from mine, just need to verify.

Coredump

Gerald Koerkenmeier, August 24, 2004 - 10:56 am UTC

struct SQLDA {
/* ub4 */ int N; /* Descriptor size in number of entries */
/* text** */ char **V; /* Ptr to Arr of addresses of main variables */
/* ub4* */ int *L; /* Ptr to Arr of lengths of buffers */
/* sb2* */ short *T; /* Ptr to Arr of types of buffers */
/* sb2** */ short **I; /* Ptr to Arr of addresses of indicator vars */
/* sb4 */ int F; /* Number of variables found by DESCRIBE */
/* text** */ char **S; /* Ptr to Arr of variable name pointers */
/* ub2* */ short *M; /* Ptr to Arr of max lengths of var. names */
/* ub2* */ short *C; /* Ptr to Arr of current lengths of var. names */
/* text** */ char **X; /* Ptr to Arr of ind. var. name pointers */
/* ub2* */ short *Y; /* Ptr to Arr of max lengths of ind. var. names*/
/* ub2* */ short *Z; /* Ptr to Arr of cur lengths of ind. var. names*/
};

typedef struct SQLDA SQLDA;

#endif

/* ----------------- */
/* defines for sqlda */
/* ----------------- */

#define SQLSQLDAAlloc(arg1, arg2, arg3, arg4) sqlaldt(arg1, arg2, arg3, arg4)

#define SQLSQLDAFree(arg1, arg2) sqlclut(arg1, arg2)

#line 17 "flat_file.pc"

extern SQLDA *sqlald();
extern void sqlclu();

August 24, 2004 - 11:12 am UTC

sorry -- i've no idea, i don't think i'll be able to "fix" this as I cannot reproduce on my side at all. everything "looks ok", i must have a bug in there somewhere that I just cannot see.

does it happen with arraysize=1?

Coredump

Gerald Koerkenmeier, August 24, 2004 - 11:15 am UTC

Unfortunately, yes. 1, 100, 1000 - all coredump.

Thanks for your time on this! I will try to find someone here in my organization who is familiar with C or Pro*C and can give me a hand. Your guidance on this has already started me in the right direction.

Thanks again!

August 24, 2004 - 11:17 am UTC

try sample10.pc in the distribution and see what it does. it is basically the shell i used. if it crashes, it would be something wrong....

Coredump

Gerald Koerkenmeier, August 24, 2004 - 11:28 am UTC

Sample10 compiles and runs fine:

rrs4sfd:/home/gk7692>make -f proc.mk sample10
/usr/ccs/bin/make -f /usr/local/opt/oracle/product/rrst/precomp/demo/proc/demo_proc.mk OBJS=sample10.o EXE=sample10 build
/usr/ccs/bin/make -f /usr/local/opt/oracle/product/rrst/precomp/demo/proc/demo_proc.mk PROCFLAGS="" PCCSRC=sample10 I_SYM=include= pc1
proc iname=sample10 include=. include=/usr/local/opt/oracle/product/rrst/precomp/public include=/usr/local/opt/oracle/product/rrst/rdbms/public include=/usr/local/opt/oracle/product/rrst/rdbms/demo include=/usr/local/opt/oracle/product/rrst/plsql/public include=/usr/local/opt/oracle/product/rrst/network/public

Pro*C/C++: Release 9.2.0.5.0 - Production on Tue Aug 24 11:14:34 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

System default option values taken from: /usr/local/opt/oracle/product/rrst/precomp/admin/pcscfg.cfg

cc +DA2.0W +DS2.0 -DSS_64BIT_SERVER -I. -I/usr/local/opt/oracle/product/rrst/precomp/public -I/usr/local/opt/oracle/product/rrst/rdbms/public -I/usr/local/opt/oracle/product/rrst/rdbms/demo -I/usr/local/opt/oracle/product/rrst/plsql/public -I/usr/local/opt/oracle/product/rrst/network/public -c sample10.c
cc: "sample10.c", line 1249: warning 728: Argument #1 converts long* to int*.
cc +DA2.0W -o sample10 sample10.o -L/usr/local/opt/oracle/product/rrst/lib/ -lclntsh `cat /usr/local/opt/oracle/product/rrst/lib/ldflags` `cat /usr/local/opt/oracle/product/rrst/lib/sysliblist` -lm -lpthread -lpthread

This wouldn't be related to the compile warnings I get with flat_file would it?

cc: "flat_file.pc", line 263: warning 604: Pointers are not assignment-compatible.
cc: "flat_file.pc", line 263: warning 563: Argument #1 is not the correct type.
cc: "flat_file.pc", line 263: warning 604: Pointers are not assignment-compatible.
cc: "flat_file.pc", line 263: warning 563: Argument #1 is not the correct type.

August 24, 2004 - 11:45 am UTC

what is your line 263...?

Coredump

Gerald Koerkenmeier, August 24, 2004 - 11:56 am UTC

In main:

vstrcpy( oracleid, USERID );

August 24, 2004 - 3:17 pm UTC

dont' worry about that -- oracleid has an unsigned char *, USERID is a signed char *, it is "safe"

Oracle internal error trapping

Peter, September 07, 2004 - 10:27 am UTC

Tom,

I am using this Pro*C programs in my data extracts. I have compiled executables and I am running multiple instances of these executables using a simple DOS batch file.

I have run the various instances against 5 different tables atleast 100 times by now and the program crashed 3 times.

I am able to trap errors in the sql select statement or errors in username/pwd. But, I am not able to trap internal errors. On one scenario the DBA was running a defrag job against the table i was extracting and my program failed. But I couldnt trap the error. Later from the time of the crash and the time of the defrag job we have assumed that to be the reason. I restarted the exes and they extracted data successfully.

Again this weekend there was a mysterious crash and I restarted all the instances again and it completed successfully during my re-run. This time I am not able to find anything reasonable on the server that might have caused the intial crash.

All 3 mysterious crashes have occured only against the same table. Any light on the problem or suggestion to trap errors. The speed of the program is amazing but I cant move the code to production until I stop the crashes.

Thanks for all your help,
Peter

September 07, 2004 - 12:06 pm UTC

define "crash" please -- is it "gpf'ing" or whatever it is called in windows?

if it were to get an oracle error, they are all caught and printed out from sqlerror_hard - onto stderr (the console). Maybe you just are not seeing them.

you could change the code to write error messages to a file instead of stderr.

ARRAYSIZE

A reader, September 07, 2004 - 1:48 pm UTC

Tom,

The code works brilliantly. I'm able to download ~5 million data in 15 odd mins.
What I wanted to know, can we play around with the arraysize parameter (from 100 to 5000) to make it even more faster ??

Its already fast than any other solutions.
But still :o)

Thanks,

September 07, 2004 - 2:25 pm UTC

i made arraysize a parameter for the sole reason that you would be able to play around with different values! go for it.

Peter, September 07, 2004 - 3:25 pm UTC

Tom,

It was not a GPF. The application just got killed.

This is the error message found in Oracle user trace files.

Fri Sep 3 14:59:08 2004
alter database datafile '/edwpro/data03/chm_insight06.dbf' resize 21770M
ORA-3297 signalled during: alter database datafile '/edwpro/data03/chm_insigh...

Looks like my query was running against the data file which was being resized.

September 07, 2004 - 3:39 pm UTC

that has nothing to do with you....

[tkyte@xtkyte-pc tkyte]$ oerr ora 3297
03297, 00000, "file contains used data beyond requested RESIZE value"
// *Cause: Some portion of the file in the region to be trimmed is
// currently in use by a database object
// *Action: Drop or move segments containing extents in this region prior to
// resizing the file, or choose a resize value such that only free
// space is in the trimmed.

they just tried to shrink the file smaller than it could go.

suggestion: log the errors to a FILE to see what the error was.

Help with Compiling

Scott P., September 22, 2004 - 4:04 pm UTC

I was able to use proc to create the .c file, but I dont have a C compiler. So I downloaded a free one "Miracle C" and I get the error "line 8: void struct member illegal
'struct sql_cursor { unsigned int curocn'
aborting compile"

Any suggestion on what compiler to use or why I am getting this error?
Thanks.

September 22, 2004 - 5:45 pm UTC

guess you would have to ask "miracle c" why? i've never even heard of them -- gcc, sure...

csv soft where I am looking for some help NOW

andré, November 18, 2004 - 12:53 pm UTC

I am many email to be sent to autoResponder,and I need some info about ( csv soft where to help my ,tahnk toyou andré

When I download very big size data, about 60GigaBytes ,how can I do?

KIM, TAE HO, November 30, 2004 - 2:20 am UTC

Tom,
When I download very big size data, about 60GigaBytes ,how can I do?

I used program ,flatten.pc, for downloading but
downloading stoped because of unknown error (no message).

How can I do for downloading of Big-file.

cf) I use nohup option,but sql was included datafile's header.
Best Regards

usage : user.sh > /filepath/out.txt
user.sh's content format :
./module_of_unload userid=user/password sqlstmt="sql source"

used source -->>>:
#include <stdio.h>
#include <string.h>
#include <ctype.h>

#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30

static char * USERID = NULL;
static char * SQLSTMT = NULL;
static char * ARRAY_SIZE = "10";

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

EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;

extern SQLDA *sqlald();
extern void sqlclu();

static void die( char * msg )
{
fprintf( stderr, "%s\n", msg );
exit(1);
}

/*
this array contains a default mapping
I am using to constrain the
lengths of returned columns. It is mapping,
for example, the Oracle
NUMBER type (type code = 2) to be 45 characters
long in a string.
*/

static int lengths[] =
{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,
18, 25, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 512, 2000 };

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
if ( !strncmp( argv[i], "sqlstmt=", 8 ) )
SQLSTMT = argv[i]+8;
else
if ( !strncmp( argv[i], "arraysize=", 10 ) )
ARRAY_SIZE = argv[i]+10;
else
{
fprintf( stderr,
"usage: %s %s %s\n",
argv[0],
"userid=xxx/xxx sqlstmt=query ",
"arraysize=<NN>\n" );
exit(1);
}
}
if ( USERID == NULL || SQLSTMT == NULL )
{
fprintf( stderr,
"usage: %s %s %s\n",
argv[0],
"userid=xxx/xxx sqlstmt=query ",
"arraysize=<NN>\n" );
exit(1);
}
}

static void sqlerror_hard()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;

fprintf(stderr,"\nORACLE error detected:");
fprintf(stderr,"\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

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

static SQLDA * process_1(char * sqlstmt, int array_size )
{
SQLDA * select_dp;
int i;
int j;
int null_ok;
int precision;
int scale;
int size = 10;

fprintf( stderr, "Unloading '%s'\n", sqlstmt );
fprintf( stderr, "Array size = %d\n", array_size );

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :sqlstmt;
EXEC SQL DECLARE C CURSOR FOR S;

if ((select_dp = sqlald(size,MAX_VNAME_LEN,MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for select descriptor." );

select_dp->N = size;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
if ( !select_dp->F ) return NULL;

if (select_dp->F < 0)
{
size = -select_dp->F;
sqlclu( select_dp );
if ((select_dp =
sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for descriptor." );
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
}
select_dp->N = select_dp->F;

for (i = 0; i < select_dp->N; i++)
select_dp->I[i] = (short *) malloc(sizeof(short) *
array_size );

for (i = 0; i < select_dp->F; i++)
{
sqlnul (&(select_dp->T[i]),
&(select_dp->T[i]), &null_ok);
if ( select_dp->T[i] <
sizeof(lengths)/sizeof(lengths[0]) )
{
if ( lengths[select_dp->T[i]] )
select_dp->L[i] = lengths[select_dp->T[i]];
else select_dp->L[i] += 5;
}
else select_dp->L[i] += 5;

select_dp->T[i] = 5;
select_dp->V[i] = (char *)malloc( select_dp->L[i] *
array_size );

for( j = MAX_VNAME_LEN-1;
j > 0 && select_dp->S[i][j] == ' ';
j--);
fprintf (stderr,
"%s%.*s", i?",":"", j+1, select_dp->S[i]);
}
fprintf( stderr, "\n" );

EXEC SQL OPEN C;
return select_dp;
}

static void process_2( SQLDA * select_dp, int array_size )
{
int last_fetch_count;
int row_count = 0;
short ind_value;
char * char_ptr;
int i,
j;

for ( last_fetch_count = 0;
;
last_fetch_count = sqlca.sqlerrd[2] )
{
EXEC SQL FOR :array_size FETCH C
USING DESCRIPTOR select_dp;

for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
{
for (i = 0; i < select_dp->F; i++)
{
ind_value = *(select_dp->I[i]+j);
char_ptr = select_dp->V[i] +
(j*select_dp->L[i]);

printf( "%s%s", i?",":"",
ind_value?"(null)":char_ptr );
}
row_count++;
printf( "\n" );
}
if ( sqlca.sqlcode > 0 ) break;
}

sqlclu(select_dp);

EXEC SQL CLOSE C;

EXEC SQL COMMIT WORK;
fprintf( stderr, "%d rows extracted\n", row_count );
}

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;
SQLDA * select_dp;

process_parms( argc, argv );

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

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

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

EXEC SQL ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

select_dp = process_1( SQLSTMT, atoi(ARRAY_SIZE) );
process_2( select_dp , atoi(ARRAY_SIZE));

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

November 30, 2004 - 7:38 am UTC

why'd you post the code? it is right above too.

anyway, all informational messages are fprintf'ed to STDERR
all data to STDOUT

so, just redirect stderr to a logfile
redirect stdout to a data file

or, read the code and delete any lines you don't like...

A weird problem with array_flat

Jun He, November 30, 2004 - 10:20 am UTC

My environment is 9iR2 and W2K. I have to deal with some varchar2 fields have many carriage returns (0D0A). I can't use "\n" as line delimiter as in array_flat.pc. So I make it a parameter like field delimiter. and take input from command prompt. The weird problems are 
1. The character I used for line delimter is ANSI 249 or F9 becomes 123 or B7 in unloaded text file.
2. The carriage return 0D0A becomes 0D0D0A.
The first one is manageable. But the second problem prevents loading into the same table because of increased length. I created a small example so that you can reproduce this. What do you think ?

SQL> set echo on;
SQL> 
SQL> drop table test;

Table dropped.

SQL> create table test (f1 varchar2(50));

Table created.

SQL> insert into test values ('line1'||chr(13)||chr(10)||
  2  'line2'||chr(13)||chr(10)||'End');

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> host

SQL> -- Run this
SQL> -- array_flat userid=tch/p1tch sqlstmt="select * from test" linedelimiter="ù" > flat.txt
SQL> 
SQL> drop directory dump2_dir;

Directory dropped.

SQL> create directory dump2_dir as 'j:\export\unload';

Directory created.

SQL> 
SQL> drop table ext_flat;

Table dropped.

SQL> create table ext_flat (f1 varchar2(60))
  2  organization external
  3  ( type oracle_loader default directory dump2_dir
  4    access parameters
  5        (records delimited by 0x'B7'
  6         fields terminated by '|'
  7         missing field values are null
  8           ( f1 char(60) )
  9        )
 10      location ('flat.txt')
 11  )
 12  reject limit unlimited;

Table created.

SQL> 
SQL> select dump(f1) from test;

DUMP(F1)                                                                        
--------------------------------------------------------------------------------
Typ=1 Len=17: 108,105,110,101,49,13,10,108,105,110,101,50,13,10,69,110,100      

SQL> select dump(f1) from ext_flat;

DUMP(F1)                                                                        
--------------------------------------------------------------------------------
Typ=1 Len=20: 108,105,110,101,49,13,13,10,108,105,110,101,50,13,13,10,69,110,100
,249                                                                                                                        

SQL> 
SQL> spool off

November 30, 2004 - 11:21 am UTC

gotta feeling this is windows doing this to you all around.

what is the database character set?

Last message

Jun He, November 30, 2004 - 10:28 am UTC

I was doing this too quick. The first problem somehow not reproducable. Just the second problem.

November 30, 2004 - 11:40 am UTC

character set, before i dig out my vmware and fire up a database...

Referring last message

Jun He, November 30, 2004 - 10:54 am UTC

Now I reproduced problem 1.
When I put array_flat line into tom.bat
array_flat userid=tch/p1tch sqlstmt="select * from test" linedelimiter="ù" > flat.txt
and run host tom.bat in sqlplus
It will replace linedelimiter F9 with B7.

November 30, 2004 - 11:50 am UTC

that would be 100% about windows then, nothing to do with Oracle.

Followup

Jun He, November 30, 2004 - 2:30 pm UTC

CHARACTER SET WE8ISO8859P1

Jun He, November 30, 2004 - 11:12 pm UTC

Why are you so sure without some test? Even if it is window thing, I like to find out why. I am kinda suspect that array_flat is not portable to window or not supportable in 9iR2 in window.

December 01, 2004 - 8:08 am UTC

array_flat is the simplest sort of C program on the planet. The code is right there in front of you. We are using "fprintf" to stderr and stdout. It is *rather trivial*.

that it works on the command line, but when you put it into a batch file it doesn't -- sort of implies "well, something is different there".

I'd guess that your character 'f9' isn't really a character that windows supports in this context (it is not an ascii character). I would not be surprised if you dumped that file to discover that the 'f9' isn't 'f9' in the bat file itself (your text editor could be doing this).

Using "strange characters" is going to cause you problems.

but hey -- you've got the code, why not just put this INTO THE PROGRAM itself.

As a matter of fact, go into notepad and do this:

echo "<alt-249>"
echo "<alt-183>"

(use the alt key and the numeric keypad)

when you try to save that it says "hey, this file containts characters in the unicode format which will be lost......."

So, i doubt your .bat file has that character, your editor lost it OR the windows command processor stripped it (it is not an ASCII character the high bit is set)

The answer seems to be right in front of you

Jun He, December 01, 2004 - 9:18 am UTC

I got the same message as well when I was doing this in notepad last night. After I saw you went through the same thing, it got me thinking if Windows is using Unicode for Notepad and the change happens when you enter ANSI alt-255 and notepad converts to Unicode. Indeed googling "unicode convert to ansi" and found many sites talk about this. Ex.
</code> http://www.csc.calpoly.edu/~bfriesen/software/builds.html <code>
Seems from NT above, Windows uses unicode as default for every utility. To enter a character like alt-255 is simply ask notepad to convert it for you. Of course it won't work.

My favorite text editor is Textpad, www.textpad.com. It can handle ANSI and Unicode. Matter of the fact, when I edit my tom.bat file, I was using textpad in ANSI without realizing the differrence. The .bat file is correct by itself with correct ANSI 0xFF. Of course embedding 0xFF into array_flat.pc will work as well. The real issue now is problem 2, some how redirecting stderr to text file. It converts 0D0A into 0D0D0A. Maybe I need to change array_flat into writing into ANSI file directly instead of stderr to avoid this conversion.

What do you think ?

December 01, 2004 - 10:03 am UTC

I think windows is a pain. I'd modify the C code to write directly to a file since redirection isn't really the "windows way", but the "unix way".

More about notepad

Jun He, December 01, 2004 - 9:23 am UTC

Digging deeper into notepad, the way to enter 0xFF or 255
for ansi, it is <alt>+255, for unicode side of ansi, it is <alt>+0255. --- Just discovered in Character Map.

Jun He, December 01, 2004 - 10:39 am UTC

The real issue is with text vs binary! File handle must be opened as binary. Default text mode will be subject to OS to handle it so that Window's Unicode mode shell introduced extra 0D. In array_pc, printf outputs unloaded text using stdout. It is text mode. I changed it to binary. Everything flies perfectly.

Yes. windows is indeed pain sometimes. But it is the Matrix now.. can we ignore it? no!

December 01, 2004 - 10:43 am UTC

I ignore it quite well thank you very much. I run windows in a window, ironic yet satisfying at the same time.

Now when it blue screens, i just click on "start this virtual machine" and all is well in the world again.

Jun He, December 01, 2004 - 11:26 am UTC

Thank you for your great books and websites, which I benefited most in the first place.

Fix for Coredump on HPUX -11/Oracle 9.2.0.5 (64-bit)

Kevin, December 15, 2004 - 9:23 pm UTC

Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production

Hi Tom,

I too ran into the coredump problem on HPUX-11/Oracle 9.2 and so worked through the Sample10/array_flat code to identify differences. It finally worked when I putin the extra 4 #include statements at the top (but by then I had made a number of other changes in the code to bring it into line with sample10. Given that I now have a working program I'm not going to spend time working out which was the critical change though the includes may be it.

I've attached the code as it worked (not sure if this is applicable for other OS's but presume that if the include files are there then it should work - not that much was changed):

#include <stdio.h>
#include <string.h>
#include <ctype.h>
#include <setjmp.h>
#include <sqlda.h>
#include <stdlib.h>
#include <sqlcpr.h>

#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30

static char * USERID = NULL;
static char * SQLSTMT = NULL;
static char * ARRAY_SIZE = "10";
static char * FEEDBACK = "0";

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

EXEC SQL INCLUDE sqlda;
EXEC SQL INCLUDE sqlca;

extern SQLDA *sqlald();
extern void sqlclu();

static void die( char * msg )
{
fprintf( stderr, "%s\n", msg );
exit(1);
}

/*
this array contains a default mapping
I am using to constrain the
lengths of returned columns. It is mapping,
for example, the Oracle
NUMBER type (type code = 2) to be 45 characters
long in a string.
*/

static int lengths[] =
{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,
18, 25, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 512, 2000 };

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
if ( !strncmp( argv[i], "sqlstmt=", 8 ) )
SQLSTMT = argv[i]+8;
else
if ( !strncmp( argv[i], "arraysize=", 10 ) )
ARRAY_SIZE = argv[i]+10;
else
if ( !strncmp( argv[i], "feedback=", 9 ) )
FEEDBACK = argv[i]+9;
else
{
fprintf( stderr,
"usage: %s %s %s\n",
argv[0],
"userid=xxx/xxx sqlstmt=query ",
"arraysize=<NN>\n" );
exit(1);
}
}
if ( USERID == NULL || SQLSTMT == NULL )
{
fprintf( stderr,
"usage: %s %s %s\n",
argv[0],
"userid=xxx/xxx sqlstmt=query ",
"arraysize=<NN> feedback=<NN>\n" );
exit(1);
}
}

static void sqlerror_hard()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;

fprintf(stderr,"\nORACLE error detected:");
fprintf(stderr,"\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

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

static void process_1(char * sqlstmt, int array_size, int feedback )
{
SQLDA * select_dp;
int i;
int j;
int null_ok;
int precision;
int scale;
int size = 10;
int last_fetch_count;
int row_count = 0;
short ind_value;
char * char_ptr;

fprintf( stderr, "Unloading '%s'\n", sqlstmt );
fprintf( stderr, "Array size = %d\n", array_size );
fprintf( stderr, "Feedback = %d\n", feedback );

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :sqlstmt;
EXEC SQL DECLARE C CURSOR FOR S;

if ((select_dp = sqlald(size,MAX_VNAME_LEN,MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for select descriptor." );

select_dp->N = size;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
if ( !select_dp->F ) return;

if (select_dp->F < 0)
{
size = -select_dp->F;
sqlclu( select_dp );
if ((select_dp =
sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for descriptor." );
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
}
select_dp->N = select_dp->F;

for (i = 0; i < select_dp->N; i++)
{
select_dp->I[i] = (short *) malloc(sizeof(short) *
array_size );
select_dp->V[i] = (char *) malloc(1);
}

for (i = 0; i < select_dp->F; i++)
{
sqlnul ((unsigned short *)&(select_dp->T[i]), (unsigned short *)&(select_dp->T[i]), &null_ok);

if ( select_dp->T[i] <
sizeof(lengths)/sizeof(lengths[0]) )
{
if ( lengths[select_dp->T[i]] )
select_dp->L[i] = lengths[select_dp->T[i]];
else select_dp->L[i] += 5;
}
else select_dp->L[i] += 5;

select_dp->T[i] = 5;
select_dp->V[i] = (char *) realloc( select_dp->V[i], select_dp->L[i] *
array_size );

for( j = MAX_VNAME_LEN-1;
j > 0 && select_dp->S[i][j] == ' ';
j--);
fprintf (stderr,
"%s%.*s", i?",":"", j+1, select_dp->S[i]);
}

fprintf( stderr, "\n" );
if (feedback > 0) fprintf( stderr, "Feedback:\n" );

/* Now get all of the data and output it to STDOUT */

EXEC SQL OPEN C;
for ( last_fetch_count = 0;
;
last_fetch_count = sqlca.sqlerrd[2] )
{
EXEC SQL FOR :array_size FETCH C
USING DESCRIPTOR select_dp;

for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
{
for (i = 0; i < select_dp->F; i++)
{
ind_value = *(select_dp->I[i]+j);
char_ptr = select_dp->V[i] +
(j*select_dp->L[i]);

printf( "%s%s", i?",":"",
ind_value?"":char_ptr );
}
row_count++;
if (feedback != 0 && row_count % feedback == 0) fprintf(stderr,".");
printf( "\n" );
}
if ( sqlca.sqlcode > 0 ) break;
}

sqlclu(select_dp);

EXEC SQL CLOSE C;

EXEC SQL COMMIT WORK;

fprintf( stderr, "\n%d rows extracted\n", row_count );
}

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;
SQLDA * select_dp;

process_parms( argc, argv );

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

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

EXEC SQL CONNECT :oracleid;
fprintf(stderr, "\n ARRAY_FLAT V2 Connected to ORACLE as user: %s\n\n",
oracleid.arr);

EXEC SQL ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

process_1( SQLSTMT, atoi(ARRAY_SIZE), atoi(FEEDBACK) );

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

For info while trying to fix this for the last week I have been runninf the PL/SQL version as an alternative. The comparison was:
ARRAY_FLAT
21579030 rows extracted with array_flat in 14 minutes (and because it writes to STDOUT I was able to compress it through a pipe so my disk usage was minimal and the elapsed time was not extended by the compression time)

PL/SQL DUMP_CSV
Rows dumped: 21597867 4hrs 57 mins

Hope this helps someone running in a similar environment

.... slight change to prog

Kevin, December 16, 2004 - 3:25 pm UTC

I just noticed that in all my changes I managed to lose the (NULL) indicator ...

so the code:
printf( "%s%s", i?",":"",
ind_value?"":char_ptr );

at or about line 207 should be changed back to

printf( "%s%s", i?",":"",
ind_value?"(null)":char_ptr );

Benchmark

Eduardo Nicdao, January 20, 2005 - 4:00 pm UTC

Would you be able to share your test.sh from your first follow-up dated Oct 7 2002? I'm not very knowledgeable in unix. I'm interested in how you determined the real, user and system times. I want to benchmark the C vs SQL*Plus unloader.

Thanks in advance.

January 20, 2005 - 7:36 pm UTC

I just used "time"

[tkyte@xtkyte-pc tkyte]$ time ls
afiedt.buf a.sql binpriv cxoffice expdat.dmp ff.tar src test1.sql testb.sql tk.prf x.lst
asktom_articles.html audit_actions.sql b.tgz dba_tab_statistics.sql exp.lst sqlload t.ctl test2.sql test.slsq t.log x.sql
AsktomShort.doc bin c.sql Desktop exp.par sqlnet.log @test testa.sql test.sql tmp

real 0m0.036s
user 0m0.000s
sys 0m0.000s

how do we process data from flat file using "HOST ARRAY"

A reader, February 12, 2005 - 4:22 am UTC

Hi

I am reading the documentation about how to use host arrays, it seems that it applies inside database but my data source are flat files I was wondering how to array process flat files? For example read 100 lines from the file and do a host array insert in pro*c code, then 100 lines more and so on until EOF. I cant find anything about array process flat files in the documentation may be it´s not related to Oracle if that´s the case may you tell me how to accomplish my goal?

By the way the code you are using to dump data to flat files is Dynamic SQL Method 4 right?

Cheers

February 12, 2005 - 12:42 pm UTC

did you read the pro*c docs? they are full of "binding host arrays"

you read file
you fill array
you say "oracle insert this array of data" when you fill array.

YOU read 100 lines
you then say "insert"

yes, i used the so called "method 4"

oracle fast extract into ascii text

Chad, March 18, 2005 - 12:03 pm UTC

I saw much faster results with Fastreader. fastreader also extracting binary data, blobs and support chained rows.
Oh chained row, such a pain :)

by the way fastreader is from www.wisdomforce.com

March 18, 2005 - 12:15 pm UTC

this was a desktop pc in the year 2002.... I would hope a server would be faster even with this code

(chained rows are no issue with SQL and blob support would be fairly straight forward).

if you have an ongoing need to move data from database 1 to database 2 -- transport it (light speed)

Improved Script - User defined RECORDSEPARATOR, FIELDSEPARATOR, DOUBLEQUOTE=TRUE/FALSE

A reader, April 07, 2005 - 8:52 pm UTC

Hi Guys,

You can have user defined FS/RS and decide whether you want everything double quoted!

Enjoy.

Thanks tom for all the help on the Comp.Databases forum and I love this page...

/* Compile Me With
gcc -O3 flatten.c -o flatten -L/oracle/rdbms/9.2.0/orax011/lib32 -lclntsh -lclntst9
*/

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

#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30

static char * USERID = NULL;
static char * SQLSTMT = NULL;
static char * ARRAY_SIZE = "10";
static char * RECSEP = NULL;
static char * FIELDSEP = NULL;
int DOUBLEQUOTE = (-1);

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

EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;

extern SQLDA *sqlald();
extern void sqlclu();

static void die( char * msg )
{
fprintf( stderr, "%s\n", msg );
exit(1);
}

/*
this array contains a default mapping
I am using to constrain the
lengths of returned columns. It is mapping,
for example, the Oracle
NUMBER type (type code = 2) to be 45 characters
long in a string.
*/

static int lengths[] =
{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,
18, 25, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 512, 2000 };

static void process_parms( argc, argv )
int argc;
char * argv[];
{
int i;

for( i = 1; i < argc; i++ )
{
if ( !strncasecmp( argv[i], "userid=", 7 ) )
USERID = argv[i]+7;
else
if ( !strncasecmp( argv[i], "sqlstmt=", 8 ) )
SQLSTMT = argv[i]+8;
else
if ( !strncasecmp( argv[i], "arraysize=", 10 ) )
ARRAY_SIZE = argv[i]+10;
else
if ( !strncasecmp( argv[i], "recsep=", 7) )
RECSEP = argv[i] + 7;
else
if ( !strncasecmp( argv[i], "fieldsep=", 9 ))
FIELDSEP = argv[i] + 9;
else
if ( !strncasecmp(argv[i], "doublequote=true", 16))
DOUBLEQUOTE=1;
else
if ( !strncasecmp(argv[i], "doublequote=false", 17))
DOUBLEQUOTE=0;
else
{
fprintf( stderr,
"usage: %s %s %s %s %s %s %s \n",
argv[0],
"userid=<user/pass@connect_string>", "sqlstmt=<Query>",
"arraysize=<Fetch Size>\n", "fieldsep=<Field Sep String>", "recsep=<Record Sep String>",
"doublequote=<TRUE/FALSE>" );
exit(1);
}
}
if ( USERID == NULL || SQLSTMT == NULL || RECSEP == NULL || FIELDSEP == NULL || DOUBLEQUOTE == (-1))
{
fprintf( stderr,
"usage: %s %s %s %s %s %s %s \n",
argv[0],
"userid=<user/pass@connect_string>", "sqlstmt=<Query>",
"arraysize=<Fetch Size>\n", "fieldsep=<Field Sep String>", "recsep=<Record Sep String>",
"doublequote=<TRUE/FALSE>" );
exit(1);
}
}

static void sqlerror_hard()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;

fprintf(stderr,"\nORACLE error detected:");
fprintf(stderr,"\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

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

static SQLDA * process_1(char * sqlstmt, int array_size )
{
SQLDA * select_dp;
int i;
int j;
int null_ok;
int precision;
int scale;
int size = 10;

fprintf( stderr, "Unloading '%s'\n", sqlstmt );
fprintf( stderr, "Array size = %d\n", array_size );
fprintf( stderr, "Field Separator = '%s'\n", FIELDSEP);
fprintf( stderr, "Record Separator = '%s'\n", RECSEP);
fprintf( stderr, "Doublequote is set to '%d'\n\n", DOUBLEQUOTE);

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :sqlstmt;
EXEC SQL DECLARE C CURSOR FOR S;

if ((select_dp = sqlald(size,MAX_VNAME_LEN,MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for select descriptor." );

select_dp->N = size;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
if ( !select_dp->F ) return NULL;

if (select_dp->F < 0)
{
size = -select_dp->F;
sqlclu( select_dp );
if ((select_dp =
sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for descriptor." );
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
}
select_dp->N = select_dp->F;

for (i = 0; i < select_dp->N; i++)
select_dp->I[i] = (short *) malloc(sizeof(short) *
array_size );

for (i = 0; i < select_dp->F; i++)
{
sqlnul (&(select_dp->T[i]),
&(select_dp->T[i]), &null_ok);
if ( select_dp->T[i] <
sizeof(lengths)/sizeof(lengths[0]) )
{
if ( lengths[select_dp->T[i]] )
select_dp->L[i] = lengths[select_dp->T[i]];
else select_dp->L[i] += 5;
}
else select_dp->L[i] += 5;

select_dp->T[i] = 5;
select_dp->V[i] = (char *)malloc( select_dp->L[i] *
array_size );

for( j = MAX_VNAME_LEN-1;
j > 0 && select_dp->S[i][j] == ' ';
j--);
fprintf (stderr,
"%s%.*s", i?",":"", j+1, select_dp->S[i]);
}
fprintf( stderr, "\n" );

EXEC SQL OPEN C;
return select_dp;
}

static void process_2( SQLDA * select_dp, int array_size )
{
int last_fetch_count;
int row_count = 0;
short ind_value;
char * char_ptr;
int i, j;

for ( last_fetch_count = 0;
;
last_fetch_count = sqlca.sqlerrd[2] )
{
EXEC SQL FOR :array_size FETCH C
USING DESCRIPTOR select_dp;

for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
{
for (i = 0; i < select_dp->F; i++)
{
ind_value = *(select_dp->I[i]+j);
char_ptr = select_dp->V[i] +
(j*select_dp->L[i]);

/* Double quote character is stored here */
printf( "%s%s%s%s", i?FIELDSEP:"", DOUBLEQUOTE?"\"":"", ind_value?"":char_ptr, DOUBLEQUOTE?"\"":"");
}
row_count++;
printf( "%s\n", RECSEP );
}
if ( sqlca.sqlcode > 0 ) break;
}

sqlclu(select_dp);

EXEC SQL CLOSE C;

EXEC SQL COMMIT WORK;
fprintf( stderr, "%d rows extracted\n", row_count );
}

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;
SQLDA * select_dp;

process_parms( argc, argv );

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

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

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

EXEC SQL ALTER SESSION
SET NLS_DATE_FORMAT = 'ddmmyyyyhh24miss';

select_dp = process_1( SQLSTMT, atoi(ARRAY_SIZE) );
process_2( select_dp , atoi(ARRAY_SIZE));

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

coredump AIX 5.2

A reader, August 01, 2005 - 11:28 am UTC

hi Tom,

Am Using your code to unload data to flat file.
Am running on AIX 5.2 but the code coredumps when i run it.
Is some KNOWN problem with AIX 5.2?
This is the output I got:

------------------------------------------------------
dump_csv userid=<> 'sqlstmt=select * from user_tables' arraysize=10

Connected to ORACLE as user: <>

Unloading 'select * from user_tables'
Array size = 10
TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME,IOT_NAME,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,
MAX_EXTENTS,PCT_INCREASE,FREELISTS,FREELIST_GROUPS,LOGGING,BACKED_UP,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_
LEN,AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BLOCKS,DEGREE,INSTANCES,CACHE,TABLE_LOCK,SAMPLE_SIZE,LAST_ANALYZED,PARTITIONED,IOT_
TYPE,TEMPORARY,SECONDARY,NESTED,BUFFER_POOL,ROW_MOVEMENT,GLOBAL_STATS,USER_STATS,DURATION,SKIP_CORRUPT,MONITORING,CLUSTER_OWNE
R,DEPENDENCIES,COMPRESSION
660
666
Segmentation fault(coredump)

-----------------------------------------------

Have already altered the code with hat you have suggested to "Gerald Koerkenmeier" i.e. adding printf( "%d\n", __LINE__ );

Please guide.

Thanks as always.

Coredump on AIX 5.2

A reader, August 01, 2005 - 11:36 am UTC

Tom,
Its me who had given the above coredump comment while running your program on AIX 5.2.
Earlier I'd appended the code with printf( "%d\n", __LINE__ ); in the process_2 only.

Now I've done the same for process_1 & here is the code output.
Please guide.

-----------------------------
dump_csv userid=<> 'sqlstmt=select * from user_tables' arraysize=10

Connected to ORACLE as user: <>

Unloading 'select * from user_tables'
Array size = 10
462
510
513
551
554
556
Cannot allocate memory for descriptor.

-----------------------------

So might be a issue of allocating memory. I'm not very good in C. But please guide.

August 01, 2005 - 12:31 pm UTC

print out the input arguments to sqlald I guess, see if they are "reasonable"

sqlald?

A reader, August 02, 2005 - 1:45 am UTC

Tom,

what do you mean by input parameters being "reasonable" ??
How tdo I check this?
what is sqlald?
Please spare my ignorance.

Thanks for your help.

August 02, 2005 - 7:32 am UTC

print them out, if you seem something like 423131641641256342153251234, that would be unreasonable.

sqlald is the function called to allocate the memory that is failing as per your output.

Lack of native ASCII dump utility

A reader, August 04, 2005 - 4:07 pm UTC

Not a question but a comment - wonder why ORACLE does not provide a fast data dump (ascii) utility. Even extending EXP with an FORMAT=ASCII would have been quite usefull!

fast Oracle data export import

George, August 14, 2005 - 2:28 am UTC

yes Oracle does not provides, so other vendors like Wisdomforce fill this spot. we use fastreader for selective fast export / import: www.wisdomforce.com

ORA-01000 : maximum open cursors exceeded

Swain, September 06, 2005 - 12:34 am UTC

Hi Tom,
Could you please explain why I see cursors in the V$OPEN_CURSOR view and in v$sesstat even though they have been explicitly closed in the code.
With each call to this function the number of open cursors increased and leads to ORA-01000 error.
Here is the part of the code used for cursor handling.

EXEC SQL PREPARE S FROM :gSQLStatement;
EXEC SQL DECLARE C CURSOR FOR S;
EXEC SQL OPEN C;
.
.
.
EXEC SQL DESCRIBE SELECT LIST FOR S INTO selda;
.
.
.
EXEC SQL FOR :array_size FETCH C USING DESCRIPTOR selda;
.
.
.
sqlclu(selda);
EXEC SQL CLOSE C;
Thanks & Regards,
Swain

September 06, 2005 - 8:10 am UTC

give me a reproducible test case, complete with Pro*C command line options. If this reproduces, you should just be able to write a tiny "main" that loops and exhibits this behaviour.

Not so fast

Sanjay, September 23, 2005 - 2:42 am UTC

I have used your Pro*C code to dump 11 million rows, it took approx 22 minutes.
I used below software, it took less than 4 minutes.
</code> http://www.wisdomforce.com/ <code>

Do Pro*C code needes further tuning ? If not, why third party software can dump much faster than oracle supplied methods.

September 23, 2005 - 9:14 am UTC

sure it could - do you see it is a relatively simple "dumb" but very much free program?

this is not an oracle supplied method, it is simply a program I wrote and you are free to use.

arraysize and maxdata

Roger, October 19, 2005 - 7:06 pm UTC

Hi Tom,

Just bought your new book. Really really cool chapter on the data types (12) (have to read more, but Chapter 10,11 look very promising).

Here is a question regarding arraysize and maxdata. 

Details:
--------
I have often run into the infamous

[quote]buffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA.[/quote] error. 

There is a really great article that describes EXACTLY why one gets the error in sqlplus. I have read it carefully and have been able to reproduce the examples.

http://www.fors.com/orasupp/d2k/plus/2171_1.HTM


If I have a function (pardon the name, I had to make it 12 characters in size...)

CREATE OR REPLACE FUNCTION TEST_FUNCTIO RETURN VARCHAR AS 
       l_tmp VARCHAR(10);

BEGIN
    l_tmp := 'hey';
    RETURN l_tmp;
END TEST_FUNCTIO;


SQL> set arraysize 2
SQL> set maxdata 8169
SQL> select test_functio from dual;
buffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA.
SQL> set maxdata 8170
SQL> select test_functio from dual;

TEST_FUNCTIO
----------------------------------------------------------------------------------------------------
hey

As you can see from the link, the length of the column is an important attribute in determine the required maxdata size in sqlplus (so is the column heading etc.) 

Question
--------
When returning a varchar from a function, what is the length of the returned value? Its definately not 10 based on the formula shown in the paper. Based on my calculations, it looks like 2672.33. So, does the function actually return a varchar of size 2672 even though I defined the return type to be varchar2(10)?

I hope I didn't confuse you and make things sound more complex than they are.

Thanks alot for your time.

October 19, 2005 - 7:57 pm UTC

a funtion returns a varchar2(4000) in sql if it returns a varchar2.

use substr() to "constrain" it.

tried substr...

Roger, October 20, 2005 - 1:31 pm UTC

Hi Tom,

Thank you for your reply.

I tried using substr (actually I had tried it before posting the question also) but still getting the error.

CREATE OR REPLACE FUNCTION TEST_FUNCTIO RETURN VARCHAR AS 
       l_tmp VARCHAR(10);

BEGIN
    l_tmp := 'hey';
    RETURN substr(l_tmp,1,5);
END TEST_FUNCTIO;

SQL> set maxdata 8169
SQL> select test_functio from dual;
buffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA.
SQL> set maxdata 8170
SQL> select test_functio from dual;

TEST_FUNCTIO
----------------------------------------------------------------------------------------------------
hey

SQL>

Can you please give an example incase I misunderstood you.

Also, in light of what you say on page 442/443 of your new book about reducing logical i/o's by using arraysize, what is the best way to make sure that you don't run into the buffer overflow error when you increase arraysize (this becomes very important if you have long running queries and after running for 3 hours, all you get is the buffer overflow error).

Thanks again for your amazing work and efforts.

October 20, 2005 - 4:52 pm UTC

select substr( test_funtio, 1, 5 ) from dual;

arraysize

Roger, October 21, 2005 - 9:59 am UTC

Hi Tom,

Thank you for you simple yet effective solution! It looks much better. However, I am a little disappointed as to why Oracle returns 4000 byte varchars from functions like that (even if I substr the return string in the function).

This must be a very common issue for alot of people, right? Returning varchar from a function must have been done a billion times. So, any idea why a 4000 byte varchar is always returned?

Also, another quick question about arraysize. What is the best way to determine what to set the arraysize to when trying to reduce logical i/o's for a query that has way too many logical i/o's (same block being access many times for fetching different rows)? An example would be very much appreciated.

Thank you once again for your time and help.

October 21, 2005 - 10:25 am UTC

because functions always return unconstrained types - no matter what, it is the way it is.

100 is a good array fetch number in general, it is what they chose for plsql in 10g, it is what I tend to use.

arraysize

Roger, October 21, 2005 - 7:51 pm UTC

Hi Tom,

Thank you for your response. I am a little confused about your statement of setting arraysize arbitraryly to 100. Can you please tell me how you came up with the number, 100? Is there anyway to look at the block level stats (see how many rows on a block for a table) and then set arraysize? Can you please give an example where you show how you came up with the arraysize 100?

Thank you for your time. Have a nice weekend.

October 22, 2005 - 10:35 am UTC

empirical.

over 100, diminishing marginal returns
upto 100, good returns

I would not micro manage this to death to figure out "given the data as it exists at 5pm today the perfect array fetch size is 92"

Don't over analyze this one.

populate oracle tables with data from flat file

Cis, October 23, 2005 - 8:34 am UTC

how to populate oracle table with the data from flat file using pro*C.(how to load oracle tables with data from flat file).

thanx.

October 23, 2005 - 1:41 pm UTC

using the insert statement?

creating ASCII dump file

thirumaran, October 28, 2005 - 12:44 pm UTC

Hi Tom,

Is there any easy method to create a comma seperated files , i have nearly 5 million records .
i used these statments to create CSV file but it failed to load

example:
spool file_a
select empno||','||ename||','||job from emp;
spool off

I am not clear on how to proceed further.Pl guide me

Thanks in ADV
Thirumaran

October 28, 2005 - 1:21 pm UTC

</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

sqlldr_exp ctl file

Thirumaran, October 28, 2005 - 5:35 pm UTC

Tom,

Thanks for quick response.
I was able to create the CTL file with DATA using your examples and the sqlldr_exp utility. It created both control file and the data file, when i tried to load data into the same table it raises errors.

Example:
------------------------------------------------------------
-- Thank you for evaluating Advanced Query Tool.
-- Please support the developer by purchasing the product.
-- At $100 this is very cheap for the features provided.
-- See www.querytool.com for further information.
------------------------------------------------------------
Create Table CSRNG.SR_REQ(
EID NUMBER,
SR_REQ_NUM CHAR(12),
TYPE_CODE CHAR(8),
GROUP_CODE CHAR(8),
PRIORITY_CODE CHAR(8),
STATUS_CODE CHAR(8),
STATUS_DATE DATE,
ORIG_SERVICE_REQUEST_EID NUMBER,
CREATION_REASON_CODE CHAR(8),
RELATED_REASON_CODE CHAR(8),
METHOD_RECEIVED_CODE CHAR(8),
GEO_STREET_NAME_EID NUMBER,
VALID_SEGMENT_FLAG CHAR(1),
LOCATION_RECORD_TYPE_CODE CHAR(8),
STREET_NUMBER NUMBER,
STREET_NAME_PREFIX CHAR(8),
STREET_NAME CHAR(40),
STREET_NAME_SUFFIX CHAR(8),
STREET_SUFFIX_DIRECTION CHAR(8),
CITY CHAR(40),
STATE_CODE CHAR(8),
COUNTY CHAR(30),
ZIP_CODE CHAR(10),
UNIT_NUMBER CHAR(20),
FLOOR CHAR(20),
BUILDING_NAME CHAR(120),
LOCATION_DETAILS VARCHAR2(2000),
WDA_1_VALUE CHAR(120),
WDA_2_VALUE CHAR(120),
WDA_3_VALUE CHAR(120),
X_COORDINATE NUMBER,
Y_COORDINATE NUMBER,
DETAILS VARCHAR2(4000),
BEGIN_RESOLUTION_DATE DATE,
CREATED_DATE DATE,
CREATED_BY_EID NUMBER,
UPDATED_DATE DATE,
UPDATED_BY_EID NUMBER,
GEO_AREA_CODE CHAR(8),
GEO_AREA_VALUE CHAR(120))
;

D:\CSRNG-Database>sqlldr_exp cng/cng@c_ng sr_req > srctrl.ctl

D:\CSRNG-Database>sqlldr cng/cng@c_ng direct=y control=srctrl.ctl log=srctrllog.log

SQL*Loader: Release 10.1.0.2.0 - Production on Sat Oct 29 00:19:59 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Load completed - logical record count 51.

THERE ARE 5 million records but recount count is only 51.

The created control file

LOAD DATA
INFILE *
INTO TABLE sr_req
REPLACE
TRAILING NULLCOLS
FIELDS TERMINATED BY '|'
(
eid
,sr_req_num
,type_code
,group_code
,priority_code
,status_code
,status_date
,orig_service_request_eid
,creation_reason_code
,related_reason_code
,method_received_code
,geo_street_name_eid
,valid_segment_flag
,location_record_type_code
,street_number
,street_name_prefix
,street_name
,street_name_suffix
,street_suffix_direction
,city
,state_code
,county
,zip_code
,unit_number
,floor
,building_name
,location_details
,wda_1_value
,wda_2_value
,wda_3_value
,x_coordinate
,y_coordinate
,details
,begin_resolution_date
,created_date
,created_by_eid
,updated_date
,updated_by_eid
,geo_area_code
,geo_area_value
)
BEGINDATA
5331685|03-00001286 |PWSSDEAD|PWSSAN |STANDARD|C-CLOSED|00-000-00||||PHONE |2272|Y|VALID |6227|
5331725|03-00001287 |TRASHBRU|TRASHAND|STANDARD|C-CLOSED|00-000-00||||PHONE |278|Y|VALID |920||T
5331833|03-00001288 |MISSEDRE|REFUSECO|STANDARD|C-CLOSED|00-000-00||||PHONE |5072|Y|VALID |5913|
5331901|03-00001289 |PWMISSGB|REFUSECO|STANDARD|C-CLOSED|00-000-00||||PHONE |3440|Y|VALID |130||
5331958|03-00001291 |PWMISSTB|TRASHAND|STANDARD|C-CLOSED|00-000-00||||PHONE |4048|Y|VALID |1300|
5331983|03-00001292 |TRASHBRU|TRASHAND|STANDARD|C-CLOSED|00-000-00||||PHONE |3446|Y|VALID |828||
5332015|03-00001293 |STREETSW|STREETCL|STANDARD|C-CLOSED|00-000-00||||PHONE |98|Y|VALID |1917||1
5332096|03-00001295 |PWMISSTB|TRASHAND|STANDARD|C-CLOSED|00-000-00||||PHONE |3323|Y|VALID |1719|
5332076|03-00001296 |PWMISSGB|REFUSECO|STANDARD|C-CLOSED|00-000-00||||PHONE |709|Y|VALID |1133||
5332145|03-00001297 |PWSSDEAD|PWSSAN |STANDARD|C-CLOSED|00-000-00||||PHONE |2195|Y|VALID |6460|
5332159|03-00001298 |PWSSDEAD|PWSSAN |STANDARD|C-CLOSED|00-000-00||||PHONE |363|Y|VALID |1100||
5332173|03-00001299 |PWSSDEAD|PWSSAN |STANDARD|C-CLOSED|00-000-00||||PHONE |63|Y|VALID |700||CA
5332188|03-00001300 |PWSSDEAD|PWSSAN |STANDARD|C-CLOSED|00-000-00||||PHONE |4048|Y|VALID |848||
5332187|03-00001301 |CLEANAN1|HORTICUL|STANDARD|C-CLOSED|00-000-00||||PHONE |3323|Y|VALID |1313|
5332226|03-00001302 |PWMISSGB|REFUSECO|STANDARD|C-CLOSED|00-000-00||||PHONE |2327|Y|VALID |206||
5332282|03-00001303 |CLEANAN1|HORTICUL|STANDARD|C-DUP |00-000-00|5332187|||PHONE |3323|Y|VALID
5332280|03-00001304 |STUMPGRI|URBANFOR|STANDARD|C-CLOSED|00-000-00||||PHONE |3550|Y|VALID |2601|
5332339|03-00001305 |MISSEDRE|REFUSECO|STANDARD|C-CLOSED|00-000-00||||PHONE |2890|Y|VALID |1209|

I created the ctl & data file from a table and when loading it back to the same table it raises error.There are no notnull or any constraints on this table.

Record 1: Rejected - Error on table SR_REQ, column STREET_NAME_PREFIX.
Column not found before end of logical record (use TRAILING NULLCOLS)

what is the cause of this error ?

Thanks
Thirumaran

October 29, 2005 - 10:56 am UTC

look at the script that sqlldr_exp generated, see anything obviously wrong? (the script is *very very very* simple, take a peek, try to debug it)

sqlldr_exp ctl file

thirumaran, October 28, 2005 - 8:40 pm UTC

Adding to my comments above. My table had 40 columns but the sqlldr_exp has created the control file &extracted data only for 15 columns.
is this an limitation with sqlldr_exp utility .

thanks
thirumaran

October 29, 2005 - 10:57 am UTC

use this:

sqlplus -s $1 << EOF > /tmp/flat$$.sql
set wrap off
set feedback off
set pagesize 0
set verify off


prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE $2
prompt prompt REPLACE
prompt prompt FIELDS TERMINATED BY '|'
prompt prompt (
select  'prompt ' || decode(column_id,1,'',',') || lower(column_name)
from    user_tab_columns
where   table_name = upper('$2')
order by column_id
/
prompt prompt )
prompt prompt BEGINDATA


prompt  select
select  lower(column_name)||'||chr(124)||'
from    user_tab_columns
where   table_name = upper('$2')
order by column_id
/
prompt  '' from $2
prompt  /
prompt  exit
exit
EOF

sqlplus -s $1 << EOF
set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF


----------------------------------------------------------------------------

tested with:

ops$tkyte@ORA9IR2> drop table t;

Table dropped.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_string long := 'create table t ( x int';
  3  begin
  4          for i in 1 .. 999
  5          loop
  6                  l_string := l_string || ',c'||i || ' int ' || case when mod(i,2)=0 then 'default 1234' end;
  7          end loop;
  8          l_string := l_string || ')';
  9          execute immediate l_string;
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> insert into t (x) values ( 123456 );

1 row created.

ops$tkyte@ORA9IR2> commit;

Commit complete.

ops$tkyte@ORA9IR2> !sqlldr_exp / t > t.ctl

ops$tkyte@ORA9IR2> delete from t;

1 row deleted.

ops$tkyte@ORA9IR2> commit;

Commit complete.

ops$tkyte@ORA9IR2> !sqlldr / t

SQL*Loader: Release 9.2.0.6.0 - Production on Sat Oct 29 11:10:09 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 1

ops$tkyte@ORA9IR2> select count(*) from t;

  COUNT(*)
----------
         1

ops$tkyte@ORA9IR2> select x, c998, c999 from t;

         X       C998       C999
---------- ---------- ----------
    123456       1234

<b>original had an issue with the last column being null</b>

sqlldr_exp ctl file

thirumaran, November 02, 2005 - 11:12 pm UTC

Excellent !!!

It worked exactly as you had mentioned.

Thanks TOM

Regarding Oracle .so libs needed to run this code

Rajul Maheshwari, November 29, 2005 - 2:24 am UTC

Hello Tom,

I am trying running unload_table program in oracle 9.0.2.0 verison, but i really don't know the .so libraries which are needed for this program to run.So please provide me the same.

November 29, 2005 - 9:53 am UTC

you need oracle installed, then you'll have everything you need. There is no list of ".so" files you can copy from one system to another.

flat_array. does it work for 9i

Ravi, January 12, 2006 - 2:58 pm UTC

Tom,
I'm getting following error.
7 # ==> array_flat.exe userid=scott/tiger@knox 'sqlstmt=select * from emp' arraysize=100
segmentation fault
[1] + Done(139) array_flat.exe userid=scott/tiger@knox 'sqlstmt=select * from emp' arraysize=100
3380 Segmentation violation array_flat.exe

I'm using Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production on Windows 2000 with MKS platform.

January 13, 2006 - 10:19 am UTC

yes, it does for me.

However, I've never tried it on a 64bit platform - you'll need to look into that yourself. It could well be that I have a 32/64 bit pointer problem.

Thanks.

Ravi, January 13, 2006 - 2:29 pm UTC

extract into flat files

Gordon, March 12, 2006 - 12:34 am UTC

We used Pro*C script for data archiving until we migrated to 64bit platform.

Our company ended up purchasing Fastreader - </code> http://www.wisdomforce.com <code>that was mentioned here. Works well for us and surprisingly Fastreader does very fast data extract

ASCII Dump

Parag Jayant Patankar, March 27, 2006 - 6:49 am UTC

March 27, 2006 - 10:04 am UTC

fprintf (stderr,
"%s%.*s", i?",":"", j+1, select_dp->S[i]);

change "," to whatever you like.

sqlldr

parag jayant patankar, March 27, 2006 - 9:26 am UTC

Hi Tom,

I executed Pro*C program for downloading data and it is working very fine and
very fast. In this program I want to change field seperator from comma (,) to
another character where I should do change ? Kindly guide me as I know very
little about "C".

Secondly, when I try to upload data using ctl file for e.g.

LOAD DATA
INFILE *
INTO TABLE tdf31
REPLACE
FIELDS TERMINATED BY ','
(
a0020
,a0090
,c81001
,c81101
,c5060
,c65401
,a9935a
,c65101
,c65201
,c65301
,c65501
,c65601
,c65701
,qdata
,qdatb
,qdatc
,qdatd
,qdate
,qdatf
,qdatg
,qdath
,qdati
,b4110
,b4110a
,b4110b
,b4110c
,b4110d
,b4110e
,b4110f
,b4110g
,b4110h
,b4110i
,b4396
,b4397
)
BEGINDATA
99,99999,20060228,20060228,20060228,20060228,20060301,3,20060228,1,0,0, , , , , ,
, , , , ,0,0,0,0,0,0,0,0,0,0,0,(null)

sqlldr xxxx/xxxxx control=tdf31.ctl log=tdf31.log

** It is giving me error for last two columns

Record 1: Rejected - Error on table TDF31, column B4397.
ORA-01722: invalid number

Table TDF31:
0 Rows successfully loaded.

If I put following data
)
BEGINDATA
99,99999,20060228,20060228,20060228,20060228,20060301,3,20060228,1,0,0, , , , , ,
, , , , ,0,0,0,0,0,0,0,0,0,0,(null),9

Record 1: Rejected - Error on table TDF31, column B4396.
ORA-01401: inserted value too large for column

If I put some value in last two columns, sqlldr uploads data.

last 3 columns of tdf31 table is

B4110I NOT NULL NUMBER(1)
B4396 CHAR(4)
B4397 NUMBER(2)

Kindly guide me about Proc field seprator and sqlldr error.

thanks & regards
pjp

March 27, 2006 - 10:12 am UTC

(null) is not a number, so it rejected it correctly. not sure what you expected?

sqlldr & proc

parag jayant patankar, March 27, 2006 - 10:11 am UTC

Hi Tom,

thaks for your answer for Pro*C program. Kindly guide me regading sqlldr error question from me.

thanks & regards
pjp

March 27, 2006 - 10:17 am UTC

I don't know what you would expect? you have an input file with the string

(null)

where a number was expected.

number & (null )

A reader, March 27, 2006 - 10:23 am UTC

Hi Tom,

I have downloaded table data to flat file using your very efficient Pro*C program. I am testing it to load same data into a table by using SQLLDR. According to me it should get upldated.

You said "(null) is not a number, so it rejected it correctly. not sure what you expected?"

I am expecting data downloaded by pro-c program should get uploaded by sqlldr when requires.

Kindly suggest.

thanks & regards
pjp

March 27, 2006 - 2:44 pm UTC

printf( "%s%s", i?",":"",
ind_value?"(null)":char_ptr );

change what is printed when null then - I'm printing the word "(null)", just print "" if you want.

disk full !!!

Parag J Patankar, April 10, 2006 - 8:53 am UTC

Hi Tom,

Thanks for your very efficient Pro*C program for downloading data to flat file. I am running this program on unix and working very fine, but it does not get aborted when "disk is full" or "file system is 100% used"

Can you pl give me Pro*C code to include in this program so it will get aborted when it will not able to write. Sorry I know very little about "C" and Pro*C.

thanks & regards
pjp

April 11, 2006 - 10:05 am UTC

it just writes to stderr - the console. It would be the shell's responsibility for that.

Or you would have to rewrite it to open it's own file and check return codes from fprintf to that file.

Simply superb

Ray, April 30, 2006 - 2:14 pm UTC

Just wanted to know what would be your personal preference among the three approaches in 9i and above and reasons : </code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

Regards
Ray

May 01, 2006 - 1:57 am UTC

need to unload to a client with relatively good speed and no need to distribute a binary file?

sqlplus.

need to unload to a client with best possible speed, infinite flexibility?

pro*c (you have the code)

need to unload on the server with modest speed from a stored procedure?

plsql.

Better than export Import ?

Ray, May 04, 2006 - 2:35 pm UTC

Now my question is if I am to transfer a 20 GB table from one server to another server, it seems that it is better using

1. Unload using Pro*C/plsql
2. SQLloader (direct load, parallel etc...)

rather that export-import (single row inserts, more redo, no parallelism ...)

Correct?

Thanks in advance

Ray

May 04, 2006 - 5:09 pm UTC

3) transportable tablespace

Missed out something

Ray, May 05, 2006 - 1:24 am UTC

Sorry missed out the context.

Transportable tablespace will work for same platform and same version of Oracle.

10g to 10g RMAN is great for across OS.

My context is: from different OS (some unix flavour to Solaris) and from 8 i to 10g.

Is there are any other options in my context.

Thanks
Ray

May 05, 2006 - 1:54 am UTC

Ray - you never mentioned any versions, so I was free to assume and I assume you are using current software. Unless and until people mention versions - I'll keep doing that.

You could

a) upgrade 8i to 10g on existing hardware
b) transport

otherwise, if it is "easy enough", you could unload and reload - sure. You could use dblinks (create table as select). You could use the sqlplus copy command.

export/import do not do single row operations - not unless the table has longs/lobs - in which case unload/load is going to be "hard" as well.

Serious apologies

Ray, May 05, 2006 - 5:06 am UTC

Hi Tom

Serious apologies for not being clear on the entire requirement. Sorry for the entire confusion.

Silly me I should have remembered that the buffer parameters for export import are for batch operations. Am I right.

If you permit me I will add on to the actual requirement.

One of the my client wants to move on from 'X' flavour of UNIX to Solaris 10. I cannot possibly suggest him to buy Oracle for two enitrely different platform (Oracle 10g on UNIX (X flavour) and Oracle 10g on Solaris). Budgetary constraint you would appreciate. Hence my question.

Hence upgrade-transport is likely to be ruled out.

Coming to the other options:
1. Hope you would agree that CTAS and copy over db link would have a network constraint (dependency). Hence I was more focussed on unload-reload and export-import.

2. My undestanding is that using unload (SQL/Pro*C) I can use SQL parallelism and hence would be faster than export. Will this be faster than using a reasonable value for the buffer parameter for export?

3.Similarly I can use parallel load using direct path using loader. Will this be faster and efficient than using a reasonable value for the buffer parameter for import?

Now that I have given the broad requirement I would like to know your approach to the problem.

Apologies if I am carrying on the same stuff for long.

Regards
Ray

May 05, 2006 - 6:59 am UTC

They do not "buy" Oracle like that - they "own" Oracle and get upgrades with this ownership.

So, ball back in your court :)

1) networks are not "slow" - in fact, networks can be faster than disk :) Hence, I focus on all things.

2) maybe yes, maybe no. It would be expensive since YOUR TIME costs more $$$$$ than anything else here. (so what if it takes 5 hours using export/import and 30 minutes with a custom program if it costs $$$$$ to have program written and takes 10 hours to debug program)

3) probably

My approach is always "the simplest way"

You haven't said "this needs to be completed in X minutes due to switchover requirements"

If I was just having to get this done - sqlplus copy might be my first choice (if I cannot upgrade)

Excellent as usual

Ray, May 05, 2006 - 11:51 am UTC

I am overwhelmed. There is always so much to learn from you. You just squeezed out the last bit of of my requirement "this needs to be completed in X minutes due to switchover requirements".

Asuming that I cannot upgrade what could be the best method, in your opinion, to minimize downtime for switchover given a Database size of around 600GB.

i.e. after having done the copy (unload-load etc...) the best way to minimize the delta (data and time) for switch over. Can logminer be helpful during this delta?

Appreciate and many thanks for your prompt responses.

Regards
Ray

May 05, 2006 - 2:59 pm UTC

we can do this with materialized views on pre-built tables with minimum downtime (something to look into)

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:744625626787#15013618923218


sqlplus copy is brutally efficient - if you use rowid ranges, you might be able to to this in parallel (have to prevent updates to the source data whilst you do this of course)

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:10498431232211 <code>

last choice would be "write a program that unloads and loads" due to the testing involved - unless the data was very very simple.

Long.

Reader, July 19, 2006 - 3:17 am UTC

I have a table with datatype long. The data in this long feild is almost 220 MB. when I am running this process I am getting null as output. Please help

July 19, 2006 - 9:09 am UTC

I did not put in support for such long fields. if you look at the lengths[] array, I map the maximum lengths there, far far short of 220mb, you'll need to take this code as a STARTING POINT and modify it for your needs.

Reading long

Laxman Bage, July 20, 2006 - 3:09 am UTC

Thanks a lot for useful tip!!!

Sql Loader

Raghav, July 22, 2006 - 10:17 am UTC

Hi Tom,

I have created a batch file for truncating a temporary table and then load the data into that table through the sqlldr. Once the data is successfully loaded into the temporary table, it inserts the data into the live table.

@echo off
sqlplus -S cpms/cpmsrmshyd@bhopal9i @c:\erp2cpms\trunc_tmp_sourcing.sql

sqlldr userid=cpms/cpmsrmshyd@bhopal9i control=c:\erp2cpms\cpms.ctl log=c:\erp2cpms\cpms.log bad=c:\erp2cpms\cpms.bad data=c:\erp2cpms\erp_data.txt

sqlplus -S cpms/cpmsrmshyd@bhopal9i
@c:\erp2cpms\scr_insert_sourcing.sql

exit

The contents of the control file are:

-----------------------------------------------------------------------------------------------------------
-- NAME Cpms.ctl
-- DESCRIPTION Control file for Data uploads from text file of ERP to CPMS
-- RETURNS None
-- CREATED BY Raghavarao RV
-- CREATED ON 28/03/2006
-- NOTES For each table there will be a block of code to validate and to load
--
-- MODIFIED (DD/MM/YYYY) DESCRIPTION
-- ON 28/03/2006 Script file Generated
-----------------------------------------------------------------------------------------------------------
LOAD DATA
INFILE 'cpms.dat'
BADFILE 'cpms.bad'
DISCARDFILE 'cpms.dsc'
APPEND

-- TABLE_SOURCING_DATA

INTO TABLE TMP_SOURCING_DATA
TRAILING NULLCOLS (
PURCHASE_DATE POSITION(1:10) DATE "DD/MM/RRRR" nullif PURCHASE_DATE = blanks,
HUB_ID POSITION(12:21) CHAR,
HUB_NAME POSITION(23:52) CHAR,
CHOUPAL_ID POSITION(54:63) CHAR,
COMMODITY_ID POSITION(65:74) CHAR,
COMMODITY_NAME POSITION(76:100) CHAR,
GROSS_QTY POSITION(102:111),
NET_QTY POSITION(113:122),
TOTAL_AMT POSITION(124:133),
GRN_ID POSITION(135:144) CHAR,
SUPPLIER_ID POSITION(146:155) CHAR,
SUPPLIER_TYPE POSITION(157:166) CHAR,
VARIETY POSITION(168:182) CHAR,
VARIETYNAME POSITION(184:208) CHAR
)

It has successfully run for some time. But, for the last few days, when I run the script, it is executing the first command successfully by giving
"Table Truncated"

But when it has to execute the second command, it is giving the message "Table Truncated" as it has fallen into an indefinite loop.

Please let me know, why the script is behaving like this?

Thanks and regards
Raghav

July 23, 2006 - 8:32 am UTC

you need to change your syntax:

...
I have created a batch file for truncating a REAL PERMANENT table and then load the data into that table through the sqlldr. Once the data is successfully loaded
into the REAL PERMANENT table, it inserts the data into the live table.
........

sorry, but given the above script, it is not infinite looping. If what you say is happening - we must be running a different script then we are looking at!

but, just run sqlldr - don't run the sqlplus script to truncate. Change the word APPEND in the ctl file to TRUNCATE and sqlldr will do the right thing. You never should have used sqlldr

Sqlldr

Raghav, July 24, 2006 - 7:05 am UTC

Hi Tom

Thanks for your suggestion. Now, keeping aside the other commands for truncating the temporary table and inserting into the real table I kept only the following line in the batch file.

sqlldr userid=cpms/cpmsrmshyd@bhopal9i control=c:\erp2cpms\cpms.ctl log=c:\erp2cpms\cpms.log bad=c:\erp2cpms\cpms.bad data=c:\erp2cpms\erp_data.txt

Now also, when I run the batch file (with only the above statement stored and saved as a batch file), the same "table truncated" message is coming. But, to bring to your notice, when copy this line and pasted in DOS prompt, all 42000 records (which are expected from the text file) have been inserted into the temporary table without any error. May I know what was the reason behind it?

Thanks and Regards
Raghav

July 24, 2006 - 10:27 am UTC

if your script only has that line AND you are getting the table truncated message, you are NOT running that script.

That is about all I can say, that runs sqlldr, sqlldr does NOT produce that message.

Sqlldr

Raghav, July 25, 2006 - 7:10 am UTC

Hi Tom

We have got the solution for the above problem. The batch file is behaving mischievously. Our local systems administrator had a solution for this. We have added the absolute path of DOS before running the scripts.

@echo off
c:
cd\
sqlldr statements

Then the script (batch file) is working fine.

Thanks and Regards
Raghav

July 25, 2006 - 11:35 am UTC

uh huh - then it was what I thought it was.

YOU WERE NOT RUNNING THE SCRIPT YOU SAID YOU WERE!

if putting explicit paths fixed it, you were apparently running something entirely different (fairly "dangerous" concept.... running things in production but not being entirely sure what you are running)

sqlldr

Raghav, July 26, 2006 - 4:14 am UTC

Hi Tom,

I am running the same batch file only. But, when I start running the same (windows 2000 pc DOS prompt), I think, the system is using the local cache (from documents and settings) and running the previously executed command / coments only. By giving the absolute path, it was not checking the local cache and the execution run properly.

Your coments pls.
Thanks and regards
Raghav

July 26, 2006 - 10:48 am UTC

no, you ran a cmd file without a specific path.

so, it searched your current working directory AND your %PATH% environment variable.

And it found it - and ran it - but it was not the one you wanted to run.

There is no "local cmd cache", that concept does not exist.

Your path and the fact you did not fully qualify the script name are what caused this and it was exactly what I suspected - the script you said you were running was in fact NOT the script being executed.

Semantic Errors

Gerald Koerkenmeier, August 01, 2006 - 11:59 am UTC

Tom, When attempting to precompile on a Windows platform, I get the following errors. Any ideas?

++: Release 9.2.0.7.0 - Production on Tue Aug 1 10:52:49 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

System default option values taken from: d:\oracle\product\920\precomp\admin\pcscfg.cfg

Semantic error at line 110, column 33, file M:\array_flat.pc:
EXEC SQL PREPARE S FROM :sqlstmt;
................................1
PCC-S-02322, found undefined identifier
Semantic error at line 110, column 27, file M:\array_flat.pc:
EXEC SQL PREPARE S FROM :sqlstmt;
..........................1
PCC-S-02319, expression type does not match usage
Semantic error at line 181, column 23, file M:\array_flat.pc:
EXEC SQL FOR :array_size FETCH C
......................1
PCC-S-02322, found undefined identifier
Semantic error at line 181, column 23, file M:\array_flat.pc:
EXEC SQL FOR :array_size FETCH C
......................1
PCC-S-02330, expecting an expression of integer type

August 01, 2006 - 7:10 pm UTC

they have different default options on windows, what is in the default options file (I don't have any windows pro*c installs)

love the unloaders -- what about very large tables?

Elaine H, September 27, 2006 - 11:26 am UTC

i downloaded your unloader utilities and think they are just awesome.

any suggestions on what to do with a very large table - 9 partitions (1.3Tb)? it also has 9 partitions of indexes, another 1.5Tb.

we will soon be migrating from 9.2 on aix to 10r2 on solaris x86. any way we do this is going to be ugly. we are planning to put the entire database in read-only mode, so that the users can continue to query against it -- they don't update at all; all updates occur during batch processing.

we are hoping to accomplish several things:
1. reorganize the main table. all the inserts are insert/append. we are slowly deleting some rows and would like to get that space back where possible.
2. move to a different block size on the new hardware.
3. take advantage of the new features in 10g.

we have toyed with the idea of upgrading in place and then using transportable tablespaces w/rman to move the big data, but then neither 1 nor 2 is accomplished. besides, on the current release of the o/s, we can't install 10r2, and no upgrade is planned for the rest of the year and we are moving to the new platform sometime in the 4th quarter (we hope).

i am fairly certain that using one of your unloaders is the way to go for the 380G worth of data that isn't in this large table, but would like your opinion as to how to move the large table. is there a partition option available in your unloader -- would it be feasible to add one? would you recommend sql copy instead? do you have another recommendation?

i really am excited about using the unloader on the small tables, so thanks so much for making them available!!!

if you have any thoughts, suggestions or links on what to do with the large table, i would really appreciate it.

September 27, 2006 - 3:53 pm UTC

1) i'd be using alter table t move partition p; if I really needed to.. (reorg)

2) #1 would do that.

so, maybe upgrade to 10.2 on aix, transport to other OS (cross platform transports) and then alter moves if you really feel it is necessary.

very large tables

Jason, September 29, 2006 - 7:53 pm UTC

Our IT recently migrated 4.3TB Oracle database to another plattform. Upgrade and the transport did not worked for us for some reason. IT used some tool called fastreader from company wisdomforce to transport the data. They unloaded data into ascii files and then loaded into new platform with sqlloader
The unload into text files part took like 5 hours. That was really good taking into account 4+ TB size

How can we convert this from delimit to fixed length

Praveen Reddy, March 14, 2007 - 10:44 am UTC

can you please give me steps to convert this from delimit to fixed length

March 14, 2007 - 2:04 pm UTC

1) write code

you would use rpad() and nvl (on nullable columns - to return a space to be rpadded) to make the fields whatever length you wanted and then concatenate them

Problem when compiling for C++, using CODE=CPP in pscfg.cfg file

Admirer, June 05, 2007 - 8:10 am UTC

Proc reports errors when trying to generate CPP source code on Oracle 8.1.7 on Sun Solaris. The error reported is "PCC-S-02322 found undefined identifier" when trying to convert process_1 function.

Why is the code mixing K&R style and ANSI_C tyle function prototypes? (check main(), process_1 and process_2)

If I try to modify the generated code to such to ANSI_C prototype, the compilation still does not work and reports the following problems:
line 513: Error: Too many arguments in call to "sqlald()".
line 559: Error: Too many arguments in call to "sqlclu()".
line 561: Error: Too many arguments in call to "sqlald()".
line 609: Error: The function "sqlnul" must have a prototype.
line 727: Error: Too many arguments in call to "sqlclu()".
line 791: Error: Formal argument 1 of type char* in call to std::strcpy(char*, const char*) is being passed unsigned char*.
line 791: Error: Formal argument 1 of type const char* in call to std::strlen(const char*) is being passed unsigned char*.
7 Error(s) detected.

Could you please help.

June 06, 2007 - 1:00 pm UTC

none of my code is c++, i don't know what you are trying to do, it is a standalone c program, if you want c++, you'll want to take the IDEA behind the code and port it to that aberration of a language ;)

CPP problem

Admirer, June 05, 2007 - 8:20 am UTC

In continuation of the previous comment, Proc seems to have a problem with process_1 because it is returnig an SQLDA type of pointer. Changing it to K&R style helps but finally it does not work if I try to compile the generated c++ code.

Awesome

reader, June 29, 2007 - 3:02 pm UTC

Tom,

I have never written a line of C code, what are the steps that I need to follow to get use this awesome utility ?

I have downloaded the c source and compiled it using proc

We are running RH Linux and Oracle Database version 10gr2.

Thanks

Column with CHAR(1)

Admirer, July 05, 2007 - 9:08 am UTC

Tom,
When I use this tool to dump a table which has column defined as "CHAR(1)", the result is ^A for this column. Where as when I try doing a sqlplus dump, I get the actual value, even when I am running the same query as "select * from tablename" for both sqlplus and your tool.

To avoid this will I have to use something like "ascii(char_column)" function? If this is true then I will not be able to do a "select * from tablename" but will have to do something like:
" select column1 ||,|| ascii(char_column2 ||,|| column3 from tablename"
I also fear that including a conversion function may reduce the speed of the dump.

Any comments please.

July 05, 2007 - 1:11 pm UTC

when you debug it and see what it might be doing wrong, let us know and then everyone can benefit.

I've not had a problem with small fields.

Timestamp columns

Suryanarayana Murthy, August 01, 2007 - 6:55 pm UTC

Hi Tom,
I used your ProC code to download data from many tables and is pretty fast. But I have problems downloading tables which have TIMESTAMP Columns. They are returned with null values instead. I have never written ProC code and so could not debug the problem. Can you help us here?
I can give you more details if you need any. We are running this on 10gR2.

Thanks,
Murthy

August 05, 2007 - 10:15 am UTC

simple 1 nanosecond fix:

use to_char() in the sqlstmt, format the timestamp as per your needs

done.

load into table using Pro*C program

william, August 02, 2007 - 5:42 am UTC

Hi Tom,
thanks for your Pro*C program it's very efficient.
The question is:
can the Pro*C program load into table the previous data extracted in the text file?

So i don't want to use SQL*Loader 'cos i think it's slower than Pro*C but i don't know how can i modify the code.

Can you have an idea?

Thank a lot.

August 05, 2007 - 10:19 am UTC

wow, 'cos i think it's slower than Pro*C

hmmm

why do you think that? given that sqlldr is a C program itself.

use sqlldr
use external tables

do not write yet more code.

32/64 bit pointer problem??

Kevin, August 17, 2007 - 4:59 pm UTC

Hi Tom, I used your code and it worked perfectly in 32-bit, however I now need to make it work in 64-bit environment but it is core dumping. You mentioned earlier that it might be a 32/64 bit pointer problem. Do you know of any issues or solutions for using this code in 64-bit? GDB output gives the following info:

Program received signal SIGSEGV, Segmentation fault
si_code: 1 - SEGV_MAPERR - Address not mapped to object.
0x9fffffffeeef65c0:0 in lxhmcnv+0x2e0 ()
from /oracle/product/10.2.0/lib/libclntsh.so.10.1

Any help or reference places would be greatly appreciated.

Thanks,
Kevin (not the same Kevin as the original post)

HillChen, September 28, 2007 - 5:08 am UTC

Hi Tom,
Use to_char() in the sqlstmt that can solve the problem of TIMESTAMP column. I will going to use "select * from XXX" in the sqlstmt,because the table has too many columns. How can I fix your program? Please help me.

Thanks!
Hill

September 28, 2007 - 5:36 pm UTC

just list the columns, you only have to type it in ONCE after all.

SQLDA for Java

william, October 25, 2007 - 9:22 am UTC

Tom,
the SQLDA structure is available in Java?

If not , do you know if there is something similar?

Thanks.

October 25, 2007 - 6:31 pm UTC

there is just what is documented in the jdbc api guides.

basically, you programmatically access the metadata about an opened result set in jdbc - there are methods to discover the number of columns, the names, the types and so on.

you would not need a sqlda for that.

Execution on Windows

pratik, November 02, 2007 - 3:46 am UTC

Hi.....
I have problem in implementing your code on windows machine..
I am not very well aware of C programming.

I had follow following step:-
1>precompilation of dataload.pc using Proc

2>then trying to compile dataload.c using borland turbo c,, but got error of undefine symbol _sqlald, _sqlclu, _sqlcxt, _sqlnul

Can you specify full step or setting require to use this code on windows machine using turbo C or Microsoft Visual C??

Thanks
Pratik

Good comparison to my Perl DBI program

Peter, November 09, 2007 - 4:40 pm UTC

One of the users said he attempted to write out 2 million rows in 8 minutes. My Perl DBI program dumps out in half the time so this solution will not work for me but is greatly appreciated.

eval {
while ($row = $sth->fetchrow_arrayref()) {
printf(IOF "%s\n", join($delimiter, @$row));
$rowCount++;
}
};

Thanks Tom.

No rule to make target `xssqlimp.c'

Peter, November 09, 2007 - 5:53 pm UTC

Hi Tom,

I attempted to compile your Pro*C program by doing the following steps and am getting this error. Would you happen to know what this file is for?

make: *** No rule to make target `xssqlimp.c', needed by `xssqlimp.o'. Stop.

1) proc iname=dump_table.pc oname=dump_table.c
2) make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk OBJS=dump_table.o EXE=dump_table

Any feedback is greatly appreciated.

Thanks.

Peter

November 10, 2007 - 11:32 am UTC

after reading the header for 10g, they changed it a bit:

$ make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk build OBJS=dump_table.o EXE=dump_table

add build.

Compiling Pro*C program with errors

Peter, November 13, 2007 - 1:24 pm UTC

Hi Again Tom,

I am assuming that C compiler is not available on my server?

$ make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk build OBJS=dump_table.o EXE=dump_table
cc -Xa -xstrconst -dalign -xF -xildoff -errtags=yes -v -xarch=v9 -xchip=ultra3 -W2,-AKNR_S -Wd,-xsafe=unboundsym -Wc,-Qiselect-funcalign=32 -xcode=abs44 -Wc,-Qgsched-trace_late=1 -Wc,-Qgsched-T5 -xalias_level=weak -D_REENTRANT -DSS_64BIT_SERVER -DBIT64 -DMACHINE64 -K PIC -c -I/opt/app/oracle/product/10.2/rdbms/demo -I/opt/app/oracle/product/10.2/rdbms/public -I/opt/app/oracle/product/10.2/plsql/public -I/opt/app/oracle/product/10.2/network/public -I/opt/app/oracle/product/10.2/precomp/public dump_table.c
make: cc: Command not found
make: *** [dump_table.o] Error 127

November 16, 2007 - 2:41 pm UTC

looks likely.

My apologies

Peter, November 13, 2007 - 1:59 pm UTC

Hi Tom,

Please disregard my previous message. It appears that someone is already using your Pro*C program here.

Thanks a bunch.

Peter

ORA-00911: invalid character

Peter, December 04, 2007 - 10:01 am UTC

Hi Tom,

We are passing a SELECT statement of (SELECT * some_table_name) and we are getting the ORA-00911 error. Is there a way to work around this? It appears that we must define all the column names for the Pro*C program to work. Hope you can assist.

Thanks.

Peter

core dump

Jeff, December 29, 2007 - 9:55 am UTC

Hi Tom,

We have one job to export all data in big table to flat file every day. The table has over 10 million rows and the flat file is about 5G. 

Now we are using a Java program to do the exporting work, it takes about 5 hours to complete. After I read your code, I think this should work perfectly well to reduce the time.

But I have some issues to compile and run your code, the server is Solaris 8, the database is 10gR2. 

The compile is working fine, just some warning. 

make ¿f demo_proc.mk sample.pc

But every time I ran, the code crashed with core dump. (I am using the demo schema HR employees table to test.)

./sample userid=hr/hr' sqlstmt=select * from employees'  arraysize=100
Memory fault(coredump)

I narrowed down the issue to this line in process_2

EXEC SQL FOR :array_size FETCH C
                      USING DESCRIPTOR select_dp;

If this line is commented out, the code will work. I used dbx to analyze the core file; it shows the memory mapping issue.

Also using the same compiler, I compiled the sample10.pc (provided by Oracle¿s in 10gR2 in precomp directory), it works no problem. And the sample10 uses the same code but works fine. 

Do you have any suggestion why the code crashed at that line? 

Thanks

Jeff

January 01, 2008 - 5:50 pm UTC

look for 32bit pointer issues I might have (I've only ever compiled 32bit - scan the code and see if I've assumed 32bit somewhere...)

A reader, January 02, 2008 - 12:18 am UTC

Tom,

I recompiled the code using 32-bit make file, and it worked. It uses only 3 minutes to extract a 4 milliion test table, if I use sqlplus and the dump_csv you provides, it would take about 30 minutes.

Thank you so much! You the best.

Jeff

explicit instruction

Josh Collier, February 13, 2008 - 12:25 pm UTC

I'd love to start using this, but I know very little about pro*c and this thread lacks specific instructions about how to compile the program. A list of steps would be great.

February 13, 2008 - 1:15 pm UTC

it varies by platform and version, please either refer it to someone you work with that does compile C or check out the demo directories where sample makefiles exist.

if you search for "make" on this page - you'll see a couple of examples, but not all will work for you depending on your platform (which I hope isn't windows :( )

Compatibility with newer versions of Oracle?

Meriadoc, March 07, 2008 - 4:21 pm UTC

Tom,

Thanks for the exellent article. I was wondering has anyone tried this for the newer versions of Oracle 9.x / 10.x?

March 10, 2008 - 10:45 am UTC

yes, it is just standard PRO*C, should work in version 42 even when that comes out.

Pro C Flat with UTF chaacters

Les Smith, May 14, 2008 - 11:58 pm UTC

Hi Tom,

I compiled your Pro*C program and it works very fast. However, I need to adapt it so that it can print out UTF-8 characters. So far I have not been successful with doing that. Do you or one of the readers have a version that outputs UTF characters?

Thanks,
Les

UTF Output

Les, May 20, 2008 - 7:39 pm UTC

The program seem to work fine with UTF after all.  However, my unix shell doesn't.  So if the environment is set to UTF the correct unicode character will be output.  Since I can't see it it on the screen I need to output to a file.  Then I need to copy the file to my local computer where I use a text editor that supports UTF. 


export LD_LIBRARY_PATH=/app/oracle/product/10.2.0/client_1/lib:/usr/lib:/usr/openwin/lib
export NLS_LANG=AMERICAN_AMERICA.UTF8
/unloader    userid=xx/xx@xxx  sqlstmt="select *  from test   "  arraysize=1000 > test.txt

Pro*C is Gone , Java is better ?!

Bob, September 24, 2008 - 1:26 am UTC

Hello Tom
is there a Java version of your Pro*C unloader ?
and theoretically is java performance in this area better than Pro*C ?

regards

September 24, 2008 - 7:04 pm UTC

there is not....

but it would be easy to code....

and I doubt that it would *exceed* the performance of C, it might meet it if you code it well enough.

pro*c is great

Amir Riaz, February 23, 2009 - 3:26 am UTC

hi tom.

1. Can i hava a pro*c compiler for oracle 11g windows. I have heard that oracle 11g have a built in c compiler in it to convert pl/sql code to c.

2. If yes then where can i find it in oracle 11 directory.

3. if no then where can i find a pro *c compiler for windows. I have looked at sun site but its for solaris. can you suggest any link or so

4. do you suggest pro*c, plsql or external tables for data unloading

thanks

Regards
Amir Riaz

February 23, 2009 - 3:59 am UTC

1) you are mixing things up.

Pro*C - program to take C code with embedded SQL calls and convert the embedded SQL calls into yet more C code which can than be compiled by the system C compiler.

Native compilation - in 11g, we can compile plsql dirction to object code (bypassing any intermediate secondary 3gl like C). Straight from PLSQL to object code in the operating system. No C involved.

You can have Pro*C with 11g, it certainly exists,

2) it is probably on the companion CD if it is not installed - look in oracle_home/bin for proc*.exe, read the documentation as well.

3) we are the only ones with Pro*C for Oracle - it is a PRE-COMPILER, not a compiler, you still need a system C compiler after that.

4) depends, if you are moving data between Oracle and Oracle - AND you had to unload/reload the data (eg: no dblinks), data pump - available as create table as select with external tables or via a plsql api or via a command line tool would be the approach I would take.

data pump cannot create a 'flat file' however, under windows, I would probably use plsql or SQLplus first and foremost because it is such a hassle to actually get everything going for pro*c and C compilation in that environment as opposed to oh say unix.

thanks you are informative as ever

Amir Riaz, February 25, 2009 - 1:52 am UTC

Hi,

thanks for the reply.

so in case i have written some code in pro*c. I have to pass through the following steps.

source code =>Precompiler=>modified source code=>compiler=>object program =>linker =>source program

but in case of native code. oracle will go like this
plsq/sql code =>object program =>linker =>source program

I think for both we need C compiler.

for windows what kind of c compiler i can user. can i user mirosoft vc compiler or sun studio what is you suggestion.

regards
Amir Riaz

February 25, 2009 - 6:45 am UTC

... I think for both we need C compiler. ...

after writing what you wrote, why do you think that?

in one case you wrote:

source code =>Precompiler=>modified source code=>compiler=>object program
=>linker =>source program

In the other - you did not have that word "compiler". You got it right in your lists - you came to the wrong conclusion.

In 11g, you do not need - nor would the presence of assist - a C compiler for native compilation. It is truly:

plsq/sql code =>object program =>linker =>source program

(assuming source program means "executable stuff")

we go straight from plsql to object code, there is no intermediate 3gl language in the way.

we support the microsoft compiler on windows.

why am i getting link errors

Amir Riaz, February 28, 2009 - 12:29 am UTC

hi tom,

I am getting link error. I have used the extration procedure you have coded above

here is my build file. i am using vc6

SET INCLUDE_PATH_STANDARD="D:\Program Files\Microsoft Visual Studio\VC98\Include"
SET INCLUDE_PATH_PROC=D:\app\amir\product\11.1.0\db_1\precomp\public
SET LIBRARY_PATH_PROC=D:\app\amir\product\11.1.0\db_1\precomp\lib\msvc\orasqx11.LIB
SET LIB="D:\Program Files\Microsoft Visual Studio\VC98\Lib"
proc sqlcheck=semantics parse=full include=%INCLUDE_PATH_STANDARD% extractcsv.pc
cl -I %INCLUDE_PATH_STANDARD% -I %INCLUDE_PATH_PROC% extractcsv.c /link %LIBRARY_PATH_PROC%

here is what i am getting
D:\extract>procCallJSP.bat

D:\extract>SET INCLUDE_PATH_STANDARD="D:\Program Files\Microsoft Visual Studio\VC98\Include"

D:\extract>SET INCLUDE_PATH_PROC=D:\app\amir\product\11.1.0\db_1\precomp\public

D:\extract>SET LIBRARY_PATH_PROC=D:\app\amir\product\11.1.0\db_1\precomp\lib\msvc\orasqx11.LIB

D:\extract>SET LIB="D:\Program Files\Microsoft Visual Studio\VC98\Lib"

D:\extract>proc sqlcheck=semantics parse=full include="D:\Program Files\Microsoft Visual Studio\VC98\Include" extractcsv.pc

Pro*C/C++: Release 11.1.0.6.0 - Production on Sat Feb 28 10:10:26 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

System default option values taken from: D:\app\amir\product\11.1.0\db_1\precomp\admin\pcscfg.cfg

D:\extract>cl -I "D:\Program Files\Microsoft Visual Studio\VC98\Include" -I D:\app\amir\product\11.1.0\db_1\precomp\public extractcsv.c /link D:\app\amir\product\11.1.0\db_1\precomp\lib\msvc\orasqx1
1.LIB
Microsoft (R) 32-bit C/C++ Standard Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.

extractcsv.c
Microsoft (R) Incremental Linker Version 6.00.8168
Copyright (C) Microsoft Corp 1992-1998. All rights reserved.

/out:extractcsv.exe
D:\app\amir\product\11.1.0\db_1\precomp\lib\msvc\orasqx11.LIB
extractcsv.obj
extractcsv.obj : error LNK2001: unresolved external symbol _sqlcxt
extractcsv.obj : error LNK2001: unresolved external symbol _sqlnul
extractcsv.obj : error LNK2001: unresolved external symbol _sqlclu
extractcsv.obj : error LNK2001: unresolved external symbol _sqlald
extractcsv.exe : fatal error LNK1120: 4 unresolved externals

I have found out that sqlcxt is an external procedure which require sqllib. similarly others are procedure or function can you help me what i am missing

regards
Amir Riaz

March 03, 2009 - 9:56 am UTC

You haven't linked in the right Oracle code.

Do you have the demo's installed? There should be a demo there to show you how to build an extproc, I'd work through that first and foremost.

thanks problem resolved

A reader, March 05, 2009 - 1:34 am UTC

thanks alot tom for you guidance. yes i am linking the wrong library. now its been resolved. thanks

regards
Amir Riaz

Not working on 10g when called from PL/SQL

Salaam Yitbarek, May 27, 2009 - 6:56 pm UTC

I've been calling this code from PL/SQL, using the RunOSCmd Java module that is used to run OS commands.

This has been working on Oracle 9i (SunOS). We're now upgrading to 10g, and it's not working anymore. Specifically, calling it straight from the OS command line works. Calling it from PL/SQL via RunOSCmd no longer works. RunOSCmd by itself works for other commands. Any idea on how the 9i to 10g change may be affecting this?

Thanks.

May 28, 2009 - 7:09 am UTC

my car won't start. I upgraded to road version 2.0. it just won't work. Well, if I push it, it works a little - but not automatically.

we are even, I've given you as much detail as you gave me regarding your problem. When you can answer me, I'll answer you.

how about at least a definition of "it's not working anymore"

Oh, and, important bit here - define what this "runoscmd java module" is.

and.... what debugging have you done? what instrumentation do you have in your java code that you can turn on to see what is happening (or not)? If you say "i have none", that would be step 0 - to add it.

Okay, more details...

Salaam Yitbarek, May 29, 2009 - 2:27 pm UTC

Okay, I thought I could get away with it... perhaps my story would ring a bell.

Here are the details.

RunOSCmd, the Java code that I use to run programs on the command line, is as follows:

------
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "RunOSCmd" AS
import java.io.*;
    import java.lang.*;
    
    public class RunOSCmd extends Object
    {
      public static int runOsCmd(String OsCmdLine,
                                 String[] CmdOutput)
     {
     Runtime rt = Runtime.getRuntime();
     int     rc = -1;
     String  coutput = " ";
     String  ot = " " ;

     if (OsCmdLine.length() < 1) {
         System.out.println("USAGE: java RunOSCmd.runOsCmd \'cmd\' OutputStringVariable");
         System.exit(1);
     } 
     try
     {
        // The exec() method in java.lang.Runtime is overloaded and
        // can be called in different forms.  The following use illustrates
        // the simplest form of exec() which takes a string as a parameter.
        //
        // Execute the command using the Runtime object variable 'rt' and
        // get the process 'p' which controls this command
        Process p = rt.exec(OsCmdLine);
   
        int bufSize = 4096;
        BufferedInputStream bis = new BufferedInputStream(p.getInputStream(), bufSize);
        int len;
        byte buffer[] = new byte[bufSize];
   
        // Echo back what the program spit out
        while ((len = bis.read(buffer, 0, bufSize)) != -1) {
           System.out.write(buffer, 0, len);
           ot = new String(buffer, 0, len) ;
           coutput = coutput + ot ;
        }
        // Wait for the process 'p' to finish and get the return code
        // from the process 
        rc = p.waitFor();
     }
     catch (Exception e)
     {
        e.printStackTrace();
        rc = -1;
     }
     finally
     {
        CmdOutput[0] = coutput;
        return rc;
     }
     }
   }
/
------

I call it using the following wrapper:

CREATE OR REPLACE FUNCTION FUNC_RUNCMDJAVA (
    fv_command   IN   VARCHAR2,
    fv_output    OUT  VARCHAR2
  )
    RETURN NUMBER
  AS
    LANGUAGE JAVA
    NAME 'RunOSCmd.runOsCmd( java.lang.String,
                             java.lang.String[] ) return integer';
/


I use the above to issue command line commands from PL/SQL, as follows:

------
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
var_ErrorCode        NUMBER;
var_Output           VARCHAR2(10000);
var_Command          VARCHAR2(4000);
BEGIN
    var_Command := '/usr2/home/cansim/cansim3export/run_test.sh';

    DBMS_OUTPUT.PUT_LINE('start');
    var_ErrorCode := FUNC_RUNCMDJAVA(var_Command,var_Output );
    DBMS_OUTPUT.PUT_LINE('->' || var_ErrorCode);
    DBMS_OUTPUT.PUT_LINE(var_Output);

END;

start
->0
 Note: Oracle set to PRD
Error is:0
PL/SQL procedure successfully completed.
------


The shell script run_test.sh from above is as follows:

------
#!/bin/ksh

. /usr/local/bin/chsid PRD

echo `ls` > /usr2/home/cansim/cansim3export/touch_test.txt

the_error=`echo $?`
echo Error is:$the_error
------


The script executed successfully - the results of the ls were put in the file touch_test.txt.

So the above does successfully execute some commands.


However, it is not successfully executing the following:

------
SQL> DECLARE
var_ErrorCode     NUMBER;
var_Output        VARCHAR2(10000);
var_Command       VARCHAR2(4000);
var_FileName      VARCHAR2(30) := 'testexport';
var_SID           VARCHAR2(10) := 'PRD';

BEGIN
    var_Command := '/usr2/home/cansim/cansim3export/run_export.sh 100 testexport PRD CANSIM2 SELECT 1 FROM DUAL';

    DBMS_OUTPUT.PUT_LINE('start');
    var_ErrorCode := FUNC_RUNCMDJAVA(var_Command,var_Output );
    DBMS_OUTPUT.PUT_LINE('->' || var_ErrorCode);
    DBMS_OUTPUT.PUT_LINE(var_Output);

END;
start
->0
 Note: Oracle set to PRD
100
testexport
PRD
CANSIM2
SELECT 1 FROM DUAL
CANSIM2/the_password
Error is:126
PL/SQL procedure successfully completed.
------

The only difference between the above anonymous block and the one before is the command that is being run.

Here, I'm calling run_export.sh. run_export.sh is just a wrapper shell script that calls exportwc, which is just a renamed version of your array_flat program.

(The reason I call the wrapper and don't call exportwc directly using:

var_Command := 'exportwc userid=CANSIM2/the_password sqlstmt="SELECT 1 FROM DUAL" arraysize=100 >> testexport'

is because I can't get RunOSCmd to work when double quotes are in the string passed to it.)

The expected result of the above anonymous block is a '"1"' in the file testexport. The file testexport was created, but it was empty.

exportwc returned an error code 126 - I don't know what that means.

Below is run_export.sh:

------
#!/bin/ksh

#run_export.sh
#
#Purpose: export CANSIM III working copies to flat file for export to Net B
#
#Depends on:
#1. Full user and group permissions in the local directory
#2. Arguments sql statement, array size, output filename, sid, and schema
#4. The program /usr/local/bin/chsid
#5. The program ./exportwc
#

USAGE="/usr2/home/cansim/cansim3export/run_export.sh arraysize outputfilename sid schema sqlstatement"
if [ $# -lt 5 ] ; then
  echo Usage is "$USAGE"
  exit 1
fi

arraysize=$1
shift
filename=$1
shift
sid=$1
shift
schema=$1
shift
sqlstmt=$*

#set database
. /usr/local/bin/chsid PRD
#. /usr/local/bin/chsid $sid

#go to directory where csv files are kept
cd /usr2/home/cansim/cansim3export

echo $arraysize
echo $filename
echo $sid
echo $schema
echo $sqlstmt
echo "$schema"/"$schema""$sid"

#run export -- the './' is necessary
./exportwc userid=$schema/the_password sqlstmt="$sqlstmt" arraysize=$arraysize >> $filename

the_error=`echo $?`
echo Error is:$the_error
------


When I run the script directly from the command line, it works. That is, I get a "1" in the file testexport:
 
------
[/usr2/home/cansim/cansim3export]:  /usr2/home/cansim/cansim3export/run_export.sh 100 testexport PRD CANSIM2 SELECT 1 FROM DUAL
Note: Oracle set to PRD
100
testexport
PRD
CANSIM2
SELECT 1 FROM DUAL
CANSIM2/the_password

Connected to ORACLE as user: CANSIM2/the_password

Unloading 'SELECT 1 FROM DUAL'
Array size = 100
1
1 rows extracted
0
[/usr2/home/cansim/cansim3export]:  more testexport
"1"
[/usr2/home/cansim/cansim3export]:
------

I'm on SunOs 5.10, Oracle 10.2.0.4.0.

This is the extent of debugging I've done.

Thanks.

June 01, 2009 - 7:27 pm UTC

so, like I said, what debugging have YOU done

have you done simple things like....

o shown that the script is run (simple echo to some trace file)
o shown the inputs are what you think they are( simple echo to a trace file)

have you done any basic debugging 101 methods? Any at all?

according to you - no, you have done none - this is what we do for a living - this is our *job*.

I care not a whit that "it runs on the command line", show us what is happening when you run it from plsql with the copious amounts of DEBUG bug that you shall add.

Yes, I debugged...

Salaam Yitbarek, June 03, 2009 - 8:10 am UTC

Yes, I put in trace statements. It's all in my posting above.

To summarize, here are the relevant echos in the shell script runexport.sh above:

  echo $arraysize
  echo $filename
  echo $sid
  echo $schema
  echo $sqlstmt
  echo "$schema"/"$schema""$sid"

  ./exportwc userid=$schema/the_password sqlstmt="$sqlstmt" arraysize=$arraysize >> $filename

  the_error=`echo $?`
  echo Error is:$the_error


The relevant output is as follows:

  100
  testexport
  PRD
  CANSIM2
  SELECT 1 FROM DUAL
  CANSIM2/the_password
  Error is:126

As you can see, the script runs - the output from the echos is correct. The parameters to exportwc (array_flat) are correct.

The problem is of course the output from exportwc (array_flat), error code 126. What this is, I don't know. Where it occurs, I'm not sure. I tried outputing trace from array_flat into a trace file, but it doesn't even get to opening the file. I think where it fails is the call to array_flat itself.

June 04, 2009 - 9:17 am UTC

touch the file, can you create that file - instead of running array_flat - run something else to create the file (prove you can create the file)

if that succeeds, then prove you can "see" array_flat (exportwc) - prove it is executable by you, include "id" in your debug output, show who you are, echo your path, make sure it makes sense

there are dozens more things you could do here - give them a try.

array_flat.pc with binds?

John, July 03, 2009 - 12:34 am UTC

I am not c coder but am really interested in getting a copy of the array_flat.pc that has bind variables functionality suggested in a previous review.

I have tried myself but I am not confident on the simplest part of parsing the command parameters never mind where to add the rest of the code in!

Really hope someone has a copy lying around :-)

July 06, 2009 - 7:31 pm UTC

just add:

execute immediate alter session set cursor_sharing=force;

to the proc code, let is auto bind. Not worth (really, truly not worth) any effort making it supply a where clause bit by bit with binds, this would be a case whereby cursor sharing force would be sensible and useful

Native Compilation

David, July 04, 2009 - 10:33 pm UTC

Using PL/SQL, we fetch 3 million records and write to a flat file using utl_file package. As you can understand it takes a while.

If we use native compilation for that PL/SQL , will it run faster?

What are the other options I have?

July 06, 2009 - 8:13 pm UTC

probably not, you are not doing very much in your plsql code.

You can array fetch (that'll make the FETCH using BULK COLLECT potentially more efficient) and you can "batch write" - eg: build a string of 32k AND THEN write it out - don't write line by line or worse yet - column by column

Or, do it in a 3gl and it'll be many many times faster.

Performance Comparison

Mark Wooldridge, July 07, 2009 - 5:22 pm UTC

I use a modified version of Tom's original csv_dump pl/sql program. We have modified it to support writing out xml/sdo_geometry as clob and blob as files also. I can unload 1233 records from a 34 column table in about .54 seconds. Its not 3 million but estimates show it could do about 2283 rows per second or about 3 million
in 36 minutes. I can dump 50874 records from all_objects in 11.54 secs or about 4408 rows/second.

need help for timestamp

Praveen Reddy, August 11, 2010 - 8:45 am UTC

i am using your code ( pro*c) to unload data from oracle and i have problem getting data for timestamp data type columns

Please advice

Thanks
Praveen

August 19, 2010 - 12:04 am UTC

my car won't start.

there, we are even. I don't know what your problem is and you do not know what mine is either.

export csv to client machine

Ravi B, July 16, 2013 - 8:40 pm UTC

Tom,

We have a product, in which one of the features is to export the result of a query to csv, on the local system where the product is run(not on the server where oracle is installed) similar to SQL developer export. Our product works on both Oracle and well as SQL Server. SQL Server has native way to export to a file on the client system which we use. For oracle we use sqlplus.

We have a performance bench mark. For about 50 million rows, SQL Server exports to CSV in about 10 minutes where as oracle takes about 2hrs 30 mins plus to export the same amount of data to csv(same hardware is used).

What is the optimal way to dump to csv to client for Oracle other than SQL Plus? We cannot use UTL_FILE because it would write only to the server.

July 17, 2013 - 5:21 pm UTC

see
http://asktom.oracle.com/Misc/httpasktomoraclecomtkyteflat.html

a custom app that just does one thing would be the most efficient. sqlplus is a "jack of all trades" tool - it is a report writing tool. while it can retrieve data pretty efficiently, it is expecting to do a lot with the data.

on a fairly dated machine:

[root@dellpe ~]# dmesg | grep -i poweredge
DMI: Dell Inc. PowerEdge 2950/0H603H, BIOS 2.7.0 10/30/2010
PCI: Dell PowerEdge 2950 detected, enabling pci=bfsort.
[root@dellpe ~]# uname -a
Linux dellpe 2.6.39-400.109.1.el6uek.x86_64 #1 SMP Tue Jun 4 23:21:51 PDT 2013 x86_64 x86_64 x86_64 GNU/Linux
[root@dellpe ~]# lscpu
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                8
On-line CPU(s) list:   0-7
Thread(s) per core:    1
Core(s) per socket:    4
Socket(s):             2
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 23
Stepping:              10
CPU MHz:               2659.958
BogoMIPS:              5320.04
Virtualization:        VT-x
L1d cache:             32K
L1i cache:             32K
L2 cache:              6144K
NUMA node0 CPU(s):     0-7

I was able to unload 100,000,000 generously sized records in under 30 minutes with sqlplus.

[tkyte@dellpe sqlstuff]$ time ./flat big_table/big_table big_table > big_table.dat

real    28m1.977s
user    17m56.219s
sys     0m58.175s
[tkyte@dellpe sqlstuff]$ ls -lag big_table.dat
-rw-rw-r--. 1 tkyte 10819579493 Jul 17 11:32 big_table.dat
[tkyte@dellpe sqlstuff]$ wc big_table.dat
  100000013   136517478 10819579493 big_table.dat
[tkyte@dellpe sqlstuff]$

and if I use the pro*c program (a custom program, just does one thing), it take about 1/3 the time:

$ time ./flat userid=big_table/big_table "sqlstmt=select * from big_table" arraysize=1000 > big_table.dat

Connected to ORACLE as user: big_table/big_table

Unloading 'select * from big_table'
Array size = 1000
ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY
100000000 rows extracted

real    11m24.712s
user    9m5.802s
sys     0m23.905s

A reader, November 23, 2015 - 6:43 pm UTC

Hi guys,

I'm working on a POC trying out Postgres to load a bunch of our CLOB data that isn't used much in one of our applications. I came across Tom's Proc*C program that looked like a good way to dump this and get it into PG. My questions are

1) Since this thread was started 15 years ago, would you say that is still a good way to go?

2) If the answer to #1 is yes, would you mind outlining the steps necessary to get to the point where I have something to run? I don't have any C experience.

The target platform is

(localhost.localdomain:oracle)> uname -a
Linux localhost.localdomain 2.6.32-573.1.1.el6.x86_64 #1 SMP Tue Jul 14 02:46:51 EDT 2015 x86_64 x86_64 x86_64 GNU/Linux

Thank you.

November 24, 2015 - 1:09 am UTC

Perhaps look at something like http://pgxn.org/dist/oracle_fdw/

and try go direct

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Yet another ASCII dump issue

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

PRO*C Compilation

prepare problem

bug in code

Slow

Excel Format

Excellent Pro*C "table dump" program!!

pro*c unload

Work Around...

I had the same problem

I've managed to fix that problem, but still having compile problems

Unloading to a flat file when running on Windows

great program

Yet another ASCII dump issue

pro*cobol

Yet another ASCII dump issue

Make error

Its realy v.good

One more question

create CSV using PRO*C and stored procedure

Please help PRO*C CSV using Stored Procedure

specify date format in SQL

Date format

Thank you very much

Array size = 100 or 10000

SHARED MEMORY

in addition

To Kom

set_bind_variables(char * bindValues[] )

Bind Variables IN clause

Great!! thanks

Strored Procedures

Please help

Clob

Add SQLLDR control information

Multi-user

Concurrent Users, Data Unloading using Pro*C

in addition...

Wor

some info for Pro*C options

Coredump

Coredump

Coredump

Coredump

Coredump

Coredump

Coredump

Coredump

Coredump

Coredump

Coredump

Coredump

Oracle internal error trapping

ARRAYSIZE

Help with Compiling

csv soft where I am looking for some help NOW

When I download very big size data, about 60GigaBytes ,how can I do?

A weird problem with array_flat

Last message

Referring last message

Followup

The answer seems to be right in front of you

More about notepad

Fix for Coredump on HPUX -11/Oracle 9.2.0.5 (64-bit)

.... slight change to prog

Benchmark

how do we process data from flat file using "HOST ARRAY"

oracle fast extract into ascii text

Improved Script - User defined RECORDSEPARATOR, FIELDSEPARATOR, DOUBLEQUOTE=TRUE/FALSE

coredump AIX 5.2

Coredump on AIX 5.2

sqlald?

Lack of native ASCII dump utility

fast Oracle data export import