Home » SQL & PL/SQL » SQL & PL/SQL » selecting data from remote database[sql server]
selecting data from remote database[sql server] [message #205771] Mon, 27 November 2006 08:45 Go to next message
raghavs_dvg
Messages: 8
Registered: November 2006
Location: mumbai
Junior Member
Hi all,

I am writing a procedure(in oracle database) that is going to store the data in oracle database by select a data from remote database(sql-server) with the help of db-link. but it's giving the following error
ora-01843 not a valid month ora-02063 preceding from <db_link name>

but if i only select it workds fine.....

plz help me out....

Re: selecting data from remote database[sql server] [message #205772 is a reply to message #205771] Mon, 27 November 2006 08:48 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
This has nothing to do with db links and is caused by something in the query you are running. Without seeing that query, I doubt anyone will be able to help.
Re: selecting data from remote database[sql server] [message #205773 is a reply to message #205772] Mon, 27 November 2006 08:53 Go to previous messageGo to next message
raghavs_dvg
Messages: 8
Registered: November 2006
Location: mumbai
Junior Member
this is the query
insert INTO hs_mmv_cmcdr
SELECT cdrrecordtype, cmr.globalcallid_callmanagerid,
cmr.globalcallid_callid, nodeid, cdr.directorynum,
cdr.callidentifier, to_date(datetimestamp,'dd/mm/yyyy hh:mi:ss am'), numberpacketssent,
numberoctetssent, numberpacketsreceived, numberoctetsreceived,
numberpacketslost, jitter, latency, pkid, directorynumpartition,
devicename, cmr.globalcallid_clusterid, cdrrecordtype_b,
globallcallid_callmanagerid_b, globalcallid_callid_b,
cdr.origlegcallidentifier, datetimeorigination, orignodeid,
origspan, origipaddr, origipport, cdr.callingpartynumber,
origcause_location, origcause_value, origmediatransportaddress_ip,
origmediatransportaddress_port, origmediacap_payloadcapability,
origmediacap_maxframesperpkt, origmediacap_g723bitrate,
destlegidentifier, destnodeid, destspan, destipaddr, destipport,
originalcalledpartynumber, cdr.finalcalledpartynumber,
destcause_location, destcause_value, destmediatransportaddress_ip,
destmediatransportaddress_port, destmediacap_payloadcapability,
destmediacap_maxframesperpkt, destmediacap_g723bitrate,
datetimeconnect, datetimedisconnect, lastredirectdn, pkid_b,
origcalledpartynumpartition, callingpartynumberpartition,
finalcalledpartynumpartition, lastredirectdnpartition, DURATION,
origdevicename, destdevicename, origcalledpartyredirectreason,
lastredirectredirectreason, destconversationid,
origcallterminationonbehalfof, destcallterminationonbehalfof,
origcalledpartyrdtonbehalfof, lastredirectredirectonbehalfof,
globalcallid_clusterid_b, joinonbehalfof
FROM calldetailrecorddiagnostic@raghu cmr, calldetailrecord@raghu cdr
WHERE (cmr.globalcallid_callmanagerid = cdr.globalcallid_callmanagerid)
AND (cmr.globalcallid_callid = cdr.globalcallid_callid)
AND ( (cmr.directorynum = cdr.callingpartynumber)
OR (cmr.directorynum = cdr.finalcalledpartynumber)
)
AND ( (cmr.callidentifier = cdr.origlegcallidentifier)
OR (cmr.callidentifier = cdr.destlegidentifier)
)
AND (cmr.globalcallid_clusterid = cdr.globalcallid_clusterid)
AND ( (cmr.jitter > 0)
OR (cmr.numberpacketslost > 0)
OR (cmr.latency > 0)
)
AND TO_DATE(datetimestamp,'mm/dd/yyyy hh:mi:ss am')
BETWEEN to_date('11/23/2006 5:45:00 PM','MM/DD/YYYY HH:MI:SS AM')
AND to_date('11/23/2006 5:50:00 PM','MM/DD/YYYY HH:MI:SS AM')
Re: selecting data from remote database[sql server] [message #205777 is a reply to message #205771] Mon, 27 November 2006 09:02 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
What is the data type of datetimestamp ? Since you are wrapping to_date around it, I assume it is a VARCHAR2. If so, there could be an invalid value in the field. I suggest you write a small PL/SQL procedure to validate the field. Try something like:

set serveroutput on 
declare
  v_date  date;
begin
   for record in (select <key fields>, datetimestamp from table) loop
     begin 
       v_date := to_date(record.datetimestamp,'dd/mm/yyyy hh:mi:ss am');
     exception when others then
       dbms_output.put_line('Error on '||record.<key fields>);
     end;
   end loop;
end;


Replace <key fields> with whatever is the primary key on the table to which datetimestamp belongs.

[Updated on: Mon, 27 November 2006 09:03]

Report message to a moderator

Re: selecting data from remote database[sql server] [message #205778 is a reply to message #205771] Mon, 27 November 2006 09:05 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Looking at it again, you seem to use two different formats for datetimestamp:


to_date(datetimestamp,'dd/mm/yyyy hh:mi:ss am'), numberpacketssent
..
AND TO_DATE(datetimestamp,'mm/dd/yyyy hh:mi:ss am') 


If it's a fixed character format, it can't be both.
Re: selecting data from remote database[sql server] [message #205779 is a reply to message #205778] Mon, 27 November 2006 09:13 Go to previous messageGo to next message
raghavs_dvg
Messages: 8
Registered: November 2006
Location: mumbai
Junior Member
hi all,

datetimestamp is date type

in sql-server the datetimestamp field is in this format 'mm/dd/yyyy hh:mi:ss am'
but i wanted to store dd/mm/yyyy hh:mi:ss format in oracle.
Re: selecting data from remote database[sql server] [message #205780 is a reply to message #205771] Mon, 27 November 2006 09:17 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
So are the calldetailrecorddiagnostic and calldetailrecord table on an Oracle database or a SQL*Server database ? Even on SQL*Server, I would not expect you to apply a TO_DATE function to a column which is already a DATE. The purpose of TO_DATE is to convert a CHARACTER string to a DATE, not to change a DATE into a different format. You would use TO_CHAR for this.
Re: selecting data from remote database[sql server] [message #205787 is a reply to message #205780] Mon, 27 November 2006 09:52 Go to previous messageGo to next message
raghavs_dvg
Messages: 8
Registered: November 2006
Location: mumbai
Junior Member
i have used to_char .... instead of to_date

Now it's giving any error,but it fails to insert a records into hs_mmv_cmcdr (says 0 records inserted) table even if records are present.

again if i fire only select stmt it gives the o/p....

insert INTO hs_mmv_cmcdr
SELECT cdrrecordtype, cmr.globalcallid_callmanagerid,
cmr.globalcallid_callid, nodeid, cdr.directorynum,
cdr.callidentifier,datetimestamp, numberpacketssent,
numberoctetssent, numberpacketsreceived, numberoctetsreceived,
numberpacketslost, jitter, latency, pkid, directorynumpartition,
devicename, cmr.globalcallid_clusterid, cdrrecordtype_b,
globallcallid_callmanagerid_b, globalcallid_callid_b,
cdr.origlegcallidentifier, datetimeorigination, orignodeid,
origspan, origipaddr, origipport, cdr.callingpartynumber,
origcause_location, origcause_value, origmediatransportaddress_ip,
origmediatransportaddress_port, origmediacap_payloadcapability,
origmediacap_maxframesperpkt, origmediacap_g723bitrate,
destlegidentifier, destnodeid, destspan, destipaddr, destipport,
originalcalledpartynumber, cdr.finalcalledpartynumber,
destcause_location, destcause_value, destmediatransportaddress_ip,
destmediatransportaddress_port, destmediacap_payloadcapability,
destmediacap_maxframesperpkt, destmediacap_g723bitrate,
datetimeconnect, datetimedisconnect, lastredirectdn, pkid_b,
origcalledpartynumpartition, callingpartynumberpartition,
finalcalledpartynumpartition, lastredirectdnpartition, DURATION,
origdevicename, destdevicename, origcalledpartyredirectreason,
lastredirectredirectreason, destconversationid,
origcallterminationonbehalfof, destcallterminationonbehalfof,
origcalledpartyrdtonbehalfof, lastredirectredirectonbehalfof,
globalcallid_clusterid_b, joinonbehalfof
FROM calldetailrecorddiagnostic@raghu cmr, calldetailrecord@raghu cdr
WHERE (cmr.globalcallid_callmanagerid = cdr.globalcallid_callmanagerid)
AND (cmr.globalcallid_callid = cdr.globalcallid_callid)
AND ( (cmr.directorynum = cdr.callingpartynumber)
OR (cmr.directorynum = cdr.finalcalledpartynumber)
)
AND ( (cmr.callidentifier = cdr.origlegcallidentifier)
OR (cmr.callidentifier = cdr.destlegidentifier)
)
AND (cmr.globalcallid_clusterid = cdr.globalcallid_clusterid)
AND ( (cmr.jitter > 0)
OR (cmr.numberpacketslost > 0)
OR (cmr.latency > 0)
)
AND to_char(datetimestamp,'dd/mm/yyyy hh24:mi:ss') between ('23/11/2006 17:45 :00')
and ('23/11/2006 17:50:36')
Re: selecting data from remote database[sql server] [message #205789 is a reply to message #205771] Mon, 27 November 2006 10:03 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
If you run the query without the insert statement, does it return any rows ? If it doesn't, there is a mistake in your query somewhere. Bear in mind that in the section:

AND to_char(datetimestamp,'dd/mm/yyyy hh24:mi:ss') between ('23/11/2006 17:45 :00')
and ('23/11/2006 17:50:36')

you are now comparing character strings rather than dates. This will probably work for records which are constrained with the narrow range of 17:45 to 17:50 on 23/11/2006. However, if you expand the date range and, for example, do:

AND to_char(datetimestamp,'dd/mm/yyyy hh24:mi:ss') between ('23/10/2006 17:45 :00')
and ('23/11/2006 17:50:36')

this will not work for something like '11/11/2006'.

Note also that you seem to have a space in "17:45 :00". This could make a difference to the comparison semantics.

I would question whether you need the to_char on this part of the query at all. You could probably just do:

AND datetimestamp between to_date('23/11/2006 17:45:00', ,'dd/mm/yyyy hh24:mi:ss')
and to_date('23/11/2006 17:50:36', ,'dd/mm/yyyy hh24:mi:ss')




[Updated on: Mon, 27 November 2006 10:04]

Report message to a moderator

Re: selecting data from remote database[sql server] [message #205809 is a reply to message #205789] Mon, 27 November 2006 13:05 Go to previous messageGo to next message
raghavs_dvg
Messages: 8
Registered: November 2006
Location: mumbai
Junior Member
If i do only select and without to_date for datetimestamp field it returns 0 rows.
i.e
AND datetimestamp between
to_date('23/11/2006 17:45:00','dd/mm/yyyy hh24:mi:ss')
and to_date('23/11/2006 17:50:00','dd/mm/yyyy hh24:mi:ss')

if i do

alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';


AND to_date(datetimestamp,'dd/mm/yyyy hh24:mi:ss') between
to_date('23/11/2006 17:45:00','dd/mm/yyyy hh24:mi:ss')
and to_date('23/11/2006 17:50:00','dd/mm/yyyy hh24:mi:ss')

then it return some records....

if i execute the above query with insert stmt then it will throw the following error message:
ORA-01847: day of the month mustbe between 1 and last day of month ORA-02063 preceding line from <db_link_name>

plz help me out....




Re: selecting data from remote database[sql server] [message #205947 is a reply to message #205771] Tue, 28 November 2006 03:49 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I don't believe your DATETIMESTAMP column is a DATE. It must be a VARCHAR2. If it was a DATE, the code I supplied would work correctly, as the following shows.

SQL> 
SQL> create table test_date (proper_date date, string_date varchar2(30));

Table created.

SQL> 
SQL> insert into test_date values (to_date('23/11/2006 17:45:05','dd/mm/yyyy hh24:mi:ss'), '23/11/2006 17:45:05');

1 row created.

SQL> 
SQL> select * from test_date
  2  where proper_date between
  3  to_date('23/11/2006 17:45:00','dd/mm/yyyy hh24:mi:ss')
  4  and to_date('23/11/2006 17:50:00','dd/mm/yyyy hh24:mi:ss');

PROPER_DA STRING_DATE
--------- ------------------------------
23-NOV-06 23/11/2006 17:45:05

SQL> 
SQL> select * from test_date
  2  where string_date between
  3  to_date('23/11/2006 17:45:00','dd/mm/yyyy hh24:mi:ss')
  4  and to_date('23/11/2006 17:50:00','dd/mm/yyyy hh24:mi:ss');
where string_date between
      *
ERROR at line 2:
ORA-01843: not a valid month


Working on that assumption, can you tell me:

Does the following query work ?

SELECT to_date(datetimestamp,'dd/mm/yyyy hh:mi:ss am')
FROM calldetailrecorddiagnostic@raghu cmr, calldetailrecord@raghu cdr
WHERE (cmr.globalcallid_callmanagerid = cdr.globalcallid_callmanagerid)
AND (cmr.globalcallid_callid = cdr.globalcallid_callid)
AND ( (cmr.directorynum = cdr.callingpartynumber)
OR (cmr.directorynum = cdr.finalcalledpartynumber)
)
AND ( (cmr.callidentifier = cdr.origlegcallidentifier)
OR (cmr.callidentifier = cdr.destlegidentifier)
)
AND (cmr.globalcallid_clusterid = cdr.globalcallid_clusterid)
AND ( (cmr.jitter > 0)
OR (cmr.numberpacketslost > 0)
OR (cmr.latency > 0)
)
AND TO_DATE(datetimestamp,'mm/dd/yyyy hh:mi:ss am') 
BETWEEN to_date('11/23/2006 5:45:00 PM','MM/DD/YYYY HH:MI:SS AM') 
AND to_date('11/23/2006 5:50:00 PM','MM/DD/YYYY HH:MI:SS AM')


Run this in SQL*Plus and let it run for all records. It may work for the first few records, if you are lucky and you don't have any dates where the day part exceeds 12.

I'm assuming BTW that these are Oracle tables. It certainly looks like Oracle syntax rather than SQL*Server.
Re: selecting data from remote database[sql server] [message #205962 is a reply to message #205947] Tue, 28 November 2006 04:11 Go to previous message
raghavs_dvg
Messages: 8
Registered: November 2006
Location: mumbai
Junior Member
believe me...
datetimestamp is datetime type present in calldetailrecorddiagnostic table (which is presenr sql-server database)
i'm connecting to sql-ver from oracle with the help of db-link(i.e using oracle Heterogonous services)
Previous Topic: A query required
Next Topic: About Error
Goto Forum:
  


Current Time: Sat Dec 10 03:34:03 CST 2016

Total time taken to generate the page: 0.10317 seconds