topic oracle db passthrough error in SAS Programming

oracle db passthrough error

nid197 — Tue, 19 May 2020 13:09:20 GMT

could you please help me with below error meassage. I want to run a passthrough query to update a datetime column in oracle db.whose exact format is datetime27.6.

please help.!

proc sql;

connect to oracle AS ORA2 (authdomain=xxxxxxx PATH ='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)

(HOST=xxxxxx)(PORT=32xxx))(CONNECT_DATA=(SERVICE_NAME=xxx)))');

execute

(

UPDATE ECGM.V_CASE_UDF_DATE_VALUE

SET UDF_VALUE=TO_TIMESTAMP('06-AUG-2019','DD-MON-YYYY')

WHERE udf_nm ='X_RECEIVE_DT_TXT' and case_rk=100558

);

disconnect from ora2;

quit;

I get a error as-

Re: oracle db passthrough error

nid197 — Tue, 19 May 2020 13:10:41 GMT

I get error as

error 22 -322:expecting a name.

Re: oracle db passthrough error

ballardw — Tue, 19 May 2020 14:26:57 GMT

With any code that generates a SAS error, copy the procedure text and any messages from the log and paste the copied text into a box opened on the forum using the </> icon. Many SAS error messages include some diagnostic characters and having the entire message will help. Paste code and log entries into a box to prevent main message windows on this forum from reformatting text.

The log would be plain text so if it contains something sensitive you can edit it after pasting into the code box.

Re: oracle db passthrough error

DavePrinsloo — Tue, 19 May 2020 15:27:43 GMT

Do you really need pass-through for the task? You have a two-level name of the table ECGM.V_CASE_UDF_DATE_VALUE. If you are connecting to the ECGM scheme, then you probably should only specify V_CASE_UDF_DATE_VALUE. As an aside, a naming conventions generally have object with a name lilke V_xxx used for views. It is is not advisable to update views.

Re: oracle db passthrough error

Kurt_Bremser — Tue, 19 May 2020 16:19:38 GMT

Add

by ora2

to the EXECUTE statement.

Re: oracle db passthrough error

nid197 — Tue, 19 May 2020 16:29:34 GMT

no its a naming convention we have it here. its a table and not a view.And I want to update it using passthrough as there are some records which are not getting updated with implicit method.Thanks

Re: oracle db passthrough error

nid197 — Tue, 19 May 2020 18:27:16 GMT

        proc sql;
28         connect to oracle as ora2 (authdomain=casemgmt_db_auth PATH ='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
29         (HOST=xxxxxxxxx)(PORT=xx))(CONNECT_DATA=(SERVICE_NAME=xxx)))');
30         execute
31         (
32         UPDATE ECMB.CASE_UDF_DATE_VALUE
33         SET UDF_VALUE=to_char('06-AUG-2019','dd/mon/yyyy hh24:mi:ss')
34         WHERE udf_nm ='X_RECEIVE_DT_TXT' and case_rk=100558
35         )by ora2;
ERROR: ORACLE execute error: ORA-00942: table or view does not exist.

Re: oracle db passthrough error

Kurt_Bremser — Tue, 19 May 2020 19:28:56 GMT

Well, the message is clear:

ERROR: ORACLE execute error: ORA-00942: table or view does not exist.

Check with your Oracle admins to get the correct table name.

Re: oracle db passthrough error

nid197 — Wed, 20 May 2020 10:08:29 GMT

No because it gives me data for select query on same table.Thanks!

25         GOPTIONS ACCESSIBLE;
26         proc sql;
27         CONNECT TO oracle as ora2 (authdomain=casemgmt_db_auth PATH
27       ! ='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxx)(PORT=xxx))(CONNECT_DATA=(SERVICE_NAME=xxx)))
27       ! ');
28         create table abc as
29         select * from connection to ora2
30         (select * from ecmb.CASE_UDF_DATE_VALUE where udf_nm ='X_RECEIVE_DT_TXT'
31           and case_rk=100558 order by valid_from_dttm desc
32         );
NOTE: Table WORK.ABC created, with 1 rows and 6 columns.

Re: oracle db passthrough error

s_lassen — Wed, 20 May 2020 11:06:09 GMT

The difference seems to be that when you read the data, you refer to the Oracle schema as "ecmb", when you try to update, you refer to it as "ECMB". Can that be what is provoking the error?

Re: oracle db passthrough error

Kurt_Bremser — Wed, 20 May 2020 12:12:58 GMT

When Oracle tells you something is not there, then it's not there, period. Check the whole thing with your Oracle admins, because it happens there.

Case sensitivity might come into play here; using lowercase might cause the SAS/ACCESS (or the Oracle client) to use quotes around the schema/library name, and that forces case sensitivity on the Oracle side.