Load failure with SP1 into oracle

[Yvonne Grossmann]

Hi,

I've installed the SP1 and now I can't load data into an Oracle database.

Now I get for any date values the error ORA-01841: (full) year must be between 04713 and +9999, and not be 0

I've exported the data from an SqlBase with unload data sql ... and now I want to import them into an Oracle 10g via ODBC with SQLTalk:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD-HH24.MI.SS';
load sql ...

Yesterday, before installing the SP 1, it worked, also without the ALTER SESSION command. But without this today I'm getting the error message ORA-01858: a non numeric character was found where a numeric was expected.

I've also tryed the manipulate the output file and changed the date to e.g. 2010-03-11 with ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
but I've got the same error.

This is a severe problem for us, since we have to distribute some data to our client databases, and until now load sql in SQLTalk was an universal way to to so.

Thanks and best regards

Yvonne

[Jeff Luther]

I've installed the SP1 and now I can't load data into an Oracle database.

I infer from that that this did work in the 5.2 before the SP1 installation?

When you installed SP1, did you uninstall 5.2 first, then do a FULL install of SP1? If not, I suggest you do this and try this again.

now I want to import them into an Oracle 10g via ODBC with SQLTalk

Oh, sounds like you are not using a TD app. for the load, but SQLTalk and doing that via an ODBC connection to Oracle? And the data look valid in your LOAD file??

What happens if you use the native Oracle router that we ship? In your SQL.INI the settings would look something like:
[win32client.dll]
comdll=sqlora32
; plus any other comdll assignments

[oragtwy]
;
remotedbname=oracle10g,@tns:oracle10g
--------------------------------------------------
with TNSNAMES.ORA in your Oracle client folder looking like this:

Code: Select all

ORACLE10G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <host url>)(PORT = <#>))
    )
    (CONNECT_DATA =
      (SID = oracle10g)
    )
  )

SUGGESTION: How about providing your load script + some data + any instructions for testing for us/me to test? If the data are proprietary, zip and email to me directly: jluther@guptatechnologies.com, otherwise just attach it to this thread.
I'll try this against ORA10g with SQLTalk myself. Thanks.

[Yvonne Grossmann]

Hi Jeff,

The test data are not a secret.

To create a test table in SqlTalk:
CREATE TABLE my_ktab (
ktab_sprach_kz VARCHAR2(3) NOT NULL,
ktab_typ VARCHAR2(10) NOT NULL,
ktab_kz VARCHAR2(10) NOT NULL,
ktab_fremd_kz VARCHAR2(10) NOT NULL,
ktab_kn VARCHAR2(100) NULL,
ktab_ab_dat DATE NULL,
ktab_typ1 VARCHAR2(10) NULL,
ktab_typ2 VARCHAR2(10) NULL,
ktab_sort_kz1 NUMBER(10) NULL,
ktab_sort_kz2 NUMBER(10) NULL,
ktab_referenz VARCHAR2(50) NULL,
ktab_table_name VARCHAR2(50) NULL,
ktab_field_name VARCHAR2(50) NULL,
ktab_ukar_boxname VARCHAR2(50) NULL,
ktab_flags VARCHAR2(254) NULL,
ktab_ltext CLOB NULL,
ktab_format CLOB NULL,
ktab_sp_kz1 VARCHAR2(2) NULL,
ktab_sp_kz2 VARCHAR2(2) NULL,
ktab_sp_kz3 VARCHAR2(2) NULL,
ktab_user_cr VARCHAR2(8) NULL,
ktab_time_cr DATE NULL,
ktab_user_mod VARCHAR2(8) NULL,
ktab_time_mod DATE NULL,
ktab_count_mod NUMBER(10) NULL
);

CREATE UNIQUE INDEX XPKmy_ktab ON my_ktab
(
ktab_sprach_kz,
ktab_typ,
ktab_kz
);

Then the data are loaded in SQLTalk:

ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
ALTER SESSION SET NLS_TERRITORY=AMERICA;
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD-HH24.MI.SS';

delete from my_ktab;

commit;

load sql ora_myktab.sql;

where the file ora_MyKtab.sql was unloaded with “unload data sql …” from a SQLBase and contains:

INSERT INTO SYSADM.MY_KTAB VALUES(
:1,
:2,
:3,
:4,
:5,
:6,
:7,
:8,
:9,
:10,
:11,
:12,
:13,
:14,
:15,
:16,
:17,
:18,
:19,
:20,
:21,
:22,
:23,
:24,
:25)
\
$DATATYPES CHARACTER,CHARACTER,CHARACTER,CHARACTER,CHARACTER,DATE,CHARACTER,CHARACTER,NUMERIC,NUMERIC,CHARACTER,CHARACTER,CHARACTER,CHARACTER,CHARACTER,CHARACTER,CHARACTER,CHARACTER,CHARACTER,CHARACTER,CHARACTER,DATETIME,CHARACTER,DATETIME,NUMERIC
"NL","SAL1","030","030","4AJahres~PM~bertrag @Soll",,"","",0,0,"","","","","",$long,$long,"11","","","SYSADM",2000-06-05-17.14.45.580000,"SYSADM",2001-10-05-16.05.28.450000,6,
Nur Sollbetrag, der bei den Bestandskonten schon mit Jahreswechsel ins ~~
neue Jahr~AN~

~PM~bertragen wurde. Dient zur schnellen Verprobung, ob noch ein Jahres~~
wechsel~AN~

wiederholt werden soll.~
//
R15,"","",L,"","",#.##0,00~
//
/

The behavior differs a little bit from installation to installation. Via ODBC I usually get the error “ORA-01858: a non numeric character was found where a numeric was expected”, on one machine only with SP1, on the other with and without. The native Oracle router runs only on one machine properly (with Oracle 9i). Here the import doesn’t cause an error, but a select in SqlTalk shows that in the clob columns ktab_ltext and ktab_format there are not the characters but the ascii codes of the characters (e.g. the string “41” instead of “A”).

SQLTalk 2.1 loads the same data without complication.

Thank You and best regards

Yvonne

[Jeff Luther]

Yvonne: This looks like a dup. to another issue I just responded to. It is much more helpful to only open one thread per issue. See other issue for my response.

[Yvonne Grossmann]

Hi Jeff,

to separate the issues: Back to the problem to load data into an oracle database with SQLTalk without using any TD application. With odbc I get the error message for the date column, and with the native router the clob are imported as the ascii values (e.g. the string '41' instead of the string 'A'). Both SQLTalk and the oracle SQL Developer show this. Do you have any idea for this problem?

Thank you and best regards

Yvonne