我想将所有表从特定用户转换为 JSON（或 XML）格式。我读过 SQL Developer 提到的“技巧”。
换句话说，我已经开始创建一个带有两个参数的过程：
作为 IDE，我使用Oracle SQL Developer，我的数据库是Oracle XE Database。
首先，该过程循环遍历给定用户的所有表，并在循环中执行以下操作：
SELECT /*p_format*/ * FROM p_user || '.' || table
不幸的是，我不能使用上面提到的这个 SELECT 语句。我需要使用命令EXECUTE IMMEDIATE <Statement>。
我面临的下一个问题如下：我想输出EXECUTE IMMEDIATE命令的结果。因此我使用了命令EXECUTE IMMEDIATE <Statement> INTO <Variable>. 编译程序并执行后，我偶然发现了以下错误：
"inconsistent datatypes: expected %s got %s"
这是我的程序代码：
CREATE OR REPLACE PROCEDURE EXPORT_TABLE_TO_FORMAT_FROM(p_format VARCHAR2, p_user VARCHAR2) IS
/***************************************************************************
Author:
Class:
School:
Date:
Function - EXPORT_TABLE_TO_JSON_FROM(p_user):
Displays the data of every table from a given User as JSON
Parameter: p_user ... User
***************************************************************************/
v_tableData VARCHAR2(32767);
v_sqlStatement VARCHAR2(200);
BEGIN
FOR tablerec IN (SELECT *
FROM ALL_TABLES
WHERE OWNER = p_user)
LOOP
v_sqlStatement := 'SELECT /*' || p_format || '*/ * FROM ' || p_user || '.' || tablerec.TABLE_NAME;
EXECUTE IMMEDIATE v_sqlStatement INTO v_tableData;
DBMS_OUTPUT.PUT_LINE (v_sqlStatement);
END LOOP;
END;
你可以看到，我虽然环给定用户的所有表和创建一个SQL语句p_format，并p_user与tablerec.TABLE_NAME。
所需的结果应该如下所示：
{"results":[{"columns":[{"name":"COUNTRY_ID","type":"CHAR"},
{"name":"COUNTRY_NAME","type":"VARCHAR2"},{"name":"REGION_ID","type":"NUMBER"}],"items":
[
{"country_id":"AR","country_name":"Argentina","region_id":2},
{"country_id":"AU","country_name":"Australia","region_id":3},
{"country_id":"BE","country_name":"Belgium","region_id":1},
{"country_id":"BR","country_name":"Brazil","region_id":2},
{"country_id":"CA","country_name":"Canada","region_id":2},
{"country_id":"CH","country_name":"Switzerland","region_id":1},
{"country_id":"CN","country_name":"China","region_id":3},
{"country_id":"DE","country_name":"Germany","region_id":1},
{"country_id":"DK","country_name":"Denmark","region_id":1},
{"country_id":"EG","country_name":"Egypt","region_id":4},
{"country_id":"FR","country_name":"France","region_id":1},
{"country_id":"IL","country_name":"Israel","region_id":4},
{"country_id":"IN","country_name":"India","region_id":3},
{"country_id":"IT","country_name":"Italy","region_id":1},
{"country_id":"JP","country_name":"Japan","region_id":3},
{"country_id":"KW","country_name":"Kuwait","region_id":4},
{"country_id":"ML","country_name":"Malaysia","region_id":3},
{"country_id":"MX","country_name":"Mexico","region_id":2},
{"country_id":"NG","country_name":"Nigeria","region_id":4},
{"country_id":"NL","country_name":"Netherlands","region_id":1},
{"country_id":"SG","country_name":"Singapore","region_id":3},
{"country_id":"UK","country_name":"United Kingdom","region_id":1},
{"country_id":"US","country_name":"United States of America","region_id":2},
{"country_id":"ZM","country_name":"Zambia","region_id":4},
{"country_id":"ZW","country_name":"Zimbabwe","region_id":4}]}]}
JSON 提示特定于 SQL Developer 和 SQLcl，而不是直接针对数据库。所以你需要在这些工具中运行整个事情。
最简单的方法是让你的脚本编写一个你可以运行的脚本，例如
spool /tmp/get_all_json.sql
select 'select /*json*/ * from '||table_name||';'
from user_tables;
spool off
@/tmp/get_all_json.sql
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句