Libname Creation for an Oracle DB

[Scottcom4]

Hi Guys,

At present we have SQL set up which allows us to access an Oracle DB via OLEDB (my understanding, but the code is attached to better explain). The thing is that I would like to establish a libname for this connect, so that I can see the tables contained within.

I have code to do this for a genuine Oracle connection, but can't get it to work in this instance.

Would anyone have any ideas on this?

Thank you in advance.

proc sql;
connect to oledb as oradb (User = XXXXXXXX Password = XXXXXX DataSource = XXXXXXXXXXXXX provider = MSDAORA.1);;
create table list_tab as
select * from connection to oradb
(select OWNER, TABLE_NAME from ALL_TABLES);
quit;

[Scottcom4]

I have been looking on the internet for hours and didn't find anything about this issue, however 5 minutes after I posted this problem I located a SUGI containing the information I was after.

However, only 90% of the tables contain data. The 10% results in the folowing error message

[ERROR] Prepare error: ICommand Prepare::Prepare Failed. SQL Statement SELECT * From CBMRV_CLAIM.

I get a similar issue when attemtion to use SQL pass through using the SELECT *, however once (a very manual method of trawling through and adding one variable at a time) I remove the offending variable it works perfectly.

Does anyone have any ideas?