PRO*C Compilation
August 24, 2001 - 11am Central time zone
Reviewer: A reader from England
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
June 22, 2002 - 10am Central time zone
Reviewer: Kapil from India
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?
Followup June 22, 2002 - 1pm Central time zone:
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
July 21, 2002 - 11am Central time zone
Reviewer: Kapil from INDIA
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
October 7, 2002 - 10am Central time zone
Reviewer: A reader
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
Followup October 7, 2002 - 12pm Central time zone:
is this a question?
The code works with any number of columns and any number of rows.
Define "slow" for me -- do you have something "faster"? I just ran on RH Linux 7.3 with 9iR2 and a
1,000,000 row table. big_wide_table has over 100 columns, big_table has 14 (1/8 the data). Ran
against all_objects as well (1/34th the data of big_table)
Timings were:
[tkyte@dhcp-reston-gen-3-west-120-222 array_flat]$ ./test.sh
Connected to ORACLE as user: big_table/big_table
Unloading 'select * from big_wide_table'
Array size = 100
ID_1,ID_2,ID_3,ID_4,ID_5,ID_6,ID_7,ID_8,OWNER_1,OWNER_2,OWNER_3,OWNER_4,OWNER_5,OWNER_6,OWNER_7,OWNE
R_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,D
ATA_OBJECT_ID_4,DATA_OBJECT_ID_5,DATA_OBJECT_ID_6,DATA_OBJECT_ID_7,DATA_OBJECT_ID_8,OBJECT_TYPE_1,OB
JECT_TYPE_2,OBJECT_TYPE_3,OBJECT_TYPE_4,OBJECT_TYPE_5,OBJECT_TYPE_6,OBJECT_TYPE_7,OBJECT_TYPE_8,CREA
TED_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_TIM
E_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,TEMP
ORARY_3,TEMPORARY_4,TEMPORARY_5,TEMPORARY_6,TEMPORARY_7,TEMPORARY_8,GENERATED_1,GENERATED_2,GENERATE
D_3,GENERATED_4,GENERATED_5,GENERATED_6,GENERATED_7,GENERATED_8,SECONDARY_1,SECONDARY_2,SECONDARY_3,
SECONDARY_4,SECONDARY_5,SECONDARY_6,SECONDARY_7,SECONDARY_8
1000000 rows extracted
real 5m9.647s
user 4m40.720s
sys 0m12.440s
5 minutes, 9seconds
Connected to ORACLE as user: big_table/big_table
Unloading 'select * from big_table'
Array size = 100
ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMES
TAMP,STATUS,TEMPORARY,GENERATED,SECONDARY
1000000 rows extracted
real 0m41.229s
user 0m38.370s
sys 0m1.530s
Hmmm, 1/8th the data, just about EXACTLY 1/8th the time - seems to not be affected by the WIDTH
of the table, how about the height?
Connected to ORACLE as user: big_table/big_table
Unloading 'select * from all_objects'
Array size = 100
OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAM
P,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
October 7, 2002 - 10am Central time zone
Reviewer: A reader from coludes
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
Followup October 7, 2002 - 12pm Central time zone:
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!!
December 3, 2002 - 10am Central time zone
Reviewer: Geoff Yaworski from Denver, CO USA
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.
March 18, 2003 - 5am Central time zone
Reviewer: Balasubramanian.S from Bangalore,KA, India
Very nice article
pro*c unload
March 28, 2003 - 7am Central time zone
Reviewer: Marcel Rosa from Brazil
I made the unload using the pro*c , how do I the load ?
Can you give me a example ?
Followup March 28, 2003 - 7am Central time zone:
sqlldr ....
look in $ORACLE_HOME/rdbms/demo/ulcase*.ctl
for example control files.
April 19, 2003 - 9pm Central time zone
Reviewer: Kamal Kishore from New Jersey, USA
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
Followup April 20, 2003 - 8am Central time zone:
what is in your
D:\Oracle\Ora_92\precomp\admin\pcscfg.cfg
file. As I recall, on windoze, the default settings are different then normal operating systems
like unix.
Here are my default settings:
[tkyte@tkyte-pc-isdn Desktop]$ proc
Pro*C/C++: Release 9.2.0.3.0 - Production on Sun Apr 20 08:50:21 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
System default option values taken from: /usr/oracle/ora920/OraHome1/precomp/admin/pcscfg.cfg
Option Name Current Value Description
-------------------------------------------------------------------------------
auto_connect no Allow automatic connection to ops$ account
char_map charz Mapping of character arrays and strings
close_on_commitno Close all cursors on COMMIT
cmax 100 CMAX Value for connection pool
cmin 2 CMIN Value for connection pool
cincr 1 CINCR Value for connection pool
ctimeout 0 CTIMEOUT Value for connection pool
cnowait 0 CNOWAIT Value for connection pool
code kr_c The type of code to be generated
comp_charset multi_byte The character set type the C compiler supports
config default Override system configuration file with another
cpool no Support connection pooling
cpp_suffix *none* Override the default C++ filename suffix
dbms native v6/v7/v8 compatibility mode
def_sqlcode no Generate '#define SQLCODE sqlca.sqlcode' macro
define *none* Define a preprocessor symbol
duration transaction Set pin duration for objects in the cache
dynamic oracle Specify Oracle or ANSI Dynamic SQL Semantics
errors yes Whether error messages are sent to the terminal
errtype *none* Name of the list file for intype file errors
fips none FIPS flagging of ANSI noncompliant usage
header *none* Specify file extension for Precompiled Headers
hold_cursor no Control holding of cursors in the cursor cache
iname *none* The name of the input file
include *none* Directory paths for included files
intype *none* The name of the input file for type information
lines no Add #line directives to the generated code
lname *none* Override default list file name
ltype short The amount of data generated in the list file
maxliteral 1024 Maximum length of a generated string literal
maxopencursors 10 Maximum number of cached open cursors
mode oracle Code conformance to Oracle or ANSI rules
nls_char *none* Specify National Language character variables
nls_local no Control how NLS character semantics are done
objects yes Support object types
oname *none* The name of the output file
oraca no Control the use of the ORACA
pagelen 80 The page length of the list file
parse full Control which non-SQL code is parsed
prefetch 1 Number of rows pre-fetched at cursor OPEN time
release_cursor no Control release of cursors from cursor cache
select_error yes Control flagging of select errors
sqlcheck syntax Amount of compile-time SQL checking
sys_include /usr/lib/gcc-liDirectory where system header files are found
/usr/lib/gcc-lib/i486-suse-linux/2.95.3/include
/usr/include
threads no Indicates a multi-threaded application
type_code oracle Use Oracle or ANSI type codes for Dynamic SQL
unsafe_null no Allow a NULL fetch without indicator variable
userid *none* A username/password [@dbname] connect string
utf16_charset nchar_charset The character set form used by UTF16 variables
varchar no Allow the use of implicit varchar structures
version recent Which version of an object is to be returned
PCC-F-02135, CMD-LINE: User asked for help
make sure the options match
Work Around...
April 19, 2003 - 10pm Central time zone
Reviewer: Kamal Kishore from New Jersey, USA
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?
Followup April 20, 2003 - 8am Central time zone:
no, it is something old with windoze -- one of the default options is different.
I had the same problem
April 20, 2003 - 1pm Central time zone
Reviewer: David Sansom from London, England
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
Followup April 20, 2003 - 2pm Central time zone:
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
April 20, 2003 - 2pm Central time zone
Reviewer: David Sansom from London, England
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
Followup April 20, 2003 - 2pm Central time zone:
the borland c compiler is not supported at all. ms visual c/c++ is.
Unloading to a flat file when running on Windows
April 21, 2003 - 5pm Central time zone
Reviewer: David Sansom from London, England
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
May 26, 2003 - 4am Central time zone
Reviewer: michel moureaux from Monaco
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 !
June 29, 2003 - 12pm Central time zone
Reviewer: A reader
Tom,
OS:R.H 7.3
Database: 9.0.1
i saved your code as t1.pc and when i give this command i get this error
$ proc iname=t1.pc
Pro*C/C++: Release 9.0.1.0.0 - Production on Sun Jun 29 09:19:10 2003
(c) Copyright 2001 Oracle Corporation. All rights reserved.
System default option values taken from: /u01/oracle/product/9.0.1/precomp/admin/pcscfg.cfg
Error at line 34, column 11 in file /usr/include/stdio.h
# include <stddef.h>
..........1
PCC-S-02015, unable to open include file
Error at line 29, column 10 in file /usr/include/bits/types.h
#include <stddef.h>
.........1
PCC-S-02015, unable to open include file
Syntax error at line 48, column 3, file /usr/include/bits/pthreadtypes.h:
Error at line 48, column 3 in file /usr/include/bits/pthreadtypes.h
size_t __guardsize;
..1
PCC-S-02201, Encountered the symbol "size_t" when expecting one of the following
:
} char, const, double, enum, float, int, long, ulong_varchar,
OCIBFileLocator OCIBlobLocator, OCIClobLocator, OCIDateTime,
OCIExtProcContext, OCIInterval, OCIRowid, OCIDate, OCINumber,
OCIRaw, OCIString, short, signed, sql_context, sql_cursor,
struct, union, unsigned, utext, uvarchar, varchar, void,
volatile, a typedef name,
The symbol "enum," was substituted for "size_t" to continue.
Syntax error at line 51, column 3, file /usr/include/bits/pthreadtypes.h:
Error at line 51, column 3 in file /usr/include/bits/pthreadtypes.h
size_t __stacksize;
..1
PCC-S-02201, Encountered the symbol "size_t" when expecting one of the following
:
} char, const, double, enum, float, int, long, ulong_varchar,
OCIBFileLocator OCIBlobLocator, OCIClobLocator, OCIDateTime,
OCIExtProcContext, OCIInterval, OCIRowid, OCIDate, OCINumber,
OCIRaw, OCIString, short, signed, sql_context, sql_cursor,
struct, union, unsigned, utext, uvarchar, varchar, void,
volatile, a typedef name,
The symbol "enum," was substituted for "size_t" to continue.
Syntax error at line 51, column 3, file /usr/include/bits/pthreadtypes.h:
Error at line 51, column 3 in file /usr/include/bits/pthreadtypes.h
size_t __stacksize;
..1
PCC-S-02201, Encountered the symbol "size_t" when expecting one of the following
:
} char, const, double, enum, float, int, long, ulong_varchar,
OCIBFileLocator OCIBlobLocator, OCIClobLocator, OCIDateTime,
OCIExtProcContext, OCIInterval, OCIRowid, OCIDate, OCINumber,
OCIRaw, OCIString, short, signed, sql_context, sql_cursor,
struct, union, unsigned, utext, uvarchar, varchar, void,
volatile, a typedef name,
The symbol "enum," was substituted for "size_t" to continue.
Error at line 14, column 10 in file /usr/include/_G_config.h
#include <stddef.h>
.........1
PCC-S-02015, unable to open include file
Error at line 48, column 10 in file /usr/include/wchar.h
#include <stddef.h>
.........1
PCC-S-02015, unable to open include file
Syntax error at line 72, column 5, file /usr/include/wchar.h:
Error at line 72, column 5 in file /usr/include/wchar.h
wint_t __wch;
....1
PCC-S-02201, Encountered the symbol "wint_t" when expecting one of the following
:
There is alot like this..and my proc is
$proc
Pro*C/C++: Release 9.0.1.0.0 - Production on Sun Jun 29 09:27:32 2003
(c) Copyright 2001 Oracle Corporation. All rights reserved.
System default option values taken from: /u01/oracle/product/9.0.1/precomp/admin/pcscfg.cfg
Option Name Current Value Description
-------------------------------------------------------------------------------
auto_connect no Allow automatic connection to ops$ account
char_map charz Mapping of character arrays and strings
close_on_commitno Close all cursors on COMMIT
code kr_c The type of code to be generated
comp_charset multi_byte The character set type the C compiler supports
config default Override system configuration file with another
cpp_suffix *none* Override the default C++ filename suffix
dbms native v6/v7/v8 compatibility mode
def_sqlcode no Generate '#define SQLCODE sqlca.sqlcode' macro
define *none* Define a preprocessor symbol
duration transaction Set pin duration for objects in the cache
dynamic oracle Specify Oracle or ANSI Dynamic SQL Semantics
errors yes Whether error messages are sent to the terminal
errtype *none* Name of the list file for intype file errors
fips none FIPS flagging of ANSI noncompliant usage
header *none* Specify file extension for Precompiled Headers
hold_cursor no Control holding of cursors in the cursor cache
iname *none* The name of the input file
include *none* Directory paths for included files
intype *none* The name of the input file for type information
lines no Add #line directives to the generated code
lname *none* Override default list file name
ltype none The amount of data generated in the list file
maxliteral 1024 Maximum length of a generated string literal
maxopencursors 10 Maximum number of cached open cursors
mode oracle Code conformance to Oracle or ANSI rules
nls_char *none* Specify National Language character variables
nls_local no Control how NLS character semantics are done
objects yes Support object types
oname *none* The name of the output file
oraca no Control the use of the ORACA
pagelen 80 The page length of the list file
parse full Control which non-SQL code is parsed
prefetch 1 Number of rows pre-fetched at cursor OPEN time
release_cursor no Control release of cursors from cursor cache
select_error yes Control flagging of select errors
sqlcheck syntax Amount of compile-time SQL checking
sys_include /usr/lib/gcc-liDirectory where system header files are found
/usr/include
threads no Indicates a multi-threaded application
type_code oracle Use Oracle or ANSI type codes for Dynamic SQL
unsafe_null no Allow a NULL fetch without indicator variable
userid *none* A username/password [@dbname] connect string
utf16_charset nchar_charset The character set form used by UTF16 variables
varchar no Allow the use of implicit varchar structures
version recent Which version of an object is to be returned
PCC-F-02135, CMD-LINE: User asked for help
Followup June 29, 2003 - 1pm Central time zone:
what is the "genealogy" of that machine? undergo an upgrade?
you might have to peek in your
/u01/oracle/product/9.0.1/precomp/admin/pcscfg.cfg
and see that the path is set right. check out the sys_include and make sure its pointing to the
right place. I've noticed when I upgrade the OS, this gets "stale"
Yet another ASCII dump issue
July 2, 2003 - 4pm Central time zone
Reviewer: Peter from Atlanta, GA
I was wondering what is required to use this same function, except getting this from a DMP file
instead of a database table itself
Followup July 3, 2003 - 8am Central time zone:
easy -- all you would need to do is run imp ;)
DMP file formats are not documented, can -- do -- and will change from release to release (thats
why a dmp from 9iR2 cannot be read by 8i for example)
I don't read dmp's
pro*cobol
August 5, 2003 - 2pm Central time zone
Reviewer: A READER
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
Followup August 5, 2003 - 2pm Central time zone:
http://asktom.oracle.com/~tkyte/flat/index.html
has all of my unloaders.
COBOL is not a language I can actually code in anymore ;) It was the first I learned (punched
cards no less) but the least liked...
You know, if you just need to unload to a flat file, you might find one of the tools linked to
above "more then suffcient" and avoid writing code of your own.
Yet another ASCII dump issue
August 21, 2003 - 6am Central time zone
Reviewer: Suvamoy Sen from Hyderabad, India
This article was just great. It was exactly the kind of code I was looking for. In his Expert
One-on-One book in the chapter on data loading Tom has provided alternate codes to do the same
using PL/SQL and SQL*PLUS. The best part is that he has even provided a PL/SQL package to generate
the control file to load the flat file data generated using the PL/SQL version of his script.
While generating the flat file, the PL/SQL version of the script encloses all the fields in quotes
'"' even the null ones. This I find very useful. The SQL*LOADER control file generation procedure
also creates a control file which expects that all fields would be enclosed in quotes. The current
Pro*C program however does not enclose the fields in quotes and additionally does not replace every
occurrence of the quote symbol in the fields with two quotes as the PL/SQL version of his program
does.
I have added a few lines to Tom's Pro*C program to do just this:
1. It would enclose every field within quotes, even the NULL ones.
2. It would separate each field using ','
and finally each line would be terminated using the charcters "~^@#|\n" (quotes not included).
I am not a professional C programmer and my routine may be inefficient. If you have better ways of
achieving this please share it with us all.
#include <stdio.h>
#include <string.h>
#include <ctype.h>
#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30
static char * USERID = NULL;
static char * SQLSTMT = NULL;
static char * ARRAY_SIZE = "10";
#define vstrcpy( a, b ) \
(strcpy( a.arr, b ), a.len = strlen( a.arr ), a.arr)
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;
extern SQLDA *sqlald();
extern void sqlclu();
static void die( char * msg )
{
fprintf( stderr, "%s\n", msg );
exit(1);
}
/*
this array contains a default mapping
I am using to constrain the
lengths of returned columns. It is mapping,
for example, the Oracle
NUMBER type (type code = 2) to be 45 characters
long in a string.
*/
static int lengths[] =
{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,
18, 25, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 512, 2000 };
static void process_parms( argc, argv )
int argc;
char * argv[];
{
int i;
for( i = 1; i < argc; i++ )
{
if ( !strncmp( argv[i], "userid=", 7 ) )
USERID = argv[i]+7;
else
if ( !strncmp( argv[i], "sqlstmt=", 8 ) )
SQLSTMT = argv[i]+8;
else
if ( !strncmp( argv[i], "arraysize=", 10 ) )
ARRAY_SIZE = argv[i]+10;
else
{
fprintf( stderr,
"usage: %s %s %s\n",
argv[0],
"userid=xxx/xxx sqlstmt=query ",
"arraysize=<NN>\n" );
exit(1);
}
}
if ( USERID == NULL || SQLSTMT == NULL )
{
fprintf( stderr,
"usage: %s %s %s\n",
argv[0],
"userid=xxx/xxx sqlstmt=query ",
"arraysize=<NN>\n" );
exit(1);
}
}
static void sqlerror_hard()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
fprintf(stderr,"\nORACLE error detected:");
fprintf(stderr,"\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
static SQLDA * process_1(char * sqlstmt, int array_size )
{
SQLDA * select_dp;
int i;
int j;
int null_ok;
int precision;
int scale;
int size = 10;
fprintf( stderr, "Unloading '%s'\n", sqlstmt );
fprintf( stderr, "Array size = %d\n", array_size );
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :sqlstmt;
EXEC SQL DECLARE C CURSOR FOR S;
if ((select_dp = sqlald(size,MAX_VNAME_LEN,MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for select descriptor." );
select_dp->N = size;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
if ( !select_dp->F ) return NULL;
if (select_dp->F < 0)
{
size = -select_dp->F;
sqlclu( select_dp );
if ((select_dp =
sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for descriptor." );
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
}
select_dp->N = select_dp->F;
for (i = 0; i < select_dp->N; i++)
select_dp->I[i] = (short *) malloc(sizeof(short) *
array_size );
for (i = 0; i < select_dp->F; i++)
{
sqlnul (&(select_dp->T[i]),
&(select_dp->T[i]), &null_ok);
if ( select_dp->T[i] <
sizeof(lengths)/sizeof(lengths[0]) )
{
if ( lengths[select_dp->T[i]] )
select_dp->L[i] = lengths[select_dp->T[i]];
else select_dp->L[i] += 5;
}
else select_dp->L[i] += 5;
select_dp->T[i] = 5;
select_dp->V[i] = (char *)malloc( select_dp->L[i] *
array_size );
for( j = MAX_VNAME_LEN-1;
j > 0 && select_dp->S[i][j] == ' ';
j--);
fprintf (stderr,
"%s%.*s", i?",":"", j+1, select_dp->S[i]);
}
fprintf( stderr, "\n" );
EXEC SQL OPEN C;
return select_dp;
}
static void process_2( SQLDA * select_dp, int array_size )
{
int last_fetch_count;
int row_count = 0;
short ind_value;
char * char_ptr, s2[8002];
int i, j, k, l = 0;
for ( last_fetch_count = 0;
;
last_fetch_count = sqlca.sqlerrd[2] )
{
EXEC SQL FOR :array_size FETCH C
USING DESCRIPTOR select_dp;
for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
{
for (i = 0; i < select_dp->F; i++)
{
ind_value = *(select_dp->I[i]+j);
char_ptr = select_dp->V[i] +
(j*select_dp->L[i]);
s2[l = 0] = '"';
for(k = 0; char_ptr[k] != '\0'; k++) {
s2[++l] = char_ptr[k];
if (char_ptr[k] == '"') s2[++l] = '"';
}
s2[++l] = '"';
s2[++l] = '\0';
printf( "%s%s", i?",":"", s2 );
}
row_count++;
printf( "~^@#|\n" );
}
if ( sqlca.sqlcode > 0 ) break;
}
sqlclu(select_dp);
EXEC SQL CLOSE C;
EXEC SQL COMMIT WORK;
fprintf( stderr, "%d rows extracted\n", row_count );
}
main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;
SQLDA * select_dp;
process_parms( argc, argv );
/* Connect to ORACLE. */
vstrcpy( oracleid, USERID );
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL CONNECT :oracleid;
fprintf(stderr, "\nConnected to ORACLE as user: %s\n\n",
oracleid.arr);
EXEC SQL ALTER SESSION
SET NLS_DATE_FORMAT = 'ddmmyyyyhh24miss';
select_dp = process_1( SQLSTMT, atoi(ARRAY_SIZE) );
process_2( select_dp , atoi(ARRAY_SIZE));
/* Disconnect from ORACLE. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
Make error
September 22, 2003 - 5pm Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO USA
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.
Followup September 22, 2003 - 8pm Central time zone:
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
September 29, 2003 - 11pm Central time zone
Reviewer: Srikanth from Srikanth, Singapore
Hi tom,
I have gone through the code and its good and I have a question to you that, can I print the same
result like 'to shi' format in my report.
each field with some spaces...
could you please help me to know where can I change the format in the code.
Regards
Sri.
Followup September 30, 2003 - 7am Central time zone:
'to shi' ???
but anyway, the code is right there -- you can modify anything at all you want.
One more question
September 30, 2003 - 1pm Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
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?
create CSV using PRO*C and stored procedure
October 16, 2003 - 10am Central time zone
Reviewer: Manish from NY USA
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
Followup October 16, 2003 - 11am Central time zone:
a ref cursor is just an opened cursor, a cursor opened by plsql.
it'll perform no differently at all then pro*c opening the cursor itself. they are after all just
cursors at the end of the day and they'll both have to do the same amount of work.
you can either recode the code to do plsql calls or use it as is, they'll perform pretty much the
same as they both just use cursors.
Please help PRO*C CSV using Stored Procedure
October 16, 2003 - 12pm Central time zone
Reviewer: Manish from NY USA
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.
Followup October 16, 2003 - 5pm Central time zone:
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
October 21, 2003 - 2pm Central time zone
Reviewer: Wor from USA
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.
Followup October 21, 2003 - 5pm Central time zone:
are you saying you would like to pass in the NLS_DATE_FORMAT?
not sure what you are trying to do?
Date format
October 23, 2003 - 9am Central time zone
Reviewer: Wor from NY
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.
Followup October 23, 2003 - 12pm Central time zone:
> ./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
October 23, 2003 - 4pm Central time zone
Reviewer: A reader
It works great!!!!
Array size = 100 or 10000
October 29, 2003 - 5pm Central time zone
Reviewer: wor from NY
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
Followup October 30, 2003 - 6am Central time zone:
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
October 30, 2003 - 10am Central time zone
Reviewer: kom from US
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.
Followup October 30, 2003 - 11am Central time zone:
add
exec sql alter session set cursor_sharing=force;
to the proc code after the connect, before the parse.
in addition
October 30, 2003 - 10am Central time zone
Reviewer: kom from US
To add to previous question
I am using oracle 8.1.6 on windows 2000.
October 30, 2003 - 2pm Central time zone
Reviewer: kom from US
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 ?
Followup October 30, 2003 - 9pm Central time zone:
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
October 30, 2003 - 4pm Central time zone
Reviewer: Menon
Search for cursor_sharing on this site.
In particular read
http://asktom.oracle.com/pls/ask/f?p=4950:8:243122254267634687::NO::F4950_P8_DISPLAYID,F4950_P8_CRIT
ERIA:5180609822543,
or get Expert one on one by Tom where he explains
the disadvantages of using cursor_sharing.
October 30, 2003 - 6pm Central time zone
Reviewer: kom from US
>>>> 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.
Followup October 30, 2003 - 10pm Central time zone:
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
October 31, 2003 - 9am Central time zone
Reviewer: kom from US
>> 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 ) .
Followup November 1, 2003 - 11am Central time zone:
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[] )
November 3, 2003 - 12pm Central time zone
Reviewer: kom from US
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.
Followup November 3, 2003 - 6pm Central time zone:
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
November 4, 2003 - 5pm Central time zone
Reviewer: kom from US
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 ?
Followup November 5, 2003 - 7am Central time zone:
search this site for
variable in list
Great!! thanks
November 5, 2003 - 11am Central time zone
Reviewer: kom from US
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
November 6, 2003 - 9am Central time zone
Reviewer: kom from US
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
November 10, 2003 - 11am Central time zone
Reviewer: kom from US
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
Clob
November 10, 2003 - 6pm Central time zone
Reviewer: Pal from US
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
Followup November 10, 2003 - 7pm Central time zone:
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
November 12, 2003 - 4pm Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO USA
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
LOAD DATA
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
Followup November 13, 2003 - 6am Central time zone:
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
December 2, 2003 - 10pm Central time zone
Reviewer: Wor from US
Hi Tom,
I have converted the above pro*C code into a C DLL which I call from VB component which is called
from ASP.
The VB component is in COM+. I am calling this C dll using standard C API calling conventions from
VB. I have exported a C funtion which wraps all the above Pro*c funtions.
Win2000 and oracle 8.1.6
C dll generates a .csv file with the name and path specified by VB dll, basically a online report.
When this whole process is called sequetially say 2000 times or more one after another in a loop
it works great!!.
say e.g. user1 click on the browser button "generate report"
ASP->calls->VB->calls->C DLL and the report is ready.
again user1 clicks button "generate report" like this 2000 times or more... works great!!
Problem:
When 2 or more users click the button at the same time then
2 or more instances of VB component is created in COM+
and both try to call C DLL using API call together and the PRO*C crashes...
Is this a problem of multi-threading ?
I read your article on multi-threading but in my case the userid/pass will always be same only and
the simultaneous calls are made from VB.
note: same userid / pass are passed by VB to C DLL.
do you want me to post my pro*C code..
how do I change the above code for multi-user access.
Please help..
Followup December 3, 2003 - 6am Central time zone:
depending on how big the "report" is, i would use the PLSQL implementation, see
http://asktom.oracle.com/~tkyte/flat/index.html
but, each extproc runs in its own address space (you should see 2 extproc.exe's out there). they
are not "threads" in the oracle process.
so, i would put in copious debug (if you have my book expert one on one Oracle -- I demonstrate
that heavily in the chapter on external procedures) and try to find out WHERE it is crashing.
Concurrent Users, Data Unloading using Pro*C
January 7, 2004 - 10am Central time zone
Reviewer: Wor from USA
Hi Tom,
To continue with my previous post.
My reports can run from 1 record 1 colum to 10 Million and 25 columns. Since I need faster speed I
want to implement using Pro*C.
As I previously said my workflow
User action on IE browser->VB component in comp+ ( calls C dll using declare API provided by VB )->
C.dll ( pro*C code )-> generates the .csv file.
Everything works fine with one user at a time ( similar to what you mentioned using command prompt,
one at a time )
But incase multi users try to create .csv files concurrently than data retrieved in select_dp gets
courrupted in between the csv file creation.
I have your book and in chapter "Data Loading->topic Unload Data" you mentioned that the code for
pro*C is on Wrox web-site. can you give me exact URL.
Since the select_dp gets corrupted can you please show me example how to modify the existing pro*C
code to handle concurrent request for csv files ( e.g if contexts have to be used, how to declare
select_dp like structures etc )
As always you are the BEST....
Thanks in advance,
Wor
Followup January 7, 2004 - 6pm Central time zone:
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.
http://asktom.oracle.com/~tkyte/flat.html
has pointers to the code.
in addition...
January 7, 2004 - 2pm Central time zone
Reviewer: Wor from USA
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
Followup January 8, 2004 - 11am Central time zone:
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
January 8, 2004 - 8am Central time zone
Reviewer: wor from USA
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.
http://asktom.oracle.com/~tkyte/flat.html has pointers to the code.
January 8, 2004 - 9am Central time zone
Reviewer: A reader
http://asktom.oracle.com/~tkyte/flat/index.html
January 8, 2004 - 12pm Central time zone
Reviewer: MP from Austin, TX
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.
Followup January 8, 2004 - 3pm Central time zone:
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
January 14, 2004 - 12pm Central time zone
Reviewer: Andrey Prokopenko from Moscow, Russia
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
August 18, 2004 - 3pm Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
Well I have the program running but I am getting a coredump. I guess I need to get some help from
a C developer, eh?
rrs4sfd:/home/gk7692/test>flat_file userid=rrs/piasa4 sqlstmt='select * from acna_ref'
arraysize=100 > acna_ref.dat
Connected to ORACLE as user: rrs/xxxxxxx
Unloading 'select * from acna_ref'
Array size = 100
COMPANY,ACNA_SEGMENT_CODE
Memory fault(coredump)
Followup August 18, 2004 - 11pm Central time zone:
did you change the code?
was does acna_ref look like?
what OS?
what compiler?
Coredump
August 23, 2004 - 12pm Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
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)
Followup August 23, 2004 - 1pm Central time zone:
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
August 23, 2004 - 2pm Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
Tom - Now I am getting the following error when I run make on the old or the new Pro*C code:
rrs4sfd:/home/gk7692>make
proc ireclen=255 lines=yes include=/usr/local/opt/oracle/product/rrst/proc/lib
iname=flat_file
Pro*C/C++: Release 9.2.0.5.0 - Production on Mon Aug 23 13:54:01 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
System default option values taken from:
/usr/local/opt/oracle/product/rrst/precomp/admin/pcscfg.cfg
/opt/ansic/bin/cc -I. -g -c flat_file.c
cc: "flat_file.pc", line 224: warning 604: Pointers are not assignment-compatible.
cc: "flat_file.pc", line 224: warning 563: Argument #1 is not the correct type.
cc: "flat_file.pc", line 224: warning 604: Pointers are not assignment-compatible.
cc: "flat_file.pc", line 224: warning 563: Argument #1 is not the correct type.
/opt/ansic/bin/cc -Wl,+s -Wl,+n +DA2.0W +DS2.0 -o flat_file
-L/usr/local/opt/oracle/product/rrst/precomp/lib/ -L/usr/local/opt/oracle/product/rrst/lib/ -t -o
flat_file \
flat_file.o -L/usr/local/opt/oracle/product/rrst/lib -lclntsh `cat
/usr/local/opt/oracle/product/rrst/lib/ldflags` `cat
/usr/local/opt/oracle/product/rrst/lib/sysliblist` -lm -lpthread -lpthread
cc: error 1400: Option t usage: -t c,name where c may be 1 or more of pc0al.
ld: Cannot specify input file (flat_file) that is the same as the output file.
Fatal error.
*** Error exit code 1
Stop.
Here is my makefile:
CC=/opt/ansic/bin/cc
TARGET=flat_file
SOURCE=flat_file.pc
$(TARGET): $(SOURCE) $(SOURCE:.pc=.c) $(SOURCE:.pc=.o)
$(CC) $(LDFLAGS) -t -o $(TARGET) \
$(SOURCE:.pc=.o) -L$(ORACLE_HOME)/lib $(PROLDLIBS)
include proc.mk
PROCFLAGS= ireclen=255 lines=yes $(PROC_ENV_FLAGS) \
include=$(ORACLE_HOME)/proc/lib
PROFLAGS=$(PROCFLAGS)
CFLAGS=-I. -g $(CC_ENV_FLAGS)
Any ideas? Sorry to trouble you
Followup August 23, 2004 - 2pm Central time zone:
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
August 23, 2004 - 2pm Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
Here are the results:
/usr/ccs/bin/make -f /usr/local/opt/oracle/product/rrst/precomp/demo/proc/demo_proc.mk
OBJS=sample1.o EXE=sample1 build
/usr/ccs/bin/make -f /usr/local/opt/oracle/product/rrst/precomp/demo/proc/demo_proc.mk
PROCFLAGS="" PCCSRC=sample1 I_SYM=include= pc1
proc iname=sample1 include=. include=/usr/local/opt/oracle/product/rrst/precomp/public
include=/usr/local/opt/oracle/product/rrst/rdbms/public
include=/usr/local/opt/oracle/product/rrst/rdbms/demo
include=/usr/local/opt/oracle/product/rrst/plsql/public
include=/usr/local/opt/oracle/product/rrst/network/public
Pro*C/C++: Release 9.2.0.5.0 - Production on Mon Aug 23 14:11:51 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
System default option values taken from:
/usr/local/opt/oracle/product/rrst/precomp/admin/pcscfg.cfg
cc +DA2.0W +DS2.0 -DSS_64BIT_SERVER -I.
-I/usr/local/opt/oracle/product/rrst/precomp/public
-I/usr/local/opt/oracle/product/rrst/rdbms/public -I/usr/local/opt/oracle/product/rrst/rdbms/demo
-I/usr/local/opt/oracle/product/rrst/plsql/public
-I/usr/local/opt/oracle/product/rrst/network/public -c sample1.c
cc +DA2.0W -o sample1 sample1.o -L/usr/local/opt/oracle/product/rrst/lib/ -lclntsh `cat
/usr/local/opt/oracle/product/rrst/lib/ldflags` `cat
/usr/local/opt/oracle/product/rrst/lib/sysliblist` -lm -lpthread -lpthread
Followup August 23, 2004 - 3pm Central time zone:
so, use those options to the compiler -- just use cc, and +DA2.0W +DS2.0 -DSS_64BIT_SERVER -I.
Coredump
August 23, 2004 - 4pm Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
Ok - I compiled the new code with the printf statements throughout process_2, and got the same
results and no debugging output:
rrs4sfd:/home/gk7692>flat_file userid=rrs/piasa4 sqlstmt='select * from
> acna_ref' arraysize=100 > acna_ref.dat
Connected to ORACLE as user: rrs/piasa4
Unloading 'select * from
acna_ref'
Array size = 100
COMPANY,ACNA_SEGMENT_CODE
Memory fault(coredump)
Followup August 23, 2004 - 4pm Central time zone:
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
August 23, 2004 - 4pm Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
I take that back - I was directing output to a file.
The last line listed in 181, which begins here:
NOTES
**************************************************************
*** ***
*** This file is SOSD. Porters must change the data types ***
*** appropriately on their platform. See notes/pcport.doc ***
*** for more information. ***
*** ***
**************************************************************
If the symbol SQLCA_STORAGE_CLASS is defined, then the SQLCA
will be defined to have this storage class. For example:
#define SQLCA_STORAGE_CLASS extern
will define the SQLCA as an extern.
If the symbol SQLCA_INIT is defined, then the SQLCA will be
statically initialized. Although this is not necessary in order
to use the SQLCA, it is a good pgming practice not to have
unitialized variables. However, some C compilers/OS's don't
allow automatic variables to be init'd in this manner. Therefore,
if you are INCLUDE'ing the SQLCA in a place where it would be
an automatic AND your C compiler/OS doesn't allow this style
of initialization, then SQLCA_INIT should be left undefined --
all others can define SQLCA_INIT if they wish.
If the symbol SQLCA_NONE is defined, then the SQLCA variable will
not be defined at all. The symbol SQLCA_NONE should not be defined
in source modules that have embedded SQL. However, source modules
that have no embedded SQL, but need to manipulate a sqlca struct
passed in as a parameter, can set the SQLCA_NONE symbol to avoid
creation of an extraneous sqlca variable.
MODIFIED
lvbcheng 07/31/98 - long to int
jbasu 12/12/94 - Bug 217878: note this is an SOSD file
losborne 08/11/92 - No sqlca var if SQLCA_NONE macro set
Clare 12/06/84 - Ch SQLCA to not be an extern.
Clare 10/21/85 - Add initialization.
Bradbury 01/05/86 - Only initialize when SQLCA_INIT set
Clare 06/12/86 - Add SQLCA_STORAGE_CLASS option.
*/
#ifndef SQLCA
#define SQLCA 1
struct sqlca
{
/* ub1 */ char sqlcaid[8];
/* b4 */ int sqlabc;
/* b4 */ int sqlcode;
struct
{
/* ub2 */ unsigned short sqlerrml;
/* ub1 */ char sqlerrmc[70];
} sqlerrm;
/* ub1 */ char sqlerrp[8];
/* b4 */ int sqlerrd[6];
/* ub1 */ char sqlwarn[8];
/* ub1 */ char sqlext[8];
};
#ifndef SQLCA_NONE
#ifdef SQLCA_STORAGE_CLASS
SQLCA_STORAGE_CLASS struct sqlca sqlca
#else
struct sqlca sqlca
#endif
#ifdef SQLCA_INIT
= {
{'S', 'Q', 'L', 'C', 'A', ' ', ' ', ' '},
sizeof(struct sqlca),
0,
{ 0, {0}},
{'N', 'O', 'T', ' ', 'S', 'E', 'T', ' '},
{0, 0, 0, 0, 0, 0},
{0, 0, 0, 0, 0, 0, 0, 0},
Followup August 23, 2004 - 4pm Central time zone:
get it from the .pc file please......
Coredump
August 23, 2004 - 4pm Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
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++ )
{
Followup August 23, 2004 - 7pm Central time zone:
what does this output?
static SQLDA * process_1(char * sqlstmt, int array_size )
{
SQLDA * select_dp;
int i;
int j;
int null_ok;
int precision;
int scale;
int size = 10;
fprintf( stderr, "Unloading '%s'\n", sqlstmt );
fprintf( stderr, "Array size = %d\n", array_size );
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :sqlstmt;
EXEC SQL DECLARE C CURSOR FOR S;
if ((select_dp = sqlald(size,MAX_VNAME_LEN,MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for select descriptor." );
select_dp->N = size;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
if ( !select_dp->F ) return NULL;
if (select_dp->F < 0)
{
size = -select_dp->F;
sqlclu( select_dp );
if ((select_dp =
sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for descriptor." );
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
}
select_dp->N = select_dp->F;
fprintf( stderr, "this has %d columns\n", select_dp->N );
for (i = 0; i < select_dp->N; i++)
select_dp->I[i] = (short *) malloc(sizeof(short) *
array_size );
for (i = 0; i < select_dp->F; i++)
{
fprintf( stderr, "length[%d] was %d\n",i, select_dp->L[i] );
sqlnul (&(select_dp->T[i]),
&(select_dp->T[i]), &null_ok);
if ( select_dp->T[i] <
sizeof(lengths)/sizeof(lengths[0]) )
{
if ( lengths[select_dp->T[i]] )
select_dp->L[i] = lengths[select_dp->T[i]];
else select_dp->L[i] += 5;
}
else select_dp->L[i] += 5;
fprintf( stderr, "setting length[%d] = %d\n", i, select_dp->L[i] );
select_dp->T[i] = 5;
select_dp->V[i] = (char *)malloc( select_dp->L[i] *
array_size );
for( j = MAX_VNAME_LEN-1;
j > 0 && select_dp->S[i][j] == ' ';
j--);
fprintf (stderr,
"%s%.*s", i?",":"", j+1, select_dp->S[i]);
}
fprintf( stderr, "\n" );
EXEC SQL OPEN C;
return select_dp;
}
static void process_2( SQLDA * select_dp, int array_size )
{
int last_fetch_count;
int row_count = 0;
short ind_value;
char * char_ptr;
int i,
j;
for ( last_fetch_count = 0;
;
last_fetch_count = sqlca.sqlerrd[2] )
{
fprintf( stderr, "going to fetch %d rows...\n", array_size );
EXEC SQL FOR :array_size FETCH C
USING DESCRIPTOR select_dp;
fprintf( stderr, "fetched %d rows...\n", sqlca.sqlerrd[2]-last_fetch_count);
for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
{
for (i = 0; i < select_dp->F; i++)
{
ind_value = *(select_dp->I[i]+j);
char_ptr = select_dp->V[i] +
(j*select_dp->L[i]);
printf( "%s%s", i?",":"",
ind_value?"":char_ptr );
}
row_count++;
printf( "\n" );
}
if ( sqlca.sqlcode > 0 ) break;
}
sqlclu(select_dp);
EXEC SQL CLOSE C;
EXEC SQL COMMIT WORK;
fprintf( stderr, "%d rows extracted\n", row_count );
}
Coredump
August 24, 2004 - 10am Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
rrs4sfd:/home/gk7692>flat_file userid=rrs/piasa3 sqlstmt='select * from acna_ref' arraysize=100
Connected to ORACLE as user: rrs/piasa4
273
Unloading 'select * from acna_ref'
Array size = 100
this has 2 columns
length[0] was 18
setting length[0] = 23
COMPANYlength[1] was 3
setting length[1] = 8
,ACNA_SEGMENT_CODE
275
going to fetch 100 rows...
Memory fault(coredump)
Followup August 24, 2004 - 10am Central time zone:
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
August 24, 2004 - 10am Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
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();
Followup August 24, 2004 - 11am Central time zone:
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
August 24, 2004 - 11am Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
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!
Followup August 24, 2004 - 11am Central time zone:
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
August 24, 2004 - 11am Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
Sample10 compiles and runs fine:
rrs4sfd:/home/gk7692>make -f proc.mk sample10
/usr/ccs/bin/make -f /usr/local/opt/oracle/product/rrst/precomp/demo/proc/demo_proc.mk
OBJS=sample10.o EXE=sample10 build
/usr/ccs/bin/make -f /usr/local/opt/oracle/product/rrst/precomp/demo/proc/demo_proc.mk
PROCFLAGS="" PCCSRC=sample10 I_SYM=include= pc1
proc iname=sample10 include=. include=/usr/local/opt/oracle/product/rrst/precomp/public
include=/usr/local/opt/oracle/product/rrst/rdbms/public
include=/usr/local/opt/oracle/product/rrst/rdbms/demo
include=/usr/local/opt/oracle/product/rrst/plsql/public
include=/usr/local/opt/oracle/product/rrst/network/public
Pro*C/C++: Release 9.2.0.5.0 - Production on Tue Aug 24 11:14:34 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
System default option values taken from:
/usr/local/opt/oracle/product/rrst/precomp/admin/pcscfg.cfg
cc +DA2.0W +DS2.0 -DSS_64BIT_SERVER -I.
-I/usr/local/opt/oracle/product/rrst/precomp/public
-I/usr/local/opt/oracle/product/rrst/rdbms/public -I/usr/local/opt/oracle/product/rrst/rdbms/demo
-I/usr/local/opt/oracle/product/rrst/plsql/public
-I/usr/local/opt/oracle/product/rrst/network/public -c sample10.c
cc: "sample10.c", line 1249: warning 728: Argument #1 converts long* to int*.
cc +DA2.0W -o sample10 sample10.o -L/usr/local/opt/oracle/product/rrst/lib/ -lclntsh `cat
/usr/local/opt/oracle/product/rrst/lib/ldflags` `cat
/usr/local/opt/oracle/product/rrst/lib/sysliblist` -lm -lpthread -lpthread
This wouldn't be related to the compile warnings I get with flat_file would it?
cc: "flat_file.pc", line 263: warning 604: Pointers are not assignment-compatible.
cc: "flat_file.pc", line 263: warning 563: Argument #1 is not the correct type.
cc: "flat_file.pc", line 263: warning 604: Pointers are not assignment-compatible.
cc: "flat_file.pc", line 263: warning 563: Argument #1 is not the correct type.
Followup August 24, 2004 - 11am Central time zone:
what is your line 263...?
Coredump
August 24, 2004 - 11am Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
In main:
vstrcpy( oracleid, USERID );
Followup August 24, 2004 - 3pm Central time zone:
dont' worry about that -- oracleid has an unsigned char *, USERID is a signed char *, it is "safe"
Oracle internal error trapping
September 7, 2004 - 10am Central time zone
Reviewer: Peter from Louisville, KY USA
Tom,
I am using this Pro*C programs in my data extracts. I have compiled executables and I am running
multiple instances of these executables using a simple DOS batch file.
I have run the various instances against 5 different tables atleast 100 times by now and the
program crashed 3 times.
I am able to trap errors in the sql select statement or errors in username/pwd. But, I am not able
to trap internal errors. On one scenario the DBA was running a defrag job against the table i was
extracting and my program failed. But I couldnt trap the error. Later from the time of the crash
and the time of the defrag job we have assumed that to be the reason. I restarted the exes and they
extracted data successfully.
Again this weekend there was a mysterious crash and I restarted all the instances again and it
completed successfully during my re-run. This time I am not able to find anything reasonable on the
server that might have caused the intial crash.
All 3 mysterious crashes have occured only against the same table. Any light on the problem or
suggestion to trap errors. The speed of the program is amazing but I cant move the code to
production until I stop the crashes.
Thanks for all your help,
Peter
Followup September 7, 2004 - 12pm Central time zone:
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
September 7, 2004 - 1pm Central time zone
Reviewer: A reader
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,
Followup September 7, 2004 - 2pm Central time zone:
i made arraysize a parameter for the sole reason that you would be able to play around with
different values! go for it.
September 7, 2004 - 3pm Central time zone
Reviewer: Peter from Louisville, KY USA
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.
Followup September 7, 2004 - 3pm Central time zone:
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
September 22, 2004 - 4pm Central time zone
Reviewer: Scott P. from Austin, TX
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.
Followup September 22, 2004 - 5pm Central time zone:
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
November 18, 2004 - 12pm Central time zone
Reviewer: andré from Québec ,canada !!!!!!!!!!!!!!!!!!!!
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?
November 30, 2004 - 2am Central time zone
Reviewer: KIM, TAE HO from SEOUL, KOREA
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);
}
Followup November 30, 2004 - 7am Central time zone:
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
November 30, 2004 - 10am Central time zone
Reviewer: Jun He from Houston
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
Followup November 30, 2004 - 11am Central time zone:
gotta feeling this is windows doing this to you all around.
what is the database character set?
Last message
November 30, 2004 - 10am Central time zone
Reviewer: Jun He from Houston
I was doing this too quick. The first problem somehow not reproducable. Just the second problem.
Followup November 30, 2004 - 11am Central time zone:
character set, before i dig out my vmware and fire up a database...
Referring last message
November 30, 2004 - 10am Central time zone
Reviewer: Jun He from Houston
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.
Followup November 30, 2004 - 11am Central time zone:
that would be 100% about windows then, nothing to do with Oracle.
Followup
November 30, 2004 - 2pm Central time zone
Reviewer: Jun He
CHARACTER SET WE8ISO8859P1
November 30, 2004 - 11pm Central time zone
Reviewer: Jun He
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.
Followup December 1, 2004 - 8am Central time zone:
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
December 1, 2004 - 9am Central time zone
Reviewer: Jun He
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.
http://www.csc.calpoly.edu/~bfriesen/software/builds.html 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 ?
Followup December 1, 2004 - 10am Central time zone:
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
December 1, 2004 - 9am Central time zone
Reviewer: Jun He
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.
December 1, 2004 - 10am Central time zone
Reviewer: Jun He
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!
Followup December 1, 2004 - 10am Central time zone:
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.
December 1, 2004 - 11am Central time zone
Reviewer: Jun He
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)
December 15, 2004 - 9pm Central time zone
Reviewer: Kevin from AUSTRALIA
<code>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
.... slight change to prog
December 16, 2004 - 3pm Central time zone
Reviewer: Kevin from Australia
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
January 20, 2005 - 4pm Central time zone
Reviewer: Eduardo Nicdao from Dearborn, MI USA
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.
Followup January 20, 2005 - 7pm Central time zone:
I just used "time"
[tkyte@xtkyte-pc tkyte]$ time ls
afiedt.buf a.sql binpriv cxoffice expdat.dmp ff.tar
src test1.sql testb.sql tk.prf x.lst
asktom_articles.html audit_actions.sql b.tgz dba_tab_statistics.sql exp.lst sqlload
t.ctl test2.sql test.slsq t.log x.sql
AsktomShort.doc bin c.sql Desktop exp.par sqlnet.log
@test testa.sql test.sql tmp
real 0m0.036s
user 0m0.000s
sys 0m0.000s
how do we process data from flat file using "HOST ARRAY"
February 12, 2005 - 4am Central time zone
Reviewer: A reader
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
Followup February 12, 2005 - 12pm Central time zone:
did you read the pro*c docs? they are full of "binding host arrays"
you read file
you fill array
you say "oracle insert this array of data" when you fill array.
YOU read 100 lines
you then say "insert"
yes, i used the so called "method 4"
oracle fast extract into ascii text
March 18, 2005 - 12pm Central time zone
Reviewer: Chad from Atlanta, GA
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
Followup March 18, 2005 - 12pm Central time zone:
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
April 7, 2005 - 8pm Central time zone
Reviewer: A reader
Hi Guys,
You can have user defined FS/RS and decide whether you want everything double quoted!
Enjoy.
Thanks tom for all the help on the Comp.Databases forum and I love this page...
/* Compile Me With
gcc -O3 flatten.c -o flatten -L/oracle/rdbms/9.2.0/orax011/lib32 -lclntsh -lclntst9
*/
#include <stdio.h>
#include <string.h>
#include <ctype.h>
#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30
static char * USERID = NULL;
static char * SQLSTMT = NULL;
static char * ARRAY_SIZE = "10";
static char * RECSEP = NULL;
static char * FIELDSEP = NULL;
int DOUBLEQUOTE = (-1);
#define vstrcpy( a, b ) \
(strcpy( a.arr, b ), a.len = strlen( a.arr ), a.arr)
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;
extern SQLDA *sqlald();
extern void sqlclu();
static void die( char * msg )
{
fprintf( stderr, "%s\n", msg );
exit(1);
}
/*
this array contains a default mapping
I am using to constrain the
lengths of returned columns. It is mapping,
for example, the Oracle
NUMBER type (type code = 2) to be 45 characters
long in a string.
*/
static int lengths[] =
{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,
18, 25, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 512, 2000 };
static void process_parms( argc, argv )
int argc;
char * argv[];
{
int i;
for( i = 1; i < argc; i++ )
{
if ( !strncasecmp( argv[i], "userid=", 7 ) )
USERID = argv[i]+7;
else
if ( !strncasecmp( argv[i], "sqlstmt=", 8 ) )
SQLSTMT = argv[i]+8;
else
if ( !strncasecmp( argv[i], "arraysize=", 10 ) )
ARRAY_SIZE = argv[i]+10;
else
if ( !strncasecmp( argv[i], "recsep=", 7) )
RECSEP = argv[i] + 7;
else
if ( !strncasecmp( argv[i], "fieldsep=", 9 ))
FIELDSEP = argv[i] + 9;
else
if ( !strncasecmp(argv[i], "doublequote=true", 16))
DOUBLEQUOTE=1;
else
if ( !strncasecmp(argv[i], "doublequote=false", 17))
DOUBLEQUOTE=0;
else
{
fprintf( stderr,
"usage: %s %s %s %s %s %s %s \n",
argv[0],
"userid=<user/pass@connect_string>", "sqlstmt=<Query>",
"arraysize=<Fetch Size>\n", "fieldsep=<Field Sep String>", "recsep=<Record Sep
String>",
"doublequote=<TRUE/FALSE>" );
exit(1);
}
}
if ( USERID == NULL || SQLSTMT == NULL || RECSEP == NULL || FIELDSEP == NULL || DOUBLEQUOTE ==
(-1))
{
fprintf( stderr,
"usage: %s %s %s %s %s %s %s \n",
argv[0],
"userid=<user/pass@connect_string>", "sqlstmt=<Query>",
"arraysize=<Fetch Size>\n", "fieldsep=<Field Sep String>", "recsep=<Record Sep
String>",
"doublequote=<TRUE/FALSE>" );
exit(1);
}
}
static void sqlerror_hard()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
fprintf(stderr,"\nORACLE error detected:");
fprintf(stderr,"\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
static SQLDA * process_1(char * sqlstmt, int array_size )
{
SQLDA * select_dp;
int i;
int j;
int null_ok;
int precision;
int scale;
int size = 10;
fprintf( stderr, "Unloading '%s'\n", sqlstmt );
fprintf( stderr, "Array size = %d\n", array_size );
fprintf( stderr, "Field Separator = '%s'\n", FIELDSEP);
fprintf( stderr, "Record Separator = '%s'\n", RECSEP);
fprintf( stderr, "Doublequote is set to '%d'\n\n", DOUBLEQUOTE);
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :sqlstmt;
EXEC SQL DECLARE C CURSOR FOR S;
if ((select_dp = sqlald(size,MAX_VNAME_LEN,MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for select descriptor." );
select_dp->N = size;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
if ( !select_dp->F ) return NULL;
if (select_dp->F < 0)
{
size = -select_dp->F;
sqlclu( select_dp );
if ((select_dp =
sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for descriptor." );
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
}
select_dp->N = select_dp->F;
for (i = 0; i < select_dp->N; i++)
select_dp->I[i] = (short *) malloc(sizeof(short) *
array_size );
for (i = 0; i < select_dp->F; i++)
{
sqlnul (&(select_dp->T[i]),
&(select_dp->T[i]), &null_ok);
if ( select_dp->T[i] <
sizeof(lengths)/sizeof(lengths[0]) )
{
if ( lengths[select_dp->T[i]] )
select_dp->L[i] = lengths[select_dp->T[i]];
else select_dp->L[i] += 5;
}
else select_dp->L[i] += 5;
select_dp->T[i] = 5;
select_dp->V[i] = (char *)malloc( select_dp->L[i] *
array_size );
for( j = MAX_VNAME_LEN-1;
j > 0 && select_dp->S[i][j] == ' ';
j--);
fprintf (stderr,
"%s%.*s", i?",":"", j+1, select_dp->S[i]);
}
fprintf( stderr, "\n" );
EXEC SQL OPEN C;
return select_dp;
}
static void process_2( SQLDA * select_dp, int array_size )
{
int last_fetch_count;
int row_count = 0;
short ind_value;
char * char_ptr;
int i, j;
for ( last_fetch_count = 0;
;
last_fetch_count = sqlca.sqlerrd[2] )
{
EXEC SQL FOR :array_size FETCH C
USING DESCRIPTOR select_dp;
for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
{
for (i = 0; i < select_dp->F; i++)
{
ind_value = *(select_dp->I[i]+j);
char_ptr = select_dp->V[i] +
(j*select_dp->L[i]);
/* Double quote character is stored here */
printf( "%s%s%s%s", i?FIELDSEP:"", DOUBLEQUOTE?"\"":"", ind_value?"":char_ptr,
DOUBLEQUOTE?"\"":"");
}
row_count++;
printf( "%s\n", RECSEP );
}
if ( sqlca.sqlcode > 0 ) break;
}
sqlclu(select_dp);
EXEC SQL CLOSE C;
EXEC SQL COMMIT WORK;
fprintf( stderr, "%d rows extracted\n", row_count );
}
main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;
SQLDA * select_dp;
process_parms( argc, argv );
/* Connect to ORACLE. */
vstrcpy( oracleid, USERID );
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL CONNECT :oracleid;
fprintf(stderr, "\nConnected to ORACLE as user: %s\n\n",
oracleid.arr);
EXEC SQL ALTER SESSION
SET NLS_DATE_FORMAT = 'ddmmyyyyhh24miss';
select_dp = process_1( SQLSTMT, atoi(ARRAY_SIZE) );
process_2( select_dp , atoi(ARRAY_SIZE));
/* Disconnect from ORACLE. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
coredump AIX 5.2
August 1, 2005 - 11am Central time zone
Reviewer: A reader
hi Tom,
Am Using your code to unload data to flat file.
Am running on AIX 5.2 but the code coredumps when i run it.
Is some KNOWN problem with AIX 5.2?
This is the output I got:
------------------------------------------------------
dump_csv userid=<> 'sqlstmt=select * from user_tables' arraysize=10
Connected to ORACLE as user: <>
Unloading 'select * from user_tables'
Array size = 10
TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME,IOT_NAME,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTEN
T,NEXT_EXTENT,MIN_EXTENTS,
MAX_EXTENTS,PCT_INCREASE,FREELISTS,FREELIST_GROUPS,LOGGING,BACKED_UP,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AV
G_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_CORRU
PT,MONITORING,CLUSTER_OWNE
R,DEPENDENCIES,COMPRESSION
660
666
Segmentation fault(coredump)
-----------------------------------------------
Have already altered the code with hat you have suggested to "Gerald Koerkenmeier" i.e. adding
printf( "%d\n", __LINE__ );
Please guide.
Thanks as always.
Coredump on AIX 5.2
August 1, 2005 - 11am Central time zone
Reviewer: A reader
Tom,
Its me who had given the above coredump comment while running your program on AIX 5.2.
Earlier I'd appended the code with printf( "%d\n", __LINE__ ); in the process_2 only.
Now I've done the same for process_1 & here is the code output.
Please guide.
-----------------------------
dump_csv userid=<> 'sqlstmt=select * from user_tables' arraysize=10
Connected to ORACLE as user: <>
Unloading 'select * from user_tables'
Array size = 10
462
510
513
551
554
556
Cannot allocate memory for descriptor.
-----------------------------
So might be a issue of allocating memory. I'm not very good in C. But please guide.
Followup August 1, 2005 - 12pm Central time zone:
print out the input arguments to sqlald I guess, see if they are "reasonable"
sqlald?
August 2, 2005 - 1am Central time zone
Reviewer: A reader
Tom,
what do you mean by input parameters being "reasonable" ??
How tdo I check this?
what is sqlald?
Please spare my ignorance.
Thanks for your help.
Followup August 2, 2005 - 7am Central time zone:
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
August 4, 2005 - 4pm Central time zone
Reviewer: A reader from Houston, TX
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
August 14, 2005 - 2am Central time zone
Reviewer: George from Atlanta, GA
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
September 6, 2005 - 12am Central time zone
Reviewer: Swain from BANGALORE (INDIA)
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
Followup September 6, 2005 - 8am Central time zone:
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
September 23, 2005 - 2am Central time zone
Reviewer: Sanjay from USA
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.
http://www.wisdomforce.com/
Do Pro*C code needes further tuning ? If not, why third party software can dump much faster than
oracle supplied methods.
Followup September 23, 2005 - 9am Central time zone:
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
October 19, 2005 - 7pm Central time zone
Reviewer: Roger from Canada
Hi Tom,
Just bought your new book. Really really cool chapter on the data types (12) (have to read more,
but Chapter 10,11 look very promising).
Here is a question regarding arraysize and maxdata.
Details:
--------
I have often run into the infamous
[quote]buffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA.[/quote] error.
There is a really great article that describes EXACTLY why one gets the error in sqlplus. I have
read it carefully and have been able to reproduce the examples.
http://www.fors.com/orasupp/d2k/plus/2171_1.HTM
If I have a function (pardon the name, I had to make it 12 characters in size...)
CREATE OR REPLACE FUNCTION TEST_FUNCTIO RETURN VARCHAR AS
l_tmp VARCHAR(10);
BEGIN
l_tmp := 'hey';
RETURN l_tmp;
END TEST_FUNCTIO;
SQL> set arraysize 2
SQL> set maxdata 8169
SQL> select test_functio from dual;
buffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA.
SQL> set maxdata 8170
SQL> select test_functio from dual;
TEST_FUNCTIO
----------------------------------------------------------------------------------------------------
hey
As you can see from the link, the length of the column is an important attribute in determine the
required maxdata size in sqlplus (so is the column heading etc.)
Question
--------
When returning a varchar from a function, what is the length of the returned value? Its definately
not 10 based on the formula shown in the paper. Based on my calculations, it looks like 2672.33.
So, does the function actually return a varchar of size 2672 even though I defined the return type
to be varchar2(10)?
I hope I didn't confuse you and make things sound more complex than they are.
Thanks alot for your time.
Followup October 19, 2005 - 7pm Central time zone:
a funtion returns a varchar2(4000) in sql if it returns a varchar2.
use substr() to "constrain" it.
tried substr...
October 20, 2005 - 1pm Central time zone
Reviewer: Roger from Canada
Hi Tom,
Thank you for your reply.
I tried using substr (actually I had tried it before posting the question also) but still getting
the error.
CREATE OR REPLACE FUNCTION TEST_FUNCTIO RETURN VARCHAR AS
l_tmp VARCHAR(10);
BEGIN
l_tmp := 'hey';
RETURN substr(l_tmp,1,5);
END TEST_FUNCTIO;
SQL> set maxdata 8169
SQL> select test_functio from dual;
buffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA.
SQL> set maxdata 8170
SQL> select test_functio from dual;
TEST_FUNCTIO
----------------------------------------------------------------------------------------------------
hey
SQL>
Can you please give an example incase I misunderstood you.
Also, in light of what you say on page 442/443 of your new book about reducing logical i/o's by
using arraysize, what is the best way to make sure that you don't run into the buffer overflow
error when you increase arraysize (this becomes very important if you have long running queries and
after running for 3 hours, all you get is the buffer overflow error).
Thanks again for your amazing work and efforts.
Followup October 20, 2005 - 4pm Central time zone:
select substr( test_funtio, 1, 5 ) from dual;
arraysize
October 21, 2005 - 9am Central time zone
Reviewer: Roger from Canada
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.
Followup October 21, 2005 - 10am Central time zone:
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
October 21, 2005 - 7pm Central time zone
Reviewer: Roger from Canada
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.
Followup October 22, 2005 - 10am Central time zone:
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
October 23, 2005 - 8am Central time zone
Reviewer: Cis from cis ,india
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.
Followup October 23, 2005 - 1pm Central time zone:
using the insert statement?
creating ASCII dump file
October 28, 2005 - 12pm Central time zone
Reviewer: thirumaran from INDIA
Hi Tom,
Is there any easy method to create a comma seperated files , i have nearly 5 million records .
i used these statments to create CSV file but it failed to load
example:
spool file_a
select empno||','||ename||','||job from emp;
spool off
I am not clear on how to proceed further.Pl guide me
Thanks in ADV
Thirumaran
sqlldr_exp ctl file
October 28, 2005 - 5pm Central time zone
Reviewer: Thirumaran from INDIA
Tom,
Thanks for quick response.
I was able to create the CTL file with DATA using your examples and the sqlldr_exp utility. It
created both control file and the data file, when i tried to load data into the same table it
raises errors.
Example:
------------------------------------------------------------
-- Thank you for evaluating Advanced Query Tool.
-- Please support the developer by purchasing the product.
-- At $100 this is very cheap for the features provided.
-- See www.querytool.com for further information.
------------------------------------------------------------
Create Table CSRNG.SR_REQ(
EID NUMBER,
SR_REQ_NUM CHAR(12),
TYPE_CODE CHAR(8),
GROUP_CODE CHAR(8),
PRIORITY_CODE CHAR(8),
STATUS_CODE CHAR(8),
STATUS_DATE DATE,
ORIG_SERVICE_REQUEST_EID NUMBER,
CREATION_REASON_CODE CHAR(8),
RELATED_REASON_CODE CHAR(8),
METHOD_RECEIVED_CODE CHAR(8),
GEO_STREET_NAME_EID NUMBER,
VALID_SEGMENT_FLAG CHAR(1),
LOCATION_RECORD_TYPE_CODE CHAR(8),
STREET_NUMBER NUMBER,
STREET_NAME_PREFIX CHAR(8),
STREET_NAME CHAR(40),
STREET_NAME_SUFFIX CHAR(8),
STREET_SUFFIX_DIRECTION CHAR(8),
CITY CHAR(40),
STATE_CODE CHAR(8),
COUNTY CHAR(30),
ZIP_CODE CHAR(10),
UNIT_NUMBER CHAR(20),
FLOOR CHAR(20),
BUILDING_NAME CHAR(120),
LOCATION_DETAILS VARCHAR2(2000),
WDA_1_VALUE CHAR(120),
WDA_2_VALUE CHAR(120),
WDA_3_VALUE CHAR(120),
X_COORDINATE NUMBER,
Y_COORDINATE NUMBER,
DETAILS VARCHAR2(4000),
BEGIN_RESOLUTION_DATE DATE,
CREATED_DATE DATE,
CREATED_BY_EID NUMBER,
UPDATED_DATE DATE,
UPDATED_BY_EID NUMBER,
GEO_AREA_CODE CHAR(8),
GEO_AREA_VALUE CHAR(120))
;
D:\CSRNG-Database>sqlldr_exp cng/cng@c_ng sr_req > srctrl.ctl
D:\CSRNG-Database>sqlldr cng/cng@c_ng direct=y control=srctrl.ctl log=srctrllog.log
SQL*Loader: Release 10.1.0.2.0 - Production on Sat Oct 29 00:19:59 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Load completed - logical record count 51.
THERE ARE 5 million records but recount count is only 51.
The created control file
LOAD DATA
INFILE *
INTO TABLE sr_req
REPLACE
TRAILING NULLCOLS
FIELDS TERMINATED BY '|'
(
eid
,sr_req_num
,type_code
,group_code
,priority_code
,status_code
,status_date
,orig_service_request_eid
,creation_reason_code
,related_reason_code
,method_received_code
,geo_street_name_eid
,valid_segment_flag
,location_record_type_code
,street_number
,street_name_prefix
,street_name
,street_name_suffix
,street_suffix_direction
,city
,state_code
,county
,zip_code
,unit_number
,floor
,building_name
,location_details
,wda_1_value
,wda_2_value
,wda_3_value
,x_coordinate
,y_coordinate
,details
,begin_resolution_date
,created_date
,created_by_eid
,updated_date
,updated_by_eid
,geo_area_code
,geo_area_value
)
BEGINDATA
5331685|03-00001286 |PWSSDEAD|PWSSAN |STANDARD|C-CLOSED|00-000-00||||PHONE |2272|Y|VALID
|6227|
5331725|03-00001287 |TRASHBRU|TRASHAND|STANDARD|C-CLOSED|00-000-00||||PHONE |278|Y|VALID
|920||T
5331833|03-00001288 |MISSEDRE|REFUSECO|STANDARD|C-CLOSED|00-000-00||||PHONE |5072|Y|VALID
|5913|
5331901|03-00001289 |PWMISSGB|REFUSECO|STANDARD|C-CLOSED|00-000-00||||PHONE |3440|Y|VALID
|130||
5331958|03-00001291 |PWMISSTB|TRASHAND|STANDARD|C-CLOSED|00-000-00||||PHONE |4048|Y|VALID
|1300|
5331983|03-00001292 |TRASHBRU|TRASHAND|STANDARD|C-CLOSED|00-000-00||||PHONE |3446|Y|VALID
|828||
5332015|03-00001293 |STREETSW|STREETCL|STANDARD|C-CLOSED|00-000-00||||PHONE |98|Y|VALID
|1917||1
5332096|03-00001295 |PWMISSTB|TRASHAND|STANDARD|C-CLOSED|00-000-00||||PHONE |3323|Y|VALID
|1719|
5332076|03-00001296 |PWMISSGB|REFUSECO|STANDARD|C-CLOSED|00-000-00||||PHONE |709|Y|VALID
|1133||
5332145|03-00001297 |PWSSDEAD|PWSSAN |STANDARD|C-CLOSED|00-000-00||||PHONE |2195|Y|VALID
|6460|
5332159|03-00001298 |PWSSDEAD|PWSSAN |STANDARD|C-CLOSED|00-000-00||||PHONE |363|Y|VALID
|1100||
5332173|03-00001299 |PWSSDEAD|PWSSAN |STANDARD|C-CLOSED|00-000-00||||PHONE |63|Y|VALID
|700||CA
5332188|03-00001300 |PWSSDEAD|PWSSAN |STANDARD|C-CLOSED|00-000-00||||PHONE |4048|Y|VALID
|848||
5332187|03-00001301 |CLEANAN1|HORTICUL|STANDARD|C-CLOSED|00-000-00||||PHONE |3323|Y|VALID
|1313|
5332226|03-00001302 |PWMISSGB|REFUSECO|STANDARD|C-CLOSED|00-000-00||||PHONE |2327|Y|VALID
|206||
5332282|03-00001303 |CLEANAN1|HORTICUL|STANDARD|C-DUP |00-000-00|5332187|||PHONE |3323|Y|VALID
5332280|03-00001304 |STUMPGRI|URBANFOR|STANDARD|C-CLOSED|00-000-00||||PHONE |3550|Y|VALID
|2601|
5332339|03-00001305 |MISSEDRE|REFUSECO|STANDARD|C-CLOSED|00-000-00||||PHONE |2890|Y|VALID
|1209|
I created the ctl & data file from a table and when loading it back to the same table it raises
error.There are no notnull or any constraints on this table.
Record 1: Rejected - Error on table SR_REQ, column STREET_NAME_PREFIX.
Column not found before end of logical record (use TRAILING NULLCOLS)
what is the cause of this error ?
Thanks
Thirumaran
Followup October 29, 2005 - 10am Central time zone:
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
October 28, 2005 - 8pm Central time zone
Reviewer: thirumaran from INDIA
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
Followup October 29, 2005 - 10am Central time zone:
use this:
sqlplus -s $1 << EOF > /tmp/flat$$.sql
set wrap off
set feedback off
set pagesize 0
set verify off
prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE $2
prompt prompt REPLACE
prompt prompt FIELDS TERMINATED BY '|'
prompt prompt (
select 'prompt ' || decode(column_id,1,'',',') || lower(column_name)
from user_tab_columns
where table_name = upper('$2')
order by column_id
/
prompt prompt )
prompt prompt BEGINDATA
prompt select
select lower(column_name)||'||chr(124)||'
from user_tab_columns
where table_name = upper('$2')
order by column_id
/
prompt '' from $2
prompt /
prompt exit
exit
EOF
sqlplus -s $1 << EOF
set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF
----------------------------------------------------------------------------
tested with:
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 l_string long := 'create table t ( x int';
3 begin
4 for i in 1 .. 999
5 loop
6 l_string := l_string || ',c'||i || ' int ' || case when mod(i,2)=0 then
'default 1234' end;
7 end loop;
8 l_string := l_string || ')';
9 execute immediate l_string;
10 end;
11 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> insert into t (x) values ( 123456 );
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> !sqlldr_exp / t > t.ctl
ops$tkyte@ORA9IR2> delete from t;
1 row deleted.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> !sqlldr / t
SQL*Loader: Release 9.2.0.6.0 - Production on Sat Oct 29 11:10:09 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 1
ops$tkyte@ORA9IR2> select count(*) from t;
COUNT(*)
----------
1
ops$tkyte@ORA9IR2> select x, c998, c999 from t;
X C998 C999
---------- ---------- ----------
123456 1234
original had an issue with the last column being null
sqlldr_exp ctl file
November 2, 2005 - 11pm Central time zone
Reviewer: thirumaran from INDIA
Excellent !!!
It worked exactly as you had mentioned.
Thanks TOM
Regarding Oracle .so libs needed to run this code
November 29, 2005 - 2am Central time zone
Reviewer: Rajul Maheshwari from Malvern,PA,USA
Hello Tom,
I am trying running unload_table program in oracle 9.0.2.0 verison, but i really don't know the .so
libraries which are needed for this program to run.So please provide me the same.
Followup November 29, 2005 - 9am Central time zone:
you need oracle installed, then you'll have everything you need. There is no list of ".so" files
you can copy from one system to another.
flat_array. does it work for 9i
January 12, 2006 - 2pm Central time zone
Reviewer: Ravi from Chicago, IL
Tom,
I'm getting following error.
7 # ==> array_flat.exe userid=scott/tiger@knox 'sqlstmt=select * from emp' arraysize=100
segmentation fault
[1] + Done(139) array_flat.exe userid=scott/tiger@knox 'sqlstmt=select * from emp' arraysize=100
3380 Segmentation violation array_flat.exe
I'm using Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production on Windows 2000 with MKS platform.
Followup January 13, 2006 - 10am Central time zone:
yes, it does for me.
However, I've never tried it on a 64bit platform - you'll need to look into that yourself. It
could well be that I have a 32/64 bit pointer problem.
Thanks.
January 13, 2006 - 2pm Central time zone
Reviewer: Ravi from Chicago, IL
extract into flat files
March 12, 2006 - 12am Central time zone
Reviewer: Gordon from Salt Lake City, CO
We used Pro*C script for data archiving until we migrated to 64bit platform.
Our company ended up purchasing Fastreader - http://www.wisdomforce.com that was mentioned here. Works well for us and surprisingly Fastreader does very fast data extract
ASCII Dump
March 27, 2006 - 6am Central time zone
Reviewer: Parag Jayant Patankar from India
Hi Tom,
I executed Pro*C program for downloading data and it is working very fine and very fast. In this
program I want to change field seperator from comma (,) to another character where I should do
change ? Kindly guide me as I know very little about "C".
thanks & regards
pjp
Followup March 27, 2006 - 10am Central time zone:
fprintf (stderr,
"%s%.*s", i?",":"", j+1, select_dp->S[i]);
change "," to whatever you like.
sqlldr
March 27, 2006 - 9am Central time zone
Reviewer: parag jayant patankar from India
Hi Tom,
I executed Pro*C program for downloading data and it is working very fine and
very fast. In this program I want to change field seperator from comma (,) to
another character where I should do change ? Kindly guide me as I know very
little about "C".
Secondly, when I try to upload data using ctl file for e.g.
LOAD DATA
INFILE *
INTO TABLE tdf31
REPLACE
FIELDS TERMINATED BY ','
(
a0020
,a0090
,c81001
,c81101
,c5060
,c65401
,a9935a
,c65101
,c65201
,c65301
,c65501
,c65601
,c65701
,qdata
,qdatb
,qdatc
,qdatd
,qdate
,qdatf
,qdatg
,qdath
,qdati
,b4110
,b4110a
,b4110b
,b4110c
,b4110d
,b4110e
,b4110f
,b4110g
,b4110h
,b4110i
,b4396
,b4397
)
BEGINDATA
99,99999,20060228,20060228,20060228,20060228,20060301,3,20060228,1,0,0, , , ,
, ,
, , , , ,0,0,0,0,0,0,0,0,0,0,0,(null)
sqlldr xxxx/xxxxx control=tdf31.ctl log=tdf31.log
** It is giving me error for last two columns
Record 1: Rejected - Error on table TDF31, column B4397.
ORA-01722: invalid number
Table TDF31:
0 Rows successfully loaded.
If I put following data
)
BEGINDATA
99,99999,20060228,20060228,20060228,20060228,20060301,3,20060228,1,0,0, , , ,
, ,
, , , , ,0,0,0,0,0,0,0,0,0,0,(null),9
Record 1: Rejected - Error on table TDF31, column B4396.
ORA-01401: inserted value too large for column
If I put some value in last two columns, sqlldr uploads data.
last 3 columns of tdf31 table is
B4110I NOT NULL NUMBER(1)
B4396 CHAR(4)
B4397 NUMBER(2)
Kindly guide me about Proc field seprator and sqlldr error.
thanks & regards
pjp
Followup March 27, 2006 - 10am Central time zone:
(null) is not a number, so it rejected it correctly. not sure what you expected?
sqlldr & proc
March 27, 2006 - 10am Central time zone
Reviewer: parag jayant patankar from India
Hi Tom,
thaks for your answer for Pro*C program. Kindly guide me regading sqlldr error question from me.
thanks & regards
pjp
Followup March 27, 2006 - 10am Central time zone:
I don't know what you would expect? you have an input file with the string
(null)
where a number was expected.
number & (null )
March 27, 2006 - 10am Central time zone
Reviewer: A reader
Hi Tom,
I have downloaded table data to flat file using your very efficient Pro*C program. I am testing it
to load same data into a table by using SQLLDR. According to me it should get upldated.
You said "(null) is not a number, so it rejected it correctly. not sure what you expected?"
I am expecting data downloaded by pro-c program should get uploaded by sqlldr when requires.
Kindly suggest.
thanks & regards
pjp
Followup March 27, 2006 - 2pm Central time zone:
printf( "%s%s", i?",":"",
ind_value?"(null)":char_ptr );
change what is printed when null then - I'm printing the word "(null)", just print "" if you want.
disk full !!!
April 10, 2006 - 8am Central time zone
Reviewer: Parag J Patankar from India
Hi Tom,
Thanks for your very efficient Pro*C program for downloading data to flat file. I am running this
program on unix and working very fine, but it does not get aborted when "disk is full" or "file
system is 100% used"
Can you pl give me Pro*C code to include in this program so it will get aborted when it will not
able to write. Sorry I know very little about "C" and Pro*C.
thanks & regards
pjp
Followup April 11, 2006 - 10am Central time zone:
it just writes to stderr - the console. It would be the shell's responsibility for that.
Or you would have to rewrite it to open it's own file and check return codes from fprintf to that
file.
Simply superb
April 30, 2006 - 2pm Central time zone
Reviewer: Ray from India
Just wanted to know what would be your personal preference among the three approaches in 9i and
above and reasons : http://asktom.oracle.com/~tkyte/flat/index.html
Regards
Ray
Followup May 1, 2006 - 1am Central time zone:
need to unload to a client with relatively good speed and no need to distribute a binary file?
sqlplus.
need to unload to a client with best possible speed, infinite flexibility?
pro*c (you have the code)
need to unload on the server with modest speed from a stored procedure?
plsql.
Better than export Import ?
May 4, 2006 - 2pm Central time zone
Reviewer: Ray from India
Now my question is if I am to transfer a 20 GB table from one server to another server, it seems
that it is better using
1. Unload using Pro*C/plsql
2. SQLloader (direct load, parallel etc...)
rather that export-import (single row inserts, more redo, no parallelism ...)
Correct?
Thanks in advance
Ray
Followup May 4, 2006 - 5pm Central time zone:
3) transportable tablespace
Missed out something
May 5, 2006 - 1am Central time zone
Reviewer: Ray from India
Sorry missed out the context.
Transportable tablespace will work for same platform and same version of Oracle.
10g to 10g RMAN is great for across OS.
My context is: from different OS (some unix flavour to Solaris) and from 8 i to 10g.
Is there are any other options in my context.
Thanks
Ray
Followup May 5, 2006 - 1am Central time zone:
Ray - you never mentioned any versions, so I was free to assume and I assume you are using current
software. Unless and until people mention versions - I'll keep doing that.
You could
a) upgrade 8i to 10g on existing hardware
b) transport
otherwise, if it is "easy enough", you could unload and reload - sure. You could use dblinks
(create table as select). You could use the sqlplus copy command.
export/import do not do single row operations - not unless the table has longs/lobs - in which case
unload/load is going to be "hard" as well.
Serious apologies
May 5, 2006 - 5am Central time zone
Reviewer: Ray from India
Hi Tom
Serious apologies for not being clear on the entire requirement. Sorry for the entire confusion.
Silly me I should have remembered that the buffer parameters for export import are for batch
operations. Am I right.
If you permit me I will add on to the actual requirement.
One of the my client wants to move on from 'X' flavour of UNIX to Solaris 10. I cannot possibly
suggest him to buy Oracle for two enitrely different platform (Oracle 10g on UNIX (X flavour) and
Oracle 10g on Solaris). Budgetary constraint you would appreciate. Hence my question.
Hence upgrade-transport is likely to be ruled out.
Coming to the other options:
1. Hope you would agree that CTAS and copy over db link would have a network constraint
(dependency). Hence I was more focussed on unload-reload and export-import.
2. My undestanding is that using unload (SQL/Pro*C) I can use SQL parallelism and hence would be
faster than export. Will this be faster than using a reasonable value for the buffer parameter for
export?
3.Similarly I can use parallel load using direct path using loader. Will this be faster and
efficient than using a reasonable value for the buffer parameter for import?
Now that I have given the broad requirement I would like to know your approach to the problem.
Apologies if I am carrying on the same stuff for long.
Regards
Ray
Followup May 5, 2006 - 6am Central time zone:
They do not "buy" Oracle like that - they "own" Oracle and get upgrades with this ownership.
So, ball back in your court :)
1) networks are not "slow" - in fact, networks can be faster than disk :) Hence, I focus on all
things.
2) maybe yes, maybe no. It would be expensive since YOUR TIME costs more $$$$$ than anything else
here. (so what if it takes 5 hours using export/import and 30 minutes with a custom program if it
costs $$$$$ to have program written and takes 10 hours to debug program)
3) probably
My approach is always "the simplest way"
You haven't said "this needs to be completed in X minutes due to switchover requirements"
If I was just having to get this done - sqlplus copy might be my first choice (if I cannot upgrade)
Excellent as usual
May 5, 2006 - 11am Central time zone
Reviewer: Ray from India
I am overwhelmed. There is always so much to learn from you. You just squeezed out the last bit of
of my requirement "this needs to be completed in X minutes due to switchover requirements".
Asuming that I cannot upgrade what could be the best method, in your opinion, to minimize downtime
for switchover given a Database size of around 600GB.
i.e. after having done the copy (unload-load etc...) the best way to minimize the delta (data and
time) for switch over. Can logminer be helpful during this delta?
Appreciate and many thanks for your prompt responses.
Regards
Ray
Long.
July 19, 2006 - 3am Central time zone
Reviewer: Reader from Singapore
I have a table with datatype long. The data in this long feild is almost 220 MB. when I am running
this process I am getting null as output. Please help
Followup July 19, 2006 - 9am Central time zone:
I did not put in support for such long fields. if you look at the lengths[] array, I map the
maximum lengths there, far far short of 220mb, you'll need to take this code as a STARTING POINT
and modify it for your needs.
Reading long
July 20, 2006 - 3am Central time zone
Reviewer: Laxman Bage from Singapore
Thanks a lot for useful tip!!!
Sql Loader
July 22, 2006 - 10am Central time zone
Reviewer: Raghav from India
Hi Tom,
I have created a batch file for truncating a temporary table and then load the data into that table
through the sqlldr. Once the data is successfully loaded into the temporary table, it inserts the
data into the live table.
@echo off
sqlplus -S cpms/cpmsrmshyd@bhopal9i @c:\erp2cpms\trunc_tmp_sourcing.sql
sqlldr userid=cpms/cpmsrmshyd@bhopal9i control=c:\erp2cpms\cpms.ctl log=c:\erp2cpms\cpms.log
bad=c:\erp2cpms\cpms.bad data=c:\erp2cpms\erp_data.txt
sqlplus -S cpms/cpmsrmshyd@bhopal9i
@c:\erp2cpms\scr_insert_sourcing.sql
exit
The contents of the control file are:
----------------------------------------------------------------------------------------------------
-------
-- NAME Cpms.ctl
-- DESCRIPTION Control file for Data uploads from text file of ERP to CPMS
-- RETURNS None
-- CREATED BY Raghavarao RV
-- CREATED ON 28/03/2006
-- NOTES For each table there will be a block of code to validate and to load
--
-- MODIFIED (DD/MM/YYYY) DESCRIPTION
-- ON 28/03/2006 Script file Generated
----------------------------------------------------------------------------------------------------
-------
LOAD DATA
INFILE 'cpms.dat'
BADFILE 'cpms.bad'
DISCARDFILE 'cpms.dsc'
APPEND
-- TABLE_SOURCING_DATA
INTO TABLE TMP_SOURCING_DATA
TRAILING NULLCOLS (
PURCHASE_DATE POSITION(1:10) DATE "DD/MM/RRRR" nullif PURCHASE_DATE = blanks,
HUB_ID POSITION(12:21) CHAR,
HUB_NAME POSITION(23:52) CHAR,
CHOUPAL_ID POSITION(54:63) CHAR,
COMMODITY_ID POSITION(65:74) CHAR,
COMMODITY_NAME POSITION(76:100) CHAR,
GROSS_QTY POSITION(102:111),
NET_QTY POSITION(113:122),
TOTAL_AMT POSITION(124:133),
GRN_ID POSITION(135:144) CHAR,
SUPPLIER_ID POSITION(146:155) CHAR,
SUPPLIER_TYPE POSITION(157:166) CHAR,
VARIETY POSITION(168:182) CHAR,
VARIETYNAME POSITION(184:208) CHAR
)
It has successfully run for some time. But, for the last few days, when I run the script, it is
executing the first command successfully by giving
"Table Truncated"
But when it has to execute the second command, it is giving the message "Table Truncated" as it has
fallen into an indefinite loop.
Please let me know, why the script is behaving like this?
Thanks and regards
Raghav
Followup July 23, 2006 - 8am Central time zone:
you need to change your syntax:
...
I have created a batch file for truncating a REAL PERMANENT table and then load the data into that
table through the sqlldr. Once the data is successfully loaded
into the REAL PERMANENT table, it inserts the data into the live table.
........
sorry, but given the above script, it is not infinite looping. If what you say is happening - we
must be running a different script then we are looking at!
but, just run sqlldr - don't run the sqlplus script to truncate. Change the word APPEND in the ctl
file to TRUNCATE and sqlldr will do the right thing. You never should have used sqlldr
Sqlldr
July 24, 2006 - 7am Central time zone
Reviewer: Raghav from India
Hi Tom
Thanks for your suggestion. Now, keeping aside the other commands for truncating the temporary
table and inserting into the real table I kept only the following line in the batch file.
sqlldr userid=cpms/cpmsrmshyd@bhopal9i control=c:\erp2cpms\cpms.ctl log=c:\erp2cpms\cpms.log
bad=c:\erp2cpms\cpms.bad data=c:\erp2cpms\erp_data.txt
Now also, when I run the batch file (with only the above statement stored and saved as a batch
file), the same "table truncated" message is coming. But, to bring to your notice, when copy this
line and pasted in DOS prompt, all 42000 records (which are expected from the text file) have been
inserted into the temporary table without any error. May I know what was the reason behind it?
Thanks and Regards
Raghav
Followup July 24, 2006 - 10am Central time zone:
if your script only has that line AND you are getting the table truncated message, you are NOT
running that script.
That is about all I can say, that runs sqlldr, sqlldr does NOT produce that message.
Sqlldr
July 25, 2006 - 7am Central time zone
Reviewer: Raghav from India
Hi Tom
We have got the solution for the above problem. The batch file is behaving mischievously. Our
local systems administrator had a solution for this. We have added the absolute path of DOS before
running the scripts.
@echo off
c:
cd\
sqlldr statements
Then the script (batch file) is working fine.
Thanks and Regards
Raghav
Followup July 25, 2006 - 11am Central time zone:
uh huh - then it was what I thought it was.
YOU WERE NOT RUNNING THE SCRIPT YOU SAID YOU WERE!
if putting explicit paths fixed it, you were apparently running something entirely different
(fairly "dangerous" concept.... running things in production but not being entirely sure what you
are running)
sqlldr
July 26, 2006 - 4am Central time zone
Reviewer: Raghav from India
Hi Tom,
I am running the same batch file only. But, when I start running the same (windows 2000 pc DOS
prompt), I think, the system is using the local cache (from documents and settings) and running the
previously executed command / coments only. By giving the absolute path, it was not checking the
local cache and the execution run properly.
Your coments pls.
Thanks and regards
Raghav
Followup July 26, 2006 - 10am Central time zone:
no, you ran a cmd file without a specific path.
so, it searched your current working directory AND your %PATH% environment variable.
And it found it - and ran it - but it was not the one you wanted to run.
There is no "local cmd cache", that concept does not exist.
Your path and the fact you did not fully qualify the script name are what caused this and it was
exactly what I suspected - the script you said you were running was in fact NOT the script being
executed.
Semantic Errors
August 1, 2006 - 11am Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
Tom, When attempting to precompile on a Windows platform, I get the following errors. Any ideas?
++: Release 9.2.0.7.0 - Production on Tue Aug 1 10:52:49 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
System default option values taken from: d:\oracle\product\920\precomp\admin\pcscfg.cfg
Semantic error at line 110, column 33, file M:\array_flat.pc:
EXEC SQL PREPARE S FROM :sqlstmt;
................................1
PCC-S-02322, found undefined identifier
Semantic error at line 110, column 27, file M:\array_flat.pc:
EXEC SQL PREPARE S FROM :sqlstmt;
..........................1
PCC-S-02319, expression type does not match usage
Semantic error at line 181, column 23, file M:\array_flat.pc:
EXEC SQL FOR :array_size FETCH C
......................1
PCC-S-02322, found undefined identifier
Semantic error at line 181, column 23, file M:\array_flat.pc:
EXEC SQL FOR :array_size FETCH C
......................1
PCC-S-02330, expecting an expression of integer type
Followup August 1, 2006 - 7pm Central time zone:
they have different default options on windows, what is in the default options file (I don't have
any windows pro*c installs)
love the unloaders -- what about very large tables?
September 27, 2006 - 11am Central time zone
Reviewer: Elaine H from Atlanta, GA
i downloaded your unloader utilities and think they are just awesome.
any suggestions on what to do with a very large table - 9 partitions (1.3Tb)? it also has 9
partitions of indexes, another 1.5Tb.
we will soon be migrating from 9.2 on aix to 10r2 on solaris x86. any way we do this is going to
be ugly. we are planning to put the entire database in read-only mode, so that the users can
continue to query against it -- they don't update at all; all updates occur during batch
processing.
we are hoping to accomplish several things:
1. reorganize the main table. all the inserts are insert/append. we are slowly deleting some rows
and would like to get that space back where possible.
2. move to a different block size on the new hardware.
3. take advantage of the new features in 10g.
we have toyed with the idea of upgrading in place and then using transportable tablespaces w/rman
to move the big data, but then neither 1 nor 2 is accomplished. besides, on the current release of
the o/s, we can't install 10r2, and no upgrade is planned for the rest of the year and we are
moving to the new platform sometime in the 4th quarter (we hope).
i am fairly certain that using one of your unloaders is the way to go for the 380G worth of data
that isn't in this large table, but would like your opinion as to how to move the large table. is
there a partition option available in your unloader -- would it be feasible to add one? would you
recommend sql copy instead? do you have another recommendation?
i really am excited about using the unloader on the small tables, so thanks so much for making them
available!!!
if you have any thoughts, suggestions or links on what to do with the large table, i would really
appreciate it.
Followup September 27, 2006 - 3pm Central time zone:
1) i'd be using alter table t move partition p; if I really needed to.. (reorg)
2) #1 would do that.
so, maybe upgrade to 10.2 on aix, transport to other OS (cross platform transports) and then alter
moves if you really feel it is necessary.
very large tables
September 29, 2006 - 7pm Central time zone
Reviewer: Jason from NJ
Our IT recently migrated 4.3TB Oracle database to another plattform. Upgrade and the transport did
not worked for us for some reason. IT used some tool called fastreader from company wisdomforce to
transport the data. They unloaded data into ascii files and then loaded into new platform with
sqlloader
The unload into text files part took like 5 hours. That was really good taking into account 4+ TB
size
How can we convert this from delimit to fixed length
March 14, 2007 - 10am Central time zone
Reviewer: Praveen Reddy from Maryland USA
can you please give me steps to convert this from delimit to fixed length
Followup March 14, 2007 - 2pm Central time zone:
1) write code
you would use rpad() and nvl (on nullable columns - to return a space to be rpadded) to make the fields whatever length you wanted and then concatenate them
Problem when compiling for C++, using CODE=CPP in pscfg.cfg file
June 5, 2007 - 8am Central time zone
Reviewer: Admirer
Proc reports errors when trying to generate CPP source code on Oracle 8.1.7 on Sun Solaris. The error reported is "PCC-S-02322 found undefined identifier" when trying to convert process_1 function.
Why is the code mixing K&R style and ANSI_C tyle function prototypes? (check main(), process_1 and process_2)
If I try to modify the generated code to such to ANSI_C prototype, the compilation still does not work and reports the following problems:
line 513: Error: Too many arguments in call to "sqlald()".
line 559: Error: Too many arguments in call to "sqlclu()".
line 561: Error: Too many arguments in call to "sqlald()".
line 609: Error: The function "sqlnul" must have a prototype.
line 727: Error: Too many arguments in call to "sqlclu()".
line 791: Error: Formal argument 1 of type char* in call to std::strcpy(char*, const char*) is being passed unsigned char*.
line 791: Error: Formal argument 1 of type const char* in call to std::strlen(const char*) is being passed unsigned char*.
7 Error(s) detected.
Could you please help.
Followup June 6, 2007 - 1pm Central time zone:
none of my code is c++, i don't know what you are trying to do, it is a standalone c program, if you want c++, you'll want to take the IDEA behind the code and port it to that aberration of a language ;)
CPP problem
June 5, 2007 - 8am Central time zone
Reviewer: Admirer
In continuation of the previous comment, Proc seems to have a problem with process_1 because it is returnig an SQLDA type of pointer. Changing it to K&R style helps but finally it does not work if I try to compile the generated c++ code.
Awesome
June 29, 2007 - 3pm Central time zone
Reviewer: reader
Tom,
I have never written a line of C code, what are the steps that I need to follow to get use this awesome utility ?
I have downloaded the c source and compiled it using proc
We are running RH Linux and Oracle Database version 10gr2.
Thanks
Column with CHAR(1)
July 5, 2007 - 9am Central time zone
Reviewer: Admirer from Belgium
Tom,
When I use this tool to dump a table which has column defined as "CHAR(1)", the result is ^A for this column. Where as when I try doing a sqlplus dump, I get the actual value, even when I am running the same query as "select * from tablename" for both sqlplus and your tool.
To avoid this will I have to use something like "ascii(char_column)" function? If this is true then I will not be able to do a "select * from tablename" but will have to do something like:
" select column1 ||,|| ascii(char_column2 ||,|| column3 from tablename"
I also fear that including a conversion function may reduce the speed of the dump.
Any comments please.
Followup July 5, 2007 - 1pm Central time zone:
when you debug it and see what it might be doing wrong, let us know and then everyone can benefit.
I've not had a problem with small fields.
Timestamp columns
August 1, 2007 - 6pm Central time zone
Reviewer: Suryanarayana Murthy from NY,USA
Hi Tom,
I used your ProC code to download data from many tables and is pretty fast. But I have problems
downloading tables which have TIMESTAMP Columns. They are returned with null values instead. I have
never written ProC code and so could not debug the problem. Can you help us here?
I can give you more details if you need any. We are running this on 10gR2.
Thanks,
Murthy
Followup August 5, 2007 - 10am Central time zone:
simple 1 nanosecond fix:
use to_char() in the sqlstmt, format the timestamp as per your needs
done.
load into table using Pro*C program
August 2, 2007 - 5am Central time zone
Reviewer: william from Italy
Hi Tom,
thanks for your Pro*C program it's very efficient.
The question is:
can the Pro*C program load into table the previous data extracted in the text file?
So i don't want to use SQL*Loader 'cos i think it's slower than Pro*C but i don't know how can i
modify the code.
Can you have an idea?
Thank a lot.
Followup August 5, 2007 - 10am Central time zone:
wow, 'cos i think it's slower than Pro*C
hmmm
why do you think that? given that sqlldr is a C program itself.
use sqlldr
use external tables
do not write yet more code.
32/64 bit pointer problem??
August 17, 2007 - 4pm Central time zone
Reviewer: Kevin from Ontario, Canada
Hi Tom, I used your code and it worked perfectly in 32-bit, however I now need to make it work in
64-bit environment but it is core dumping. You mentioned earlier that it might be a 32/64 bit
pointer problem. Do you know of any issues or solutions for using this code in 64-bit? GDB output
gives the following info:
Program received signal SIGSEGV, Segmentation fault
si_code: 1 - SEGV_MAPERR - Address not mapped to object.
0x9fffffffeeef65c0:0 in lxhmcnv+0x2e0 ()
from /oracle/product/10.2.0/lib/libclntsh.so.10.1
Any help or reference places would be greatly appreciated.
Thanks,
Kevin (not the same Kevin as the original post)
September 28, 2007 - 5am Central time zone
Reviewer: HillChen from Taipei,Taiwan
Hi Tom,
Use to_char() in the sqlstmt that can solve the problem of TIMESTAMP column. I will going to use
"select * from XXX" in the sqlstmt,because the table has too many columns. How can I fix your
program? Please help me.
Thanks!
Hill
Followup September 28, 2007 - 5pm Central time zone:
just list the columns, you only have to type it in ONCE after all.
SQLDA for Java
October 25, 2007 - 9am Central time zone
Reviewer: william from Italy
Tom,
the SQLDA structure is available in Java?
If not , do you know if there is something similar?
Thanks.
Followup October 25, 2007 - 6pm Central time zone:
there is just what is documented in the jdbc api guides.
basically, you programmatically access the metadata about an opened result set in jdbc - there are methods to discover the number of columns, the names, the types and so on.
you would not need a sqlda for that.
Execution on Windows
November 2, 2007 - 3am Central time zone
Reviewer: pratik from India
Hi.....
I have problem in implementing your code on windows machine..
I am not very well aware of C programming.
I had follow following step:-
1>precompilation of dataload.pc using Proc
2>then trying to compile dataload.c using borland turbo c,, but got error of undefine symbol
_sqlald, _sqlclu, _sqlcxt, _sqlnul
Can you specify full step or setting require to use this code on windows machine using turbo C or
Microsoft Visual C??
Thanks
Pratik
Good comparison to my Perl DBI program
November 9, 2007 - 4pm Central time zone
Reviewer: Peter from Phoenix, AZ
One of the users said he attempted to write out 2 million rows in 8 minutes. My Perl DBI program
dumps out in half the time so this solution will not work for me but is greatly appreciated.
eval {
while ($row = $sth->fetchrow_arrayref()) {
printf(IOF "%s\n", join($delimiter, @$row));
$rowCount++;
}
};
Thanks Tom.
No rule to make target `xssqlimp.c'
November 9, 2007 - 5pm Central time zone
Reviewer: Peter from Phoenix, AZ
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
Followup November 10, 2007 - 11am Central time zone:
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
November 13, 2007 - 1pm Central time zone
Reviewer: Peter from Phoenix, AZ
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
Followup November 16, 2007 - 2pm Central time zone:
looks likely.
My apologies
November 13, 2007 - 1pm Central time zone
Reviewer: Peter from Phoenix, AZ
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
December 4, 2007 - 10am Central time zone
Reviewer: Peter from Phoenix, AZ
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
December 29, 2007 - 9am Central time zone
Reviewer: Jeff from St Louis MO
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
Followup January 1, 2008 - 5pm Central time zone:
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...)
January 2, 2008 - 12am Central time zone
Reviewer: A reader
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
February 13, 2008 - 12pm Central time zone
Reviewer: Josh Collier from pdx
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.
Followup February 13, 2008 - 1pm Central time zone:
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?
March 7, 2008 - 4pm Central time zone
Reviewer: Meriadoc from Canada
Tom,
Thanks for the exellent article. I was wondering has anyone tried this for the newer versions of
Oracle 9.x / 10.x?
Followup March 10, 2008 - 10am Central time zone:
yes, it is just standard PRO*C, should work in version 42 even when that comes out.
Pro C Flat with UTF chaacters
May 14, 2008 - 11pm Central time zone
Reviewer: Les Smith from Sacramento, CA USA
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
May 20, 2008 - 7pm Central time zone
Reviewer: Les from Sacramento, CA
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 ?!
September 24, 2008 - 1am Central time zone
Reviewer: Bob from NY
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
Followup September 24, 2008 - 7pm Central time zone:
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
February 23, 2009 - 3am Central time zone
Reviewer: Amir Riaz
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
Followup February 23, 2009 - 3am Central time zone:
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
February 25, 2009 - 1am Central time zone
Reviewer: Amir Riaz
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
Followup February 25, 2009 - 6am Central time zone:
... 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
February 28, 2009 - 12am Central time zone
Reviewer: Amir Riaz
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
Followup March 3, 2009 - 9am Central time zone:
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
March 5, 2009 - 1am Central time zone
Reviewer: A reader
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
May 27, 2009 - 6pm Central time zone
Reviewer: Salaam Yitbarek from Ottawa, ON Canada
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.
Followup May 28, 2009 - 7am Central time zone:
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...
May 29, 2009 - 2pm Central time zone
Reviewer: Salaam Yitbarek from Ottawa, ON Canada
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.
Followup June 1, 2009 - 7pm Central time zone:
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...
June 3, 2009 - 8am Central time zone
Reviewer: Salaam Yitbarek from Ottawa, Canada
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.
Followup June 4, 2009 - 9am Central time zone:
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?
July 3, 2009 - 12am Central time zone
Reviewer: John from Australia
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 :-)
Followup July 6, 2009 - 7pm Central time zone:
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
July 4, 2009 - 10pm Central time zone
Reviewer: David from USA
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?
Followup July 6, 2009 - 8pm Central time zone:
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
July 7, 2009 - 5pm Central time zone
Reviewer: Mark Wooldridge from Warrenton VA.
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.
|