You need to provide more explanation about what you are wanting to do.
Many times I have to use a small table to bring data from a schema to another.
What does that mean? What database and version are you talking about?
In Oracle you can just query the data from a schema if you have the SELECT privileges to do so. And you can use CTAS to create a table in YOUR schema that contains that data:
CREATE TABLE myTable AS SELECT * FROM yourSchema.yourTable;
That creates a new table in a schema using data from another schema.
With many schemas and diverse level of access to them (select at least), create multiple dblinks is not an option.
Again - for Oracle that makes no sense because you do NOT use dblinks to access data in different schemas of a database.
So here came the question: Can I extend SQL Dev in some way to get this script from result grid?
Why would you create a script from a result grid?
1. where is the data now? database, schema, table
2. where do you need the data? database, schema, table
3. why do you want to use sql developer to do whatever it is you want to do?
I re-write slightly my original post trying to be clearer. The whole idea is that I have a query with any level of complexity in schema A that dumps a small dataset like this:
1134 REPORT ABCD
1254 REPORT JKLM
2568 REPORT PQRS
I need to use this dataset in schema B.
It would be easy (?) to get the dataset from result grid in schema A using export wizard with INSERT format, and then create a table in schema B and execute INSERTs stataments. Or using CTAS technique in schema B with a dblink to schema A for the SELECT part of CTAS.
The problem is I don't have privileges to create tables nor dblinks, so my only option is to use in schema B a script that mimics a new table.
select '1134' as code, 'REPORT ABCD' as description from dual union
select '1254' as code, 'REPORT JKLM' as description from dual union
select '2568' as code, 'REPORT PQRS' as description from dual;
This script would be a sub-query inside a bigger script that will do some processing.
Can SQL Dev help me to create this script from result grid as an export option?
1 person found this helpful
Maybe you could add a feature request (Oracle SQL Developer Exchange). If more people find it useful, then they'll rate it.
You could ask for a check box next to the "Format" list of the "Export wizard" showed only when the "insert" option is selected. It could allow you to select whether you want to create inserts based on "select .... from dual union" or with "insert into ..."
Thanks God that someone understood what I need!.
Maybe extending to add new export options is ask too much of SQLDev.
So I'll follow your advice and add a feature request in Oracle SQL Developer Exchange.
I'm still thinking that it would be an interesting export option at the same level of 'insert' option.
Having said this, I'll close this thread