1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Unix Server Time Query

Discussion in 'SQL PL/SQL' started by kruitzpola, Jun 23, 2010.

  1. kruitzpola

    kruitzpola Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    Hi All,

    I am a C++ developer and have basic knowledge of oracle & sql. My job profile involves fixing of bugs.

    While fixing one of such bugs i found a query to get the unix server time...
    "SELECT SYSDATE - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS') * 24 * 60 * 60 as currenttime FROM DUAL"...

    And while fixing the same bug on a WINDOWS background program involved in our project, i need the same result..i.e. unix server time to be inserted instead of the webserver time....

    However it doesnt seem to work in this case. i think the problem is with the "dual"... for some reason it isn't executing the queries which fetch from dual..(i know that dual is a dummy table)...

    any other alternative or any other query.......??? plz help.... thanks in advance..
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The query should work regardless of the platform on which Oracle is installed. Prove, please, that queries usimg dual do not work on this Windows server.
     
  3. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    That query should work. There must be some other problem. What error are you getting?
     
  4. kruitzpola

    kruitzpola Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    I know it doesnt sound logical, but i also tried other queries with "dual".. like
    "select current_date from dual";
    "select sysdate from dual";
    "SELECT SUBSTR(systimestamp,30,6) as timezone FROM dual";.. but it isnt working...

    but when i replace the above queries with other queries such as :
    "sprintf(unixSQL, "SELECT F_DOCNUMBER FROM FnDocument WHERE (CHARTDOCID = %s) AND (F_DOCCLASSNAME = '%s')", m_acCurrChartDocID, m_UserParams.acFileNetMedicalRecordsDocumentClass);".. it works fine...

    My oracle version is 'Oracle Data Provider for .NET 10.2.0.1.0'... i'm not sure thats exactly the version, but thats what i found...

    I get the following exception when the 'execute' function is called with the above query:
    'First-chance exception in RLRecommit.exe (KERNEL32.DLL): 0xE06D7363: Microsoft C++ Exception.'...... Please let me know if you need any other info....
     
  5. kruitzpola

    kruitzpola Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    Could I write a stored procedure to overwrite the time value in a particular table as soon as "a particular entry is inserted" in that table..... ???

    The problem is that i want to replace the windows system time with the unix server time in the queue entry being inserted.

    Any help or advice is greatly appreciated... Thanks
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You write a trigger to replace the submitted time with the UNIX equivalent.