mundial
asked on
UTL_FILE and DBMS_OUTPUT
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
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!
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!
ASKER
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
--- mundial
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.
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.
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
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
ASKER
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
ASKER
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
ASKER
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
ASKER
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
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.
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.
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)
s := s + length(x);
--
dbms_output.put_line(x);
--
if s > length(line)
then
exit;
end if;
--
end loop;
--
end wrap_put_line;
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)
s := s + length(x);
--
dbms_output.put_line(x);
--
if s > length(line)
then
exit;
end if;
--
end loop;
--
end wrap_put_line;
to particulary overcome 255 limit of dbms_output :
set linesize <max_linesixe_you_want>
set serveroutput on seze <totoal_bytes_of_output>