IT Process Automation

Expand all | Collapse all

How to pass inputs as parameters in Oracle queries using Database operators in ITPAM

  • 1.  How to pass inputs as parameters in Oracle queries using Database operators in ITPAM

    Posted 10-10-2016 02:48 AM

    Hi All,

     

    I want to know how I can pass values as parameters  in Oracle queries using database operators in ITPAM. I have tried '?' to pass the value in inline text and defining the input in parameter set but it doesn't work I guess for oracle.

    I tried to use the expression set to pass the value but it is also failing (error: invalid SQL statement).

    For e.g. create user test identified by testpaswrd

     

    Now, I want to pass the username 'test' and password for that user 'testpaswrd' dynamically as parameters.

    If someone has done it, please share your thoughts.

     

    Thanks,

    Vidit Goel  



  • 2.  Re: How to pass inputs as parameters in Oracle queries using Database operators in ITPAM

    Posted 10-13-2016 08:05 AM

    Vidit,

     

    In MS SQL the '?' needs to be wrapped in '()', I don't have anyway to test Oracle syntax but there's the MS syntax for what it's worth.

     

    Elwynn.



  • 3.  Re: How to pass inputs as parameters in Oracle queries using Database operators in ITPAM

    Posted 10-17-2016 06:35 AM

    Hi Elwynn,

     

    I tried using the '?' with '()' but still the issue remains.

    If you have any other information regarding the issue, do share.

     

    iqbmo03 : Can you please help me with this issue ?

     

    Thanks,

    Vidit Goel



  • 4.  Re: How to pass inputs as parameters in Oracle queries using Database operators in ITPAM

    Posted 10-17-2016 09:30 AM

    Vidit,

     

    Here's another way I used to use to pass variables into where clauses in the expression field (rather than inline text) but now that I figured out how to use input parameters I prefer them because they're easier to debug and get working.

     

    The way this method works is double quotes surrounding single quotes then the + to concatenate the string. MS SQL wants it's strings quoted with single quotes so they are on the inside next to the + Here again the syntax is for MS SQL and you'll need to adapt it to Oracle but it's a second possibility.

    resource_name = '"+Process.ComputerName+"'

     

     

    I've also written PowerShell and used the input parameters to pass in values there as well. In PS you begin by declaring the parameter at the top of the script,using the PS syntax of $var_name, and then within the script you reference the variable simply as $var_name without using any quotation marks, parentheses, etc

     

    PS declaration:

    param([string]$var1, $var2, $var3,)

     

    PS use:

    Get-ChildItem -Path $var1 ......

     

    Good luck

     

    Elwynn



  • 5.  Re: How to pass inputs as parameters in Oracle queries using Database operators in ITPAM

    Posted 10-18-2016 01:10 PM

    Vidit,

     

    Are you still facing this challenge, or did the help offered here help? Can you come back and let us know?



  • 6.  Re: How to pass inputs as parameters in Oracle queries using Database operators in ITPAM

    Posted 10-19-2016 02:33 AM
      |   view attached

    Lenn,

     

    I am testing this solution at my end. There are issues in PAM currently. I will update on this asap once the solution is being tested.

     

    Thanks and Regards,

    Vidit Goel

    HCL Technologies Ltd (US-Geo Automation)

    M: +91 9717308301         www.hcl.com<http://www.hcl.com/>

     



  • 7.  Re: How to pass inputs as parameters in Oracle queries using Database operators in ITPAM

    Posted 10-19-2016 09:15 AM

    Thanks, Vidit. Please let us know!



  • 8.  Re: How to pass inputs as parameters in Oracle queries using Database operators in ITPAM

    Posted 04-24-2018 03:20 AM

    say i have to use if null is passed as input in the query , how could this affect when replaced within quotes ''