DATA Step, Macro, Functions and more

Pass-Through automatic variable does not work

Reply
Contributor
Posts: 58

Pass-Through automatic variable does not work

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

Super Contributor
Posts: 418

Re: Pass-Through automatic variable does not work

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!

Contributor
Posts: 58

Re: Pass-Through automatic variable does not work

Posted in reply to Anotherdream

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 CODESmiley SurprisedRA-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

Contributor
Posts: 65

Re: Pass-Through automatic variable does not work

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

Ask a Question
Discussion stats
  • 3 replies
  • 257 views
  • 0 likes
  • 3 in conversation