我的sql脚本如何确定它是在sqldeveloper还是sqlplus中运行? [英] how can my sql script determine whether it is running in sqldeveloper or sqlplus?

问题描述

我正在尝试通过编写脚本来检测是SQL * Plus还是SQLDeveloper来解决SQLDeveloper的行大小错误.像这样:

COLUMN SET_THE_LINE new_value TARGETLINESIZE noprint
SELECT DECODE ('&&_SQLPLUS_RELEASE.','',5,500) as SET_THE_LINE from dual;
SET LINESIZE &&TARGETLINESIZE

我知道SQL * Plus总是设置_SQLPLUS_RELEASE的值，并且可以理解，SQLDeveloper不会.

不幸的是，SQLDeveloper总是提示输入_SQLPLUS_RELEASE的值.

有什么建议吗?

解决方案

我不熟悉SQL Developer的linesize错误，因此不确定最终结果应该是什么.但是您可以为此使用 sys_context :

select sys_context('USERENV', 'MODULE') from dual;

哪个给:

SYS_CONTEXT('USERENV','MODULE')
--------------------------------------------------------------------------------
SQL*Plus

...或:

SYS_CONTEXT('USERENV','MODULE')                                           
-------------------------------
SQL Developer 

因此您可以调整自己的状态:

column set_the_line new_value targetlinesize noprint
set termout off
select case sys_context('USERENV', 'MODULE')
    when 'SQL Developer' then 5 else 500 end as set_the_line from dual;
set termout on
set linesize &&targetlinesize

然后用

进行测试

show linesize

在SQL * Plus中提供linesize 500，在SQL Developer中提供linesize 5.

---

如果您可能尚未在SQL * Plus中建立连接，则只需先定义该值即可；即使您可以包含whenever sqlerror以防万一login.sql将其设置为退出，您甚至无需执行任何特殊操作即可将错误隐藏在select之外的select中，但是您也许之后必须知道要重置它.

define targetlinesize=500
whenever sqlerror continue
set termout off
column set_the_line new_value targetlinesize noprint
select case sys_context('USERENV', 'MODULE')
    when 'SQL Developer' then 5 else 500 end as set_the_line from dual;
set termout on
set linesize &&targetlinesize
show linesize

如果选择成功，column值将覆盖它，如果选择失败，则不触摸它.如果我将其放在名为client.sql的文件中并以以下方式运行:

sqlplus -s /nolog @client

我只得到以下输出:

linesize 500

同样的事情在SQL Developer中运行，再次提供linesize 5.

I'm trying to work around the SQLDeveloper linesize bug by writing my script to detect whether it's SQL*Plus or SQLDeveloper. Something like:

COLUMN SET_THE_LINE new_value TARGETLINESIZE noprint
SELECT DECODE ('&&_SQLPLUS_RELEASE.','',5,500) as SET_THE_LINE from dual;
SET LINESIZE &&TARGETLINESIZE

I know that SQL*Plus always sets the value of _SQLPLUS_RELEASE and, understandably, SQLDeveloper does not.

Unfortunately SQLDeveloper always prompts for the value of _SQLPLUS_RELEASE.

Any suggestions?

解决方案

I'm not familiar with an SQL Developer linesize bug, so not sure what the end result is supposed to be. But you can use sys_context for this:

select sys_context('USERENV', 'MODULE') from dual;

Which gives:

SYS_CONTEXT('USERENV','MODULE')
--------------------------------------------------------------------------------
SQL*Plus

... or:

SYS_CONTEXT('USERENV','MODULE')                                           
-------------------------------
SQL Developer 

So you can adapt what you have as:

column set_the_line new_value targetlinesize noprint
set termout off
select case sys_context('USERENV', 'MODULE')
    when 'SQL Developer' then 5 else 500 end as set_the_line from dual;
set termout on
set linesize &&targetlinesize

And then test with

show linesize

which gives linesize 500 in SQL*Plus and linesize 5 in SQL Developer.

---

If you might not be connected yet in SQL*Plus, just define the value first; you don't even need to do anything special to hide the error from the select beyond the set termout off, though you could include a whenever sqlerror just in case your login.sql is setting it to exit - but then you maybe have to know to reset it afterwards.

define targetlinesize=500
whenever sqlerror continue
set termout off
column set_the_line new_value targetlinesize noprint
select case sys_context('USERENV', 'MODULE')
    when 'SQL Developer' then 5 else 500 end as set_the_line from dual;
set termout on
set linesize &&targetlinesize
show linesize

The column value will override it if the select succeeds, and not touch it if it fails. If I put that in a file called client.sql and run it as:

sqlplus -s /nolog @client

I only get this output:

linesize 500

And the same thing runs in SQL Developer, giving linesize 5 again.

这篇关于我的sql脚本如何确定它是在sqldeveloper还是sqlplus中运行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！