Quickly PASS Parameters and log redirection to PLSQL anonymously

PL/SQL is a compilation language, so theseCodeThe block must be processed by the compiler before execution. Compilation is a check process that ensures that the referenced objects in the code exist and the statements have correct syntax. The code can be run after compilation, but must be run in the PL/SQL engine.

PL/SQL code blocks can be anonymous blocks or named blocks. The anonymous PL/SQL code block is a PL/SQL code that does not have a name in the header. In this case, you can use interactive tools such as SQL * Plus to send anonymous blocks to the PL/SQL engine. These code blocks will then run immediately. Note that PL/SQL is a compilation language, so anonymous blocks are compiled and run, and then disappear. If you want to run the code again, you must send the complete code block to the PL/SQL engine again. The Code will be compiled and run again in the PL/SQL engine, and then disappear. To make it easier to run again, anonymous blocks can be stored in the script file of the operating system.

PASS Parameters to anonymous users quickly:Passing parameters to anonymous block scripts is the same as passing parameters to pure SQL scripts. That is to say, the variable for receiving parameters in an anonymous fast object must be & 1, & 2 ...... and so on. For example, if the variable for parameters received by the anonymous block script is & A, & B..., the parameter passed when the anonymous block script is directly called is invalid, sqlplus will prompt you to re-enter the parameter.

 1   Example:  2 SQL > @ / Home / TMN / Zhaoxj /  Test. SQL hello  3 SQL >   Declare  4     2 V_partition Varchar2 ( 32 ): =  Partition  ;-- V_partition varchar2 (32): = '& partition' 
5     3   Begin    
  6     4 Dbms_output.put_line ( &  Partition );   
7     5   End  ;
  8     6   /    
9 Enter ValueFor  Partition: Hello
  10 Old 4 : Dbms_output.put_line ( &  Partition );
  11 New 4  : Dbms_output.put_line (Hello );
  12   Hello
  13 SQL > 

The parameter hello after the script is not passed in at first. Solution: Change & partition to & 1.

 1 SQL> @ / Home / TMN / Zhaoxj /  Test. SQL hello  2 SQL >   Declare  3     2 V_partition Varchar2 ( 32 ) = '  & 1  '  ;  4     3    Begin  5     4 Dbms_output.put_line ( &  1  );  6     5    End  ; 7     6    /  8 Old 4 : Dbms_output.put_line ( &  1  );  9 New 4  : Dbms_output.put_line (Hello );  10   Hello  11 SQL> 

It can be seen that the parameters for receiving anonymous PLSQL blocks are the same as those for receiving pure SQL scripts.

Use spool to redirect PLSQL output to a specified file:

 1 Spool / Home / TMN / Zhaoxj / Logdir / Week /&  1 . Log     2   Declare  3 V_partitionVarchar2 ( 32 ): =   '  & 1  '  ;  4 V_date Varchar2 ( 32 ): =   '  & 2  ' ; -- Current summary date  5 V_monday Varchar2 ( 32 ): =   '  & 3  ' ; --  Specific date of Monday  6 V_flag Number : =   &  4 ; --  Judge whether the time is Monday  7   Begin  8   Dbms_output.put_line (v_partition );  9   Dbms_output.put_line (v_date );  10   Dbms_output.put_line (v_monday );  11   Dbms_output.put_line (v_flag );  12   End  ; 13   /  14 Spool Off 

Test results:

 1 SQL > @ / Home / TMN / Zhaoxj / Test. SQL p_1d_20120620 2012  -  06  - 20   2012  -  06  -  18   3  2 SQL > Spool / Home / TMN / Zhaoxj / Logdir / Week/&  1 . Log  3 SQL >   Declare  4     2 V_partition Varchar2 ( 32 ): =   '  & 1  '  ; 5     3 V_date Varchar2 ( 32 ): =   '  & 2  ' ; --  Current summary date  6     4 V_monday Varchar2 ( 32 ):=   '  & 3  ' ; --  Specific date of Monday  7     5 V_flag Number : =   &  4 ; --  Judge whether the time is Monday  8    6    Begin  9     7  Dbms_output.put_line (v_partition );  10     8  Dbms_output.put_line (v_date );  11     9  Dbms_output.put_line (v_monday );  12    10  Dbms_output.put_line (v_flag ); 13    11    End  ;  14    12    /  15 Old 2 : V_partition Varchar2 ( 32 ): =   '  & 1  ' ;  16 New 2 : V_partition Varchar2 ( 32 ): =   '  P_1d_20120620  '  ;  17 Old 3 : V_date Varchar2 (32 ): =   '  & 2  ' ; --  Current summary date  18 New 3 : V_date Varchar2 ( 32 ): =   '  2012-06-20  ' ; --  Current summary date  19 Old 4 : V_monday Varchar2 ( 32 ): =   '  & 3  ' ; --  Specific date of Monday  20 New 4 : V_mondayVarchar2 ( 32 ): =   '  2012-06-18  ' ; --  Specific date of Monday  21 Old 5 : V_flag Number : =   &  4 ;--  Judge whether the time is Monday  22 New 5 : V_flag Number : =   3 ; --  Judge whether the time is Monday  23   P_1d_20120620  24   2012  -  06 -  20  25   2012  -  06  -  18  26   3  27   28 PL / SQL Procedure  Successfully completed. 29   30 SQL >   31 SQL > Spool Off 

All the above content will be entered in/home/TMN/zhaoxj/logdir/week/p_1d_2012061_log.