Schema Conversion to SQL File

[mohit_sharan]

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...

[adewri]

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

[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

[adewri]

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 ??

[mohit_sharan]

Hi Giri,
how one CAN GENERAGE THE INSERT COMMANDS WITH COMMAND GENERATION? Do u have steps.....?

[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=....)

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

[mohit_sharan]

Hi Giri,

Thanks a lot for the steps. But, I used TOAD software to generate table data into script.

[engiri]

Toad will also use the pretty same method.
For your information

[adewri]

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.

[abhaysk]

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.