at which section set commands in oracle procedures can be used (declaration or executable)

Question

at which section we can write the set cmds(i.e like set pagesize 250) in oracle procedures

Answer 1

No where.

Those command are sqlplus specific -- never apply to pl/sql.

Answer 2

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.

Answer 3

I see you have a similar question with an "oracle" tag and thought this general orientation might help.

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.

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.

If you are trying to produce output from an Oracle database for reporting or post-processing you generally have these Oracle-based options:

• 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.

• 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.

• 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).

Answer 4

System.out.println ("Hello World");

How does it work?

<xml>"Hello World!!"</xml>