PLS-00306 When inserting into an 8i db from a dblink 9i (TIMESTAMP)

[ecasadella]

If I issue this from a sqlplus works fine, but inside a PL/SQL gives PLS-00306: wrong number or types of arguments in call to 'TO_CHAR'

INSERT INTO sdr_service_detail_record
select to_date(to_char(SDR_ADD_DTZ,'ddmmyyyy h24:mi:ss'),'ddmmyyyy h24:mi:ss') SDR_ADD_DTZ
FROM sdr_service_detail_record@PLATAFORMA_PAR


Column SDR_ADD_DTZ in DB 9i is TIMESTAMP

TIA.

EDC
--------------------------------------
This is Unix-Land. In quiet nights, you can hear the Windows machines
reboot.

 

[lewisp]

Check that the PL/SQL script is not picking up a different table or synonym. If you are running the PL/SQL as a stored procedure you will probably be running it with the privileges of the creator.

 

[Dagon]

In 8i, SQL in PL/SQL was not the same as ordinary SQL. There were a lot of restrictions (for example, you couldn't use CASE statements and analytic functions). It may be you are hitting such a restriction. The TO_CHAR function probably doesn't accept a TIMESTAMP parameter.

 

[Beilstwh]

The problem is that oracle 8i does not have the timestamp type. When you called to_char from 8i against a timestamp column in 9i, to_char said "what the heck it that".

Bill
Oracle DBA/Developer
New York State, USA

 

[ecasadella]

The weird thing is that if I call that statement from a sqlplus works ok, while inside a PL/SQL it doesn't. But I solver the problem by creating view with the column preformated as it is in the select, and inserting from the view inside the block works perfect.

Regards.

EDC
--------------------------------------
This is Unix-Land. In quiet nights, you can hear the Windows machines
reboot.