batch-file - Returning table fro multiple select UNION put in an array
问题描述
I want to use filtered data from a sql script and use it to run in the upgrade in DB using the filtered list to upgrade.
1) I have a SQL file which is changing daily and it looks like the following:
select
'OBJECT1' object_name,
'PACKAGE BODY' object_type,
'999999' target_vers,
(Select statement which finds current version),
Decode (decode which comperes target version and current version) status
from DUAL union
select (the same 5 columns) .. ... ..
It could reach 100+ selects
2) I want to use only the table entries which target version>current version (sometimes target version could be < current version)
3) The object name corresponds to .code directory where the corresponding sql, view, pks is found.
4) After the filtered step I want to run these sql file in db server (looks like it would need to be done in loop).
5) since I am new to cmd/batch not sure how to do this.
for /f %%k in ('sqlplus -s %LOGIN%/%LOGIN%@%DBSERVER% @%SCRIPTPATH%\CHECK_CODE.sql') do (
set theValue=%%k
)
The code above runs the sql file mentioned above,
spool new.txt
select ......
spool off
/
exit
This returns the below txt file
OBJECT_NAME OBJECT_TYPE TARGE
--------------------------- ------------ -----
INSTALLED_VERS
--------------------------------------------------------------------------------
STATUS
-------------------------
OBJECT_NAME PACKAGE BODY 72021
72021
--- INSTALLED ---
Now I would want to do a loop only through the needed objects to update the DB.
for /f %%a in ('List of entries which need upgrade')
do (
sqlplus -s %LOGIN%/%LOGIN%@%DBSERVER% @%%a.[object_name].[object extension]
)
The Question is how can I get the filtered data from the first SQL script into an array in CMD which would then use these to run the SQL files (which are identicall to object_name)
解决方案
最终做了类似的事情:
DEL UPGRADE_LOG.TXT
set SCRIPTPATH=%CD%
set BAKNAME=LGH_670
set SCHEMAPATH=X:\HF\QA\Builds\Build Area\LGH\Linedata Global Hedge 6.7.0\QA1\LGH\Oracle_Objects
set LOGIN=LGH_670
set APPSERVER=LN1QAAPP11
set DBSERVER=LN1QADB11_FM.WORLD
set num=0
ECHO ********************************* Loging is starting before services being stopped *************************************** >> UPGRADE_LOG.TXT
ECHO *** Stopping services >> UPGRADE_LOG.TXT
NET STOP %LOGIN% >> UPGRADE_LOG.TXT
ECHO *** Checking V_MQ >> UPGRADE_LOG.TXT
for /f %%k in ('sqlplus -s %LOGIN%/%LOGIN%@%DBSERVER% @%SCRIPTPATH%\help_scripts\MESSEGE_QUEUE.sql') do (set theValue=%%k)
cat V_MQ.txt | sed 's/[[:space:]]//g' >> V_MQ_2.txt
find /c "norowsselected" V_MQ_2.txt
if %errorlevel% equ 1 goto queufull
goto queueempty
:queueempty
del V_MQ_2.txt
del V_MQ.txt
for /f %%k in ('sqlplus -s %LOGIN%/%LOGIN%@%DBSERVER% @%SCRIPTPATH%\help_scripts\USER_OBJECTS.sql') do (set theValue=%%k)
cat USER_OBJECTS.txt | sed 's/[[:space:]]//g' >> USER_OBJECTS_2.txt
find /c "norowsselected" USER_OBJECTS_2.txt
if %errorlevel% equ 1 goto queufull
goto validobjects
:validobjects
del USER_OBJECTS_2.txt
del USER_OBJECTS.txt
ECHO ***Coppying data from "%SCHEMAPATH%" >> UPGRADE_LOG.TXT
xcopy /s "%SCHEMAPATH%\*.*" *.* /Y
ECHO *** Creating Upgrade scripts and cleaning up the folder >> UPGRADE_LOG.TXT
call %SCRIPTPATH%\replace_string.bat V_WHAT V_WHAT_BAK CHECK_CODE.sql
REM ################### Here CHECK_CODE would be checked ##############################
echo select Object_name, object_type from ( > CHECK_CODE.sql.new
type CHECK_CODE.sql >> CHECK_CODE.sql.new
type CHECK_CODE.sql.new > CHECK_CODE.sql
del CHECK_CODE.sql.new
echo spool CODE_CHECK.txt > CHECK_CODE.sql.new
type CHECK_CODE.sql >>CHECK_CODE.sql.new
type CHECK_CODE.sql.new > CHECK_CODE.sql
del CHECK_CODE.sql.new
echo set headsep off > CHECK_CODE.sql.new
type CHECK_CODE.sql >>CHECK_CODE.sql.new
type CHECK_CODE.sql.new > CHECK_CODE.sql
del CHECK_CODE.sql.new
echo set pagesize 0 > CHECK_CODE.sql.new
type CHECK_CODE.sql >>CHECK_CODE.sql.new
type CHECK_CODE.sql.new > CHECK_CODE.sql
del CHECK_CODE.sql.new
echo create table V_WHAT_BAK as select * from V_WHAT; > CHECK_CODE.sql.new
type CHECK_CODE.sql >>CHECK_CODE.sql.new
type CHECK_CODE.sql.new > CHECK_CODE.sql
del CHECK_CODE.sql.new
REM Adding last lines in the system
echo ) where status='### NEEDS UPGRADE ###'; >> CHECK_CODE.sql
echo spool off >> CHECK_CODE.sql
echo DROP TABLE v_what_bak; >> CHECK_CODE.sql
echo / >> CHECK_CODE.sql
echo exit >> CHECK_CODE.sql
REM Here We need to run in the script for alternative check.
for /f %%k in ('sqlplus -s %LOGIN%/%LOGIN%@%DBSERVER% @%SCRIPTPATH%\CHECK_CODE.sql') do (set theValue=%%k)
推荐阅读
- django - 在 Django 中的 post save 信号中发送电子邮件的问题
- json - 如何在 Swift for MacOS 中解析来自 Yahoo Finance 的 JSON
- selenium - 如何在 xpath 中使用撇号 (')?
- node.js - JWT 签名令牌过期即使在代码中更改后浏览器应用程序中也不会更改
- php - 相关模型返回集合时如何访问子相关模型
- pandas - 如何比较两个数据帧以推断第三个数据帧
- angular - 为什么 Angular 会破坏我的 Google 字体 SCSS 导入?
- node.js - 当我试图通过sellerId 获取库存号时,为什么$and 运算符在mongodb mongoose 中不起作用?
- java - Flink Statefun Bootstrap 和状态过期
- python - Django 使用相关模型中的列表注释查询集