There seems to be an issue with the LOAD command, where it respects relative paths on the command line but not in SQL Developer or VS Code.
I'm aware I can use the full path for the data file but that doesn't help when someone in the team clones the source code repository into an unexpected location.
I'd be grateful for any assistance getting this working, please. (Or acknowledgement that it's a bug with SQL Developer).
Here is a worked example on macOS Sonoma 14.6.1 and AWS RDS Oracle Database 19.0.0.0.0.
Thanks.
$ mkdir -p /tmp/test/script
$ vi /tmp/test/script/create_table.sql
$ vi /tmp/test/script/test_data.csv
$ vi /tmp/test/script/load_data.sql
$ vi /tmp/test/test.sql
create_table.sql
CREATE TABLE test_table (
id INTEGER
, value VARCHAR2(10)
);
test_data.csv
"ID","VALUE"
"1","one"
load_data.sql
LOAD test_table &1
test.sql
@script/create_table.sql
@script/load_data.sql script/test_data.csv
commit;
Command line output:
$ cd /tmp/test
$ sql /nolog
SQLcl: Release 19.2.1 Production on Thu Aug 29 13:06:15 2024
Copyright (c) 1982, 2024, Oracle. All rights reserved.
SQL> CONNECT ***@***
Password? (**********?) ********************
Connected.
SQL> version
Oracle SQLDeveloper Command-Line (SQLcl) version: 19.2.1.0
SQL> @test.sql
Table TEST_TABLE created.
--Number of rows processed: 2
--Number of rows in error: 0
0 - SUCCESS: Load processed without errors
Commit complete.
SQL> SELECT *
2 FROM test_table;
ID VALUE
---------- ----------
1 one
SQL Developer 23.1.1.345.
- Open
/tmp/test.
- Connect.
- Run Script (F5).
Table TEST_TABLE created.
File script/test_data.csv does not exist
Commit complete.
VS Code 1.92.2 with SQL Developer extension 24.2.1.
- Open
/tmp/test.
- Connect.
- Run Script (F5).
Table is created but the LOAD command neither succeeds nor errors.
Table TEST_TABLE created.
Commit complete.