Hi folks,
We are using Viya's SAS Intelligent Decisioning.
I woulld like to do publish from SAS to ORACLE (updating) by MAS using a DS2 package.
This doesn't seem to be very well documented.
Would you have some DS2 package code snippet how to do this?
Any pointer is very welcome.
TIA,
KC
You have to use DS2 SQLStmt package. Please see some examples here https://go.documentation.sas.com/doc/en/masag/5.5/p1cnf8o1b4ez1dn1y0mlgrh1ory0.htm
We're surprised we could use sqlstmt as it could not be used on CAS.
Way to go.
Thanks very much!
KR
You're welcome.
For whatever reason we cannot get it running.
Does anybody have possible a completely stripped-down code snippet that updates just a single a numeric Oracle column?
That would possibly rule out other root causes.
Kind regards, guys
too many.
we don't have the orignal data. so, we just replaced the VALUES expression 'insert into ora.... values (?,?), by a SELECT expression like 'insert into coldtemps select * from where...' Just something based on SASHELP.CLASS to get it running. Nothing fancy. We can make it complicated ourselves. 🙂
I think this way it is simpler
package vmassst2;
dcl package logger logr('App.Test');
dcl package sqlstmt ss;
dcl package SQLStmt srollback;
method insert2( int quantity,
in_out int rc );
if null( srollback ) then do;
srollback = _NEW_ sqlstmt( 'rollback' );
if null( srollback ) then do;
logr.log( 'e', 'SQLStmt ROLLBACK instantiation failed.' );
rc = -1;
goto Exit;
end;
end;
if null( ss ) then do;
ss = _NEW_ sqlstmt( 'insert QUANTITY into ora_dstest1.vmassst2 select QUANTITY from MY_SAMPLE_TBL where QUANTITY is not missing' );
/* not tested. Logic: insert value QUANTITY of MY_SAMPLE_TBL in place of value QUANTITY of VMASSST2 */
if null( ss ) then do;
logr.log( 'e', 'SQLStmt insert instantiation failed.' );
rc = -1;
goto Exit;
end;
end;
rc = ss.execute();
if ( rc ) then do;
logr.log( 'e', 'execute failed. rc=$s', rc );
goto tRollback;
end;
tRollback:
logr.log( 'd', 'Rolling back uncommitted work...', k );
srollback.execute();
end;
Exit:
end;
endpackage;
I cannot provide real errors and log due to compliance.
I know this isn't very helpful, I am sorry.
If the logic of that stripped-down code is not too far from wrong than we can shift our attention on data or environment.
As usual, it's the tiny things...
Thank you very much for your efforts.
VALIDATE says the basic error is that the package method sqlstmt.sqlstmt call is not supported by DS2 in CAS.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!