at which section we can write the set cmds(i.e like set pagesize 250) in oracle procedures
我们可以在哪个部分编写oracle过程中的set cmds(即set pagesize 250)
3 个解决方案
#1
I see you have a similar question with an "oracle" tag and thought this general orientation might help.
我看到你有一个类似的问题与“oracle”标签,并认为这个一般方向可能会有所帮助。
SQL*Plus is a client program that provides an environment for issuing SQL commands to an Oracle database that also has some directives (the SET commands) that control the environment and formatting used for the duration of a session at the client. You may enter PL/SQL code in what are called "anonymous blocks", delimited by BEGIN/END, but this code is parsed at run time and never stored as a procedure in the database even though you are executing procedural code.
SQL * Plus是一个客户端程序,它提供了一个向Oracle数据库发出SQL命令的环境,该数据库还有一些指令(SET命令),用于控制客户端会话期间使用的环境和格式。您可以在BEGIN / END分隔的所谓“匿名块”中输入PL / SQL代码,但此代码在运行时解析,即使您正在执行过程代码,也不会将其作为过程存储在数据库中。
To blur the lines somewhat further, PL/SQL code (stored procedures/packages as well as anonymous blocks) may contain a calls to the DBMS_OUTPUT package that produces output, but this output is only visible if executing from a client environment that can receive the output. There are also size limitations to output that comes from DBMS_OUTPUT that can make its use problematic.
为了进一步模糊这些行,PL / SQL代码(存储过程/包以及匿名块)可能包含对产生输出的DBMS_OUTPUT包的调用,但只有从可以接收到的客户端环境执行时,此输出才可见。输出。来自DBMS_OUTPUT的输出也存在大小限制,这可能会使其使用成为问题。
If you are trying to produce output from an Oracle database for reporting or post-processing you generally have these Oracle-based options:
如果您尝试从Oracle数据库生成输出以进行报告或后处理,则通常具有以下基于Oracle的选项:
-
If you have access to the database host file system you can use the Oracle UTL_FILE package to write directly to a file. You'll have to code the cursors, loops, and output formatting yourself in a PL/SQL procedure or anonymous block. Although the PL/SQL code will only write to the host filesystem, it can be called from any client.
如果您有权访问数据库主机文件系统,则可以使用Oracle UTL_FILE包直接写入文件。您必须在PL / SQL过程或匿名块中自己编码游标,循环和输出格式。虽然PL / SQL代码只会写入主机文件系统,但可以从任何客户端调用它。
-
If you need the output somewhere else and the output will fit within the DBMS_OUTPUT line and buffer limitations, you have the greatest formatting control if you write PL/SQL code that outputs exactly what you want and invoking this PL/SQL with the SQL*Plus SPOOL /SPOOL OFF directives to save the output on the client filesystem.
如果您需要其他地方的输出并且输出符合DBMS_OUTPUT行和缓冲区限制,那么如果您编写的PL / SQL代码能够准确输出您想要的内容并使用SQL * Plus调用此PL / SQL,则可以获得最大的格式控制SPOOL / SPOOL OFF指令将输出保存在客户端文件系统上。
-
If your output doesn't fall into the above category (e.g. producing a 100 million row CSV file with 500 character wide lines), you might be able to get what you want by using the appropriate SQL functions in a query to get the results formatted as you need them and then using SQL*Plus SET directives to turn off everything (headings, page breaks, etc) that is not part of the result set (again using SPOOL /SPOOL OFF).
如果您的输出不属于上述类别(例如,生成一个包含500个字符宽行的1亿行CSV文件),您可以通过在查询中使用适当的SQL函数来获得所需的格式,以获得格式化的结果因为你需要它们然后使用SQL * Plus SET指令来关闭不属于结果集的所有内容(标题,分页符等)(再次使用SPOOL / SPOOL OFF)。
#2
No where.
Those command are sqlplus specific -- never apply to pl/sql.
这些命令是特定于sqlplus的 - 永远不适用于pl / sql。
#3
if you are using sqlplus, you can set them in sqlplus\admin\glogin.sql from within your oracle client install. this will get run whenever you open your sqlplus app. it would not be applied to a single procedure.
如果您使用的是sqlplus,则可以在oracle客户端安装中的sqlplus \ admin \ glogin.sql中设置它们。这将在您打开sqlplus应用程序时运行。它不适用于单一程序。
#1
I see you have a similar question with an "oracle" tag and thought this general orientation might help.
我看到你有一个类似的问题与“oracle”标签,并认为这个一般方向可能会有所帮助。
SQL*Plus is a client program that provides an environment for issuing SQL commands to an Oracle database that also has some directives (the SET commands) that control the environment and formatting used for the duration of a session at the client. You may enter PL/SQL code in what are called "anonymous blocks", delimited by BEGIN/END, but this code is parsed at run time and never stored as a procedure in the database even though you are executing procedural code.
SQL * Plus是一个客户端程序,它提供了一个向Oracle数据库发出SQL命令的环境,该数据库还有一些指令(SET命令),用于控制客户端会话期间使用的环境和格式。您可以在BEGIN / END分隔的所谓“匿名块”中输入PL / SQL代码,但此代码在运行时解析,即使您正在执行过程代码,也不会将其作为过程存储在数据库中。
To blur the lines somewhat further, PL/SQL code (stored procedures/packages as well as anonymous blocks) may contain a calls to the DBMS_OUTPUT package that produces output, but this output is only visible if executing from a client environment that can receive the output. There are also size limitations to output that comes from DBMS_OUTPUT that can make its use problematic.
为了进一步模糊这些行,PL / SQL代码(存储过程/包以及匿名块)可能包含对产生输出的DBMS_OUTPUT包的调用,但只有从可以接收到的客户端环境执行时,此输出才可见。输出。来自DBMS_OUTPUT的输出也存在大小限制,这可能会使其使用成为问题。
If you are trying to produce output from an Oracle database for reporting or post-processing you generally have these Oracle-based options:
如果您尝试从Oracle数据库生成输出以进行报告或后处理,则通常具有以下基于Oracle的选项:
-
If you have access to the database host file system you can use the Oracle UTL_FILE package to write directly to a file. You'll have to code the cursors, loops, and output formatting yourself in a PL/SQL procedure or anonymous block. Although the PL/SQL code will only write to the host filesystem, it can be called from any client.
如果您有权访问数据库主机文件系统,则可以使用Oracle UTL_FILE包直接写入文件。您必须在PL / SQL过程或匿名块中自己编码游标,循环和输出格式。虽然PL / SQL代码只会写入主机文件系统,但可以从任何客户端调用它。
-
If you need the output somewhere else and the output will fit within the DBMS_OUTPUT line and buffer limitations, you have the greatest formatting control if you write PL/SQL code that outputs exactly what you want and invoking this PL/SQL with the SQL*Plus SPOOL /SPOOL OFF directives to save the output on the client filesystem.
如果您需要其他地方的输出并且输出符合DBMS_OUTPUT行和缓冲区限制,那么如果您编写的PL / SQL代码能够准确输出您想要的内容并使用SQL * Plus调用此PL / SQL,则可以获得最大的格式控制SPOOL / SPOOL OFF指令将输出保存在客户端文件系统上。
-
If your output doesn't fall into the above category (e.g. producing a 100 million row CSV file with 500 character wide lines), you might be able to get what you want by using the appropriate SQL functions in a query to get the results formatted as you need them and then using SQL*Plus SET directives to turn off everything (headings, page breaks, etc) that is not part of the result set (again using SPOOL /SPOOL OFF).
如果您的输出不属于上述类别(例如,生成一个包含500个字符宽行的1亿行CSV文件),您可以通过在查询中使用适当的SQL函数来获得所需的格式,以获得格式化的结果因为你需要它们然后使用SQL * Plus SET指令来关闭不属于结果集的所有内容(标题,分页符等)(再次使用SPOOL / SPOOL OFF)。
#2
No where.
Those command are sqlplus specific -- never apply to pl/sql.
这些命令是特定于sqlplus的 - 永远不适用于pl / sql。
#3
if you are using sqlplus, you can set them in sqlplus\admin\glogin.sql from within your oracle client install. this will get run whenever you open your sqlplus app. it would not be applied to a single procedure.
如果您使用的是sqlplus,则可以在oracle客户端安装中的sqlplus \ admin \ glogin.sql中设置它们。这将在您打开sqlplus应用程序时运行。它不适用于单一程序。