Automic Workload Automation

Expand all | Collapse all

database schema for Prompt Sets

  • 1.  database schema for Prompt Sets

    Posted 05-21-2019 11:59 AM

    where can I find the Prompt Sets in the database in order to create a report?



  • 2.  Re: database schema for Prompt Sets

    Posted 05-21-2019 12:17 PM

    select *
    from oh
    where oh_otype = 'PRPT'
    and oh_client = 100
    and OH_DeleteFlag = 0
    order by oh_name



  • 3.  Re: database schema for Prompt Sets

    Posted 05-21-2019 12:42 PM

    Thanks, that give me the names of the Prompt Sets.

    Another question is, can I list all the prompts inside each Prompt Set.

     

    Roger Shirley

    Ops Analyst

    702-281-7789



  • 4.  Re: database schema for Prompt Sets

    Posted 05-21-2019 12:52 PM

    select oh_name "Object Name", opse_name "Variable Name", opse_value "Value"
    from uc4.dbo.opse
    inner join uc4.dbo.oh on opse_oh_idnr = oh_idnr
    where oh_otype = 'PRPT' and oh_client = 100
    and OH_DELETEFLAG=0 and OH_REFIDNR=0
    order by oh_name, opse_name

     

    Also keep in mind that the values in these promptset objects are typically overridden in the parent objects that use them.



  • 5.  Re: database schema for Prompt Sets

    Posted 05-21-2019 02:03 PM

    Thanks, again

    I getting this error

    Query could not be executed: 'ORA-00933: SQL command not properly ended

     

    We have an Oracle database, I cannot find any reference to: uc4.dbo.opse

     

    Roger Shirley

    Ops Analyst

    702-281-7789



  • 6.  Re: database schema for Prompt Sets

    Posted 05-21-2019 02:05 PM

    Reduce the table names to just "oh" and "opse".



  • 7.  Re: database schema for Prompt Sets

    Posted 05-21-2019 04:47 PM

    This worked.

    select oh_name "Object Name", opse_name "Variable Name", opse_value "Value"

    from opse

    inner join oh on opse_oh_idnr = oh_idnr

    where oh_otype = 'PRPT' and oh_client = 693

    and OH_DELETEFLAG=0 and OH_REFIDNR=0

    order by oh_name, opse_name

     

    Now I would like to be able to list the Prompt Values that are used when the jobs run in process flow.

     

    Thanks,

     

    Roger Shirley

    Ops Analyst

    702-281-7789



  • 8.  Re: database schema for Prompt Sets

    Posted 05-21-2019 06:00 PM

    I have never accomplished what you are asking for.  Too complex.

     

    Say for instance you have a promptset that is used by a job that is used by a workflow that is used by another workflow that is used by a schedule.  Technically speaking the final value that is fed into the promptset at runtime could have come from any one of the objects in the mentioned parent/child relationships.

     

    On the other hand, maybe you'd like to go data mining in the run-history areas of the database?  Then you could capture what was actually used on the previous execution.  (This too is something I have never accomplished and would not be of much help.)



  • 9.  Re: database schema for Prompt Sets

    Posted 05-22-2019 11:17 AM

    Good morning Pete,

    thanks for all your help.

     

    Roger Shirley

    Ops Analyst

    702-281-7789