BookmarkSubscribeRSS Feed
garag
Calcite | Level 5

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

3 REPLIES 3
Anotherdream
Quartz | Level 8

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!

garag
Calcite | Level 5

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

KarlK
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 994 views
  • 0 likes
  • 3 in conversation