Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> ProC/C++ question urgent!
I have the following problem:
I need to insert into a table where some columns have DATE format.
For example a table MYTABLE:
(
ACCOUNT_NUMBER NUMBER, DATE_SYS DATE
the thing is the DATE_SYS can be beyond year 2000, so the "default" format
'DD-MON-YY' doesn't work correctly (e.g. '01-FEB-02' is interpreted as
Feb.01, 1902,
not Feb.01,2002 as it should have). That's why I have to use 'RRMMDD'
format.
I use the following INSERT statement to populate the table:
double acctno;
char dsys[7];
[.. code which initializes acctno and dsys ...]
EXEC SQL AT MYDBASE INSERT INTO MYTABLE VALUES
(:acctno, TO_DATE(:dsys,'RRMMDD'));
It works OK without problems. The problem is, however, in performance.
I have to insert millions of rows with AVG_ROW_LEN = 220 into a table
similar to above
only with 74 columns instead of 2,
and it takes a whole day to do this using the INSERT statement similar to
above.
I believe I can impove significantly if I somehow can get rid of TO_DATE
function call
and load DATE fields directly. Also I would want to use bind array to
insert a couple hundred
rows in one INSERT statement, and TO_DATE function call prevents me from
doing this.
So the more specific question is, can I do something like this ?: EXEC SQL AT MYDBASE INSERT INTO MYTABLE VALUES (acctno, :dsys)
where dsys is NOT in "default" 'DD-MON-YY' format.
And if I can, what type of variable dsys should be and how do I initialize
it so Oracle does not throw up with "inconsistent datatype" error ?
I tried to make dsys a double, and long, and make it equal the number of
seconds since
year 1970, but Oracle invariably threw up with "incompatible datatype"
error for the DATE_SYS field.
Thanks for any suggestions,
Sam
Received on Thu Sep 11 1997 - 00:00:00 CDT