asked on

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?

slightwv (䄆 Netminder)

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

YZlat

ASKER

I am trying to view the data in the same session

slightwv (䄆 Netminder)

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.

YZlat

ASKER

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

YZlat

ASKER

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

slightwv (䄆 Netminder)

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

YZlat

ASKER

OK, I opened the script and ran it with F5 - run script. it does not appear to run. can you help?

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gerwin Jansen

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

ASKER CERTIFIED SOLUTION

YZlat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gerwin Jansen

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.

YZlat

ASKER

solved it myself