Re: Can we give runtime parameters to sql script in HP PPM tool (110 Views)
Reply
Occasional Visitor
Pavan Ranga
Posts: 2
Registered: ‎09-29-2008
Message 1 of 6 (185 Views)

Can we give runtime parameters to sql script in HP PPM tool

Is there is anyway that we can create an object in HP PPM tool, that will behave in similar way as SQL*PLUS like taking runtime parameters, running a file in the same folder with @ command? Please let me know, is there is anyway to do that?
Please use plain text.
Honored Contributor
Erik Cole
Posts: 858
Registered: ‎02-11-2008
Message 2 of 6 (185 Views)

Re: Can we give runtime parameters to sql script in HP PPM tool

You can use PPM execution step commands to run scripts, etc
Please use plain text.
Occasional Visitor
Pavan Ranga
Posts: 2
Registered: ‎09-29-2008
Message 3 of 6 (185 Views)

Re: Can we give runtime parameters to sql script in HP PPM tool

I dont know, whether we can give parameters at runtime in that way? Please let me know
Please use plain text.
Honored Contributor
Erik Cole
Posts: 858
Registered: ‎02-11-2008
Message 4 of 6 (185 Views)

Re: Can we give runtime parameters to sql script in HP PPM tool

Sure you can. Take a look at the special commands in the workbench for examples.

ksc_copy_client_server as an example takes several runtime parameters (passed via tokens) and just calls the unix ftp command to execute a file transfer.
Please use plain text.
Occasional Visitor
jlkalpana
Posts: 1
Registered: ‎12-23-2013
Message 5 of 6 (130 Views)

Re: Can we give runtime parameters to sql script in HP PPM tool

I have similar kind of question..

I have a command in my Request type which will update the 'Reponsibility' field in database. I am using ksc_local_exec command to run sql script at the destination environment which will be selected by User from front end.

Here is the code..
ksc_local_exec [AS.ORACLE_HOME]/bin/[AS.SQLPLUS] [DEST_ENV="[REQD.VP.ORACLE_INSTANCE]".DB_USERNAME]/[DEST_ENV="[REQD.VP.ORACLE_INSTANCE]".DB_PASSWORD]@[REQD.VP.ORACLE_INSTANCE] @[AS.BASE_PATH]scripts/xxdbd_update_user_responsibility '[REQD.VP.ORACLE_USER_ID]' '[REQD.P.RESPONSIBILITY.TO_STRING]'

This code is using tnsname.ora file for updating the table.. Is there any way that I can configure this using DB_LINK?
so that I need not to worry about tnsname.ora file.

Could you please help?
Please use plain text.
Honored Contributor
Jason Nichols KY
Posts: 393
Registered: ‎05-06-2008
Message 6 of 6 (110 Views)

Re: Can we give runtime parameters to sql script in HP PPM tool

Let me preface this with "I am not a DBA", but my understanding DB Links is that you create a DB from one DB schema to another so that you have access to the other when you are logged in as the first. So, for your scenario, I would think that you would have to have a DB Link created from some instance that you do have in your tnsnames.ora file (like the PPM instance) to the other DB that you don't want to have to keep in your tnsnames.ora file. I'm not absolutely certain, but I would think you would still need the tnsnames.ora entry for the DB Link to be created, but then again, I'm no DBA, so I could be wrong on that point. However, once you have the DB Link created, I would think that would have to update the SQL Code to accept another parameter for the DB Link name and then your preface your table references with the DB Link variable. This is just a suggestion of what's coming off the top of my head, so I don't know for sure that it will work.
Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation