help! - ERROR: CLI prepare error: Unable to retrieve error message

[LizzySAS]

Hi there,

I am trying to read in an Oracle Clinical view which contains extended text fields via SQL in SAS Enterprise Guide.

I am receiving the error below:

ERROR: CLI prepare error: Unable to retrieve error message.

SQL statement: select * from RXC.RESPONSE_LOBST.

ERROR: SQL View WORK.ALL_LOBS could not be processed because at least one of the data sets, or views, referenced directly (or

       indirectly) by it could not be located, or opened successfully.

I can open the same table with Crystal Reports, and have used this method of pulling views from Oracle Clinical millions of times before. I can only conclude it's because of the amount of data stored in the extended text field. Can anyone confirm this? And does anyone know a work around for this problem?

Thank you,

Rob

[TomKari]

The SAS/Access guide to Oracle states that all character data types can be read successfully. Also, your message points in a completely different direction.

My feeling is that either a table or view is missing, or you can't open it (possibly due to permissions somewhere?) I suggest you perhaps try to access the base tables used in the view, and see if one of them chokes?

Tom

[LizzySAS]

Hi Tom,

As described in my reply to Jaap, all other tables are accessible. I don't believe there could be any view specific restrictions in place on the servers.

Regarding: "I suggest you perhaps try to access the base tables used in the view, and see if one of them chokes?" I opened the base table containing the OC Extended text in Crystal Report again without issue. I cannot open this base table in SAS. I experience am given the following error pop-up in SAS Enterprise Guide when I attempt to open the table (note, this is different from the error described in my initial post, which appears in the log):

"The open data operation failed. The following error occurred. [Error] SQL View Work.OC_LOBS could not be processed because at least one of the data sets, or views, referenced directly (or indirectly) by it could not be located, or opened successfully."

[jakarman]

Use sas code Sql statements and the sastrace option so you can see what Sql code is done.
You are saying using Eguide. Is it:

a/ sas code

b/ genereate code somewhere in a taks

/c query builder  ? 

[LizzySAS]

Hi Jaap,

A) SAS Code.

Using the following code, the bolded syntax results in a table which cannot be viewed. The other views are created successfully without issue. OC table name has been verified.

proc sql;

  connect to ODBC(dsn="SASCLIN" uid=&ocuid pwd=&ocpw);

  create view OC_STUDY_AE as

  select * from connection to ODBC(select * from STUDY$TEST.AE);

  create view OC_RECEIVED_DCMS as

  select * from connection to ODBC(select * from RXC.RECEIVED_DCMST);

  create view OC_LOBS as

  select * from connection to ODBC(select * from RXC.RESPONSE_LOBST);

run;

quit;

[Kurt_Bremser]

Crystal Reports is a SAP software, I have no idea how it accesses the database (directly or through the Oracle client).

But I know that SAS uses a local Oracle client to access the Oracle DB. Have you tried to read the data using the Oracle Client? I suspect a misconfiguration there.

[LizzySAS]

Hi Kurt,

My apologies but your reply is a little above my technical understanding of the backend regarding OC/SAS engines.

Could you rephrase the following? "Have you tried to read the data using the Oracle Client?"

[Scott_Mitchell]

Hi Lizzy,

Are you using the ORACLE Client or OLEDB Client to connect to your ORACLE DB?

I worked for a company that used OLEDB to do this and we had a couple of issues, one generated an error very similar to the one you are experiencing.  It appeared to occur only with tables that had dates contained within.  If you know the table structure, try extracting a single field that doesn't contain a date and see if you get the same error, if not try a date field.  In the end we used TO_DATE(TO_CHAR(TRANS_POSTED_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') to extract the relevant dates.

I hope this is of some use.

Regards,

Scott

[LizzySAS]

Hi Scott, almost every table from OC contains dates. As mentioned in my reply to Jaap, I've been able to pull in other tables and create SAS datasets without issue. There's something about this particular table, which is causing a problem.

Going to continue to verify things on my end. Thank you all for your responses thus far. Much appreciated.

[jakarman]

Lizzy, that is a lot of information passing by. As a pitty al lot is very technical, but I/we will try.

I am seeing an ODBC connection being used with a DSN= and uid/psw. I am concluding you are using EGuide wit a local connection running on your Windows desktop.
You are creating views with proc sql.  That will result in SAS views stored in SAS work. This makes sense as your error message is about the view WORK.OC_LOBS and that one is something you can click on in SAS.

For Kurt's question for information: with Oracle it is normally better to use the Oracle client. It is offering a better interface to Oracle. The disadvantage is you are needing the SAS/ACCESS to Oracle being licensed installed and configured. The ODBC and Oracle have a lot of different topics been described. It is almost DBA work. As you are getting data from Oracle using ODBC in a SAS-view let us proceed on that.

- SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (Oracle specifics)

- SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (ODBC specifics)

Your request in SAS is handled by the SAS sql implementation. This one is translated to the related DBMS by implicit usage or explicit coding.

In your proc sql you have used an connect to statement. That is part of the explicit approach.

SAS(R) 9.3 SQL Procedure User's Guide (connect statement) see the note on the possible difference on SAS-sql and the dbms-sql.

To follow the SQL translations in those lingo's there is a trace option.

SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition  (SASTRACE= System Option)

Back to you error message. It is saying it cannot access data within Oracle using the SAS view.

What could be the case is that the user/pswd connection is not stored/kept correctly in the SAS view. When the other tables are public readable and this one is protected it could be the cause.

SAS(R) 9.3 SQL Procedure User's Guide (Create View) is mentioning the "using libname"is required.
This makes sence as the connection in proc sql is temporary but a libname has a longer timespan being indepent on that.  

[LizzySAS]

Hi Jaap,

Thank you, that was very enlightening. I hope you do not take offense if I skim over some of your suggested solutions, but it appears my DBA has encountered this error before with a colleague of mine. The error is due to a massive text field which exists in the new oracle clinical tables for extended text fields (10,000 characters), which SAS cannot handle directly.

I am coordinating with my colleague to replicate the solution and will post it here once it is complete for future reference.

Thank you for all of your assistance and explanations. Have learned a lot through this experience...

Rob

[jakarman]

Rob,  jus to add as last thing. The processed datatypes with a Oracle-client and a ODBC-client are different. Part of the SQL-lingo differences.

The problem could be in the ODBC approach as SAS chars have their limit at 32k including the CLOB oracle type.    

[Kurt_Bremser]

You are most probably right. Googling "odbc string length limit" reveals a lot of information that ODBC seems to have a rather small limit for strings. Another argument for connecting directly to the DB with the fitting ACCESS module, even if it takes additional work to set up.

[LizzySAS · Accepted solution]

Hi Jaap,

I believe you are correct. The CLOB field is memo. The revised SQL code was able to read in the table successfully:

	create view OC_LOBS as
	select * from connection to ODBC(select to_char(value_text_clob), response_id, clinical_study_id, DCM_question_ID from RXC.RESPONSE_LOBST);

Thanks all for your help.

Rob

[jakarman]

Just a brainwave... Why are you trying to use views?
I you are needing a reference to a oracle table (human language view) code that in the SAS language as a libname.         
In that case you have more control and will more easy see possible problems.