Schema Conversion to SQL File
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Schema Conversion to SQL File

  1. #1
    Join Date
    May 2003
    Location
    Bangalore , India
    Posts
    31

    Schema Conversion to SQL File

    Hi,

    I am using oracle 8.1.7 and the os is windows 2000. Is there any posibility to convert the entire objects of the schema including data into a .sql file. Like for a table the sql file will be having insert statement with data to be inserted when it is going to be executed in another schema.... as we have demobld.sql in oracle where it creates the table as well as inserts the value in the table. My requirement is the same. I dont want dump file.

    Thanks in advance...
    Mohit Sharan
    DBA
    Bangalore.
    Cell No : 91-9845211820

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Use DBATool (its free)

    http://www.databee.com/

    Take a full export of you database with rows=n. And this tool will read your dump file and create sql scripts that can be used to create the database and the schema as well.

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Apr 2003
    Posts
    353
    You can get the
    commands required to create the
    schema..

    Export the database without data.

    Import that file with user name and the following parameter

    indexfile=

    The above will generate the commands.

    search and replace the unwanted refs.


    You CAN GENERAGE THE INSERT COMMANDS WITH COMMAND GENERATION.

    REGARDS
    GIRI

  4. #4
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by engiri
    You can get the
    commands required to create the
    schema..

    Export the database without data.

    Import that file with user name and the following parameter

    indexfile=

    The above will generate the commands.

    search and replace the unwanted refs.


    You CAN GENERAGE THE INSERT COMMANDS WITH COMMAND GENERATION.

    REGARDS
    GIRI
    I think a schema contains more than tables and indexes. The Indexfile will give you only the table and index scripts, what about the procedures, packages, views, grants, synonyms, triggers, types etc. Can you get them as well from you indexfile ??
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  5. #5
    Join Date
    May 2003
    Location
    Bangalore , India
    Posts
    31
    Hi Giri,
    how one CAN GENERAGE THE INSERT COMMANDS WITH COMMAND GENERATION? Do u have steps.....?
    Mohit Sharan
    DBA
    Bangalore.
    Cell No : 91-9845211820

  6. #6
    Join Date
    Apr 2003
    Posts
    353
    My correction with Mr.
    adewri is

    The commands also can be generated by..

    export the database with nodata. -->use rows=n

    import the dump file with ignore=n(WHich is default)..
    the failed commands will be logged into the
    log file that you are specifying. (log=....)

    Regarding the insert command .. generating..

    You have to write a procedure..

    step 1:Cursor selects the tables of that schema
    Loop starting
    Step 2:Cursor selects the number of columns in the table.
    Loop
    step 2: For The number of columns generate the required
    columns selection command .
    step 3: accumulate the selection columns into a variable.
    Need to add '' if the field is of type char
    step 4: add to the insert into command with that table name

    Use Dba_tab_columns to find the column list of a table

    regards
    giri

  7. #7
    Join Date
    May 2003
    Location
    Bangalore , India
    Posts
    31
    Hi Giri,

    Thanks a lot for the steps. But, I used TOAD software to generate table data into script.
    Mohit Sharan
    DBA
    Bangalore.
    Cell No : 91-9845211820

  8. #8
    Join Date
    Apr 2003
    Posts
    353
    Toad will also use the pretty same method.
    For your information

  9. #9
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by engiri
    My correction with Mr.
    adewri is

    The commands also can be generated by..

    export the database with nodata. -->use rows=n

    import the dump file with ignore=n(WHich is default)..
    the failed commands will be logged into the
    log file that you are specifying. (log=....)


    The log file contains only the output that comes on screen while doing import or export. And its a less likely that import of valid procedure and package will fail.

    You cannot get the schema objects that way except for tables and indexes. Its not at all reliable if you want to get other schema object creation scripts.
    Last edited by adewri; 06-04-2003 at 07:52 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by engiri
    Regarding the insert command .. generating..

    You have to write a procedure..

    step 1:Cursor selects the tables of that schema
    Loop starting
    Step 2:Cursor selects the number of columns in the table.
    Loop
    step 2: For The number of columns generate the required
    columns selection command .
    step 3: accumulate the selection columns into a variable.
    Need to add '' if the field is of type char
    step 4: add to the insert into command with that table name

    Use Dba_tab_columns to find the column list of a table

    regards
    giri
    Can do without cursors.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

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


Click Here to Expand Forum to Full Width