AutoSys Workload Automation

 View Only
  • 1.  Passing null value with DBPROC job

    Posted Jun 28, 2016 12:55 PM


    I'm looking for a way to pass a null value to a stored procedure. Everything is set up correctly, i.e., I can pass a number to a NUMBER type parameter, I can pass a string to a VARCHAR type parameter. I don't know how to pass a value of null to said NUMBER/VARCHAR type parameter.



  • 2.  Re: Passing null value with DBPROC job

    Posted Jun 28, 2016 01:05 PM

    Which scheduler are you using?



  • 3.  Re: Passing null value with DBPROC job

    Posted Jun 28, 2016 01:24 PM

    Autosys



  • 4.  Re: Passing null value with DBPROC job

    Posted Jun 28, 2016 01:29 PM

    if you dont give it a paramater shouldnt NULL be the default. just  not setting stdout and err files they go to dev/null.

    what error are you getting?

    is it possible that fieldd is NOT allowed to be NULL?

     

    Steve C.



  • 5.  Re: Passing null value with DBPROC job

    Posted Jun 29, 2016 03:54 PM

    If I don't give a parameter as you suggested, the error message is Invalid column type. I have gone over the parameter types of the stored proc and the DBPROC job type to verify numerous times and positive that they match.

     

    On your third point, that is not the case as I am able to pass NULL values when calling the procedure from a script such as schema.storedProd_a('test', 1, 1, true, NULL, NULL) and no one complains.



  • 6.  Re: Passing null value with DBPROC job
    Best Answer

    Posted Jun 28, 2016 01:35 PM

    If it's an input parameter then you may be able to specify null as a value in sp_arg:

     

    sp_arg: ignore=yes|no, name=arg_name, argtype=IN|OUT|INOUT, datatype=type, value=arg_value

     

    where

    datatype=VARCHAR

    value=NULL



  • 7.  Re: Passing null value with DBPROC job

    Posted Jun 28, 2016 02:14 PM

    cheater.. you have the books :-p



  • 8.  Re: Passing null value with DBPROC job

    Posted Jun 29, 2016 03:59 PM

    Here is one IN arg that I tried passing NULL to.

    datatype=NUMERIC, argtype=IN, name=issueNO, ignore=yes, value=NULL

     

    When I run, the error message is: Error wrapping value: issueNO:NULL - null, Cannot set: issueNO to NULL

    I understand this is because I set the datatype as NUMERIC so it expects a number, so trying to find a way if I can pass NULL.

     

    The other issues if I do this with a VARCHAR (string) type, it may treat NULL as "NULL" (string) and not null.