CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldevelopersql-insert
Jeremy
Jeremy

Reputation: 65

Oracle SQL Developer : Insert into values as select from another sql file

Is it possible to insert the values into Oracle SQL Table from SQL Developer as a result of external script

INSERT
INTO TABLE_A
  (
    ID,
    VERSION
  ) VALUES (@select.sql)

Statement in the file select.sql looks like

SELECT ID, VERSION from TABLE_X;

Upvotes: 0

Views: 1459

Answers (2)

William Robertson
William Robertson

Reputation: 16001

This will not work in SQL Developer 20 using 'Run as script' (F5) (or in a PL/SQL Developer Command window, for that matter). If it has to work from SQL Developer, you are out of luck.

It will work in SQL*Plus and SQLcl if you clean up the syntax and place the call on its own line.

test_insert.sql

insert into table_a
( id
, version )
@@select.sql
/

select.sql

select rownum, 1
from   xmltable('1 to 10')

(note that you have to decide which script contains the terminating ; or /.)

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 9 11:27:40 2020
Version 19.3.0.0.0

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

Last Successful login time: Wed Sep 09 2020 11:26:30 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


SQL> @test_insert

10 rows created.

SQL>

SQL> l
  1  insert into table_a
  2  ( id
  3  , version )
  4  select rownum, 1
  5* from   xmltable('1 to 10')

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142753

The way you put it, I don't think that it is possible.

Here's what might work, if you put some effort in it:

  • if select.sql file is located on a database server, in a directory which is a source for an Oracle directory object, and
  • if user - which is supposed to run this code - has read privilege on that directory, then
  • write a PL/SQL script which would
    • use UTL_FILE to read select.sql, compose a statement (your example shows only one line; I guess there might be complex statements in there) and put it into a local variable
    • alternatively, you could even use select.sql file as an external table with just one column
    • compose another - insert - statement (note that yours is wrong; there shouldn't be the values keyword there)
    • execute it using execute immediate (so yes, this is dynamic SQL)

Would I want to do that? I don't think so, but - you might, now that you know how.

Upvotes: 0

Related Questions