Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

How to run SQL query from object type and export results to txt file?

Highlighted
bzdafro
Collector

How to run SQL query from object type and export results to txt file?

Hi,

I need to run the SQL statement below from an object type and have it save the results to a text file. I need it in text file format to run a vb script against it. I can export from SQL runner, but I am trying to automate it from a workflow. I am not sure how to do this. Do I use ksc_itg_run_sql?


select meaning from knta_lookups
where lookup_type like 'Windows%'
and enabled_flag='Y'
3 REPLIES
Erik Cole
Honored Contributor

Re: How to run SQL query from object type and export results to txt file?

So you're wanting to do this from an execution step? I'd probably use a 'Workflow Step Commands' execution type and add a ksc_local_exec command pointing to sqlplus & my SQL script, such as:

ksc_local_exec [AS.ORACLE_HOME]/bin/[AS.SQLPLUS] [AS.DB_USERNAME]/[AS.DB_PASSWORD]@[AS.DB_CONNECTION_STRING] @./scripts/your_script.sql

and use a SPOOL command in your script to direct the output to whatever file/path you want.
Carlos Jose Gom
Super Collector

Re: How to run SQL query from object type and export results to txt file?

You can also use a PL/SQL from an execution step that launch the query and writes your file in some path.

Re: How to run SQL query from object type and export results to txt file?

Hi,

have you tried ksc_run_sql?
I found this in documentation for ITG 6.0 and it's still working.

http://support.openview.hp.com/selfsolve/document/KM237382/binary/PPM6.0_CommandsTokens.pdf

Make sure you set a source environment on execution step, otherwise the command won't be executed.

ksc_run_sql
------------
This command runs a SQL query against the chosen environment. The result of
the last row queried is returned in the [SQL_OUTPUT] token. The result of the
entire query is placed in the [AS.PKG_TRANSFER_PATH][PKGL.SEQ].txt
file in .

Regards,
Michael
//Add this to "OnDomLoad" event