Announcement

Collapse
No announcement yet.

Cannot assign null value to variable

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Cannot assign null value to variable

    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?

  • #2
    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
    Regards
    Gary Cunningham
    www.cbmc.co.uk
    www.mavenwire.com

    Comment


    • #3
      Re: Cannot assign null value to variable

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

      Comment


      • #4
        Re: Cannot assign null value to variable

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

        Comment


        • #5
          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

          Comment


          • #6
            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.

            Comment


            • #7
              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

              Comment


              • #8
                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

                Comment

                Working...
                X