我正在尝试运行一系列脚本文件，但我希望每个脚本文件都打印其文件名，以便进行报告。
因此，目的是从一个通用脚本调用每个SQL文件：
@SQL_File_1;
@SQL_File_2;
@SQL_File_n;
但我需要每个SQL打印它的结果，所以我需要每个打印：
DBMS_OUTPUT.PUT_LINE({Filename} || ' updated ' || {Number of records});
如何检索文件名？这件事容易做吗？
如果您正在运行SQL*Plus，则可以根据注释进行操作，但前提是您设置了APPINFO，例如
SQL> select module from v$session where sid = sys_context('USERENV','SID');
MODULE
----------------------------------------------------------------
SQL*Plus
SQL> host cat x:tempmyfile.sql
select module from v$session where sid = sys_context('USERENV','SID');
SQL> @x:tempmyfile.sql
MODULE
----------------------------------------------------------------
SQL*Plus
SQL> SET APPINFO ON
SQL> @x:tempmyfile.sql
MODULE
----------------------------------------------------------------
01@ x:tempmyfile.sql
这也适用于SQLcl。
我已经有一段时间没有使用TOAD了，但我认为它也支持appinfo
该实用程序执行指定目录中的所有sql文件，并用显示sql文件名的提示命令补充对sql文件的调用。
例如输入文件
C:upworkpowershell-oracle_gitsql
31.12.2020 11:42 28 SQL_FILE1.sql
31.12.2020 11:42 28 SQL_FILE2.sql
31.12.2020 11:43 28 SQL_FILE3.sql
31.12.2020 11:43 28 SQL_FILE4.sql
31.12.2020 11:43 28 SQL_FILE5.sql
例如输出日志
SET session NLS_LANG: AMERICAN_AMERICA.CL8MSWIN1251
===========================================================================================
Script start time : 2020-12-31 11:50:11
Found SQL file C:upworkpowershell-oracle_gitsqlSQL_FILE1.sql
Found SQL file C:upworkpowershell-oracle_gitsqlSQL_FILE2.sql
Found SQL file C:upworkpowershell-oracle_gitsqlSQL_FILE3.sql
Found SQL file C:upworkpowershell-oracle_gitsqlSQL_FILE4.sql
Found SQL file C:upworkpowershell-oracle_gitsqlSQL_FILE5.sql
-------------------------------------------------------------------------------------------
例如输出sqlplus短日志，如果set termout OFF
SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 31 11:48:58 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
Session altered.
Elapsed: 00:00:00.00
SQL>
Session altered.
Elapsed: 00:00:00.00
SQL> SQL> SQL> Start script: @C:upworkpowershell-oracle_gitsqlSQL_FILE1.sql
SQL> SQL> Stop script: @C:upworkpowershell-oracle_gitsqlSQL_FILE1.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:upworkpowershell-oracle_gitsqlSQL_FILE2.sql
SQL> SQL> Stop script: @C:upworkpowershell-oracle_gitsqlSQL_FILE2.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:upworkpowershell-oracle_gitsqlSQL_FILE3.sql
SQL> SQL> Stop script: @C:upworkpowershell-oracle_gitsqlSQL_FILE3.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:upworkpowershell-oracle_gitsqlSQL_FILE4.sql
SQL> SQL> Stop script: @C:upworkpowershell-oracle_gitsqlSQL_FILE4.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:upworkpowershell-oracle_gitsqlSQL_FILE5.sql
SQL> SQL> Stop script: @C:upworkpowershell-oracle_gitsqlSQL_FILE5.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
例如，sqlplus输出完整日志，如果set termout ON
SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 31 11:50:12 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
Session altered.
Elapsed: 00:00:00.00
SQL>
Session altered.
Elapsed: 00:00:00.00
SQL> SQL> SQL> Start script: @C:upworkpowershell-oracle_gitsqlSQL_FILE1.sql
SQL> SQL> select 1, sysdate from dual;
1 SYSDATE
---------- -------------------
1 31.12.2020 11:50:09
1 row selected.
Elapsed: 00:00:00.00
SQL> Stop script: @C:upworkpowershell-oracle_gitsqlSQL_FILE1.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:upworkpowershell-oracle_gitsqlSQL_FILE2.sql
SQL> SQL> select 2, sysdate from dual;
2 SYSDATE
---------- -------------------
2 31.12.2020 11:50:09
1 row selected.
Elapsed: 00:00:00.00
SQL> Stop script: @C:upworkpowershell-oracle_gitsqlSQL_FILE2.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:upworkpowershell-oracle_gitsqlSQL_FILE3.sql
SQL> SQL> select 3, sysdate from dual;
3 SYSDATE
---------- -------------------
3 31.12.2020 11:50:09
1 row selected.
Elapsed: 00:00:00.00
SQL> Stop script: @C:upworkpowershell-oracle_gitsqlSQL_FILE3.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:upworkpowershell-oracle_gitsqlSQL_FILE4.sql
SQL> SQL> select 4, sysdate from dual;
4 SYSDATE
---------- -------------------
4 31.12.2020 11:50:09
1 row selected.
Elapsed: 00:00:00.00
SQL> Stop script: @C:upworkpowershell-oracle_gitsqlSQL_FILE4.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:upworkpowershell-oracle_gitsqlSQL_FILE5.sql
SQL> SQL> select 5, sysdate from dual;
5 SYSDATE
---------- -------------------
5 31.12.2020 11:50:09
1 row selected.
Elapsed: 00:00:00.00
SQL> Stop script: @C:upworkpowershell-oracle_gitsqlSQL_FILE5.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
此powershell脚本在调用sql脚本之前将sqlplus命令PROMPTsql文件名添加到sql文件中。
<# .SYNOPSIS
This script adds PROMPT sql file names to sql file before call sql script.
Author: Dmitry Demin dmitrydemin1973@gmail.com
.DESCRIPTION
This script adds PROMPT sql file names to sql file before call sql script.
.PARAMETER sql_file_input
Specify the input sql script.
.PARAMETER sql_file_output
Specify the output sql script.
.PARAMETER log_file
Specify the log file.
.EXAMPLE
This script adds PROMPT sql file names to sql file before call sql script.
.add_prompt_file_names.ps1 -sql_file_input .sqlstart.sql -sql_file_output .sqlstart_prompt.sql -log_file log_file.log
#>
param(
[string]$sql_file_input="C:upworkpowershell-oracle_gitsqlstart.sql",
[string]$sql_file_output="C:upworkpowershell-oracle_gitsqlstart_prompt.sql",
[string]$log_file="log_add_prompt.log"
)
$upper_line = "PROMPT ""Start script: "
$bottom_line = "PROMPT ""---------------------------------------------------------------------------------------------------------"""
$date_time_start = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
Write-host "Script start time : $date_time_start "
try
{
echo "Script start time : $date_time_start ">>$log_file
}
catch {
Write-Host "Log File $log_file. Other type of error was found:"
Write-Host "Exception type is $($_.Exception.GetType().Name)"
exit
}
echo "===========================================================================================" | tee-object -Append -filepath $log_file
echo "Input sql file: $sql_file_input" | tee-object -Append -filepath $log_file
echo "Output sql file: $sql_file_output" | tee-object -Append -filepath $log_file
$data_file = Get-Content $sql_file_input
$null | Set-Content -Path $sql_file_output
foreach ($line_file in $data_file)
{
if ($line_file.TrimStart().StartsWith("@"))
{
$start_prompt_line= $upper_line + $line_file.TrimStart().replace("@","") + """"
Out-File -filepath $sql_file_output -append -inputobject $start_prompt_line -encoding default
Out-File -filepath $sql_file_output -append -inputobject $line_file.TrimStart() -encoding default
Out-File -filepath $sql_file_output -append -inputobject $bottom_line -encoding default
}
else
{
Out-File -filepath $sql_file_output -append -inputobject $line_file -encoding default
}
}
例如
C:upworkpowershell-oracle_git>powershell .add_prompt_file_names.ps1 -sql_file_input ./sql/start.sql -sql_file_output ./sql/start_output.sql
Script start time : 2021-01-03 16:32:41
============================================================================
Input sql file: ./sql/start.sql
Output sql file: ./sql/start_output.sql
输入文件/sql/start.sql
REM
REM
REM
---
rem start 1
@SQL_FILE1.sql
rem start 2
@SQL_FILE2.sql
rem start 3
@SQL_FILE3.sql
rem start 4
@SQL_FILE4.sql
rem start 5
@SQL_FILE5.sql
REM
REM
---
输出sql文件：/sql/start_output.sql
REM
REM
REM
---
rem start 1
PROMPT "Start script: SQL_FILE1.sql"
@SQL_FILE1.sql
PROMPT "---------------------------------------------------------------------------------------------------------"
rem start 2
PROMPT "Start script: SQL_FILE2.sql"
@SQL_FILE2.sql
PROMPT "---------------------------------------------------------------------------------------------------------"
rem start 3
PROMPT "Start script: SQL_FILE3.sql"
@SQL_FILE3.sql
PROMPT "---------------------------------------------------------------------------------------------------------"
rem start 4
PROMPT "Start script: SQL_FILE4.sql"
@SQL_FILE4.sql
PROMPT "---------------------------------------------------------------------------------------------------------"
rem start 5
PROMPT "Start script: SQL_FILE5.sql"
@SQL_FILE5.sql
PROMPT "---------------------------------------------------------------------------------------------------------"
REM
REM
---