What are the supported Oracle SQL commands that you can use in an SQL Job?

[Legacy User]

Hello,

I'm able to run the following (Process tab) successfully:

SQL_SET_STATEMENT_TERMINATOR TERM='/'; 
BEGIN 
UC_REORG('MQCP001'); 
UC_REORG('MQCP002'); 
END; 
/ 


But if I add either a WHENEVER SQLERROR EXIT 2 or ALTER TABLE... command, it fails with an ORA error of Invalid SQL statement.

Does anybody have a list of Oracle SQL commands that are supported/not supported in a SQL job?

Thanks!

[Legacy User]

Hi,

Invalid SQL Statement and ORA-XXXX Errors in general are generated by the DB Server.
I dont know any unsupported SQL commands.

Cheers, Daniel

[Legacy User]

Hi Daniel_Trimmel_2011

Yes I understand that ORA-*** is an Oracle DB error. I guess to rephrase my question - what Oracle SQL commands works/does not work with our [Oracle] SQL Job? Because WHENEVER SQLERROR EXIT 2 works on Toad/SQL*Plus but it throws an Invalid SQL statement when used in our Oracle SQL Job. EXEC and DESC also does not work in our Oracle SQL Job.

Thanks!

[laura_albrecht_automic]

I'm not sure if this is exactly the same, but you mentioned that DESC doesn't work in a SQL job.  I ran into that as well and figured out you can't do that directly in the Process tab.  You have to go to the Forms tab, click the "insert line at current position" button and then expand out Automation Engine commands under the SQL folder on the right side.  Then if you select Get Columns of a Table and then identify the table name - that will do the DESC as you expect.  I don't know why it works that way, but perhaps that will lead you to a solution for your other questions. I see another option there for SQL error handling.

I hope this helps.

[Legacy User]

Thank you LauraAlbrecht608310

Not quite the same but that may work - Lucas_Amorim_9853
 can you check?
Also, 'SQL Statements' - 'User Defined' looks like it does have limitations on what it can and can not process. 

[Lucas_Amorim_8287]

HiChristine Chavez,

I've checked and that runs fine when querying tables.
When querying packages, I get no errors with a blank report.
But what I really will need is to execute them.
Are you guys able to execute packs?
I've seen Pete's comment on previous post:
https://community.automic.com/discussion/comment/23520

*******************
Pete Wirfssaid:

What was your error message?  Does it give any clues?  In my case I used no delimiters around my parameter list and I only had one parameter.  This is what I tested with and it worked fine;

exec eis_staging.dbo.bp_call_job bj_create_weekly_event;
*******************

Do I need to configure something in Automic to be able to run?

[Legacy User]

Hi Lucas_Amorim_9853
What is the exact script that you put in the Process tab? Can you post it here.
This is what I have on my test SQL Job - Process.

SQL_GET_COLUMNS TABLE="OH";

SQL_ON_ERROR ACTION="ABEND";

SQL_SET_STATEMENT_TERMINATOR TERM='@';

BEGIN

UC_REORG('MQ1CP001');

procPrintHelloWorld();

dbms_output.put_line('test');

 END;

@

The result or error message is in the agent log file. Edit your Job, go to "SQL" tab, check the "Agent log" under Optional reports. 


When you run your job again,  you'll see the "Agent log" tab in Reports.https://us.v-cdn.net/5019921/uploads/editor/qa/p10kageb358t.png" width="658">

[Lucas_Amorim_8287]

Hi Christine Chavez,

 

 

At the moment I am just playing around trying to make it work.

In this example what I have onForm:https://us.v-cdn.net/5019921/uploads/editor/mu/ngcg9tr15td4.png" width="878">

 

Which fillsProcesswith:
https://us.v-cdn.net/5019921/uploads/editor/13/pe7wzhddazli.png" width="664">

 

And terminates with report:
https://us.v-cdn.net/5019921/uploads/editor/e1/q270b6x7gjho.png" width="1224">

Please remember that my DB knowledge is very basic, so I might be trying something that doesn't make sense.
The commands were given to me by the Oracle Team and they work fine from their end.

 

[Legacy User]

Please review the suggestions and screen shot posted previously.
Put your package inside a Begin/End statement.

SQL_SET_STATEMENT_TERMINATOR TERM='@'; BEGIN GMF_GB_REPORTING.PKG_DM_RESPONSE_INCRMNTL.SP_DMRESPONSE_TRNCTWRKTBL; END; @

Regards,
Christine

[Lucas_Amorim_8287]

Christine Chavez said:

Please review the suggestions and screen shot posted previously.
Put your package inside a Begin/End statement.

SQL_SET_STATEMENT_TERMINATOR TERM='@'; BEGIN GMF_GB_REPORTING.PKG_DM_RESPONSE_INCRMNTL.SP_DMRESPONSE_TRNCTWRKTBL; END; @

Regards,
Christine

ThanksChristine Chavez,

That worked.
I couldn't find a way to get description but using the above will run the packs, which is what I needed :)

[Steven_Nguyen_130]

Update:

I have tried the example above and did not have any success until I put EXEC in front of the store procedure on MSSQL 2014 and AE 11.2.4.

 

Working example below:

SQL_SET_STATEMENT_TERMINATOR TERM='@';
BEGIN
  EXEC csictl.sp_af_code_rpt;
END;
@

[Legacy User]

@steven nguyen There's really not much restriction for MSSQL. Only for Oracle SQL. You should be able to run an MSSQL stored proc without using sql_set_satement_terminator.