[mka40]

Hi,
I want to create the scripts(code) for all objects in oracle such as tables, views, SPs and triggers any scripts etc individually based on the applications in the Oracle for VSS. We have more than one applications running on the Oracle Server.

Please let me know (Is there any quick way that I can do using scripts and save as their individual names).

Thanks
mka

[anacedent]

use DBMS_METADATA to extract the DDL

[mka40]

anacedent,

Thanks for the reply. I am new to Oracle(basically SQL dba). Where can I get the complete syntax of the DBMS_METADATA?(with all parameters)

Thanks
mka

[LKBrwn_DBA]

1) Do an export for the schema with ROWS=N

exp <uid>/<pw> file=expdata.dmp owner=<schema> rows=n

2) Download DDL Wizard from www.databee.com (FREE).

3) Open the export file with DDL Wizard and generate the code.

[madhavi_m]

Look at the post in this link:
http://www.dbforums.com/showthread.p...35#post3774835

Let me know if you have any questions.

Madhavi

[mka40]

Madhavi,

I have created
file#1 as: (get_object.sql)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Spool &2..&3..&1..sql

Select dbms_metadata.get_ddl('&3','&1','&2' ) from dual;

Spool off;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
File#2 as (bigscript.sql)

Spool bigscript.sql

SELECT '@@get_object ' || OBJECT_NAME || ' ' || OWNER || ' ' || OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION','TRIGGER',’PACKAGE’) AND OWNER NOT IN (‘SYS’,’SYSTEM’);

Spool off;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

These 2 files are located on Windows 2000 server under C:\oracle_vss\

I am trying to execute the file from SQLPLUS prompt (SQL>) on windows 2000 as

@c:\oracle_vss\bigscript.sql
AND
@@c:\oracle_vss\bigscript.sql

I am getting the error as follows:
SP2-0310: unable to open file "c:\oracle_vss\bigscript.sql"

Please let me know what is missing or what is wrong with my code?.

Thanks in advance.
mka

[madhavi_m]

Are you trying to execute from the command prompt?

Try checking the file...what it contains?

First you try to execute in sqlplus and see, its working or not?

[beilstwh]

If the files are really in c:\oracle_vss on the server, where are you starting up sql#plus. It MUST be on the server, or the scripts should be on your workstation is you are running it from there.

[mka40]

I try to execute in sqlplus as

SQL>@c:\oracle_vss\bigscript.sql

I got error as follows:
SP2-0310: unable to open file "c:\oracle_vss\bigscript.sql"

Also I tried
SQL>@@c:\oracle_vss\bigscript.sql

I got same error as follows:
SP2-0310: unable to open file "c:\oracle_vss\bigscript.sql"

I don't know what is wrong?.
Thanks
mka

[madhavi_m]

You do the following In sqlplus:

On the Menu

File-->open--> go to the location where your bigscript is, then try to open the file and then execute.

You just say
SQL>@bigscript

[mka40]

Hi,

1. On the Menu , if I do same as you said, I am getting the error as follows:
ERROR at line 1:
ORA-00900: invalid SQL statement

(File-->open--> go to the location where your bigscript is, then try to open the file and then execute.)

2. If I execute the following, the file bigscript.sql got wipedout.(this command removes the contents of the file bigscript.sql)

(You just say
SQL>@bigscript)

[madhavi_m]

When you open the file, it should like this:

SQL>SELECT '@@getObject ' || OBJECT_NAME || ' ' || OWNER || ' ' || OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN ('PROCEDURE') AND OWNER NOT IN ('SYS','SYSTEM');
@@getObject PROC1 MM63063 PROCEDURE
@@getObject SET_ALL_RANKS MM63063 PROCEDURE
@@getObject SET_ALL_RANKS2 MM63063 PROCEDURE
@@getObject SET_ALL_RANKS3 MM63063 PROCEDURE
@@getObject SET_ALL_RANKS4 MM63063 PROCEDURE
@@getObject SET_RANK MM63063 PROCEDURE
@@getObject UPDATE_BILL MM63063 PROCEDURE
>SQL Spool Off;
>@bigscript (It should extract the ddl's)


I am not sure, what's going on with you.

Try to do it from the start (in sqlplus):
1) get_object.sql

2) spool bigscript.sql
blah..blah.....
After the select statement, it should give the output as above
SQL>Spool off;

3)@bigscript

[mka40]

madhavi,

I got the bigscript.sql as

@@get_object WM$WORKSPACES_TABLE WMSYS TABLE
@@get_object CONTENTSCHEMAIS XDB FUNCTION
@@get_object DBMS_XDB XDB PACKAGE
@@get_object DBMS_XDBUTIL_INT XDB PACKAGE
@@get_object DBMS_XDBZ XDB PACKAGE
@@get_object DBMS_XDBZ0 XDB PACKAGE
@@get_object DBMS_XDB_VERSION XDB PACKAGE
@@get_object DBMS_XMLDOM XDB PACKAGE
@@get_object DBMS_XMLPARSER XDB PACKAGE
@@get_object DBMS_XMLSCHEMA XDB PACKAGE
@@get_object DBMS_XMLSCHEMA_INT XDB PACKAGE
@@get_object DBMS_XSLPROCESSOR XDB PACKAGE
@@get_object XDB$ACL XDB TABLE

Not as you mentioned below

SQL>SELECT '@@getObject ' || OBJECT_NAME || ' ' || OWNER || ' ' || OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN ('PROCEDURE') AND OWNER NOT IN ('SYS','SYSTEM');
@@getObject PROC1 MM63063 PROCEDURE
@@getObject SET_ALL_RANKS MM63063 PROCEDURE
@@getObject SET_ALL_RANKS2 MM63063 PROCEDURE
@@getObject SET_ALL_RANKS3 MM63063 PROCEDURE
@@getObject SET_ALL_RANKS4 MM63063 PROCEDURE
@@getObject SET_RANK MM63063 PROCEDURE
@@getObject UPDATE_BILL MM63063 PROCEDURE
>SQL Spool Off;

Do I need to include these statements manually? (as below)


SELECT '@@getObject ' || OBJECT_NAME || ' ' || OWNER || ' ' || OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN ('PROCEDURE') AND OWNER NOT IN ('SYS','SYSTEM');

@@get_object CONTENTSCHEMAIS XDB FUNCTION
@@get_object DBMS_XDB XDB PACKAGE
@@get_object DBMS_XDBUTIL_INT XDB PACKAGE
....
....
....


>SQL Spool Off;




AND the output files for all files as

old 1: Select dbms_metadata.get_ddl('&3','&1','&2' ) from dual
new 1: Select dbms_metadata.get_ddl('PACKAGE','DBMS_XDB','XDB' ) from dual
ERROR:
ORA-31603: object "DBMS_XDB" of type PACKAGE not found in schema "XDB"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 628
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

no rows selected


DO you have any idea that I am missing.

Thanks

[LKBrwn_DBA]

By this time you could have done the export, downloaded the FREE DDL Wizard and generated all the scripts you needed!

[mka40]

LKBrwn_DBA,

Can you explain me how to create export files DDL Wizard ?...

Thanks
mka