topic Pass-Through automatic variable does not work in SAS Programming

Pass-Through automatic variable does not work

garag — Wed, 22 May 2013 15:46:26 GMT

Hi,

I was trying to execute a sql Pass-Through versus an Oracle db.

In the same sql Pass-Through I execute 3 different execute, 2 create table and a final merge. The last merge generate the following error:

ERROR: ORACLE execute error: ORA-01400: cannot insert NULL into ("blablabla").

After the sql Pass-Through, if I try to put the following automatic variables

%put ORACLE EXIT CODE: &SQLXRC;

%put ORACLE MESSAGE CODE: %superq(sqlxmsg);

I get respectively 0 and empty string, and no message has returned to me unlike what mentioned in SAS documentation

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001360755.htm

Could someone help me to understand what is wrong?

Many thanks.

regards

Re: Pass-Through automatic variable does not work

Anotherdream — Wed, 22 May 2013 15:53:32 GMT

Go to your actual oracle code and run your exact pass thorugh. If you do, do you get the following error? It almost sounds like you created a table with a key on it, and then are trying to insert a null value into the key (which you cannot do).

However I cannot judge without seeing your entire full code!

Re: Pass-Through automatic variable does not work

garag — Wed, 22 May 2013 16:28:23 GMT

Hi,

If I try to run the same come in Oracle sql window I get the same error, it 's clear, the pass-through works fine.

My question was about the automatic variables SQLXRC and sqlxmsg that, according to SAS documentation, should contains the Oracle error and Oracle error message.

Instead, after an Oracle pass-through completed with error, if I execute

%put ORACLE EXIT CODE: &SQLXRC;

%put ORACLE MESSAGE CODE: %superq(sqlxmsg);

I get

ORACLE EXIT CODDE: 0

ORACLE MESSAGE CODE:

insted in the log some rows above there was written:

ERROR: ORACLE execute error: ORA-01400: cannot insert NULL into ("blablabla").

I expected that, after the failed pass-troguh, running the following code:

%put ORACLE EXIT CODE: &SQLXRC;

%put ORACLE MESSAGE CODE: %superq(sqlxmsg);

I get:

ORACLE EXIT CODE:ORA-01400

ORACLE MESSAGE CODE: cannot insert NULL into ("blablabla").

or something like this. I repeat, the problem it seems on automatic variables

I hope it's clear now.

Thanks

Re: Pass-Through automatic variable does not work

KarlK — Thu, 23 May 2013 14:39:14 GMT

Are you executing the %puts after all your pass-thru statements have finished, or after each one? You mentioned your pass-thru job has multiple steps. Unless I'm mistaken, SQLXRC and SQLXMSG are reset to 0 and blank prior to the execution of any pass-thru command. So if it's an intermediate step that's generating the error (for example, one of your create table statements) AND you follow that with another statement, the second statement will clear out the messages from the first statement.

Just a guess, of course, without actually looking at your code.

HTH,

Karl