修改oracle sql查询输出格式

发布于 2024-12-05 05:34:18 字数 1050 浏览 1 评论 0原文

我想更改 oracle sql 脚本的输出格式。假设我有一个名为：active_user.sh 的脚本，在其中我只编写我的查询。现在的问题是，当我 bash 脚本时，输出显示没有标题，并且仅显示值。

脚本是：

export CONNECT_STRING=$1
if [ x$2 == x ]
then echo First Parameter is connection string to DB and Second parameter have to be ORACLE_HOME variable && exit 1
else export ORACLE_HOME=$2
fi
export ORACLE_SID=OMEGA #fake
export PATH=$ORACLE_HOME/bin:$PATH
RAND=$$
sqlplus -s /nolog <<-EOF > /tmp/${RAND}.sql_out.temp

connect $CONNECT_STRING
set HEADING OFF
set PAGESIZE 0
set linesize 120
col metric_name format a40
col value format 999999990.9999
select count(*) from v\$session where username is not null  and status='ACTIVE';
EOF

cat /tmp/${RAND}.sql_out.temp

这是运行脚本的命令，输出是：

[root@oracle-test scripts]# ./active_users.sh "ora/orapass123@mydb" /opt/oracle/instantclient_11_2
         1

    23.0000

<但是当我在 sqlplus 中运行查询时，它返回如下内容：

COUNT(*)
----------
1

原文

I want to change output format of my oracle sql script.
Consider I have a script named: active_user.sh which inside it I just write my query. Now the problem is when I bash the script the output displayed without caption and only values are shown.

The script is:

export CONNECT_STRING=$1
if [ x$2 == x ]
then echo First Parameter is connection string to DB and Second parameter have to be ORACLE_HOME variable && exit 1
else export ORACLE_HOME=$2
fi
export ORACLE_SID=OMEGA #fake
export PATH=$ORACLE_HOME/bin:$PATH
RAND=$
sqlplus -s /nolog <<-EOF > /tmp/${RAND}.sql_out.temp

connect $CONNECT_STRING
set HEADING OFF
set PAGESIZE 0
set linesize 120
col metric_name format a40
col value format 999999990.9999
select count(*) from v\$session where username is not null  and status='ACTIVE';
EOF

cat /tmp/${RAND}.sql_out.temp

And this is the command to run the script and the output is:

[root@oracle-test scripts]# ./active_users.sh "ora/orapass123@mydb" /opt/oracle/instantclient_11_2
         1

    23.0000

But when I run the query in sqlplus it returns something like this:

COUNT(*)
----------
1

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

春夜浅 2024-12-12 05:34:18

如果要打印列标题，则必须将 set HEADING 设置为 ON。

您还可以使用 SPOOL 命令，如下所示（抱歉，我现在无法测试）：

spool /tmp/${RAND}.sql_out.temp
set heading on
set pagesize 1000
set tab on
set linesize 120 wrap off
column "yourcount" format a40
col metric_name format a40
col value format 999999990.9999
select count(*) yourcount from v\$session where username is not null  and status='ACTIVE';

If you want to print the column headings, you have to put set HEADING ON.

You can also using SPOOL command, something like this (sorry, I can't testint now):

spool /tmp/${RAND}.sql_out.temp
set heading on
set pagesize 1000
set tab on
set linesize 120 wrap off
column "yourcount" format a40
col metric_name format a40
col value format 999999990.9999
select count(*) yourcount from v\$session where username is not null  and status='ACTIVE';

回复收藏 0 原文

~没有更多了~