Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: passing NULL as a Parameter in DB Call Procedure

  1. #1
    Join Date
    Jan 2016
    Posts
    1

    Exclamation passing NULL as a Parameter in DB Call Procedure

    Hi All,

    Relatively new to pentaho and i am trying to get a job to run a database stored procedure. Ive struggled through lots of other posts and got to a place now where it is trying to run the procedure but getting the error

    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 1

    THe issue is that the procedure has 3 arguments, but i would like the first two to be NULL. I currently have a set that is placing NULL inaide a parameter. It seems as though Pentaho is treating this as a string so when running it is PROCEDURE('NULL','NULL','14-JAN-2016).

    Is there a way i can get pentaho to run this as PROCEDURE(NULL,NULL,'14-JAN-2016')?

    THe procedure runs fine with these parameters in SQL Developer or sqlplus.

    THanks
    Gareth

  2. #2
    Join Date
    Aug 2011
    Posts
    360

    Default

    Hi,

    1. Which step do you use?
    2. Have you used a variable or a sql parameter and a field (with ? in your query?)

    If you use a sql parameter with a field, i.e. with ?, just set a null value in the field (i mean not "null" text value,
    but an empty field).
    If you use variable, you have something like
    PROCEDURE ('${var}'.....)
    So if the variable is set as null text, it translate to 'null' so still a string.
    If you write PROCEDURE( ${var}.....) it will work with var=null, but then will not work with real text value,
    because of missing quotes.

    So, you could do
    PROCEDURE ( if ( ''='${var}', null, '${var}'),.....)

  3. #3
    Join Date
    Aug 2011
    Posts
    360

    Default

    Quote Originally Posted by Mathias.CH View Post
    Hi,

    1. Which step do you use?
    2. Have you used a variable or a sql parameter and a field (with ? in your query?)

    If you use a sql parameter with a field, i.e. with ?, just set a null value in the field (i mean not "null" text value,
    but an empty field).
    If you use variable, you have something like
    PROCEDURE ('${var}'.....)
    So if the variable is set as null text, it translate to 'null' so still a string.
    If you write PROCEDURE( ${var}.....) it will work with var=null, but then will not work with real text value,
    because of missing quotes.

    So, you could do
    PROCEDURE ( if ( ''='${var}', null, '${var}'),.....)
    i forgot: and then set var as empty

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.