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

Question

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;

Why you can not write insert query inside sql file "select.sql"? — Lokoba
– Lokoba, Commented Sep 9, 2020 at 5:36
This will work in SQL*Plus and SQLcl if you place @select.sql in a line on its own and fix the INSERT syntax (either insert...select or insert...values, not both). Is that sufficient? It doesn't work in SQL Developer using F5 ('Run as script'). — William Robertson
– William Robertson, Commented Sep 9, 2020 at 10:21

William Robertson · Accepted Answer · 2020-09-09 20:52:09Z

2

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')

edited Sep 9, 2020 at 20:52

answered Sep 9, 2020 at 10:30

William Robertson

16.2k4 gold badges40 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Littlefoot Over a year ago

Ha! Thank you for showing me that impossible actually is possible.

Littlefoot · Accepted Answer · 2020-09-09 06:16:07Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related