Batch File call sqlplus to execute SQL

Today, a new task is required to create a batch file for the customer to maintain the database. It is said that the management tool using access2003 was too slow. This time, the maintained SQL file is directly written into a package and then called with sqlplus. It should be executed soon. However, users do not know much about the database, and a batch processing file can be executed simply by inputting one or two conditions.

First, write a title output to let the user know what the batch processing is.

@ Echo ************************************** *
@ Echo database cleanup Tool
@ Echo ************************************** *

 

The following requirements require the user to enter the user connection information of the database. It may be better to write this information. Whether there are other ways to interact.

@ Echo off

Set/P p_user = "Enter the user name"

Set/P p_password = "enter your password"

Set/P p_sid = "Enter the database connection string (TNS name )"

Set/P p_data = "Enter the maintenance time (yyyy/mm/DD )"

 

Determine input content (for example)

If "% p_data %" = "" (Goto: err1)

 

Before executing the command, ask the user to confirm that choice is intended for this place. However, choice is not supported in my XP command window. I will discuss it later.

Set/p rtn = "does the task start to run until the maintenance time reaches % p_data %? (Y/n )"

If "% RTN %" = "Y" (Goto: execute_delete)
If "% RTN %" = "Y" (Goto: execute_delete) else (Goto: end)

 

Next, call the execution statement. Here the passing parameters and the method of specifying the current directory are used.

Sqlplus % p_user %/% p_password % @ % p_sid % @ "% ~ Dp0delete_start. SQL "'% p_data %'

 

% ~ Dp0 indicates the current directory name (ending with/). If there is a directory name with spaces, it may not be recognized, so it is enclosed.

You can use & 1, & 2 ...... Access

 

After the call is completed, the result is displayed, and then the window is closed with a key.

@ Echo ************************************** *
@ Echo the database cleanup tool is complete
@ Echo ************************************** *

Pause
Goto: End

 

Below is the content of the SQL text, simple write

Output to file

Spool ../clean_up.log
Set serveroutput on

 

The declared variable kekka is the result status number, kekka_txt is the result information, and lastdate is the execution condition. Lastdate: Time variable passed by batch processing

Declare
Kekka number;
Kekka_txt varchar2 (1000 );
Lastdate Date: = '& 1 ';
Begin
Pkg_delete.del (lastdate, kekka, kekka_txt );
Dbms_output.put_line (kekka );
Dbms_output.put_line (kekka_txt );
End;
/

Spool off
Quit

 

This basically meets the requirements for users.

However, two files must be transmitted to the user and both files must be in the same directory.

Think about how to use a file.

 

It seems that only one SQL file can be called in batch processing to meet the requirements.

. Generate the SQL file to be called in the batch file.

 

Method 2:

Define the generated file name in the batch process and output the file content to the file.

Set sqlfile1_sqlfile.txt
Echo spool ../clean_up.log> % sqlfile %

Echo set serveroutput on> % sqlfile %
Echo declare> % sqlfile %
Echo kekka number; >>% sqlfile %
Echo kekka_txt varchar2 (1000); >>% sqlfile %
Echo lastdate Date: = '^ & 1'; >>% sqlfile %

Note that a ^ should be added to the output of & or %, otherwise this line of content will not be output.

 

Echo begin >>% sqlfile %
Echo pkg_delete.del (lastdate, kekka, kekka_txt); >>% sqlfile %
Echo dbms_output.put_line (kekka); >>% sqlfile %
Echo dbms_output.put_line (kekka_txt); >>% sqlfile %
Echo end; >>% sqlfile %
Echo/> % sqlfile %

Echo spool off >>% sqlfile %
Echo quit> % sqlfile %

 

Sqlplus % p_user %/% p_password % @ % p_sid % @ "% sqlfile %" '% p_data %'

 

Basically.