Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Munzer.

Asked: January 19, 2002 - 10:05 am UTC

Last updated: July 23, 2010 - 6:41 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom:

1. I am using SQL*PLUS for windows to query data on a unix oracle database connecting through net8.

Is there a way for me to update a stored procedure in the database. How would you retrieve it for editing.

2. Is there a way in SQL*PLUS to run a statement that backs up all of the stored procedures into a floppy disk?

Thank you,

and Tom said...

1) I use a script called getcode:

set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &1..sql
prompt set define off
select decode( type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1', '/'||chr(10),
null) ||
decode(line,1,'create or replace ', '' ) ||
text text
from user_source
where name = upper('&&1')
order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100


Just run:

SQL> @getcode foo
SQL> edit foo
SQL> @foo

and you'll have retrieved a procedure/package/function named foo, edited it, and updated it in the database.....


2) I have a script, getallcode:

set termout off
set heading off
set feedback off
set linesize 50
spool xtmpx.sql
select '@getcode ' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
/
spool off
spool getallcode_INSTALL
select '@' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
/
spool off
set heading on
set feedback on
set linesize 130
set termout on
@xtmpx.sql
---------------------------------------------------------------

that will put into the CURRENT WORKING DIRECTORY all of your code in separate files with one extra script getallcode_INSTALL that will rerun the code.



Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Mark, January 19, 2002 - 2:20 pm UTC

But how does getallcode_INSTALL install successfully into a new schema if there are inter-dependencies between any of the functions/procedures/packages?

Tom Kyte
January 19, 2002 - 5:49 pm UTC

I've never ever worried about that (and often wonder why people are so obsessed with this) since they will compile all by themselves the very next time.

If you would like a bullet proof way to make sure they compile "cleanly", see my book -- in there I have a routine that'll do a "compile schema" without any problems in all circumstances. Also, dbms_utility has a compile schema which works most of the time.

I jsut never worry or even think about it.

SQL*PLUS

Munzer, January 19, 2002 - 2:35 pm UTC

Tom:

Also, do you know why when I do SETLINESIZE 1500 in case i have a large record to see a columnar list of all columns , SQL*PLUS sometimes cuts it out at the right even though it did not display 1500 characters yet.

Any ideas?


Tom Kyte
January 19, 2002 - 5:50 pm UTC

is sqlplus "cutting it off" or is your terminal emulator. Give me some method to reproduce (i've heard of issues with some versions of the "gui" sqlplusw.exe on windows but as I never use it -- always use sqlplus.exe -- i haven't hit it).

SQL*PLUS

Munzer, January 20, 2002 - 11:59 am UTC

Tom:

Bascially, I have a large table of 20 columns and 2 columns are varchar2(500). when i do select i do select * from tab rather than typing all column names and using substr in some varchar2 columns.

Thanks,

Tom Kyte
January 20, 2002 - 2:41 pm UTC

yes, well that is a statement of fact.

do you mean that this is what causes some truncation? If so, what exactly are the other circumstances like....

o OS
o versions
o executable name (eg: sqlplusw.exe sqlplus.exe sqlplus)
o your linesize setting
o the steps I could take to reproduce this exactly.

details details details -- i need them all.

GETALLCODE script

Munz, February 04, 2002 - 6:31 pm UTC

Tom:

I tried running you getallcode script and it does not do anything. Does it take a long time to run. I waited about 10 minutes and nothing happens on the screen.

Tom Kyte
February 05, 2002 - 8:07 am UTC

depends on the amount of code you have. did you see that if it created the xtmpx.sql script or not? did you look from another session to see if you were idle or not?

Its always worked for me.

edit store procedure

dan malumphy, February 05, 2002 - 1:14 pm UTC

I found this very usefull. I also use the set pause off. Perhaps that is why the previous fellow had a problem

When real coding is to be done...

A reader, February 05, 2002 - 2:51 pm UTC

Very useful scripts for editing one or two bits of code, but when real coding is neeeded, most people I know use one particular green tool reviewed here:
</code> http://www.orafaq.com/tools/index.htm <code>

Tom Kyte
February 05, 2002 - 6:39 pm UTC

geez -- I guess i don't do real coding then.... I use these pretty much all of the time and since I work in lots of places -- I know they always work (i always have these and vi no matter where I go -- pretty much all one needs)...




RE: When real coding is to be done...

Mark A. Williams, February 05, 2002 - 9:19 pm UTC

I find it remarkably amusing that when someone gives a 1 or 2 star rating they are almost always named "A reader". It just seems like heckling from a dark corner in the back of the theater to me....

I wonder, how is "real" coding measured? By the number of lines of code? Is longer better? I can always tell when someone is using that little green tool, because I run my databases with o7_dictionary_accessibility=false. It doesn't take long before I get that phone call...

The guys in our "intranet group" are always telling me I need to get a gui tool -- for what I say? All I need is veritas, oracle, & sun (telnet and sqlplus included all for free thank you very much). We had one consultant in who couldn't start a database without using OEM (literally) - he lasted one-half day. Maybe it is just the Windows generation, but since when is "point & click" a developer and/or DBA requirement?

OK, I'm done with my rant now.

- Mark

SQL*PLUS scripts

Munz, February 06, 2002 - 9:05 pm UTC

Tom:

it is working nicely now. My SQL*PLUS session was hanging.

get code

mo, October 31, 2002 - 12:35 pm UTC

Tom:

I modified the getallcode script to get "triggers" too but it is not working.
it is giving me a file with set define off statement only.

do you know why.

select '@getcode ' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE','TRIGGER' )
/
spool off
spool getallcode_INSTALL
select '@' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE','TRIGGER' )

2. Do I have to get package body to get new code for packages.

3. How can I modify this to get todays changes only. like gettodaycode and then recomile the changes into other instances.

4. How would you best manage the table column changes between development and test and production. Would you just run the samq "alter" statement or you drop the table and export/import. However this may result in screwing up the table constraints. correct?

Tom Kyte
October 31, 2002 - 9:13 pm UTC

1)
see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:803098416492 <code>

for how I extract triggers. triggers are not stored in user_source like procedures and such.

2) huh?

3) filter on LAST_DDL_TIME (where last_ddl_time >= trunc(sysdate) for example)

4) Well, changes need to move from DEV to TEST to PROD. PROD cannot be fixed with "exp/imp" from TEST (maybe test could from dev but PROD -- never)

So, TEST *needs* to supply TEST with a valid UPGRADE script that does alters to add/drop columns and fix data. TEST needs to have been backed up before running such a script so TEST can be restored when said script *fails* (as it will the first time -- murphy rules). When DEV gives TEST a working upgrade script, TEST will check that into the configuration mgmt tool you are using (different from the CM tool your developers are using and they are using one right...) and then release that to PROD for implementation.






getcode

mo, October 31, 2002 - 10:02 pm UTC

Tom:

1. What i meant is do you need to add 'PACKAGE BODY' or the type package will get that automatically.

Also, can i add 'VIEW' here.

THanks

Tom Kyte
November 01, 2002 - 6:33 am UTC

If you run it - you would see that by getting the NAME of the package and then running GETCODE on that name -- I pick up both the spec and body.

You could, but it won't work.

search for

getaview

on this site (or -- better yet, based on the sheer VOLUME of questions you ask -- you better have my book "Expert one on one". I have this all documented in the chapter on export import, you can read all about it -- getting code, triggers, views out of the database to move to another database)



coding in vi.....

Robert, November 01, 2002 - 2:19 pm UTC

>>...and vi no matter where I go -- pretty much all one >>needs

you ARE my hero !




print_table

mo, September 23, 2003 - 4:27 pm UTC

Tom:

How do you get this to work:

exec print_table(select * from table where name=''Mike'' ');

It does not like the single or double quotes in the search name.

Thanks

Tom Kyte
September 23, 2003 - 6:23 pm UTC

exec print_table( 'select * from table where name = ''Mike'' ' );

works fine

ops$tkyte@ORA920LAP> exec print_table( 'select * from dual where dummy = ''X'' ' );
DUMMY                         : X
-----------------

PL/SQL procedure successfully completed.
 

print table

sam, December 19, 2006 - 6:18 pm UTC

Tom:

I have one column that is varchar2(1000). Hoe can I make print table display that in SQL*PLUS for windows.


BEGIN print_Table('select * from parts_email_parameters where request_id=628'); END;

*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "IMSADMIN.PRINT_TABLE", line 38
ORA-06512: at line 1

Tom Kyte
December 20, 2006 - 8:03 am UTC

10gr2 will allow for 32k per line.

right now, you'll have to do the "obvious", edit the code and have it print substrs of the string


ops$tkyte%ORA10GR2> create or replace procedure prt ( p_str in varchar2 )
  2  is
  3     l_str   long := p_str;
  4  begin
  5     loop
  6        exit when l_str is null;
  7        dbms_output.put_line( substr( l_str, 1, 250 ) );
  8        l_str := substr( l_str, 251 );
  9     end loop;
 10  end;
 11  /

Procedure created.
 

print table

sam, December 21, 2006 - 12:54 pm UTC

Tom;

Where do you use this function at in the print_Table code.
Is it in the in the dbms_output.put_line statement?

for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );

dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
end loop;

Tom Kyte
December 22, 2006 - 6:05 am UTC

huh?

what function. I see many function/procedure calls in that snippet, not really sure what you are referring to

or do you mean about the 255 dbms_output.put_line issue, if so, well - yes, you would use P instead of calling dbms_output.put_line.

Muhammad Ali Siddiqui, March 02, 2007 - 2:01 am UTC

Dear Tom, Your site is a valuable place to get all the knowledge and stuff related to oracle database.

I saw on many places that you use print_table but am unable to find script so that i can create the same on my test database.

Can you share the script?

Thanks and God bless you for the work and support that you are giving to DBA's all round the world.

Thanks

Extra Lines

sam, July 19, 2010 - 3:28 pm UTC

Tom:

I use getcode script all the time to move objects from one instance to another.

I noticed though that the code moved into new instance does not look like it was in the source instance.
It adds a lot of blank lines which makes it less readable and not nicely formatted.

What do you do to fix this issue so that code looks identical between source and target?

is there a way to get rid of all those blank lines now or I have to do it manually in every package/procedure.
Tom Kyte
July 23, 2010 - 6:41 am UTC

give me an example to work with first.


ops$tkyte%ORA10GR2> create or replace procedure p
  2  as
  3  begin
  4          dbms_output.put_line( 'this is my procedure' );
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA10GR2> set echo off
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> !cat p.sql<b>
set define off

create or replace procedure p
as
begin
        dbms_output.put_line( 'this is my procedure' );
end;
/
set define on
</b>
ops$tkyte%ORA10GR2>


I'm not aware of what you are talking about.

getcode

A reader, July 23, 2010 - 3:48 pm UTC

Tom:

your example works.

Maybe it has to do with htp.p somehow or "packages" or procedures with "long" lines.

I need to monitor the circumstance it happens under.


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library