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?
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?
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,
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.
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>
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?
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
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
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
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;
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
March 04, 2007 - 12:51 pm UTC
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.
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.