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;



Diagnostic Script for Concurrent Requests .

source:REQCHECK.sql - Diagnostic Script for Concurrent Requests (Doc ID 164978.1) This Script...
  • u011350763
  • u011350763
  • 2013年07月09日 12:10
  • 429

Example of Simulating concurrent requests

protected slaresearch.WebService1SoapClient client; protected monitor.monitorSoapClient moni...
  • solaris_navi
  • solaris_navi
  • 2011年08月26日 17:38
  • 338

Diagnostic Script for Concurrent Requests

source:REQCHECK.sql - Diagnostic Script for Concurrent Requests (Doc ID 164978.1)This Script is made...
  • pan_tian
  • pan_tian
  • 2013年03月04日 17:06
  • 2413

Concurrent的实现

来源: 深入理解Java内存模型(五)——锁     java的CAS同时具有 volatile 读和volatile写的内存语义,      因此Java线程之间的通信现在有了下面四种方式:  ...
  • kainever
  • kainever
  • 2015年07月25日 22:17
  • 504

Python爬虫大杀器之Requests快速入门

转载:http://blog.csdn.net/iloveyin/article/details/21444613 快速上手 迫不及待了吗?本页内容为如何入门Requests提供了...
  • wickedvalley
  • wickedvalley
  • 2016年07月22日 15:04
  • 438

How To Print Concurrent Requests in PDF Format

Subject: How To Print Concurrent Requests in PDF Format ...
  • dinya2003
  • dinya2003
  • 2006年07月11日 12:41
  • 2837

Java --- 并发库concurrent包学习总结

我们都知道,在JDK1.5之前,Java中要进行业务并发时,通常需要有程序员独立完成代码实现,当然也有一些开源的框架提供了这些功能,但是这些依然没有JDK自带的功能使用起来方便。而当针对高质量Java...
  • u011680118
  • u011680118
  • 2016年03月22日 18:57
  • 835

Using Concurrent Requests for running sqlplus scripts

Concurrent request execution method can be pointed to a Sqlplus script. This option is available in ...
  • luyushuang
  • luyushuang
  • 2013年12月18日 14:07
  • 752

JAVA CAS原理深度分析 concurrent实现

JAVA CAS原理深度分析 concurrent实现
  • xinyuan_java
  • xinyuan_java
  • 2016年08月09日 11:20
  • 1253

java concurrent包介绍及使用

前面一个系列的文章都在围绕hash展开,今天准备先说下concurrent包,这个系列可能会以使用场景说明为主,concurrent包本身的代码分析可能比较少; 我在这方面的实践经验较为有限,有错误欢...
  • xkzju2010
  • xkzju2010
  • 2016年07月25日 14:41
  • 1254
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Concurrent Requests - Parameters
举报原因:
原因补充:

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