retaining leading space in dbms_output
A reader, November 02, 2004 - 12:44 pm UTC
9ir2:
Is there a way to preserve leading spaces in the
dbms_output.put_line when executing from sql plus?
I am trying to print some indented lines (indented with
space) but dbms_output siently removes leading space.
If I hard code a tab though, it is preserved.
thank you
November 03, 2004 - 5:49 am UTC
set serveroutput on format wrapped
ops$tkyte@ORA9IR2> exec dbms_output.put_line( ' hello' );
hello
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> set serveroutput on format wrapped
ops$tkyte@ORA9IR2> exec dbms_output.put_line( ' hello' );
hello
PL/SQL procedure successfully completed.
thanx!
A reader, November 03, 2004 - 11:31 am UTC
What about Oracle 7.1.5, PLSQL 2.1.5???
Howard, January 31, 2005 - 9:46 am UTC
This does not seem to apply to the sbove version of Oracle, and I could really use it right now!!!!
January 31, 2005 - 10:02 am UTC
wow, way back machine and a half.
That software is alot older than my daughter and she is almost 10!
From way before windows 95 even....
if you can live with a tab as the first character, I vaguely recall using
dbms_output.put_line( chr(9) || .... )
or a '.'
dbms_output.put_line( '.' || .... )
and then trimming it off using sed (you must be on a non-windows platform :)
Oracle 7.....
Howard Lunn, February 03, 2005 - 7:07 am UTC
Yup - Good old OPEN VMS on DEC Alpha boxes. Thanks for the response, already been trying stuff and came to the same conclusion.
Thanks,
Howard
What's the difference in the original response?
Basil, April 04, 2006 - 1:06 pm UTC
I can't see any difference in the set serveroutput commands, or in the dbms_output.put_line statements in the original response. Am I missing something?
April 04, 2006 - 7:31 pm UTC
don't know what you mean, be more explicit please.
Output during execution.
Bill, April 05, 2006 - 9:53 am UTC
I understand that DBMS_OUTPUT will only be displayed afer the execution of the sql block. I also know that I can get up to date information from a sql block using a combination of inserts (or update) and commit so that I can see the status using a select. I can also use a pipe with another application dumping out the info coming from the block. or even utl_file. However, does oracle have or are they planning to have a simple utility like DBMS_OUTPUT that will output from the block while it is running. This would be wonderful for debugging a running application.
April 05, 2006 - 6:11 pm UTC
have you used dbms_application_info?
search for it on this site - you can set a row in v$session_longops OR just set any of the three fields in v$session (client_info, action, module)
Scott Mattes, April 05, 2006 - 11:52 am UTC
Basil from TX is probably referring to this
"
retaining leading space in dbms_output November 02, 2004
Reviewer: A reader
9ir2:
Is there a way to preserve leading spaces in the
dbms_output.put_line when executing from sql plus?
I am trying to print some indented lines (indented with
space) but dbms_output siently removes leading space.
If I hard code a tab though, it is preserved.
thank you
Followup:
set serveroutput on format wrapped
ops$tkyte@ORA9IR2> exec dbms_output.put_line( ' hello' );
hello
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> set serveroutput on format wrapped
ops$tkyte@ORA9IR2> exec dbms_output.put_line( ' hello' );
hello
PL/SQL procedure successfully completed.
"
normalizing the lines it looks like this
"
set serveroutput on format wrapped
exec dbms_output.put_line( ' hello' );
hello
PL/SQL procedure successfully completed.
set serveroutput on format wrapped
exec dbms_output.put_line( ' hello' );
hello
"
with an order by we see
"
set serveroutput on format wrapped
set serveroutput on format wrapped
exec dbms_output.put_line( ' hello' );
exec dbms_output.put_line( ' hello' );
hello
hello
"
how do the same commands give different output? I tried it on SQL Plus and got the ' hello' version for both (using 9.2).
April 06, 2006 - 9:11 am UTC
and your test is case where?
ops$tkyte@ORA9IR2> show serveroutput
serveroutput ON size 1000000 format WORD_WRAPPED
ops$tkyte@ORA9IR2> exec dbms_output.put_line( ' hello world');
hello world
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> set serveroutput on format wrapped
ops$tkyte@ORA9IR2> exec dbms_output.put_line( ' hello world');
hello world
PL/SQL procedure successfully completed.
Dawn, April 06, 2006 - 5:25 am UTC
Scott,
I think Tom's first line in his response above was to say "This is the command: set serveroutput on format wrapped".
He then runs through the test cases, the first line is presumably executed without the "format wrapped" bit set, eg:
test without format wrapped;
set format wrapped;
test with format wrapped;
At least, that's how it reads to me!
A reader, April 06, 2006 - 8:36 am UTC
Dawn,
That makes sense now, Thank You.
DBMS_OUTPUT and string delimiters
Desb, April 18, 2006 - 12:14 pm UTC
I am developing a hot backup script which creates a file which dynamically puts the tablespaces in backup mode and backs up the files constituting each tablespace by creating a command file using dbms_output. This part works fine.
However, trying to create a backup control file is causing me problems as I can't create the output string with the backup control file name in single quotes. I've tried various ways to do this and the closest I can get is to use
dbms_output.put_line('alter database backup controlfile to '||'d:\backup\fm1\data\fm1.ctl');
which gives me
alter database backup controlfile to d:\backup\fm1\data\fm1.ctl.
Of course without the filename in quotes I get an error. I've tried various ways to create the statement but so far have been unable to output it with the filename in single quotes. Any ideas would be gratefully received. I'm sure there's a way round it but I seem to have reached a dead end !
April 18, 2006 - 2:47 pm UTC
dbms_output.put_line( 'how''s this - a string with a '' (quote) in it' );
It worked
A reader, April 19, 2006 - 5:05 am UTC
dbms_output.put_line('alter database backup controlfile to ''d:\backup\fm1\data\fm1.ctl'';');
produced:-
alter database backup controlfile to 'd:\backup\fm1\data\fm1.ctl';
as required.
Another elliptical, but very helpful answer.
Thanks very much. BTW, this is why I never liked programming !
April 19, 2006 - 8:42 am UTC
elliptical? interesting choice of terms, I did not get the reference myself.
Really controlling the output:
martina, March 05, 2008 - 5:40 am UTC
Hi Tom,
i wrote a package av_output with the main procedure put_line which works fine against oru-10027. (We're still at 9.2.0.7). Now my question: do i have a chance to find out the status of the buffer within the package, or if serveroutput is set to "on".
It is like this:
SQL:
set serveroutput on/off
Produce a lot of output with my Package
initialize my package only if serveroutput is set on
Produce another lot of output with my Package
Thank you!
March 05, 2008 - 8:10 am UTC
the "state" of serveroutput is not exposed - you could try a "get/put" to see if you get what you just put...
still trying to really control the output
martina, March 13, 2009 - 10:57 am UTC
Hi Tom,
I'm still working with the procedure av_output which limits the output so I don't get buffer overflow. it usually works well. in some cases we still get the oru-10027. i cannot reproduce the problem as the actually generated output is not available. with my test i generate output systematically and the output is limited successfully.
do you have any suggestion what i am missing? the only thing i can think of is to limit the the buffer_size (in my procedure) to 900000 instead of 1mio.
thank you
martina
p.s. happy anniversary!
March 13, 2009 - 11:09 am UTC
When the limit was at 1,000,000 it was actually some number less than 1,000,000 - they packed the data in a weird way - if you used strings of a certain length - you'd get less possible output than if you used shorter (or longer) strings.
So, 1,000,000 was the maximum upper limit, but it could be less.
So, your approach of saying "900,000" is reasonable - until you get to 10g when you can go unlimited.
Catching exception when DBMS_OUTPUT too big.
Dylan, May 01, 2009 - 3:31 pm UTC
I have a procedure that generates a large varchar2 string and then prints it with dbms_output. All seemed fine to me till somebody told me they were getting errors with it. Ended up being due to them being on an older client (less than 10.2).
I figured I could code around it before they got around to upgrading their client so I thought I'd try and catch the exception, and print in segments instead. When I do some playing around from the older client, it doesn't work as I'd expect...
declare
string_too_big exception;
lv_string varchar2(3000);
pragma exception_init(string_too_big, -6502);
begin
for x in 1 .. 3000
loop
lv_string := lv_string || '*';
end loop;
dbms_output.put_line(lv_string);
exception
when string_too_big then
dbms_output.put_line('String too big.');
when others then
dbms_output.put_line('Some other reason.');
end;
/
ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1
Why isn't the exception block catching the error being raised when the string is too big?
May 02, 2009 - 12:00 pm UTC
Oh, I see, (the error you want to catch would be -20000, not 6502.
The problem is NOT during runtime, you are apparently using an old client against a 10.2 or above database. The database code *runs fine*
It is when the client goes to PICK UP THE STRING from the database that you are hitting the exception.
sqlplus is doing something like this:
a) call procedure/code - it runs and fills a dbms_output buffer, an array in a package.
b) then sqlplus calls dbms_output.GET_LINES to get the buffered output to print. THIS is the call that fails.
the pre 10g client is using a bind buffer that is not "big enough" - the code executed fine, it was the (b) call that failed - NOT your code!
$ sqlplus scott/tiger@ora10gr2
<b>
SQL*Plus: Release 9.2.0.8.0 - Production on Sat May 2 11:42:10 2009
</b>
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
<b>Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
</b>With the Partitioning, OLAP, Data Mining and Real Application Testing options
scott%ORA10GR2> set echo on
scott%ORA10GR2> @test
scott%ORA10GR2> drop table t;
Table dropped.
scott%ORA10GR2>
scott%ORA10GR2> create table t ( msg varchar2(40) );
Table created.
scott%ORA10GR2>
scott%ORA10GR2> declare
2 string_too_big exception;
3 lv_string varchar2(3000);
4 pragma exception_init(string_too_big, -6502);
5 begin
6 for x in 1 .. 3000
7 loop
8 lv_string := lv_string || '*';
9 end loop;
10 dbms_output.put_line(lv_string);
11 insert into t values ( 'I was totally sucessful......' );
12 exception
13 when string_too_big then
14 dbms_output.put_line('String too big.');
15 when others then
16 dbms_output.put_line('Some other reason.');
17 end;
18 /
ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1
PL/SQL procedure successfully completed.
scott%ORA10GR2>
scott%ORA10GR2>
scott%ORA10GR2> select * from t;
<b>
MSG
----------------------------------------
I was totally sucessful......
</b>
You'd have to patch the CLIENT in this case - sqlplus. But that 'patch' is really 'use current client code'.
Using set serveroutput command in trigger
Hemanshu Sheth, December 25, 2011 - 12:35 pm UTC
set serveroutput on
spool c:\cld.log
begin
dbms_output.put_line(dbms_utility.format_call_stack);
end;
spool off
host type c:\cld.log >> c:\CallFrom.log
set serveroutput off
Above is a part of begin..end block in a trigger (oracle 9i). I'm using TOAD 9.5 While compiling a trigger, it gives following error.
.. Found 'serveroutput', Expecting: ISOLATION READ USE
but if I run this block as a script (F5) then it runs perfectly & creates the appended file "callfrom.log" in the desired path. So, how to use setserveroutput command in trigger?
December 25, 2011 - 2:06 pm UTC
serveroutput is strictly a sql plus thing, you cannot
serveroutput
spool
host
or anything "sqlplus command" - sqlplus commands are for sqlplus only and take place on the client machine.
You would have to use UTL_FILE to write the call stack out to a file. You in general will almost NEVER run a host command from a trigger.
Using set serveroutput command in trigger
Hemanshu Sheth, December 25, 2011 - 10:18 pm UTC
Thanks a lot for your confirmation that it will not run.
But, as I mentioned in another thread, I just want to troubleshoot the misbehaviour of application to find out that particular procedure from which bad data is inserted. So, I'll just use it now & then drop the trigger.
So, in that case, could you give me the syntax of using UTL_FILE to write the call stack to a file & it should append everytime to that file.
Thanking you
Hemanshu Sheth
December 26, 2011 - 10:37 am UTC
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_file.htm to do this in a 'thread safe' manner, you'll need to incorporate dbms_lock - since you want to append to a file
you can:
a) dbms_lock to get a named lock in exclusive mode
b) utl_file.fopen in append mode
c) utl_file.put_line to write the line
d) utl_file.fclose to close the file
e) dbms_lock to release the lock.
Or more simply, you can just insert into a logging table of your own design and put a systimestamp in there, along with whatever identifying information you want. That would be easiest.
Using set serveroutput command in trigger
Hemanshu Sheth, December 31, 2011 - 9:32 am UTC
Thank you for all your help & guidance, I finally ended up with a simple select statement in my trigger to insert into my logging table, as below:
SELECT 'Call '|| substr(dbms_utility.format_call_stack,103) INTO :NEW.V_MODULE FROM DUAL ;
I only feared that dbms_utility.format_call_stack would not work as a select projection.
Wish you a happy new year 2012 ahead..
From Hemanshu, Mumbai, India.
December 31, 2011 - 9:50 am UTC
and even if it didn't work in the insert/select - you could have:
l_local_variable := 'Call ' || substr( .... );
insert into my_table( ...) values ( ..., l_local_variable, ... );
And now fetching the procedure name from Front-End
Hemanshu, January 01, 2012 - 10:41 pm UTC
Thank you.
This was as far as calling preocedure names defined in PL/SQL. Now for calling procedure names from Front-End (.NET in my case), I suppose programming would have to be done in front-end like initialising a global variable in every procedure to be tracked.
Is there anyway where Oracle (9i) can help like communicate with front-end code & fetch proc names etc? (so that coding could be saved). If not in 9i then in 10g, 11g??
Regrds
Hemanshu
January 02, 2012 - 8:32 am UTC
Is there anyway where Oracle (9i) can help like communicate with front-end code
& fetch proc names etc? (so that coding could be saved). If not in 9i then in
10g, 11g??
I don't know what you mean - can you elaborate on what you are trying to do?
Fetching the procedure name from Front-End
Hemanshu Sheth, January 03, 2012 - 9:45 am UTC
I basically have a developer background. My application is written in .net platform (backend being oracle9i), source code being bundled in MS Visual Sourcesafe.
I want to track the inserts happening in one transaction table where records are being inserted from diff procedures (basically various saving routines scattered in the .net sourcecode).
IDEAL way would be..
step 1. define a public variable say, m_ModuleName := ""
step 2. identify the procedures where records are inserted in above table & populate the name of that procedure in above variable in each procedure. e.g.
procedure one
...
m_modulename := "one"
...
procedure two
...
m_modulename := "two"
...
function three
...
m_modulename := "three"
...
Above would have to be done in .net source code.
step 3. In oracle, create before insert trigger and have the following line.
...
SELECT 'Called By '|| m_ModuleName INTO :NEW.V_MODULE FROM DUAL
...
BUT, I want to avoid doing step 1 & 2 as it involves coding in .Net. Since .Net developers are not available with us right now and as our application source code is huge, it is very difficult / time consuming by present developers to trace the entire sourcecode & update each & every occurrence of this insert.
I thought as we had in old DOS programming like Clipper or FoxPro, there was an in-built funcion called PROCNAME(p) which used to give proc name based on input parameter 'p'. If p=0 then return current proc name, if 1 then prev proc name, if 2 then name of proc which called prev proc & so on.
Is such functionality available here? like, can oracle communicate with front-end code by way of say, connection string or IIS service or Data source (ODBC-DSN) etc & fetch name of the proc which fired this trigger, without maintaining global variable in source code & update it in each procedure?
No one would have asked such silly but if it can be done then it's great & a lot of time in coding .net front-end could be saved by my organisation.
Thanks & Regrds
Hemanshu Sheth
January 03, 2012 - 11:47 am UTC
have them call "dbms_application_info.set_module( :x, :y )" after binding their module name and other useful information for the module/action_name inputs.
ops$tkyte%ORA11GR2> exec dbms_application_info.set_module( 'aaaa', 'bbbb' );
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
ops$tkyte%ORA11GR2> select sys_context( 'userenv', 'action' ) from dual;
SYS_CONTEXT('USERENV','ACTION')
-------------------------------------------------------------------------------
bbbb
Elapsed: 00:00:00.00
ops$tkyte%ORA11GR2> select sys_context( 'userenv', 'module' ) from dual;
SYS_CONTEXT('USERENV','MODULE')
-------------------------------------------------------------------------------
aaaa
Elapsed: 00:00:00.00
Now, as far as avoiding 1&2 - you should realize that is quite impossible. The Oracle client libraries have no idea, no clue what your modules or procedures are - none.
I thought as we had in old DOS programming like Clipper or FoxPro, there was an in-built funcion called PROCNAME(p) which used to give proc name based on input parameter 'p'. If p=0 then return current proc name, if 1 then prev proc name, if 2 then name of proc which called prev proc & so on. this about this - you wrote everything in Clipper or Foxpro - that was your "language". Here you are .nyet talking to C talking over the network. We work with dozens of languages - java, C, C++, python, ruby, php, etc etc etc etc. Many of those compiled languages have no real way to identify who "called" them (and in fact, the call stack would be very very very large with dozens of layers of code).
If you had written all database code in PL/SQL, we could do this - you'd have a single language to deal with. I could tell you the name, line number, everything - but only because it would all be "plsql" and plsql can do that.
Instrumentation, as you now realize, is something that should be designed into the application from day one -
error handling
auditing
instrumentation
archival
those frameworks should be well defined and regimented before a single line of code is written and all developers would follow those rules.
But, sigh, not many people think about that until they've gone production and worry about tables getting too large, have to try to figure out who did what and why, have to try to figure out what the heck the code is doing, and have to deal with errors in the application.
Fetching the procedure name from Front-End
Hemanshu Sheth, January 04, 2012 - 10:52 am UTC
Yes, I fully agree. You must have noted my last line that I didn't have any hope either. But, due to the ignorance of the initial developers has put me in such a situation.
As you rightly said, one must have everything clearly documented on papaer in black & white, even before you attempt developing an application. That is where the role of systems analyst comes into effect.
But, current developers espesially of microsoft development tools, are so careless because microsft provides most of the functions / features in-built so that a developer hardly has to write basic building blocks. They get everything readymade.
Anyway, this leads us on the different track. I was searching for a solution in oracle. Now, we will have to hire experienced developers to sort out the mess of our source code.
Thanks for your help & sharing your views.
January 04, 2012 - 3:28 pm UTC
They get everything
readymade.
apparently not everything :)