oracle db passthrough error

[nid197]

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-

[nid197]

I get error as

error 22 -322:expecting a name.

[ballardw]

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.

[DavePrinsloo]

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.

[nid197]

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

[Kurt_Bremser]

Add

by ora2

to the EXECUTE statement.

[nid197]

Spoiler

        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.

[Kurt_Bremser]

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.

[nid197]

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.

[s_lassen]

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?

[Kurt_Bremser]

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.