How does Oracle check where the stored procedure is executed?

The methods to view the execution progress of stored procedures in Oracle are: use the DBMS_OUTPUT package to output progress information. Use the DBMS_APPLICATION_INFO package to obtain execution information. Query the V$SESSION_LONGOPS view to obtain long operation information. Use the INSTR() function to inspect the code and output progress information.

How to check the execution progress of Oracle stored procedures

In Oracle, the common methods to check the execution progress of stored procedures are as follows ：

1. Use the DBMS_OUTPUT package

The DBMS_OUTPUT package provides the function of outputting messages to the client session. You can use this package in stored procedure code to output progress information during execution.

Example:

<code>DECLARE
  BEGIN
    DBMS_OUTPUT.PUT_LINE('处理第 1000 条记录...');
    /* 其他代码 */
  END;</code>

2. Using the DBMS_APPLICATION_INFO package

The DBMS_APPLICATION_INFO package allows an application to access information about the current session, including the statements being executed. You can use this package in stored procedure code to get execution progress.

Example:

<code>DECLARE
  info VARCHAR2(4000);
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('my_package', 'my_procedure');
  LOOP
    DBMS_APPLICATION_INFO.GET_MODULE_INFO(info);
    DBMS_OUTPUT.PUT_LINE(info);
    /* 其他代码 */
  END LOOP;
END;</code>

3. Using the V$SESSION_LONGOPS view

The V$SESSION_LONGOPS view provides information about the long operations currently being performed. Includes stored procedures. You can query this view to obtain the execution progress of a stored procedure.

Example:

<code>SELECT operation,
       elapsed_seconds,
       total_work
FROM V$SESSION_LONGOPS
WHERE operation_type = 'PARSE'
  AND operation LIKE '%my_procedure%';</code>

4. Using the INSTR() function

The INSTR() function can be used to find occurrences of substrings in a string First position. You can use this function to examine the stored procedure code and output progress information as different sections are executed.

Example:

<code>DECLARE
  code VARCHAR2(4000);
BEGIN
  SELECT text INTO code FROM user_source WHERE name = 'my_procedure';
  DBMS_OUTPUT.PUT_LINE('执行到 ' || INSTR(code, '/* 进度信息 */') || ' 行...');
  /* 其他代码 */
END;</code>

The above is the detailed content of How does Oracle check where the stored procedure is executed?. For more information, please follow other related articles on the PHP Chinese website!