Embedded tabs in text 1

[BJCooperIT]

Once again I have to find a back door approach to do my job due to lack of privileges - arrrrghhhh! The DBA group has decided that developers, in the development database, can see the source of procedures and functions, but not packages. Don't ask why, I don't know. They only say that if I need to see the source then I can get it from the database.

I am almost there with the following script, but there is one tiny problem to overcome. When then source has embedded tabs, the extracted source shows without the indention.

SET echo OFF
SET verify OFF
SET feed OFF
SET head OFF
SET show OFF
SET linesize 100
SET pagesize 0
accept in_owner prompt "Enter Owner Name: "
accept in_name  prompt "Enter Source Name: "
accept in_type  prompt "Enter Type Name: "
col text format a100 heading "Source Text" word_wrapped
spool extract_source.lst

SELECT text
FROM   all_source src
WHERE  src.owner = nvl(upper('&in_owner'),src.owner)
AND    src.NAME  = upper('&in_name')
AND    src.TYPE  = nvl(upper('&in_type'),src.TYPE)
ORDER  BY src.owner, src.NAME, src.TYPE, src.line;

spool OFF
edit extract_source.lst

The output of a function (not mine) shows like:

function addmonth (inmonth number)

return varchar2 IS
BEGIN
if inmonth > 0 THEN
return add_months(sysdate,inmonth);
end if;
exception
when others THEN
return '000';
end addmonth;

Replace and translate don't seem to help me take the tab and translate it to spaces so the the code is readable. It should be:

function addmonth (inmonth number)

return varchar2 IS
BEGIN
   if inmonth > 0 THEN
       return add_months(sysdate,inmonth);
   end if;
   exception
      when others THEN
         return '000';
end addmonth;

I can't write my own function for this because we need to use this in a database where I have no privileges. Is there any Oracle function or trick you can think of to do this?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[stefanhei]

Hi,

your problem is caused by the following line:

col text format a100 heading "Source Text" word_wrapped

word_wrapped left justifies each new line skipping leading whitespaces.

The following code will wrap in the middle of a word if needed, but whitespaces are not skipped.

col text format a100 heading "Source Text" wrapped

Stefan

 

[BJCooperIT]

Thanks Stefan, that line was a leftover from another script. I never gave a thought to the SQL*Plus environment causing my problem.

Alas, while the script now does exactly what I need it to do, I still do not have privileges to extract package bodies from the schema where they reside. However, it comes in handy for procedures and functions.

It is hard to diagnose problems when you cannot see the source... ahhh, the consulting life....

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[johnherman]

Here's a solution for replacing CR and LF that will also work for tabs.

while instr(new_comments,chr(13),1) > 0 loop
new_comments := replace
(new_comments,chr(13)||chr(10),'~');
end loop;

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright