Project and Portfolio Management Practitioners Forum
cancel

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

Highlighted
bzdafro
Honored Contributor.

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
Acclaimed 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
Trusted Contributor.

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.
Michael.Ebert
Super Contributor.

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