关闭

Concurrent Requests - Parameters

标签: concurrentSA
142人阅读 评论(0) 收藏 举报
分类:

摘自:https://oracle101.co.uk/sysadmin/concurrent-requests-parameters/

***这个在AP:R11+DB:9.2.0.8的版本运行总是报错:ORA-00907: missing right parenthesis;在R12环境中测试是OK的!

Concurrent Requests - Parameters

Posted on Monday 9th May 2016 | Category: SysAdmin | Link

Often when you're analysing a concurrent request via SQL, you might want to see the parameters which were used when that request was submitted. You can see basic information via the argument_text on the fnd_concurrent_requests table - for example, for a run of Gather Schema Statistics you might see something like ALL, 10, 3, NOBACKUP, , LASTRUN, GATHER, , Y.

You can see the same values in the argument1argument2argument3 fields on the same fnd_concurrent_requests table:

Screenshot showing argument fields

What this doesn't tell you is the name of the parameter which the argument text relates to.

Half way there

You can extract the parameters linked to a concurrent request definition via SQL - e.g. taking Gather Schema Statistics as our example job, whose concurrent_program_name isFNDGSCST:

    SELECT cp.concurrent_program_name CP_Name           -- The Concurrent Program name
         , dfcu.end_user_column_name Column_name        -- The real argument name
         , lv.meaning data_type                         -- The data type of argument
         , ffv.maximum_size                             -- The length of the argument
         , dfcu.required_flag                           -- The argument required or not
         , dfcu.display_flag                            -- The argument displayed or not on Oracle Form
         , dfcu.default_value                           -- The default value of the argument
         , dfcu.column_seq_num                          -- The argument sequence number 
    FROM apps.fnd_concurrent_programs_vl cp
    JOIN apps.fnd_descr_flex_col_usage_vl dfcu ON dfcu.descriptive_flexfield_name ='$SRS$.'||cp.concurrent_program_name
    JOIN apps.fnd_flex_value_sets ffv ON ffv.flex_value_set_id = dfcu.flex_value_set_id
    JOIN apps.fnd_lookup_values_vl lv ON lv.lookup_code = ffv.format_type AND lv.lookup_type = 'FIELD_TYPE' AND lv.enabled_flag = 'Y' AND lv.security_group_id = 0 AND lv.view_application_id = 0
  WHERE  cp.concurrent_program_name = 'FNDGSCST'
ORDER BY cp.concurrent_program_name
       , dfcu.column_seq_num;

That returns this:

Screenshot of parameters SQL output

That's useful in that we can see the parameters linked to the job definition.

The Answer

Ideally we want to be able to see the list of parameters on a job, and the values entered against them for any given request_id. The SQL below allows you to do that. This is sample output:

Screenshot of parameters and values against a concurrent job

-- ##########################################################################################################
-- Concurrent Requests - accessing parameter data
-- ##########################################################################################################
 
    WITH tbl_job_data AS (SELECT fcr.argument1
                               , fcr.argument2
                               , fcr.argument3
                               , fcr.argument4
                               , fcr.argument5
                               , fcr.argument6
                               , fcr.argument7
                               , fcr.argument8
                               , fcr.argument9
                               , fcr.argument10
                               , fcr.argument11
                               , fcr.argument12
                               , fcr.argument13
                               , fcr.argument14
                               , fcr.argument15
                               , fcr.argument16
                               , fcr.argument17
                               , fcr.argument18
                               , fcr.argument19
                               , fcr.argument20
                               , fcr.argument21
                               , fcr.argument22
                               , fcr.argument23
                               , fcr.argument24
                               , fcr.argument25
                            FROM applsys.fnd_concurrent_requests fcr
                           WHERE fcr.request_id = :reqid)
       , tbl_params   AS (SELECT dfcu.column_seq_num col_seq
                               , dfcu.end_user_column_name col_prompt
                               , fcr.request_id
                               , fcr.completion_text
                               , cp.user_concurrent_program_name job_name
                               , dfcu.display_flag
                               , '' col_data
                               , '' col_attrib 
                            FROM apps.fnd_concurrent_programs_vl cp
                            JOIN apps.fnd_descr_flex_col_usage_vl dfcu ON dfcu.descriptive_flexfield_name ='$SRS$.' || cp.concurrent_program_name
                            JOIN apps.fnd_flex_value_sets ffv          ON ffv.flex_value_set_id           = dfcu.flex_value_set_id
                            JOIN apps.fnd_lookup_values_vl lv          ON lv.lookup_code                  = ffv.format_type
                            JOIN applsys.fnd_concurrent_requests fcr   ON fcr.concurrent_program_id       = cp.concurrent_program_id
                             AND lv.lookup_type = 'FIELD_TYPE'
                             AND lv.enabled_flag = 'Y'
                             AND lv.security_group_id = 0
                             AND lv.view_application_id = 0
                           WHERE fcr.request_id = :reqid
                        ORDER BY dfcu.column_seq_num)
    SELECT y.request_id
         , y.job_name
         , y.col_seq seq
         , y.col_prompt param
         , x.col_attrib job_value
         , y.completion_text
              FROM (SELECT row_number() OVER (ORDER BY LPAD (REGEXP_SUBSTR (d.col_data, '\d+'), 3, '0')) AS r_num
                         , NULL AS col_prompt 
                         , d.col_data 
                         , d.col_attrib 
                      FROM tbl_job_data 
                          UNPIVOT INCLUDE NULLS 
                                (col_attrib
                             FOR col_data IN (argument1
                                            , argument2
                                            , argument3
                                            , argument4
                                            , argument5
                                            , argument6
                                            , argument7
                                            , argument8
                                            , argument9
                                            , argument10
                                            , argument11
                                            , argument12
                                            , argument13
                                            , argument14
                                            , argument15
                                            , argument16
                                            , argument17
                                            , argument18
                                            , argument19
                                            , argument20
                                            , argument21
                                            , argument22
                                            , argument23
                                            , argument24
                                            , argument25)
                                 ) d
                    ) x
                , (SELECT   request_id
                          , job_name
                          , completion_text
                          , col_seq 
                          , col_prompt
                          , ROW_NUMBER () OVER (ORDER BY col_seq)  AS r_num
                      FROM tbl_params
                  ) y
            WHERE x.r_num  = y.r_num
         ORDER BY x.r_num;



0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:43858次
    • 积分:579
    • 等级:
    • 排名:千里之外
    • 原创:3篇
    • 转载:75篇
    • 译文:0篇
    • 评论:2条
    文章分类
    最新评论