1

I am trying to import dump file into Oracle using cx_Oracle. I am able to execute command by command. Can any one suggest how to do this?

I'm currently using:

imp servicely/tiger@ubuntu file=/home/hemalatha/hemalatha_data/test_data/oracle/schema_only.sql full=y.

But, I'm getting the error: IMP-00037: Character set marker unknown

Ben
  • 51,770
  • 36
  • 127
  • 149
  • You wouldn't; you'd use `suprocess.call(['impdp', ..., ])` as `impdp` is an executable. – Ben May 26 '14 at 11:12
  • Actually, i am giving the below command. imp servicely/tiger@ubuntu file=/home/hemalatha/hemalatha_data/test_data/oracle/schema_only.sql full=y. But i am getting "IMP-00037: Character set marker unknown" –  May 26 '14 at 11:48
  • That's the command line. That's nothing to do with Python. Are you calling this from Python? – Ben May 26 '14 at 11:52
  • Yes. I am calling from python. can you pls give example how to import oracle dump into oracle, i am getting the error "error: IMP-00037: Character set marker unknown"? –  May 26 '14 at 12:03

1 Answers1

1

I think you are confused about what you actually have.

You say

I am able to execute command by command

and also your supposed import file has a .sql extension. I think you have a script full of SQL statements. You do not have an export file, which is a binary file generated by exp or expdp. imp and impdp are used to import the files generated by exp and expdp respectively. They will act confused if you give them a SQL script to run.

If you have a SQL script to run against the database, use SQL*Plus. Here's a simple SQL*Plus script which doesn't do much:

PROMPT Hello, we are in SQL*Plus

SELECT * FROM DUAL;

Here's a simple Python script to run this script in SQL*Plus and display the output:

import subprocess

script = subprocess.Popen(["sqlplus", "-L", "user/password", "@script.sql"],
                          stderr=subprocess.STDOUT, stdin=subprocess.PIPE,
                          stdout=subprocess.PIPE)
script.stdin.close()  # Force SQL*Plus to exit after reading script.
for line in script.stdout:
    print line.rstrip()

Note that we need to connect to SQL*Plus's input and close it, as otherwise SQL*Plus will not exit and the script will hang.

When I run this, I get the following output:

SQL*Plus: Release 11.2.0.2.0 Production on Mon May 26 14:22:34 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Hello, we are in SQL*Plus

D
-
X

SQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
Luke Woodward
  • 63,336
  • 16
  • 89
  • 104
  • Thank you so much. It's working for me. One more small doubt. How should i export the oracle tables as a .sql dump file which is readable –  May 28 '14 at 13:29
  • @Hemalatha: many Oracle tools, such as SQL Developer, TOAD or PL/SQL Developer, will be able to do this for you. SQL Developer is free, and a question that covers how to export data with it is here: http://stackoverflow.com/questions/2552711/how-to-export-data-with-oracle-sql-developer – Luke Woodward May 29 '14 at 19:00
  • Sorry. I don't want to use the tools for my project, I need to write the code for the exporting oracle dump as a SQL script. If you know the answer, please tell me? –  May 30 '14 at 05:38
  • @Hemalatha: off the top of my head there's no way to do that using just the database. I certainly wouldn't write my own code to do this; I'd use the tools. I imagine it would be possible to write code to do this yourself, using the data dictionary views to find out what columns a table contains and what data type each column contains. However, this is a nontrivial task; please don't expect me to write it for you here. – Luke Woodward May 30 '14 at 19:35