Discussion:
Oracle query in Data Flow task
(too old to reply)
Usha
2009-03-16 23:37:04 UTC
Permalink
I am using Data Flow task for the below query.

I have a source connection as MyOrclConn which uses Oracle Provider for OLEDB.
I am trying to write a query which selects the records from Oracle based on
the RunDate that is in Audit_Date in SQL Server Db (MySqlSvrConn).

My purpose here is to get the new records since last run from the source db.
So I want to write something like this.

SELECT * FROM OrclDB.MyOrclTbl
Where MyOrclTbl.DateModified > (Select RunDate From SqlSvrDb.dbo.Audit_Date)

My understanding is since I have selected Oracle connection it can
understand only Oracle syntax and objects. So it is unable to get the value
from Audit_Date.
How can I do this? Do I need to use Parameter? Or something else? How do I
do this?

Thanks!
MC
2009-03-18 06:27:46 UTC
Permalink
I would use variable so it filters based on that value. So, first populate
the variable value from SQL server, then use that variable to build oracle
statement

MC
Post by Usha
I am using Data Flow task for the below query.
I have a source connection as MyOrclConn which uses Oracle Provider for OLEDB.
I am trying to write a query which selects the records from Oracle based on
the RunDate that is in Audit_Date in SQL Server Db (MySqlSvrConn).
My purpose here is to get the new records since last run from the source db.
So I want to write something like this.
SELECT * FROM OrclDB.MyOrclTbl
Where MyOrclTbl.DateModified > (Select RunDate From
SqlSvrDb.dbo.Audit_Date)
My understanding is since I have selected Oracle connection it can
understand only Oracle syntax and objects. So it is unable to get the value
from Audit_Date.
How can I do this? Do I need to use Parameter? Or something else? How do I
do this?
Thanks!
Usha
2009-03-18 18:13:33 UTC
Permalink
I am trying to build the below sql expression to store it in a variable but
it is giving me cast error for the operands.

"select * from OrclTbl where load_date <"
+(DT_DBTIMESTAMP)@[User::varFromDate]

I even tried the below still it gives me the same error
"select * from OrclTbl where load_date <"
+ getdate()
Post by MC
I would use variable so it filters based on that value. So, first populate
the variable value from SQL server, then use that variable to build oracle
statement
MC
Post by Usha
I am using Data Flow task for the below query.
I have a source connection as MyOrclConn which uses Oracle Provider for OLEDB.
I am trying to write a query which selects the records from Oracle based on
the RunDate that is in Audit_Date in SQL Server Db (MySqlSvrConn).
My purpose here is to get the new records since last run from the source db.
So I want to write something like this.
SELECT * FROM OrclDB.MyOrclTbl
Where MyOrclTbl.DateModified > (Select RunDate From
SqlSvrDb.dbo.Audit_Date)
My understanding is since I have selected Oracle connection it can
understand only Oracle syntax and objects. So it is unable to get the value
from Audit_Date.
How can I do this? Do I need to use Parameter? Or something else? How do I
do this?
Thanks!
MR2Turbo
2009-03-20 16:02:21 UTC
Permalink
Oracle is somewhat demanding when it comes to dates. Have you tried:
'select * from OrclTbl where load_date < to_date(''' +
Post by Usha
I am trying to build the below sql expression to store it in a variable but
it is giving me cast error for the operands.
"select * from OrclTbl where load_date <"
I even tried the below still it gives me the same error
"select * from OrclTbl where load_date <"
+ getdate()
Post by MC
I would use variable so it filters based on that value. So, first populate
the variable value from SQL server, then use that variable to build oracle
statement
MC
Post by Usha
I am using Data Flow task for the below query.
I have a source connection as MyOrclConn which uses Oracle Provider for OLEDB.
I am trying to write a query which selects the records from Oracle
based
on
the RunDate that is in Audit_Date in SQL Server Db (MySqlSvrConn).
My purpose here is to get the new records since last run from the
source
db.
So I want to write something like this.
SELECT * FROM OrclDB.MyOrclTbl
Where MyOrclTbl.DateModified > (Select RunDate From
SqlSvrDb.dbo.Audit_Date)
My understanding is since I have selected Oracle connection it can
understand only Oracle syntax and objects. So it is unable to get the value
from Audit_Date.
How can I do this? Do I need to use Parameter? Or something else? How do I
do this?
Thanks!
MC
2009-03-21 12:19:49 UTC
Permalink
Anyway, first write a query that works on Oracle side. Then you go about
replacing hardcoded value with varibale.

MC
Post by MR2Turbo
'select * from OrclTbl where load_date < to_date(''' +
Post by Usha
I am trying to build the below sql expression to store it in a variable but
it is giving me cast error for the operands.
"select * from OrclTbl where load_date <"
I even tried the below still it gives me the same error
"select * from OrclTbl where load_date <"
+ getdate()
Post by MC
I would use variable so it filters based on that value. So, first populate
the variable value from SQL server, then use that variable to build oracle
statement
MC
Post by Usha
I am using Data Flow task for the below query.
I have a source connection as MyOrclConn which uses Oracle Provider
for
OLEDB.
I am trying to write a query which selects the records from Oracle
based
on
the RunDate that is in Audit_Date in SQL Server Db (MySqlSvrConn).
My purpose here is to get the new records since last run from the
source
db.
So I want to write something like this.
SELECT * FROM OrclDB.MyOrclTbl
Where MyOrclTbl.DateModified > (Select RunDate From
SqlSvrDb.dbo.Audit_Date)
My understanding is since I have selected Oracle connection it can
understand only Oracle syntax and objects. So it is unable to get the value
from Audit_Date.
How can I do this? Do I need to use Parameter? Or something else? How do I
do this?
Thanks!
Loading...