Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2002
    Posts
    22

    Angry Unanswered: I want to create the scripts for all objects in oracle for VSS.

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use DBMS_METADATA to extract the DDL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2002
    Posts
    22
    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

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    1) Do an export for the schema with ROWS=N
    Code:
    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.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Aug 2004
    Posts
    24
    Look at the post in this link:
    http://www.dbforums.com/showthread.p...35#post3774835

    Let me know if you have any questions.

    Madhavi

  6. #6
    Join Date
    Apr 2002
    Posts
    22

    I have problem: SP2-0310: unable to open file "c:\oracle_vss\bigscript.sql

    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

  7. #7
    Join Date
    Aug 2004
    Posts
    24
    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?

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Apr 2002
    Posts
    22

    still error

    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

  10. #10
    Join Date
    Aug 2004
    Posts
    24
    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

  11. #11
    Join Date
    Apr 2002
    Posts
    22
    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)

  12. #12
    Join Date
    Aug 2004
    Posts
    24
    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

  13. #13
    Join Date
    Apr 2002
    Posts
    22
    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

  14. #14
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    By this time you could have done the export, downloaded the FREE DDL Wizard and generated all the scripts you needed!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  15. #15
    Join Date
    Apr 2002
    Posts
    22

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

    LKBrwn_DBA,

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

    Thanks
    mka

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •