Need help with SQL Developer tools

I am using SQL Developer Export Wizard to get the data from tables in production db to a sql file to then export into development db. I exported the data into sql file in the form of INSERT statements but looks like the tables exported are not preceeded by schema name. How can I ensure wizard adds the schema?

Another thing is I have an issue running .sql scripts from SQL Developer.

I tried @"C:\Data\Prod\myscript.sql" and it looks like it is running but then no data shows up in the tables.

Can someone help?
LVL 35
YZlatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>and it looks like it is running but then no data shows up in the tables.

Are there commits in the script?

If not, the inserted data will not be viewable to any other session/connection until it has been committed.

>>How can I ensure wizard adds the schema?

Can't help with that.  I don't use that tool.

I would just connect to the schema that owns the objects being inserted into.

Maybe edit the file and do a global search/replace?
0
YZlatAuthor Commented:
I am trying to view the data in the same session
0
slightwv (䄆 Netminder) Commented:
I'm not a sql developer person but how can you be executing a script and query a table at the same time in the same session?  Seems like two things at the exact same time...

I'm thinking that if you query the v$session table, you will see two sessions.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

YZlatAuthor Commented:
slightwv, I am having a problem opening a large .sql file with SQL Developer. When I go to File->Open and select my file, I am getting an error "An error occurred while opening file
java.io.IOException: exception loading C:\Data\Prod\myscript.sql
0
YZlatAuthor Commented:
Before, when I was trying to run the file using:
 @"C:\Data\Prod\myscript.sql"

I put SELECT * FROM Table1  in the same window, the same session. It didn't return anything
0
slightwv (䄆 Netminder) Commented:
>>I am having a problem opening a large .sql file with SQL Developer

Maybe that's the reason I don't use SQL Developer?  There might be a file size limitation on it.  I don't know.  You'll have to go to the docs or open an SR with Oracle Support.

>>he file using: ... I put SELECT * FROM Table1  in the same window, the same session

Maybe the script wasn't actually executing?  Did you 'execute' it?

I cannot imagine SQL Developer letting you execute a script then select from the table in the same window and it runs in the same session.

Think about it.  That would really be doing two independent tasks from the same connection.  It really can't be done.
0
YZlatAuthor Commented:
OK, I opened the script and ran it with F5 - run script. it does not appear to run. can you help?
0
slightwv (䄆 Netminder) Commented:
>>can you help?

Sure.  Don't use SQL Developer.  Use sqlplus.

I've not done much with SQL Developer.

Per the docs, it looks like you can execute a script file.

See if you are following these steps from the docs:
http://docs.oracle.com/cd/E12151_01/doc.150/e12152/intro.htm#CHDIJIEF

If that doesn't help, can you post the first 10 or so lines from the script file?
0
Gerwin Jansen, EE MVETopic Advisor Commented:
>> @"C:\Data\Prod\myscript.sql"
How large is this file?

I never had Java IO exceptions in SQL Developer and I've been working with quite large sheets.

<edit>
Can you post some lines from the script here?
0
YZlatAuthor Commented:
problem solved - I used sqlplus with spooling instead and found some issues with T-SQL. That's why the file appeared not to run - it would just hang waiting for user input because some fields in t-sql inserts contained ampersand which tells Oracle it's a variable and user input needed. In order to escape ampersands I added

SET DEFINE OFF

to my script
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gerwin Jansen, EE MVETopic Advisor Commented:
I understand you've solved it - great. But what is the T-SQL you're referring to? This hasn't come up in the question you've asked nor the expert's comments, you didn't reply to questions for pieces of your code btw.

Also: the SQL Developer output file would not contain & characters (or they would have been escaped) - I'd suggest that you close this question instead of accepting the answer (yours) as the answer isn't really matching the question.
0
YZlatAuthor Commented:
solved it myself
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.