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
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!
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: