Solved

UTL_FILE and DBMS_OUTPUT

Posted on 2002-03-26
14
2,360 Views
Last Modified: 2008-03-10
UTL_FILE writes text/script files into server directory. We want the same to happen in client directory. Spooling is one way. Using DBMS_OUTPUT.PUT_LINE, I managed to split the argument string into 255 chars and display on a number of lines. But I want in a single line. DBMS_OUTPUT.PUT followed by DBMS_OUTPUT.NEW_LINE could be one soltion. But I get error in SCOTT schema with dopl as procedure name:
BEGIN dopl; END;

*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 99
ORA-06512: at "SCOTT.DOPL", line 12
ORA-06512: at line 1
How can I manage to display let us say 500 characters in a single line in SQL*Plus using SQL and PL/SQL. Can any of the other built in or custom packages help.
--- mundial
0
Comment
Question by:mundial
  • 6
  • 3
  • 3
  • +2
14 Comments
 
LVL 5

Accepted Solution

by:
sora earned 30 total points
ID: 6898565
Use SORA_PUT_LINE procedure instead of DBMS_OUTPUT.PUT_LINE !!!!


Here it is...........

SQL>

create or replace procedure
sora_put_line( line in varchar2, limit in integer)
is
x varchar2(4000);
n pls_integer := null;
s pls_integer := 1;
begin
--
n:=length(line);
x:=line;
Loop
 x := substr(line,s,least(limit,(length(line)-s+1) ));
 s := s + length(x);
 --
 dbms_output.put_line(x);
 --
 if s > length(line)
 then
   exit;
 end if;
 --
end loop;
--
end sora_put_line;
/


Create the procedure first by running above script.

Usage:

In your code, use it anywhere just like you use DBMS_OUTPUT.PUT_LINE e/g

SORA_OUTPUT_LINE(your_string, 255)

Note: the limit (second parameter must not be more than 255) but your string can be as long as you like!!!!



sora
0
 
LVL 9

Expert Comment

by:konektor
ID: 6898791
if u can use oracle forms, then text_io package is avaliable
to particulary overcome 255 limit of dbms_output :

set linesize <max_linesixe_you_want>
set serveroutput on seze <totoal_bytes_of_output>
0
 

Author Comment

by:mundial
ID: 6898859
sora, When I pass a big string of type varchar2(500) to your procedure, the output is on different lines. I want in one continuous line with konektor's commands,
set linesize <max_linesixe_you_want>
set serveroutput on size <totoal_bytes_of_output> in place to accommodate 500 characters on a single line. I cannot use Oracle forms as my application.
--- mundial
0
 
LVL 5

Expert Comment

by:sora
ID: 6898932
that is not possible. The physical limit in SQL*PLUS screen is 255 chars per line.

set linesize <max_line_size>
set serveroutput on size 1000000

will not work when the number of characters exceeds 255 chars per line

May I know why you want in one continuous line?

sora
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6899695
mundial,
   The reason you're having trouble is because it is a security issue for you to have your application create files on the client.  SQL*Plus is a known applicaiton that you can start that will allow you to create files on client machines.  So can Reports.

   My question (beside why sora thought this should be an answer rather than a comment), is whether the user is requesting this file, or whether it is something your application wants to do regardless of the user's actions/desires?

   If the user is making the request, then maybe PL/SQL isn't the best place to handle the transmission.  You could still generate the content in PL/SQL (to a table) and then use that to generate the file client-side using a variety of techniques (even java), depending on your architecture.  If the user doesn't know the file will be created as a by-product of one of their actions, then you need some application you can run client-side that will receive the file (this could be the application that invoked the pl/sql - I don't know).

   If you could post a simple explanation of what your application is doing when this file needs to be generated and how the user is running it (client/server, web forms, jsp, java applet, java application, etc.), I think we might be able to give you better advice.

Good luck!
0
 

Author Comment

by:mundial
ID: 6904594
We are using e-commerce application server and using UTL_FILE we get output files in server directory. But server wants privacy and hence we want to generate on client side. We developed this already in java but rejected since server connect details not to be revealed.
--- mundial
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6905103
mundial,
   I'm still not clear on whether this is for an in-house (intranet) application, or for public (internet) use.  If it is intranet-based, then I'd suggest using networking software to provide the server with access to shared devices.  That way, when you write the files using UTL_FILE, it would still be written by the server, but not onto one of the server drives and there would be no need for anyone to access a server-based drive.  If it is a public-use or internet application, then you may have a great deal more complexity.

   What I need to know - if it is an internet app - is this: When the application decides that it is time to generate this file, what is the application that decides this written in and how are users interacting with this application?

Good luck!

P.S.  We've already exchanged discussions on minimum point levels and I understand your future plans.  This one seems like more than simple, so you might let others on this thread know that you might be able to take care of them later for help offered now.
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.

 
LVL 5

Expert Comment

by:sora
ID: 6909535
mundial -

To answer your questions you initially posted - though it now seems to be branching to an altogether different topic.

I would suggest you close this and open a more general one aimed at getting responses on how to exchange files between client and server with the more emphasis on the security restrictions you mentioned.

To summarise, and to answer your initial questions:

SQL*PLUS does not allow for more than 255 bytes per line and changing no setting is going to change that.

If the server wants to be secure, which means you cannot use UTL_FILE (although I don't see why a certain directory cannot be assigned by UTL_FILE_DIR=<directory name> which gets rid of any potential security considerations, then youare forced to use TEXT_IO (as suggested by Konektor) but only if you are usng forms OR you would have to write your own program to read from a database connection and write to a file.

(DrSQL - This question has been asked a several times already and what I proposed previously was acceptable.
And this is why I posted it as an answer. Besides the question carries a whopping 30 points you see! - can you imagine what a difference it would make to my current measly points!)

sora
0
 

Author Comment

by:mundial
ID: 6913082
Dear sora, konektor and DrSQL, It has become difficult to rate now. Tommorow in office I will see in text pad if things can be got in a straight line. So, please excuse me I am giving it to sora - mundial
0
 

Author Comment

by:mundial
ID: 6913083
Dear sora, konektor and DrSQL, It has become difficult to rate now. Tommorow in office I will see in text pad if things can be got in a straight line. So, please excuse me I am giving it to sora - mundial
0
 

Author Comment

by:mundial
ID: 6913085
Dear sora, konektor and DrSQL, It has become difficult to rate now. Tommorow in office I will see in text pad if things can be got in a straight line. So, please excuse me I am giving it to sora - mundial
0
 

Author Comment

by:mundial
ID: 6913094
Dear sora, konektor and DrSQL, It has become difficult to rate now. Tommorow in office I will see in text pad if things can be got in a straight line. So, please excuse me I am giving it to sora - mundial
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6913785
mundial,
   That's fine.  You will need to click on one of sora's comments or answer to accept it and then grade it.  If you wanted to split the points with konektor you would first reduce the point value on this one (that's an option you have) and then post a new question like "For konektor's help on Q20281913" with the appropriate point value.  Then accept konektor's first comment to award the points.  But you do HAVE to click on an accept button.  I hope it works out.

Good luck!

P.S.  If this isn't clear, just go to the "Customer Support" topic area and post a question with a title something like "HELP!" and explain the issue in the text area.

P.P.S.  Please try to keep to the EE recommended minimum of 50 points for an easy question.
0
 

Expert Comment

by:echleon
ID: 11123191
I modified the code above for some work I needed to do - the change is simply so it wraps by both a string length and a delimiter. It was usefull to output sql generated by pl/sql so it remained valid.

Cheers,
John

create or replace procedure
wrap_put_line( line in varchar2, limit in integer, delimiter in varchar2)
is
x varchar2(4000);
n pls_integer := null;
s pls_integer := 1;
begin
--
n:=length(line);
x:=line;
Loop
 --
 x := substr(line, s, instr(substr(line,1,limit),delimiter,-1,1));
 s := s + length(x);
 --
 dbms_output.put_line(x);
 --
 if s > length(line)
 then
   exit;
 end if;
 --
end loop;
--
end wrap_put_line;
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.

Join & Write a Comment

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…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

760 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

25 Experts available now in Live!

Get 1:1 Help Now