RESTful 服务依赖于ORDS（Oracle REST Data Services），所以在进行本实验前，请先确认数据库服务器上的ORDS服务已启动：
$ systemctl status ords
● ords.service - Oracle REST Data Services
Loaded: loaded (/etc/systemd/system/ords.service; disabled; vendor preset: disabled)
Active: active (running) since Thu 2024-05-30 00:47:59 GMT; 4 days ago
Process: 53329 ExecStart=/usr/bin/bash -c /etc/init.d/ords start (code=exited, status=0/SUCCESS)
Main PID: 53458 (java)
Tasks: 0 (limit: 202282)
Memory: 452.0K
CGroup: /system.slice/ords.service
‣ 53458 java -Doracle.dbtools.cmdline.home=/opt/oracle/ords -Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8 -Djav>
登录Database Actions（即Web版的SQL Developer）。
首先需要在表一级启用REST：
再次右键单击对象，此时出现新的菜单cURL Command：
首先试一下GET ALL命令：
$ curl -s --location "http://127.0.0.1:8080/ords/hr/csv_data/"
{"items":[{"col1":"e40a9db6","col2":"1b","col3":27531,"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/hr/csv_data/AAASPYAAMAABv8vAAA"}]},{"col1":"6182c817","col2":"73","col3":5355332,"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/hr/csv_data/AAASPYAAMAABv8vAAB"}]},{"col1":"5ed9e437","col2":"d2","col3":4834758,"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/hr/csv_data/AAASPYAAMAABv8vAAC"}]},{"col1":"d77868a1","col2":"64","col3":2129797,"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/hr/csv_data/AAASPYAAMAABv8vAAD"}]},{"col1":"09bfaa73","col2":"89","col3":3294896,"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/hr/csv_data/AAASPYAAMAABv8vAAE"}]},{"col1":"5054cca2","col2":"97","col3":3474577,"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/hr/csv_data/AAASPYAAMAABv8vAAF"}]},{"col1":"d20e9eb4","col2":"7c","col3":2728720,"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/hr/csv_data/AAASPYAAMAABv8vAAG"}]}],"hasMore":false,"limit":25,"offset":0,"count":7,"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/hr/csv_data/"},{"rel":"edit","href":"http://127.0.0.1:8080/ords/hr/csv_data/"},{"rel":"describedby","href":"http://127.0.0.1:8080/ords/hr/metadata-catalog/csv_data/"},{"rel":"first","href":"http://127.0.0.1:8080/ords/hr/csv_data/"}]}
由输出可知，在URL中使用了rowid作为唯一标识。这也可以从GET Single命令的格式中得到验证。
试一下GET Single命令：
$ curl -s --location "http://127.0.0.1:8080/ords/hr/csv_data/AAASPYAAMAABv8vAAA"
{"col1":"e40a9db6","col2":"1b","col3":27531,"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/hr/csv_data/AAASPYAAMAABv8vAAA"},{"rel":"edit","href":"http://127.0.0.1:8080/ords/hr/csv_data/AAASPYAAMAABv8vAAA"},{"rel":"describedby","href":"http://127.0.0.1:8080/ords/hr/metadata-catalog/csv_data/item"},{"rel":"collection","href":"http://127.0.0.1:8080/ords/hr/csv_data/"}]}
再试下BATCH LOAD命令。在界面中的显示如下：
curl -v -X POST -H "Content-Type: <CONTENT_TYPE>" "http://127.0.0.1:8080/ords/hr/csv_data/batchload" -d "@<FILE_NAME>"
<FILE_NAME>指定为2M.csv，此文件从网上下载：
$ curl -o 2M.csv https://c4u04.objectstorage.us-ashburn-1.oci.customer-oci.com/p/EcTjWk2IuZPZeNnD_fYMcgUhdNDIDA6rt9gaFj_WZMiL7VvxPBNMY60837hu5hga/n/c4u04/b/livelabsfiles/o/developer-library/2M.csv
$ ls -lh 2M.csv
-rw-r--r-- 1 XiaoYu 197121 61M May 30 11:43 2M.csv
<CONTENT_TYPE>为text/csv。
因此命令为：
curl -v -X POST -H "Content-Type:text/csv" "http://127.0.0.1:8080/ords/hr/csv_data/batchload" -d "@2M.csv"
万万没想到，执行出错了：
$ curl -v -X POST -H "Content-Type:text/csv" "http://127.0.0.1:8080/ords/hr/csv_data/batchload" -d "@2M.csv"
Note: Unnecessary use of -X or --request, POST is already inferred.
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0* Trying 127.0.0.1:8080...
* Connected to 127.0.0.1 (127.0.0.1) port 8080
> POST /ords/hr/csv_data/batchload HTTP/1.1
> Host: 127.0.0.1:8080
> User-Agent: curl/8.6.0
> Accept: */*
> Content-Type:text/csv
> Content-Length: 59130062
> Expect: 100-continue
>
< HTTP/1.1 100 Continue
} [65536 bytes data]
90 56.3M 0 0 90 51.2M 0 69.1M --:--:-- --:--:-- --:--:-- 69.0M* We are completely uploaded and fine
100 56.3M 0 0 100 56.3M 0 18.7M 0:00:03 0:00:03 --:--:-- 18.7M< HTTP/1.1 200 OK
< Content-Type: text/plain
< Transfer-Encoding: chunked
<
{ [193 bytes data]
* Leftovers after chunking: 11 bytes
100 56.3M 0 182 100 56.3M 55 17.2M 0:00:03 0:00:03 --:--:-- 17.2M#ERROR Column in header row COL3e40a9db6 is not defined for table.
#INFO Number of rows processed: 0
#INFO Number of rows in error: 0
#INFO No rows committed
SEVERE: Load terminated
* Connection #0 to host 127.0.0.1 left intact
把-d选项改为--data-binary就好了：
$ file 2M.csv
2M.csv: ASCII text, with CRLF line terminators
$ curl --write-out '%{time_total}' -X POST --data-binary "@2M.csv" -H "Content-Type: text/csv" http://localhost:8080/ords/hr/csv_data/batchload
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 60.3M 0 170 100 60.3M 5 1948k 0:00:31 0:00:31 --:--:-- 2038k#INFO Number of rows processed: 2,097,148
#INFO Number of rows in error: 0
#INFO Last row processed in final committed batch: 2,097,148
SUCCESS: Processed without errors
31.735870
参考
- 4.1 About RESTful Web Services
- Oracle LiveLabs: ORDS: Build Powerful, Secure, RESTful ORDS APIs for Your Oracle Autonomous Database
- REST Enable Schema Aliases and SQL Developer Web
- cURL vs Python: loading data from a file via Oracle REST API
