Thanks very much
saved me much time not having to re-write the same script ;)

I did only a very minor change:
spool getallcode_INSTALL
to:
spool getallcode_INSTALL.sql

Best Regards
Yogeeraj
PS. i was wondering there is a similar script that can help me do the same for tables n views. I know there are some tools offering the similar facility but... if you want to automate things they don't help much..

If your stored procedures are not wrapped than may be you are better of by selecting the source code from _dba_source and compiling the output under the new user account.

Tom:

1. I did
exp userid=old_user/pw owner=old_user rows=n

imp userid=new_user/pw fromuser=old_user touser=new_user

and it exported data. Any ideas why?

2. I did it twice in two weeks. The 2nd time it told me that objects already exist and did not overwrite them.

How would you export procedures, functions, and packages only (code only) and overwrite the old ones.

Thank you,

Tom,
i lost all the scripts to create the table structure, procedures, triggers and now i need to install this same application on other location which also requires the database creation. so now i need to export the table structures without rows, packages and function by the method you told but how we export the triggers and will it work on other database if they have the same language.

does any other method to create the same database structure over there

Tom
Thanks for your valuable time.

I am using Oracle 8.1.7 on Linux7.2. Actually my question was that i need to generate the whole database structure through export and import it to our another location without data in tables. I read your another solution which was given to another DBA. By that solution what i got now is that we can export the whole database with

Rows =N
Triggers = y
constraint = y

and it will export the whole structure of the database with packages, procedures,table constraints,table structures, views,Synonyms and triggers without any table data. And at our new location I will create a database only with system tablespace and import the export file. It will automatically create all the tablespaces and datafiles.

Please rectify me if i am wrong
......

But now i have doubts

1)if i give Index =y
than will those indexes will be recreated on the tables without rows.

2) If i have a different directory structure over there can i open the export file in vi editor and change the file locations.

3)Can i export the database from os Linux 7.2 with oracle 8.1.7 and import it on Linux 8 with oracle 8.1.7 or Linux 9.

Hi


I am trying to import a schema into a new database, this schema has packages which references remote objects using database links. The database links are created but the destination database are not up even though I expect the packages get created during import but invalid but I see these messeages in import log file

IMP-00003: ORACLE error 4052 encountered
ORA-04052: error occurred when looking up remote object SF_ADMIN.PB_LOGPRC@PRCI
ORA-00604: error occurred at recursive SQL level 1
ORA-12545: Connect failed because target host or object does not exist
IMP-00017: following statement failed with ORACLE error 4043:
"ALTER PROCEDURE "P_VABS" COMPILE TIMESTAMP '2003-08-26:12:33:01'"
IMP-00003: ORACLE error 4043 encountered
ORA-04043: object P_VABS does not exist

I dont understand this, why the procedure is NOT created? I expect to see this:

5/1 PL/SQL: SQL Statement ignored
5/22 PL/SQL: ORA-04052: error occurred when looking up remote object
LSC.X@LNX101
ORA-00604: error occurred at recursive SQL level 1
ORA-02068: following severe error from LNX101
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

I have done this test in sql*plus

create or replace procedure lsc$dblink01
as
a number;
begin
select 1 into a from x@lnx101;
end;
/

Warning: Procedure created with compilation errors.

lnx101 is DOWN

Errors for PROCEDURE LSC$DBLINK01:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
5/22 PL/SQL: ORA-04052: error occurred when looking up remote object
LSC.X@LNX101.US.ORACLE.COM
ORA-00604: error occurred at recursive SQL level 1
ORA-02068: following severe error from LNX101
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

You see, it gets created but invalid, why this doesnt happen with import? Import first runs CREATE PROCEDURE .... ... as well but doesnt create the procedure!

Hi

I want to share this with you, I asked the last question regarding ORA-04052.

I am not sure if this is correct behaviour but the problem is this, if the procedure has code like this (tab_x is a public synonym pointing to a remote object)

l_var1 tab_x.code_id%type;

then this doesnt even get created

if the procedure has code like this

select 1
into blah
from tab_x
where rownum < 2

then procedure creates but gets invalidated

cheers

Hi

I want to share this again with you.
I mentioned the problem I had with ORA-04052 and PL/SQL codes.
There is another problem not sure if it´s version related.

If export is from 8.1.7.4 and import is 9.2.0.6 then if in the import parfile RESUMABLE is used then we get ORA-04052 for some PL/SQL codes as well.

Cheers

Tom:

I think that getallcode pulls everything defiend as "code" in DB except triggers and views. How can you modify it to pull triggers? or do you have to create a separate script for that.

Tom,

On Oracle 10.2.0.4 on Sunos 5.10

SQL> create or replace package test
  2  is
  3    procedure test;
  4  end;
  5  /

Package created.

SQL> create or replace package body test
  2  is
  3    procedure test
  4    is
  5    begin
  6      null;
  7    end;
  8  end;
  9  /

Package body created.

When I run the original code I get:

create or replace package test
is
  procedure test;
end;
/
create or replace package body test

is
  procedure test
  is
  begin
    null;
  end;
end;
/

i.e there is a blank line after the

create or replace package body

A minor point, I know, but if there is some procedure that logs errors in the application, the error line number against an error logged in production might not match that in a development environment.

I may be missing something obvious, but I have tried amending the code to avoid the insert of the blank line, but to no avail.

Thanks

select text
  from (
select line, type, decode(line,1,'create or replace ', '') || text text from user_source where name = upper( '&&1' )
union all
select 1 line, 'MIDDLE' type, '/' text from dual
where exists(
select line, type, decode(line,1,'create or replace ', '') || text text from user_source where name = upper( '&&1' )
and type = 'PACKAGE BODY'
)
       )
 order by decode(type,'PACKAGE',1,'PACKAGE BODY',3,'MIDDLE',2, /* default */ 1 ), line
/

Hi
Can you tell me where export data stored when exp is used?

Dear Tom,

I have been "playing" around with your getcode and getallcode scripts, but my spooled files are all filled with 4000 column long lines (plus chr(10) and chr(13) I theorized because I get 4002 long lines!?) making my spooled packages all about 10megs big (instead of 60kb when I do a manual copy paste).
After reinserting these huge spooled package files into a schema followed by AGAIN running the getallcode script, it dies with a ORA-01489 error message inside the spooled files (because of that being the output of the sql query).

Since I never ever post anything, (this is my 1st post ever, but I promised I would have my part finished by tomorrow morning, far from it!). Generally just google around other people's questions and answers then combining the data found online, I have spend already 6 hours on this tiny piece of script having tried everything to get rid of, what appears to be, spaces till EOL fillings!

I tried a whole bunch of techniques but nothing appears to work, if you have a simple answer it would be greatly appreciated.

Thanks in advance,
Richard

ps. I have found many others struggling with the same, never leading to an answer on any forum. Consequently, Rate 4 has been given.

set termout off

define e=vi
column E new_value E
column S new_value SETTINGS
select decode( substr( dbms_utility.port_string, 1, 5 ), 'IBMPC', 'vi', 'vi' ) E,
       decode( substr( dbms_utility.port_string, 1, 5 ),
               'IBMPC', 'c:\temp\xtmpx'||to_char(sysdate,'yyyymmddsssss')||'.sql',
                        '/tmp/xtmpx' || to_char(sysdate,'yyyymmddsssss') ||'.sql' ) S
  from dual;
define _editor=&E


set serveroutput on size 1000000

column object_name format a30
column segment_name format a30
column file_name format a40
column name format a30
column file_name format a30
column what format a30 word_wrapped

set trimspool on
set long 5000
set linesize 79
set pagesize 9999

define gname=idle
column global_name new_value gname
select lower(user) || '%' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name
  from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '

column P_TO format a20 word_wrapped
column P_FROM format a20 word_wrapped
column P_SUBJ format a20 word_wrapped
column P_CC format a20 word_wrapped
column P_BCC format a20 word_wrapped
column P_BODY format a40 word_wrapped

column plan_plus_exp format a100
set tab off
set termout on

Thank you very much for your follow up.

I am locally running an XE edition of Oracle. I could only find a GLOGIN.SQL which is EMPTY :) (besides the comments from Oracle -- NAME/DESCRIPTION/USAGE.
Tried it with running your login.sql, no restuls..

Instead, I started from scratch:
dropped all packages, left one object type in the user_source table (which had nothing to do with the getallcode script to begin with, so which I believe to be unaffected by anything previously done).

simple test-----

spool c:/temp/empdump.sql --(yes, it's a windoze)
select * from emp;
spool off

results are good! Just what you expect: the output with decent lines.

now I do this:

spool c:/temp/typedump.sql
select * from user_source where type = 'TYPE';
spool off

and voila: (actually, I shouldn't paste this here... we don't want 5 pages of blanks per rowline.
The column field "TEXT" appears to be utterly damaged, or does the database always fill this field with 4000 characters? (I tried the same adding trimspool on, and others)


NAME TYPE LINE TEXT
------------------------------ ------------ ---- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(I took out the rest as this specific line continues 68x and has 60 characters in width giving a grand total of 4080 character '-' in this header line. (80 times '-' more due to NAME, TYPE, LINE columns I suppose)

Am I leaking somewhat over into another topic here?

Any help is greatly appreciated. I was astonished this morning to see your answer, 10 years of answering questions and still such a fast reply! Thanks!

(Oops, so many tabs open, this review ended up also in the "P11_QUESTION_ID:1317802337341", not my intention at all!! Please delete)

Sorry for the confusing interpretation of the previous example. It was about having 4000 blanks 
whereas normally there would be a newline or C/R immediately after the last character. (I thought 
the heading would be trimmed according to the longest line, of course it doesn't)

One thing is sure, it has nothing to do with your script getcode or getallcode, I see that now 
after running this:

CREATE TABLE TABLE1 
(
  COLUMN1 VARCHAR2(4000) 
);

INSERT INTO TABLE1 VALUES ('Hello World! Once more we meet again');

running script "spool.sql":

column text format a80 word_wrapped   /* next to placing it here I also ran this command in SQL> 
and on a SQL Worksheet in SQL Developer */
set termout off
set heading off
set trimspool on
set feedback off
set linesize 50
spool c:/temp/table.sql
SELECT * FROM TABLE1;
spool off

output file: table1.sql:
Hello World! Once more we meet again                                                                
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
 
So this is where the blanks end again. So yes, max varchar2 is 4000, I realize, but no trimming, no 
column text format (or I run it wrongly, I tested it everywhere, not even with ltrim, trim within 
the SQL query, only "to_clob(column1)" gave me somewhat satisfying result (shortened lines), but 
still...  I sent the same script to my brother in order to check it on his server, to no avail. 
Same results, no, actually he had 1000 characters per line ?? (so he would have set line length 
1000 in his login or so? Would have to ask). Am I the only one getting these long lines without 
being able to trim or limit them? Can't be.

I'm sorry that this leaked over into another topic as I understand now it has nothing to do with 
the origianl script you provided. However, since the script is so useful, more people must have had 
this issue as I am having, so this is an add-on. Therefore, I rate the review Useful - 3 

I recreated the script to work with the UTL FILE package. If I need the files locally, I'll copy 
them from the server.

I don't know what the case is that I can't trim nor limit these lines (maybe hard-code the column 
text format parameter in the glogin.sql). But the script using UTL FILE is doing it's job well, 
thanks to your script I was able to write it. Thank you for your time, very appreciated, even 
inspiring.
My brother wondered whether the same person is still answering these questions after so many years, 
he doubted it. (Tom isn't Tom anymore?) One thing he was sure of: Thanks to this forum "asktom", 
Oracle became a lot more attractive to many people!

I found those scripts to be very useful for extracting packages, procedures, functions etc. Thanks for the same. The following enhanced scripts will all a DBA to extract all those objects for all users from one database to another. This is especially useful after an exp from an older version of the database and imp to a newer version.

1. getallcode.sql
set termout off
set heading off
set feedback off
set linesize 50
spool xtmpx.sql
select '@getcode ' || owner || ' ' || object_name
from all_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
and owner not in ( 'SYS', 'SYSTEM', 'OUTLN', 'ORACLE_OCM', 'DBSNMP' )
/
spool off
spool getallcode_INSTALL.sql
select '@' || object_name
from all_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
and owner not in ( 'SYS', 'SYSTEM', 'OUTLN', 'ORACLE_OCM', 'DBSNMP' )
/
spool off
set heading on
set feedback on
set linesize 130
set termout on
@xtmpx.sql

--

2. getcode.sql
set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &2..sql
prompt set define off
select decode( type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1', '/'||chr(10),
null) ||
case when line=1 then 'CREATE OR REPLACE ' || replace( upper(text), upper( name), owner || '.' || upper(name) )
else text
end
text
from all_source
where name = upper('&&2')
and owner = 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

Saved us a lot of time when we had to copy a database to a fresh schema and get all of the objects! Long story of why a simple expdp and impdp would not work.

the getallcode is great!

Thanks retired Tom!