• Questions
• # Yet another ASCII dump issue

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

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

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, "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

### 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

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

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

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

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

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

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

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
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
type_code oracle Use Oracle or ANSI type codes for Dynamic SQL
unsafe_null no Allow a NULL fetch without indicator variable
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

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)

### 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

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, "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...

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

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.

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 Tom, How would you add the SQLLDR control header to the output automatically, as you did with the SQL*Plus script?$ sqlldr_exp scott/tiger dept
INFILE *
INTO TABLE dept
REPLACE
FIELDS TERMINATED BY '|'
(
deptno
,dname
,loc
)
BEGINDATA
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|RESTON
40|OPERATIONS|BOSTON

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.

December 03, 2003 - 6:47 am UTC

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

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.

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....
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.

has pointers to the code.

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.
has pointers to the code.

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

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

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

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

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

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 ***
*** ***
**************************************************************

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.
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, "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
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

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

### 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.

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
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 fill array
you say "oracle insert this array of data" when you fill array.

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, "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 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: <>

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__ );

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.

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

Connected to ORACLE as user: <>

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?

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.



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,

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

Thirumaran

October 28, 2005 - 1:21 pm UTC

### 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.
-- 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

}
};

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

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