Your SAS programs, embedded in web apps and elsewhere

Throwing Oracle errors when running Stored process

Reply
Regular Contributor
Posts: 151

Throwing Oracle errors when running Stored process

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

Super User
Posts: 5,430

Re: Throwing Oracle errors when running Stored process

Posted in reply to sasprofile

RTL!

Clearly you are violating constraints on the Oracle table.

Examine stored process logic with the constraints and your interaction.

Data never sleeps
Super User
Posts: 7,808

Re: Throwing Oracle errors when running Stored process

Posted in reply to sasprofile

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 151

Re: Throwing Oracle errors when running Stored process

Posted in reply to KurtBremser

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.

Super User
Posts: 5,430

Re: Throwing Oracle errors when running Stored process

Posted in reply to sasprofile

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.

Data never sleeps
Regular Contributor
Posts: 151

Re: Throwing Oracle errors when running Stored process

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.

Frequent Contributor
Posts: 106

Re: Throwing Oracle errors when running Stored process

Posted in reply to sasprofile

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 Smiley Happy

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.

Ask a Question
Discussion stats
  • 6 replies
  • 537 views
  • 0 likes
  • 4 in conversation