Concurrent Requests - Parameters

转载 2016年08月31日 16:49:27

摘自: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;



Parameter 'xxxx' not found. Available parameters are [list] at java.util.concurrent.FutureTask

Parameter 'categoryIds' not found. Available parameters are [list] at java.util.concurrent.FutureTas...

用户运行concurrent requests(并发请求)报错——日志文件最大超过2g

用户在运行并发请求的时候,发现并发请求无法运行,在日志文件发现如下内容:Concurrent Processing - Concurrent Reports Failing With Errors R...

Example of Simulating concurrent requests

protected slaresearch.WebService1SoapClient client; protected monitor.monitorSoapClient moni...

requests官方文档下载

  • 2017年12月06日 16:11
  • 38KB
  • 下载

requests-py

  • 2017年07月03日 00:25
  • 2.92MB
  • 下载

zabbix自定义用户key与参数User parameters(24)

为什么要自定义KEY 有时候我们想让被监控端执行一个zabbix没有预定义的检测,zabbix的用户自定义参数功能提供了这个方法。我们可以在客户端配置文件zabbix_angentd.conf里...

Python.Requests.Essentials.1784395412

  • 2015年06月25日 09:56
  • 1.26MB
  • 下载

4.有关日期格式属性修改常识,v$nls_parameters,between and,查询指定部门的员工信息,in和null,like模糊查询,order by后面可以跟:列名、表达式、别名、序号

1  有关日期格式属性修改 常识 NLS_DATE_FORMAT           DD-MON-RR select sysdate from dual;          NLS_CU...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Concurrent Requests - Parameters
举报原因:
原因补充:

(最多只允许输入30个字)