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.
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).
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.