Solved

Setting of Environment Variable inside a block..(Oracle)

Posted on 2000-05-07
7
824 Views
Last Modified: 2008-03-04
hi..
How do we set an environment variable inside a block(PL/SQL)?
I wish to include this small piece of statement (below)in my PL/SQL block..can i do it? I do not want to set it explicitly.?How to set it ?

SET SERVEROUT ON SIZE 100000
0
Comment
Question by:chincholikar
  • 3
  • 2
  • 2
7 Comments
 
LVL 4

Expert Comment

by:srikant033100
ID: 2787290
hi

u can put this statement inside ur pl/sql block

create table a (a number);

spool abc.sql
set serveroutput on size 100000
begin
insert into a values(1);
end;
spool off

This file will run and put an record in the table.
0
 

Author Comment

by:chincholikar
ID: 2787946
hi..

I think u have not understood my question.I just want to use this
(SET SERVEROUT ON SIZE 100000) statement inside my PL/SQL block.
Why do i need to un-necessary create a table and  insert rows in to it..
IF i use with in my PL/SQL block i get the below error...

 PLS-00103: Encountered the symbol "SERVEROUT" when expecting one
 of the following:
 transaction
0
 
LVL 4

Expert Comment

by:srikant033100
ID: 2787972
hi

i know u dont want to create a table but first tell me that why u want to change the environmental variable in the pl/sql block. The example above will also serve ur purpose ok it is not inside the block but still it is tightly with it and should solve ur problem and lastly why to u need it.

Srikant Sarda
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Accepted Solution

by:
kravicp earned 5 total points
ID: 2791062
SERVEROUTPUT is SQL+ variable it is not the standard SQL. it is provided by oracle as a variable to set the output of any progarm to the standard output device.
this cnnot be used inside a pl/sql block b'cas in pl/sql block u can use only standard SQL. to get the out of ur program to the standard output device use DBMS_OUTPUT.PUT_LINE function.
0
 

Author Comment

by:chincholikar
ID: 2791777
hi..

My question was if i cannot use this functionality with in PL/SQL block..Then how will i solve my problem.
The only thing i wish to say is I do not wish to write explicitly
SET SERVEROUT ON SIZE 100000 on sql prompt...
0
 

Expert Comment

by:kravicp
ID: 2791996
hi,
i think this answer will solve ur problem.
change the Glogin.sql file in ur ORACLE_HOME/sqlplus/admin directory.
add set SERVEROUTPUT on
0
 

Author Comment

by:chincholikar
ID: 2792151
hi..kravicp

Thanks for the solution.Anyway I wanted to set thru PL/SQL block..but u said its not possible as it is not Standard SQL.It worked when i added SET SERVEROUT ON SIZE 100000 in glogin.sql file.Anyway thanks for the answer.

ALLOT 05 POINTS TO MR.KRAVICP.

Regards,
Girish Chincholikar.
India.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now