topic Throwing Oracle errors when running Stored process in Developers

Throwing Oracle errors when running Stored process

sasprofile — Fri, 21 Aug 2015 04:12:02 GMT

Hi All,

I have an Issue with SAS AMO 5.1.

all other users are able to update the records without any issues except me.

When am trying to run a Stored process to update some records via a Interface. it is throwing below errors

it throws below two error prompt windows as shown and the data is never getting updated.

Error window 1

SAS Add-In

stored process CD_QM_UPDATE encountered errors while opening the results

we can continue to open the results or postpone processing by placing them in the status window.

Continue Postpone Abort (These are buttons)

When I click continue above below it prompts with a different below window

When I checked the log window of first error it shown below errors in that

ERROR: ORACLE execute error: ORA-00001: unique constraint (PDN_ST.SWA_PROD_CDQ_UNQ) violated.

ERROR: ROLLBACK issued due to errors for data set FQTAB. TB_SWA_PROD_CDQ.DATA.

Error window 2

SAS Add-In

stored process "Name" CD_QM_UPDATE has completed with no visual content. You can refresh this analysis through the Manage content dialog box

When I close this window the records never updates

any one has faced similar kind of experience

I would really appreciate if any one can help me with this issue

Thanks in Advance

Re: Throwing Oracle errors when running Stored process

LinusH — Fri, 21 Aug 2015 04:46:21 GMT

RTL!

Clearly you are violating constraints on the Oracle table.

Examine stored process logic with the constraints and your interaction.

Re: Throwing Oracle errors when running Stored process

Kurt_Bremser — Fri, 21 Aug 2015 06:59:06 GMT

Your stored process tries to do something that violates the integrity constraints imposed on the table(s) in question.

Inspect the constraints/integrity rules defined for the table(s). Since only one user is affected, it may be that you lack permissions for one of the tables which must be updated to make the transaction valid.

Re: Throwing Oracle errors when running Stored process

sasprofile — Fri, 21 Aug 2015 18:17:45 GMT

It works for all other users when they are updating but only its not working for one user, and even the user for whom its working he tried to connecte to the sas server with his credential and tried to update stored process from non working sas amo and its throwing same errors.so its not a permissions issue.

Re: Throwing Oracle errors when running Stored process

LinusH — Fri, 21 Aug 2015 19:31:04 GMT

Or local installation / configuration issue?

Sounds like you need to dig into bits and bytes.

I sugest that you open a track to SAS tech support.

Re: Throwing Oracle errors when running Stored process

sasprofile — Fri, 21 Aug 2015 20:03:12 GMT

I have already open sas track,they are not very helpful they are suggesting to contact Oracle DBA to find out the constraints on the Oracle table we are getting error.

Re: Throwing Oracle errors when running Stored process

boemskats — Fri, 28 Aug 2015 08:57:21 GMT

I'd suggest making an identical copy of your stored process for testing with this particular user, and looking at adding some of the SASTRACE options for debugging (probably ',,,d'), and looking in the STP logs to see what values are being sent to Oracle and what logic is executing.

SAS are correct - this problem is an Oracle problem, but using the SASTRACE option above you should be able to figure it out with your DBA. I know they're very friendly people

If you struggle to get anywhere when talking to them, try creating that user a test stored process with this inside:

proc sql;

CONNECT TO ORACLE (USER=XXX PASS=XXX PATH=XXX);

create table userconstraints as
select * from connection to oracle (

SELECT * FROM user_constraints

);

DISCONNECT from ORACLE;

quit;

proc print data=userconstraints; run;

This will give you something to present to your DBA. It may be worth comparing the output for users where it is working and others where it is not.