Capture Oracle stored procedure output parameter value into UC4 variable

[Anon Anon]

I am new to UC4 with no training. I'm executing code in a variable task to run an Oracle stored procedure that has one input parameter and two output parameters.  I need to capture the value returned by the second output parameter into a UC4 variable.  I have been trying to figure this out for days and have not found the solution.  Can anyone please set me on the right path?

[Anon Anon]

I'm assuming that you are executing a UNIX type Job object and the value that you wish to capture is written to STDOUT.  That being true then you could place the required logic in either the job's Post Process or a successor task.  Here is a sample using the Post Process. 

I'm also assuming that when you say ". . into a UC4 variable" you mean a UC4 Variable object and ". . a variable task" you mean a Job object.

Untested code:  where "output parameter" is the character string you want to capture.

:SET &rid = PREP_PROCESS_REPORT(,,REP,"*output parameter*") :PROCESS &rid : SET &parm = GET_PROCESS_LINE(&rid) : PRINT &parm : PUT_VAR variable_object_name,validity_keyword,&parm : ENDPROCESS :CLOSE_PROCESS &rid

p.s. The Innovate user conference starts in Austin on October 7, a good place to learn this kind of stuff.

[Anon Anon]

I'm executing a UNIX type job.  The value I'm wishing to capture is not written to STDOUT.  The online help does not explain how to use STDOUT at my beginning level of understanding.  Can you please explain this to me?  Thank you so much.

[Anon Anon]

STDOUT is where, by default, UNIX type systems write their output.  This, in UC4, is captured and normally made available in the Report tab of a job's execution.

So, for example, if you execute the following in your shell script it will appear in the job's Report and is viewable through the User Interface after selecting the proper RunID through a Statistics selection or an Edit for the object.

echo this is me

If it's not being written to STDOUT by your stored procedure then where is it being written?  A file, for example?

[Anon Anon]

I can run the stored procedure in Toad/Oracle using the following code.

 

DECLARE

  O_ERROR_CODE VARCHAR2(100);

  O_ERROR_MESSAGE VARCHAR2(100);

BEGIN

  Q179313.TR_TEST_SP_OUTPUT(SYSDATE, O_ERROR_CODE, O_ERROR_MESSAGE);

  DBMS_OUTPUT.PUT_LINE(O_ERROR_CODE);

  DBMS_OUTPUT.PUT_LINE(O_ERROR_MESSAGE);

END;

 

This is the DBMS OUTPUT in Toad:

1)     01

2)     123 owned prices are updated.  456 ticket prices are updated.

 

I use this same code in a UC4 Variable SQL Statement (minus the DBMS_OUTPUT lines.)  I've also created a Unix type job using GET_VAR using this code:

:DEFINE &OUT2213#, string
:SET &OUT2213# = GET_VAR('NAME_OF_MY_VARIABLE_OBJECT', O_ERROR_MESSAGE)
:SET &out# = SEND_MAIL('[e-mail]',,'TEST SUBJECT', &OUT2213#)

The email is sent with the subject, but nothing in the email body. 

I need to capture the O_ERROR_MESSAGE output parameter value in UC4, embed it into the email body (if possible) to send nightly to a distro group.  

This is my first time using UC4, and this is as far as I've gotten with it.  I'm new to Unix type jobs as well, so I am not familiar with STDOUT.

Thanks again for your help.

 

Tracey

[Anon Anon]

Where are the 3 Script Functions specified in the Unix Job object, the Pre Process, Process or Post Process tab?

Hopefully you realize that all Script functions in the Pre Process and Process tabs are executed prior to the actual execution of the shell commands (JCL) on the Unix host.  Could this be the source of your problem.

I know nothing of Toad, other that as a Ginger Baker drum solo with Cream, I'm old  ;)

[Anon Anon]

The 3 script functions are in Process tab.  I'll move them to Post Process tab tomorrow morning and get back to you with the results. 

Toad for Oracle is an application used to manage relational  databases using SQL - much like SQL Server Management Studio for SQL Server.

I had a friend who was into Cream back in the day, but I'm not really familiar.  I probably grew up on it and just don't know it.  I'll YouTube search "Ginger Baker drum solo Toad" this evening and see for myself.  :-)

Thank you ever so much for your help today.  I really appreciate it.


Tracey

[Anon Anon]

Where is the logic that places the O_ERROR_MESSAGE keyword value contents into the NAME_OF_MY_VARIABLE_OBJECT Variable object referenced in the second line?  Some PUT_VAR function had to have executed previously so there will be something to get, else a blank character is returned.

Like lots of drum solos, it was better when you saw it live and were slightly under the influence.

[Pete Wirfs]

I use TOAD in my line of work.  I don't believe UC4 variable objects are capable of running PL/SQL blocks of code, which is what you have here.  

The solution I would use here would be to build a new JOBS of type SQL, put your PL/SQL code inside of its process script, and use Marks post-process script to make use of the results.  (SQL objects are smart enough to treat DBMS OUTPUT as if it were SDTOUT.)  This presumes however that your datacenter has SQL objects included in your environment.

If you don't have SQL objects, then you may need to set up a UNIX job that runs SQLPLUS from the command line, and pass it the name of a script file containing your commands.  But I'm unsure of where DBMS OUTPUT goes in this case?

The best bet would be to find a similar solution that someone else already has working at your shop, and mimic that.

 

 Pete

[Anon Anon]

We use Oracle's PL/SQL as Unix jobs and its output is written to STDOUT and as such it is available to UC4 as the job's Report.  We usually execute a job as a single command to a Shell Script and do not normally have the actual commands in the job's Process tab.

Here's a very small sample:

! 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

[Anon Anon]

Mark, I'm trying to understand the script you posted that starts
    :SET &rid = PREP_PROCESS_REPORT(,,REP,"*output parameter*")
I'm new to UC4 and the scripting language, and I don't understand it yet.  I'm searching the online help files to find out more about it, so hopefully the light will go on.  (btw, the drum solo online was STILL pretty intense.)

Pete, unfortunately there's no similar solution at our shop to mimic. This will be the first of its kind here IF I can get it to work.  There are a couple of go-to people here, but this particular solution seems to elude them, too, for now.

The SQL Statement does run when I execute the unix type job.  I can tell because it changes a value in a test table.  (If I exchange my SQL Statement for one that selects values from a table, it does return the row into the results in UC4.)  Unfortunately for me, the SQL statement I need to run doesn't select values from a table - it is supposed to return output parameters.  Therein lies my problem.  It still seems to me that the output parameters should be returned to UC4, but maybe they're not.

Thank you. I'll keep trying.
Tracey

[Anon Anon]

Tracey:

The "*output parameter*" is a mask or filter to search for in the Report and only return lines that match.  You could, for example, just have a "*" and that would return all lines.  In your example, you show the second line of output as

123 owned prices are updated.  456 ticket prices are updated. 

You could, have a filter of "*prices are updated*" to have only that line returned.

Here's a link to the PRE_PROCESS_REPORT documentation.

I'm definitely a neophyte when it comes to lots of SQL.  What does

 . . . doesn't select values from a table - it is supposed to return output parameters.

mean?  Where are the output parameters returned: a report (apparently not to stdout), file, table, etc.?  Depending on where this is placed a UC4 Script function might be able to read it.

[Anon Anon]

What I mean to say is if I write this in the SQL statement:
  select * from xyz.employees where rownum < 2
a row is returned into UC4 that I can read.

If I replace that with the SQL script I actually need to run, which is this:

  DECLARE
    O_ERROR_CODE VARCHAR2(100);
    O_ERROR_MESSAGE VARCHAR2(100);
  BEGIN
    Q179313.TR_TEST_SP_OUTPUT(SYSDATE, O_ERROR_CODE, O_ERROR_MESSAGE);
  END;

I have not figured out how to read the output parameter of the stored procedure that is assign to O_ERROR_MESSAGE in UC4.  The value is in the output parameter and is passed out of the stored procedure, but I do not know the mechanism to read that value in UC4.

[Anon Anon]

Assuming that I'm interpreting the following correctly you are placing the lines in a buffer.  It appears that a subsequent bit of SQL would write those to STDOUT where you could then use the PREP_PROCESS.

I found the following regarding the DBMS_OUTPUT statement.

The PUT Procedure and PUT_LINE Procedure in this package enable you to place information in a buffer that can be read by another trigger, procedure, or package. In a separate PL/SQL procedure or anonymous block, you can display the buffered information by calling the GET_LINE Procedure and GET_LINES Procedure.

This is way out of my league so I think I'm done unless there is something UC4 specific that I can answer.  Besides I'll be at the user conference Tuesday through Friday in Austin.  

[Anon Anon]

I have done as you suggested, Pete, and created a new JOBS or type SQL.  I copied my PL/SQL code into the PROCESS script:

  DECLARE
     O_ERROR_CODE VARCHAR2(100);
     O_ERROR_MESSAGE VARCHAR2(100);
   BEGIN
     Q179313.TR_TEST_SP_OUTPUT(SYSDATE, O_ERROR_CODE, O_ERROR_MESSAGE);
   END;

I copied Mark's script into POST-PROCESS, changing the filter value:
  :SET &rid = PREP_PROCESS_REPORT(,,REP,"*owned prices*")
  :PROCESS &rid
  : SET &parm = GET_PROCESS_LINE(&rid)
  : PRINT &parm
  : PUT_VAR variable_object_name,validity_keyword,&parm
  : ENDPROCESS
  :CLOSE_PROCESS &rid

Do you know what else I need to change?  I apologize for all the beginner questions.  When I try to save this, I get an error message that says

  UC4 Version 9.00A235-461
  =============================
  Error: U4006586 Error found in object
  'MY_JOB_NAME(Post Process)', line '5'.
  ('U1001310 Function 'validity_keyword' is not defined.')

[Pete Wirfs]

You are on the right track!

For testing purposes, I would comment out the :PUT_VAR statement for now and just get the :PRINT statement working.  Then come back and fix the :PUT_VAR statement to store the value in a new static variable.

[Anon Anon]

What is the name of your Variable object?  What is its Attributes for the Validity Keyword, "Freely selected"?  My example assumes that attribute.

Isn't there anybody else in your company that you could ask?  Is there no training budget to get you up to speed?

[Pete Wirfs]

And you need to add your DBMS_OUTPUT statements back into your SQL as well...

[Pete Wirfs]

And you should make sure you can get your SQL job to connect to the correct database and successfully run your SQL statement before you worry about working on the post-process script!

[Anon Anon]

And whatever is occurring with the SQL actually writes to the Report tab and has the needed information to use in the Post Process logic.

[Anon Anon]

Mark,
Thank you so much for responding to my post.  You have been more than patient.  No, we probably won't get training and there is no example of this to learn from.  I spun my wheels for several days before I posted here out of frustration.  I'm not the only newbie here who is struggling, although I'm the only one trying to tackle this particular issue that I know of.  Thank you again.
Tracey

[Anon Anon]

The host value I'm looking for is not available in the dropdown in the JOBS of type SQL, so I am not able to connect to the DB using this method.  I'll keep at it and will post back if I have any luck with this.  Thanks again to both of you.

[Pete Wirfs]

The key to successful logon to your Oracle database is the Server and Database values on your SQL tab of the SQL job.  We always populate the Server field with "servername:portnumber", and the database name is the appropriate Oracle instance name.

since your shop probably already does SQL jobs, there should be a working example in your system that you can duplicate and modify.

[Anon Anon]

There are not any other SQL JOBS in house that I know of.  They are mostly Unix Jobs that use variables, which run the SQL that does not return any values to UC4.  (That's what I used for most of the week.)  There are very few jobs to look at for examples.  I just sent a request to have the attributes added for that server/database in the Jobs type SQL attributes.  After they add it, I'll proceed and let you know how it goes.

Thanks,
Tracey

[Anon Anon]

It is actually the Host value that is not available.  I have the correct Server and Database name.  I've emailed our UC4 support asking to add the host value I need.  I'll keep you posted.  Thanks!

[Anon Anon]

Lesson learned.  Trying to capture the Oracle procedure's output parameter value was a dead-end road for me this time around.  There probably is a way to do it, but I'm a beginner and ran out of time pursuing it.  I ended up creating an Oracle table and then adding code to the procedure to write the output values to that table.  It was pretty straight-forward executing the procedure and selecting table values to use in an email all in UC4.  Thanks for all of your responses!  In spite of having to use an alternate method, I learned a lot from the experience.  I'll update this post if I ever find the answer to it.

[Anon Anon]

i have wasted about 3 days on this as well.  It is a VERY BAD shortcoming of this product that you cannot easily capture the result of a function (PL/SQL , TSQL ...) I have a very simply stored procedure which returns a number.  There does not seem to be any way for Automic to retrieve the number from the SQL execution.  I have tried numerous ways most identified by others in the above discussion.  None of them work.
Here is the code.
***************
SQL_SET_STATEMENT_TERMINATOR TERM='@';

declare x integer;

begin

 x := owner.ora_package_name.ora_function_name (

      p_parm1=>'&PARMVARA1',

      p_parm2=>'&USER') ;

  EXCEPTION

   WHEN OTHERS THEN

     RAISE;

End;
@

: SET  &FUNCTRETVAL = x

: PRINT "The function return value is:  &FUNCTRETVAL "

-- Second option as a unix script.
****************

set -x

set ORAENV_ASK=NO

export ORAENV_ASK

set ORACLE_SID=&APPCTLDBNAME

export ORACLE_SID

. /usr/local/bin/oraenv

sqlplus /  <<eof

set echo off

variable rc number

begin

   :rc := 1;

   :rc := owner.ora_package_name.ora_function_name (

       p_parm1=>'&PARMVARA1',

      p_parm2=>'&USER')  ;

  EXCEPTION

   WHEN OTHERS THEN

  RAISE;

End;

/

exit :rc;

/

eof

I cannot understand why neither of these methods are supported.  I can run this exact code at a unix prompt and report the return code $?.
I understand there is a way to have a post process read the report.  
But in the first case the report does not contain the output of x so reading the report is of no value.  In the second I am not yet sure why it is not working.  I am still investigating.  

Does anyone have any suggestions.

[Pete Wirfs]

I would add a dbms_output function to your first example to display the results on the report.  Then I would capture it with a post-process script.

Another option to consider would be a UC4 VARIABLE of type SQL, and invoke this VARIABLE at the appropriate time.  But this object type requires that the desired connection objects are available.

[Anon Anon]

Sam:

As I previously responded to Tracey:

 Hopefully you realize that all Script functions in the Pre Process and Process tabs are executed prior to the actual execution of the shell commands (JCL) on the Unix host. 

At least in your first example of the two:

SQL stuff . . . . : SET  &FUNCTRETVAL = x
: PRINT "The function return value is:  &FUNCTRETVAL "

will be executed before the actual SQL executes so the script variable will not be properly set.   However I am a V8 user and I cannot even save an object and I get the following message

Error: U4006586 Error found in object 'MY_TEST_QUERY(Process)', line '7'.
('U1001310 Function 'x' is not defined.')

so it is not abundantly clear to me what's happening for you on whatever version of AE your are using.

Pete's approach for the Post Process, in my experience, should produce the results that you are seeking.

[Anon Anon]

in the first example the object is a type SQL not a Unix script.
The second example is a unix script.
I will try moving these to the post process.  I think I may already have tried this.  But I will try again.

: SET  &FUNCTRETVAL = x
: PRINT "The function return value is:  &FUNCTRETVAL "

[Anon Anon]

Moving to the post process did not work.  Automic still does not know that x is a variable not a literal.
Writing the value to the report using method 1 does not seem an option for the same reason.  Automic cannot see the SQL variable called x as a variable not as a literal.  
What I don't understand is if I can use an Automic variable in the WHERE block of a Sql statement or as a PARAMETER in the function, why then does this not work.

begin

&FUNCTRETVAL := owner.ora_package_name.ora_function_name (

      p_parm1=>'&PARMVARA1',

      p_parm2=>'&USER') ;

  EXCEPTION

   WHEN OTHERS THEN

     RAISE;

End;

Where &FUNCTRETVAL is an Automic variable, just as &PARMVARA1 and &user are Automic variables.
This would be the simplest code possible.  No fuss no muss.  But it does not work.
It seems very silly to have to scrape values out of a report in order to simply capture a return code from a function, regardless of the database in use.  

[Anon Anon]

Also I found this in the manual "SEC_SQL/SEC_SQLi" relating to objects of type VARA and to PromptSets .  I don't really understand all the references yet.  VARA objects do not have PromptSets as far as I can tell.  The docs appear to indicate that a VARA object can have a variable defined using these "methods" to receive a value from a SQL statement, I am not sure if this applies to a function return value.  I am reviewing with one of our power users.  I am fairly new to Automic.  I have 3 or 4 jobs in production.  BTW we just upgraded to version 10.