How to view dynamically generated SQL in a stored procedure

Today, at work, there is a need to look at the SQL generated in the stored procedure to check with the actual business requirements.

After a multi-query, think of the following methods:

1, in the stored procedure is usually involved in the execute immediate MySQL place, plus dbms_output. Put_Line (MySQL), and the execute immediate MySQL annotation in order to reduce the run time.

2, if because the MySQL character is too long, quoted string buffer is too small, then in another way, the first definition v_pos number: = 1; and then output through the following statement:

1  while V_pos<=  LENGTH (mysql) LOOP2)     ); 3     V_pos:=v_pos+; 4 END LOOP;

3. After compiling the above stored procedure, execute the following statement under Command window:

Set  on ; Declare begin dbms_output.enable (200000);p roc_one_to_many_source_erp_jc ('qkjtj_bas_ ITEMINFO_DWJJ','004002'); -- The stored procedure name adjusts itself and the parameters are filled in End;

4, so that the output of SQL is not with the number, please add it by itself after each SQL;
In addition, the second way to output the SQL, will be forced with a space, please remove the space yourself, there are many ways, I am in Word by the ^p to replace the way to.

5, the last copy of SQL to the tool to format a bit can be read.

6, temporarily only think of such a method, there are other ways to optimize.

How to view dynamically generated SQL in a stored procedure