Cannot assign null value to variable

[Kiseleva]

Hi!
I have several agents which I want to run one by one. First agent runs when OR created, the next agent listens if the previouse is finished (OR status changed).
Something like chain of agents.
All of the agents are based on variables, but for some situations the saved query returns NULL. And it's normal and I want to know about that. But there is a problem: the agent where variable is NULL fails with the error:
Cannot assign null value to variable
This break my chain!

Please, any ideas about that. Actually I can't understand why variable can't be NULL?

[cunninghamg]

Re: Cannot assign null value to variable

Hi

When assigning values to variables in OTM - the sql has to return something.

The best way to ensure this is to surround your sql with:

Select nvl(( <Your SQL Statement> ), 'NO_VALUE') From Dual

Here the value NO_VALUE will be returned instead of null.

The default value must be of the same "Type" as the potentially returned value - so if the sql would have returned a number so the default would have to be a number.

Also if testing for variable values in IF statement expressions, make sure there are no spaces (like i have used underscore).

Regards
Gary

[Kiseleva]

Re: Cannot assign null value to variable

Thank you, Gary! It's working.
I've managed to create my chain without breaks.

[pableras]

Re: Cannot assign null value to variable

more info please! i get the same problem with de refnums in order base

[pableras]

Re: Cannot assign null value to variable

more info please! i get the same problem with de refnums in order base .

order base is create in ebs and send to otm , otm search info and insert in refnum but i get the varible null in agent . i run in toad the query and is valid

[pableras]

Cannot assign null value to variable

the error is in the order base refnum assign variable before insert the data in the table but return null when i search the info.

i run the query in toad and ist correct.

[prabhuramasami]

Re: Cannot assign null value to variable

Could any one please let me know how to handle when a query returns more than one value in assigned variable and to handle null value in the same query.
Eg. My query returns a order release having more than one shipment. All these shipments id to be taken and passed in a variable. If the same query returns null value its working fine. But more than one value returns its throwing "single row sub query error'. Please advise.
query: select nvl((select shipment_gid from view_shipment_order_release where order_release_gid = $gid),'1') from dual

[Shailesh_Sawant]

Re: Cannot assign null value to variable

Hi,
You can use rownum=1 in query to avoid "single row sub query error', if you want list of all the shipment id separated by comma then you can use concat plsql function

Regards
Shailesh