parnassusinvest
asked on
Using Oracle 11g functions in Microsoft Query Wizard in Excel 2010
I'm trying to use the Oracle pipelined table() function in the microsoft query wizard but I'm getting the error, "Could not add the table..."
My query looks like this inside the Sql Statement prompt in the Microsoft query wizard:
Select * from table(MyData.SomeTable_Test('03 /28/2013') );
Does the Oracle table function work in microsoft query wizard? Some other Oracle functions like to_date works.
Thanks, PI
My query looks like this inside the Sql Statement prompt in the Microsoft query wizard:
Select * from table(MyData.SomeTable_Test('03
Does the Oracle table function work in microsoft query wizard? Some other Oracle functions like to_date works.
Thanks, PI
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>table functions require "strongly typed" parameters
try this:
try this:
select * from table(MyData.SomeTable_Test( (select to_date('03/28/2013','mm/dd/yyyy') from dual) ))
ASKER
It works but it's not perfect
I would like to remind you that MM/DD/YYYY is NOT a universal standard
(in fact it is relatively rare, mostly used only in North America, ~<10% of the world pop)
'2013-03-28' may be better
it's possible that simply changing to YYYY-MM-DD will help.
(please) drop any preconceived notions about the "format" of dates
As you probably know, even in excel, a number can be formatted to date. Databases have their own special requirements regarding dates too. This is especially true of table functions in Oracle which require "strongly typed" parameters in my experience.
best of luck with this, Cheers, Paul.
(in fact it is relatively rare, mostly used only in North America, ~<10% of the world pop)
'2013-03-28' may be better
it's possible that simply changing to YYYY-MM-DD will help.
(please) drop any preconceived notions about the "format" of dates
As you probably know, even in excel, a number can be formatted to date. Databases have their own special requirements regarding dates too. This is especially true of table functions in Oracle which require "strongly typed" parameters in my experience.
best of luck with this, Cheers, Paul.
Calling the table function most probably requires a schema owner prefix e.g.
select * from table(schemaowner.someTabl
so if 'MyData' in your example is the schema owner then that bit should be ok
Also table functions require "strongly typed" parameters, so if that '03/28/2013' is intended to be a date then make it into a date (as it stands it is a string of near meaningless 8 digits plus 2 separators)
select * from table(schemaowner.someTabl
might work
for more details on table functions
ps: <10% of the world uses mm/dd/yyyy as their typical date sequence, please don't assume software will immediately understand it.