Desktop productivity for business analysts and programmers

Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

Reply
Frequent Contributor
Posts: 148

Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

Hi friends,

I have an issue when am trying to access Oracle database from SAS.

I created a ODBC Connection to connect to Oracle DB and added the connection entries to tnsnames.ora on sSolarisserver.

and tested the connection with the libname statement on server and on EG and it has successfully assigned.

but While registering the tables in SAS management console(after creating the library).I am getting the following error:

  "No tables were retrieved from this query.Your connection information were incorrect"

When I opened the SAS log,

NOTE: SAS Initialization used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

     

NOTE: The autoexec file, /sas/biconfig/Lev1/SASMain/WorkspaceServer/autoexec.sas, was executed at server initialization. No server

      log was specified.  Add the log option to the server startup command to see details of the autoexec execution (refer to

      "Specifying Logging Options" under "Server Startup Command" in the Server Administrator's Guide).

1          LIBNAME GDIW ORACLE  PRESERVE_COL_NAMES=YES  PRESERVE_TAB_NAMES=YES  MULTI_DATASRC_OPT=IN_CLAUSE  DBINDEX=YES

1        ! PATH=pde_pdiw  SCHEMA=ph  USER=pdrew  PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;

NOTE: Libref PDIW was successfully assigned as follows:

      Engine:        ORACLE

      Physical Name: pde_pdiw

Library is successfully assigned,am not sure why am getting this error.

I have license for SAS/ACCESS Interface to ORACLE and SAS/ACCESS Interface to ODBC and able to resgister tables

from SAS Management Console for all other Oracle librraies

Please help me with this,I would really appreciate all your help

Thanks in advance.

Respected Advisor
Posts: 3,836

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

Defining libraries in the autoexec is the old pre-SAS9 way of doing things. With SAS Metadata you need also to define a database server. " /sas/biconfig/Lev1/SASMain" indicates that you're still using SAS9.1.3 but assuming that you've just maintained old path names here the link to the relevant SAS9.4 docu (read page 114 - 118.): http://support.sas.com/documentation/cdl/en/bidsag/68193/PDF/default/bidsag.pdf

Respected Advisor
Posts: 3,063

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

Your LIBNAME statement says you are using the ORACLE engine. Yet you say you are using ODBC connections in SMC. If you are using only ODBC in SMC, the LIBNAME statement in your post has nothing to do with what you are defining in SMC, and as Patrick says is being assigned via a SAS AUTOEXEC file.

Valued Guide
Posts: 3,206

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

Defining libraries can be done in many ways. See: http://blogs.sas.com/content/sgf/2013/05/15/pre-assign-sas-libraries-if-so-which-method/

Some products and the work of the users like data mining make it very impractical data access to be managed by a godfather DBA and not aligned IT staff.

You library is correctly assigned in the autoexec with access to all users open.  Needing a secured access having sensitive data to Oracle you would need to better isolated credentials access leaving it all to Oracle.

When you want to register those tables you are doing that on behalf of an DBA admin role within the SAS environment. That process is not necessary using the same APPserver where the users are running.  To overcome that someone at SAS has chosen the way SMC works is to assign the libname first with all definitions that should exist in SAS metadata and use that for importing the metadata. What is going along with this is that an open shared account to access Oracle must exist in the SAS meta data. They failed in checking this with common security guidelines using sensitive data.

You can try the metallib procedure leaving the data management process to the data-owner http://support.sas.com/documentation/onlinedoc/guide/EG43MetaLibraries.pdf 

---->-- ja karman --<-----
Frequent Contributor
Posts: 148

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

Thank you all for your prompt responses

am using SAS 9.2 on Solaris 10 server

and I did not setup anything new for ODBC connection I have just added connection entries

in tnsnames.ora file for new data base requirement on Solaris machine.

and I followed same steps I did for other oracle libraries before and am able to register tables for other oracle libraries but not for this library.

so what steps I need to follow to overcome this issue now

please help

Valued Guide
Posts: 3,206

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

Your autoexec connection is working. When users are using a libname in their sas-code that will work also.

What doesn't work is that automatic implied library connection used by the SMC importing the data. It could be an external identified=yes and oracle type def is missing. There must be some logging in a appserver somewhere. Can you find that one?

But making is more easy why not using the proc metalib eguide code?  Avoiding SMC is avoiding a lot of the mentioned trouble.

---->-- ja karman --<-----
Frequent Contributor
Posts: 148

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

SO if i run below code from EG then all the tables will be registered from source.

please let me know if there is anything wrong in the code.

proc metalib;

omr (library="My oracle library name");

run;

Valued Guide
Posts: 3,206

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

SAS(R) 9.2 Language Interfaces to Metadata   See also the restrictions (mentioned before).

---->-- ja karman --<-----
Frequent Contributor
Posts: 148

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

I had ran the below code from EG to register the tables in Oracle library but still I don't see any tables even though it has executed without any errors.

proc metalib;

omr (library="ORACLE CN_IW");

run;

This is the log file I got in the EG and am not sure where its still going wrong

1          ;*';*";*/;quit;run;

2          OPTIONS PAGENO=MIN;

3          %LET _CLIENTTASKLABEL='Program';

4          %LET _CLIENTPROJECTPATH='';

5          %LET _CLIENTPROJECTNAME='';

6          %LET _SASPROGRAMFILE=;

7         

8          ODS _ALL_ CLOSE;

9          OPTIONS DEV=ACTIVEX;

NOTE: Procedures may not support all options or statements for all devices. For details, see the documentation for each procedure.

10         GOPTIONS XPIXELS=0 YPIXELS=0;

11         FILENAME EGSR TEMP;

12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue

12       ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome9.3/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css") NOGTITLE NOGFOOTNOTE

12       ! GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

13        

14         GOPTIONS ACCESSIBLE;

15         proc metalib;

16         omr(libraRy="ORACLE CN_IW");

17         RUN;

NOTE: A total of 0 tables were analyzed for library "ORACLE CN_IW".

NOTE: Metadata for 0 tables was updated.

NOTE: Metadata for 0 tables was added.

NOTE: Metadata for 0 tables matched the data sources.

NOTE: 0 other tables were not processed due to error or UPDATE_RULE.

NOTE: PROCEDURE METALIB used (Total process time):

      real time           0.34 seconds

      cpu time            0.03 seconds

     

18        

19        

20         GOPTIONS NOACCESSIBLE;

21         %LET _CLIENTTASKLABEL=;

22         %LET _CLIENTPROJECTPATH=;

23         %LET _CLIENTPROJECTNAME=;

24         %LET _SASPROGRAMFILE=;

25        

26         ;*';*";*/;quit;run;

27         ODS _ALL_ CLOSE;

28        

29        

30         QUIT; RUN;

31        

Respected Advisor
Posts: 3,063

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

In my experience it is quite possible to assign without error a LIBNAME to an external database that doesn't show any tables. It is usually the result of the SCHEMA / DATABASE names not being correct. I note you are using Unix so your schema/database names may be case-sensitive.

I suggest you try creating a LIBNAME statement in EG which matches the one you are trying to create in SMC, and see if any tables are visible in the EG server list. This is how I normally troubleshoot database connections.

Frequent Contributor
Posts: 148

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

I copied the libname statement of the library from SMC and ran it in EG and its assigning successfully without errors but tables are not registering.

Respected Advisor
Posts: 3,063

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

Please post your LIBNAME statement.

Frequent Contributor
Posts: 148

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

The schema was in lower case "ph" and I updated the schema for Oracle Library to upper case "PH" and am able to register the tables

but when am trying to open the registered data sets its throwing below error.

SAS Enterprise Guide cannot open the date file: "ORACLE CN_IW.PC_ADD_DAILY_DLOC

Error opening data "PC_ADD_DAILY_DLOC".

[Error] File CN_IW.PC_ADD_DAILY_DLOC does not exist.

[Error] The datasource table CN_IW.PC_ADD_DAILY_DLOC does not exist.

Libname statement is below I got from SMC for the Oracle Library

LIBNAME GDIW ORACLE  PRESERVE_COL_NAMES=YES  PRESERVE_TAB_NAMES=YES  DBINDEX=YES  PATH=pde_pdiw  SCHEMA=PH  USER=pdrew  PASSWORD="{SAS002}9C943B50407B77954D67D4302E92999C" ;

Respected Advisor
Posts: 3,063

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

Are you able to open any tables in CN_IW or do they all give you the same error as PC_ADD_DAILY_DLOC?

If you run your GDIW LIBNAME in EG then Expand GDIW in the server list does it show PC_ADD_DAILY_DLOC? If so what happens if you try to open this table? Do you get the same error as when opening via CN_IW?

Frequent Contributor
Posts: 148

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

When I changed Multi data source optimization to IN_CLAUSE from None then am able to register tables and able to open tables without any issues from EG.

I did as per below steps

For the Oracle Library under properties-->options-->Advanced options-->Optimization--->I selected to IN_CLAUSE from None for Multi data source optimization

then am able to open the data sets from EG registering.

I think it might have solved

but i have one question when I got oracle connection information i have been given lower case schema "ph" not the upper case.

but when am entering "ph" schema its not working, its working only when am entering it in upper case.

so am not sure whether the user had given wrong information or oracle library wont accept schema in lower case

Ask a Question
Discussion stats
  • 15 replies
  • 981 views
  • 0 likes
  • 4 in conversation