topic Re: Libname Creation for an Oracle DB in SAS Procedures

Libname Creation for an Oracle DB

Scottcom4 — Wed, 17 Feb 2010 01:51:19 GMT

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;

Re: Libname Creation for an Oracle DB

Scottcom4 — Wed, 17 Feb 2010 02:48:06 GMT

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?