Does anyone run Oracle SQL*Plus jobs in Automic, where the actual sql script in in Automic?

[Legacy User]

 

When we converted from AM8, to Automation Engine v9, we were not able to put the contents of the sql script for sql*plus jobs into Automic.  We had to create a job and prompset to run the sql*plus job command line.  The actual sql scripts are stored out on a file share.  We would like to utilize some of the features of Automic, such as variables, and version control, etc. and have the scripts inside of Automic.  Is anyone currently doing this successfully and willing to share how they have it setup?  I am trying to remember the issue we had.  It was either something with the password and encryption, or it was not properly interpreting the sql commands correctly.  It has been a while since we have tried it, and situations have come up where we want to explore this option.  If there is not an option to do so, is there an option out there that someone would be willing to share where they are running command like we are, but passing variables to the sql. 

[Anon Anon]

You could use a Windows job and specify the SQLPLUS - Exe as interpreter.

Be sure to checkt the radiobuton "interpreter" in Windows Tab in the job and start the agent with the correct INI file settings:

ECPEXE= [patch to interpreter]
ECPEXT=[extension]

[Anon Anon]

The following executes under OM V8 as a UNIX type job in our environment.  The code below is placed in the object's Process tab.  In this example the script variable&clientis set to that of the executing client's number.

! Executes SQL against the Automic/UC4 tables. ! :SET &client = SYS_ACT_CLIENT() . ./.profile; $ORACLE_HOME/bin/sqlplus -S  << EOSQL $CONNECTION ! Oracle sql statements follow ! set tab off linesize 300 pagesize 0 ! select oh_name, HACL_Client  from oh, HOST, HACL where OH_Idnr=HOST_OH_Idnr and HOST_Active=1   and (HACL_Execute=1 or HACL_Read=1 or HACL_Write=1)   and OH_DeleteFlag =0   and OH_Idnr = HACL_OH_Idnr   and HACL_Client=&client order by oh_name; ! ! Oracle sql statements precede EOSQL !

[Pete Wirfs]

 

We are on V9 and we purchased the RA agent add-on.  This has allowed me to use Oracle/SQL jobs in place of external scripts.  There was a trick however.  I prefix my script with;

sql_set_statement_terminator term="

;

And terminate my script with a

@" character.  Otherwise the agent will try to execute your PL/SQL logic line-by-line.  Oracle needs to compile the entire PL/SQL script as a single unit of work.

[Anon Anon]

The SQLPlus scripts with variables should work fine in a SQL job. (I have jobs that are set up that way.)

At the time when we did your migration, I think we did had to use the Command Line for the SQL Loader jobs and just did the same for SQLPlus rather than pulling all the scripts into the UI. 

 

[Prashant_Gupta_9370]

Hi Jennifer,

Can you please post an example of the setup that was done? I need to migrate a SQL job from V9 Applications Manager (which had program type = AWSQLP, and the location had the directory path of the .sql file to execute). We are migrating such jobs to ONE Automation, and I am not sure how to setup these JOBS.

[Prashant_Gupta_9370]

Here is an example of the job to be migrated from V9 AM to Automic ONE Automation.

https://us.v-cdn.net/5019921/uploads/editor/4m/ilpc8ajpb8i1.png" width="818">