Project and Portfolio Management Support and News Forum
Showing results for 
Search instead for 
Do you mean 

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

Occasional Visitor

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?
5 REPLIES
Honored Contributor Honored Contributor

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

You can use PPM execution step commands to run scripts, etc
Occasional Visitor

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
Honored Contributor Honored Contributor

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.
Visitor

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?
Highlighted
Honored Contributor Honored Contributor

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.