Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2005

    Unanswered: Help with PL/SQL datatransfer from Oracle to SQL server

    I have been trying this for a few days still could not make it work. I am new here and please help! I am not sure which problems I have, on Oracle PL/SQL side or SQL 2000 DTS side. Any suggestions/comments are highly appreciated!

    I created a simple DTS package in Enterprise Mgr to select a data set from Oracle server and transfer them to SQL 2000 server. Here is the select query for data transfer.
    select * from ord_fx where trade_date='7-Oct-2005'
    It works fine and transfer the correct data set.

    Now I want to have updated data transfered on daily basis and revise the query:
    declare CurrDate date :=round(sysdate);
    select * from ord_FX
    where status_Flag is null
    and to_date(Entry_date, 'DD-MM-YYYY'>='CurrDate';
    However, I got the following error message when I click on Preview... button.
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2004
    Try running that in sql*plus and you'll get the same error. You first of all need a begin statement. Secondly, if that query pulls back more than one row, you need to make that a cursor - if not, you need to select into variable(s).

    Use it, love it.
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Aug 2005
    Thank you for your reply. Just want to be clear I am just trying to use select statement in SQL 2000 Server DTS package query window to get sub set of data from a table of Oracle server.
    I am doing the same thing as first select statement. The only difference between first and second codes is I want to date as a variable so that I do not have to "hard coded" date everyday for current date. First query works fine and I did not do anything like linked server and etc.
    Please help! I am confused.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts