pass a variable to Oracle SQL query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

pass a variable to Oracle SQL query

Author

Topic

in2art
Starting Member

2 Posts

Posted - 2006-12-29 : 14:21:53

How do I pass a date variable to an Oracle SQL query in my SP?

General purpose of package is to import data (weekly) from several sources and combine to produce output files. One data source is an Oracle db (using ODBC connection). Other sources are .txt and .csv files; we are using an ActiveX script to set the Friday date (manually updated each week) and this date is used to select the correct input files (i.e. data_mmddyy.csv or data_yyyymmdd.txt). We manually update the “As Of Date” and “date adjustment” in each of the Oracle db queries.

We are concerned with 3 pieces of information; the data date (Friday), the load date on the Oracle db and the date adjustment.

Example:

Data date, load date, date adjustment, comment
9/29/06, 9/30/06, -1, month end example
12/22/06, 12/25/06, -3, holiday example
12/15/06, 12/17/06, -2 typical week example

I am looking for a better way to do this. For starters, I would like to either be able to pass variables for the “as of date” and “date adjustment” (eg: -2), so that I only had to manually update in one place, or pick it from a table. The above example (sans comment) could be the table used to select date. Or maybe just select the max as of date in the Oracle db query and adjust the date in SQL table?

current (simplified) query example

select
trade_ticket_id as CLIENT_ID,
/*
************** change date here (1 - 2 of 3)******************
*/
as_of_date-2 as POS_DATE,
as_of_date-2 as PXS_DATE,
today_close_mkt_price_pct as PRICE,
'EDFUT' as PXS_SOURCE,
book_cd,
product_group_cd,
deal_id
from dw.futures
/*
************** change date here (3 of 3)******************
*/
where as_of_date = to_date('12/10/2006','mm/dd/yyyy')
and trade_type_cd = 'FUT'
and TRADE_STATUS_CD in ('DONE','VER')
and contr_id = 'EUROUSD'
order by trade_ticket_id

All suggestions for improvements would be greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-29 : 14:33:53

You'd be better off if you posted your question on an Oracle forum, such as the one over at dbforums.com

This site is for SQL Server, so I'm not sure if your question will be answered here or not.

Tara Kizer

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-12-29 : 16:49:05

you posted this in the TSQL area, but you are talking about an activex script. Is this really a DTS question?

Anyway, google is going to be your friend here. Keep in mind that working with Oracle from SQL Server is sometimes not very straightforward. Especially if you are trying to execute oracle stored procedures from SQL Server.

Here is a good thread that may help you (if this is really a TSQL question) http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/2e09919496c37d4c/d35d52854200e301

-ec

EDIT:
fixed link

in2art
Starting Member

2 Posts

Posted - 2006-12-29 : 17:32:59

This is a SQL Server DTS package that does several things, one of which is get data from an Oracle db (ODBC Connection, query) and insert it into a table on the SQL server. It is there combined with data from other sources to create a set of output files. ActiveX is used to pass a variable to select the files for the correct date. I would like to do something similar in the Oracle SQL query. That is why I picked this forum, but I realize this is not "the norm".

I read the thread suggested by eyechart, it is in regards to getting a stored procedure to run at all (they were using a different method to connect). My query runs fine. I am able to get data from Oracle into SQL; but would like to eliminate is multiple manual changes to the package (update dates in the where clause of the query). I don't have any rights on the Oracle side, my query just reads, so must do whatever I need to within the DTS package.

What I hope to do is to get a variable into that query somehow; preferably from a table that exists in the SQL database, or from anything inside the DTS package (like the ActiveX task) where I would only have to update one place.

I hope that makes sense. I appreciate any input and may also try posting to the mentioned forum. Thanks!

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-12-30 : 02:16:18

I think you need to investigate using global variables and dynamic properties in your DTS packages. What you are attempting to do is somewhat common - running standard SELECT statement against Oracle is fairly easy. When you go the route of attempting to execute oracle stored procedures from DTS or TSQL things get hairy. Also, assisting with DTS questions is sometimes difficult in a forum like this because of the visual nature of the development environment.

Anyway, here are a couple of articles from sqldts.com to get you started:

global variables http://www.sqldts.com/default.aspx?205
dynamic properties http://www.sqldts.com/default.aspx?252

keep us posted on your progress.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources