BookmarkSubscribeRSS Feed
KidCat
Fluorite | Level 6

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

12 REPLIES 12
KidCat
Fluorite | Level 6

 

We're surprised we could use sqlstmt as it could not be used on CAS.

Way to go.

Thanks very much!

KR

KidCat
Fluorite | Level 6

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

alexal
SAS Employee
Could you provide more information other than "cannot get it running"? Do you see any errors in the logs?
KidCat
Fluorite | Level 6

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. 🙂

KidCat
Fluorite | Level 6

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;

 

KidCat
Fluorite | Level 6

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.

 

alexal
SAS Employee
If you cannot provide the logs here, then I would recommend you to contact SAS Technical Support https://support.sas.com/en/technical-support/submit-a-support-request.html where you can upload the logs to a secure location. I'm sorry, I won't be able to help without seeing what is going on in the logs...
KidCat
Fluorite | Level 6

VALIDATE says the basic error is that the package method sqlstmt.sqlstmt call is not supported by DS2 in CAS.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Discussion stats
  • 12 replies
  • 6518 views
  • 0 likes
  • 2 in conversation