4 Replies Latest reply: Jun 4, 2014 7:48 PM by OPR RSS

    Extending SQLDev - Export as select from dual

    OPR

      Hi SQL Dev Gurus

       

      I read that is posible to extend SQL Dev to add new features, so I present my case for an extension:

      Many times I need a small dataset brought from tables in one schema to be used in another.one.

      With many schemas and diverse level of access to them (select at least), create multiple dblinks is not an option.

      So I use that I call an export as select from dual. Here an example:

      I have a given query that dump this:

       

      CODE  DESCRIPTION

      1134  REPORT ABCD

      1254  REPORT JKLM

      2568  REPORT PQRS

       

      I need to generate a single SQL statement that dumps the same stuff, something like:

       

      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;

       

      Then just copy/paste this SQL statement in the "another" schema and voilà!. The dataset can be used.

      So here comes the question: Can I extend SQL Dev in some way to get this script from result grid?

       

      Thanks in advance

      Oscar

       

      PS: I think this export as select from dual could be a nice option in export wizard

        • 1. Re: Extending SQLDev - Export as select from dual
          rp0428

          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?

           

          • 2. Re: Extending SQLDev - Export as select from dual
            OPR

            Hi

            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:

             

            CODE  DESCRIPTION

            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?

             

            Thanks

            Oscar

            • 3. Re: Extending SQLDev - Export as select from dual
              user502334

              Hi,

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

              • 4. Re: Extending SQLDev - Export as select from dual
                OPR

                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

                 

                Thanks

                Oscar