Hot to perfrom explicit pass through in CAS with Using Oracle

[#1]

I was trying to run explicit pass through code, that actually executes in Oracle and  gives an output in CAS

I have tried running a simple code, but this isn’t working and am getting the below error. I tried to run this as per recommendation of SAS.

https://communities.sas.com/t5/SAS-Communities-Library/SAS-Viya-3-4-What-s-New-in-CAS-Data-Access/ta...

Do you guys have ever seen this before ? for your info i am using SAS viya 3.3

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 72          73         proc fedsql sessref=mySession _method ; 74            create table hdfs_kim.KIM_FACT_TEST{options replace=true} as 75               select    CT."CONTACT_KEY",CT."CONTACT_DATE_KEY", CT."KURT_ID_OWNER", 76                          CT."ACTION_CATEGORY",CT."ACTIVITY_DESC",CT."ACTIVITY_ID",CT."ACTIVITY_MAIN_OBJECTIVE",CT."ACTIVITY_OBJEC 76       ! TIVE" from 77               connection to ORA_KIM(select df. "CONTACT_KEY",df."CONTACT_DATE_KEY", df."KURT_ID_OWNER", 78                          ex."ACTION_CATEGORY",ex."ACTIVITY_DESC",ex."ACTIVITY_ID",ex."ACTIVITY_MAIN_OBJECTIVE",ex."ACTIVITY_OBJEC 78       ! TIVE" 79                          from "KIM_CAMPAIGN_DETAIL_FACT" df left outer join "KIM_CAMPAIGN_DETAIL_FACT_EXT" ex on 80                            df."CONTACT_KEY" = ex."CONTACT_KEY" 81                              WHERE df."CONTACT_DATE_KEY" = 20181119) CT ; NOTE: Executing action 'fedSql.execDirect'. ERROR: Catalog name not found ERROR: The action stopped due to errors. NOTE: Action 'fedSql.execDirect' used (Total process time): NOTE:       real time               0.024378 seconds NOTE:       cpu time                0.097488 seconds (399.90%) NOTE:       total nodes             17 (936 cores) NOTE:       total memory            8.36T NOTE:       memory                  29.21M (0.00%) ERROR: The FedSQL action was not successful. NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements. 82          83               quit ;  NOTE: PROCEDURE FEDSQL used (Total process time):       real time           0.05 seconds       cpu time            0.00 seconds

/* connecting*/

options cashost="xxx-yyy-rr-001.corp.lat.no" casport=5570;

cas;

caslib _all_ assign;

*cas mySession sessopts=(caslib=hdfs_kim timeout=3600 locale="en_US" metrics=true);

cas mySession sessopts=(caslib=hdfs_kim timeout=3600 locale="en_US" metrics=true);

caslib casora datasource=(srctype="oracle",username="ABC",password="xxxxxxxxxx ",

   path="//a-ftd.llllll.xxxxxx.cc:9999/1293",schema="TEST")  LIBREF=ORA_KIM;

proc fedsql sessref=mySession _method ;

   create table hdfs_abc.xyz {options replace=true} as

      select    CT."CONTACT_KEY",CT."CONTACT_DATE_KEY", CT."CUST_NO",

                 CT."ACTION_CATEGORY",CT."ACTIVITY_DESC",CT."ACTIVITY_ID",CT."ACTIVITY_MAIN_OBJECTIVE",CT."ACTIVITY_OBJECTIVE" from

      connection to ORA_KIM(select df. "CONTACT_KEY",df."CONTACT_DATE_KEY", df."CUST_NO",

                 ex."ACTION_CATEGORY",ex."ACTIVITY_DESC",ex."ACTIVITY_ID",ex."ACTIVITY_MAIN_OBJECTIVE",ex."ACTIVITY_OBJECTIVE"

                 from "FACT" df left outer join "FACT_EXT" ex on

                   df."CONTACT_KEY" = ex."CONTACT_KEY"

                     WHERE df."CONTACT_DATE_KEY" = 20181119) CT ;

      quit ;

[#2]

Have a look at the example below. It creates a session cas library pointing to an Oracle database. As part of the library definition also the schema name is defined. You then use the CAS library name in the explicit FEDSQL pass through, so it knows how to talk to the DBMS.

/* test sas libname, the schema is set to the user */
libname v9ora oracle path="&oraPath" user="&oraUser" password="&oraPw";
proc contents data=v9ora._all_ nods;
run;

cas sugus sessopts=(metrics=true);

libname casuser cas caslib="casuser";

proc cas;
  action table.dropCaslib / caslib="xora" quiet=TRUE ; 
  action  table.addCaslib result=r /
      caslib="xora"
      datasource={srctype="oracle",
                  username="&oraUser",
                  password="&oraPw",
                  path="&oraPath"
                  schema="&oraSchema"
    };
   print r;
run;


proc cas;
  action table.fileinfo / caslib="xora" ;
run;

proc fedsql sessref=sugus;
  drop table casuser.dbms_result force;
  
  create table casuser.dbms_result as
  select * from connection to xora (
    /* DBMS specific SQL */
    select
      customer_country
      , customer_name
      , customer_age
      , max(customer_age) over(partition by customer_country ) as max_age
      , min(customer_age) over(partition by customer_country ) as min_age
      , avg(customer_age) over(partition by customer_country ) as avg_age
      , count(*) over(partition by customer_country ) as nkunden
    from
      kunden
    where
      customer_id between 4100 and 4200
    order by
      customer_country
      , customer_age desc

  );
quit;

[#3]

Thanks - This works like charm !

But do you know if it possible to run oracle stored process in SAS Viya ?

for example like :

proc sql;

connect to oracle (user=userid password=password path=database_name);

execute (execute st_pr_name('parm')) by oracle;

disconnect from oracle;

quit;

[#4 · Accepted solution]

Yes you can use

proc fedsql;
  execute ( create table create_test (name varchar(20) ) ) by v9ora;
quit;

Please note, this time there is no SESSREF option in Proc FEDSQL, so this means the SQL is executed from the SAS process, but not run within the CAS server. v9ora refers to a SAS libref pointing to Oracle.