Concurrent Requests - Parameters

摘自：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 argument1, argument2, argument3 fields on the same fnd_concurrent_requests table:

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:

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:

-- ########################################################################################################## -- Concurrent Requests - accessing parameter data -- https://community.oracle.com/message/13365904 -- ##########################################################################################################        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;

---