Problem getting data from Oracle Package to SQL Server or CSV file

Question

hi there,

i have an Oracle package and there are certain functions embedded into it. i'm interested in one of it's function's data to be dumped into a SQL Server table (or even a *.csv file for that matter).

i invoke/get the data of this function using something like..

select user_name.package_name.function_name(<input_parameter_date>) from dual;
this query when executed in SQL Developer fetches the result set. i need to get this data to a SQL Server table / csv file.

some prelim info before this...i have read-only access on Oracle instance..so i can't create any
temp / physical table to get the above output and then use it in SQL Server...

things i've tried..

1. when i use SQL Server's import/export utility, it just shows me the Oracle db's tables and nothing else. i can't see any packages and furthermore, i can't use oracle query in this sql server utility for obvious reasons of incompatibility.

2. i thought of exporting this result set to *.csv or *.xlsx file. but the result set fetched in the output window doesn't appear in a grid format and so the export (by right clicking resultset in o/p window and selecting 'export') option doesn't pop up.

3. To get the grid output, i hit ctrl+enter. but this grid output now displays only a single row (i.e. all 1000+ recs clubbed into a single row) and under a single column. that column name appears as
"user_name.package_name.function_name(<input_parameter_date>)", i.e. it treats the function call in the select clause as a column name. and i repeat, the output is only a single row (all the data clubbed into one single row and above column). furthermore, even if i think of exporting this needless output to csv file (since now the o/p is into grid), the next dialog box after export, has the connection drop down disabled...and my current connection doesn't seem to be selected into the drop down. this is quite weird.

4. i tried to output the query results to a csv using below statements, but it only creates a lank/empty myfile.csv; and furthermore, in the SQL developer's output window, i get to see that the it skips set statements.

set recsep off
set echo
off;
set feedback off;
set colsep ';'
spool myfile.csv;
    select user_name.package_name.function_name(<input_parameter_date>) from dual;
spool off;

line 1: SQLPLUS Command Skipped: set recsep off
line 4: SQLPLUS Command Skipped: set colsep ';'

5. i saved the sql developer's output to a text file. but that's not what i expect. the text file data again doesn't appear in required format..

so the long story short...the much talked query in my description above returns some 1000 recs, lets say..so, i need to get all of these in a sql server table or a csv, anything..but despite trying all the above things, i couldn't achieve it....do i need to write any custom .net script in SSIS to connect to Oracle package's function and fetch this data? any pointers please....many thanks!

Answer 1

Have you tried using SSIS and one of the Oracle data source providers?  This should allow you to run the oracle package functions.

Answer 2

Input parameters with Oracle procedures/packages are problematic in SSIS even using the Oracle client.  Which tasks are you using in SSIS to run these packages? and which drivers are you using?

---

Regards, Steve @dataonwheels http://www.dataonwheels.com

Answer 3

Hi,

if you really need to call native oracle packages then I would try the EXECUTE 'Oracle SQL' AT LinkedServer syntax

http://msdn.microsoft.com/en-us/library/ms188332.aspx

best regards