文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >在oracle12c中使用JSON_VALUE中的11000多个字符使用JSON

问在oracle12c中使用JSON_VALUE中的11000多个字符使用JSON
EN

Stack Overflow用户
提问于 2017-01-04 21:11:04
回答 2查看 2.4K关注 0票数 0

我正在Oracle 12c上运行以下代码：

代码语言：javascript
复制
DECLARE
       l_json clob;
       l_var varchar2(90);
       l_query clob;
       mypath clob;
       l_path_value varchar2(100);
BEGIN
    SELECT json_column into l_json from my_table;
    SELECT path_column into l_path_value from my_table;  -- this returns 'watchers'
    mypath := '$.second."'||l_path_value||'"'; -- $.second."watchers"
    l_query := 'select json_value('''||l_json||''', '''||mypath||''' ERROR ON ERROR)  from dual';
    execute Immediate l_query into l_var;
    DBMS_OUTPUT.PUT_LINE('var: '||l_var);
END;

运行此程序后，我得到的结果如下： 错误报告- ORA-40441: JSON语法错误 ORA-06512:在第12行 40441。00000 - "JSON语法错误“ *原因:提供的JavaScript对象符号(JSON)数据无效 语法，无法被解析。 *Action:为JSON数据提供正确的语法。

我预期结果如下：

变量:0

那么，传递的JSON长度有问题吗？

我的儿子看起来：

代码语言：javascript
复制
  {
"first": {
    "private": false,
    "html_url": "https://github.com/mralexgray/Alfred-Google-Translate",
    "description": "Extension for Alfred that will do a Google translate for you",
    "fork": true,
    "url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate",
    "forks_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/forks",
    "keys_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/keys{/key_id}",
    "collaborators_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/collaborators{/collaborator}",
    "teams_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/teams",
    "hooks_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/hooks",
    "issue_events_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues/events{/number}",
    "events_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/events",
    "assignees_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/assignees{/user}",
    "branches_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/branches{/branch}",
    "tags_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/tags",
    "blobs_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/blobs{/sha}",
    "git_tags_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/tags{/sha}",
    "git_refs_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/refs{/sha}",
    "trees_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/trees{/sha}",
    "statuses_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/statuses/{sha}",
    "languages_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/languages",
    "stargazers_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/stargazers",
    "contributors_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/contributors",
    "subscribers_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/subscribers",
    "subscription_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/subscription",
    "commits_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/commits{/sha}",
    "git_commits_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/commits{/sha}",
    "comments_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/comments{/number}",
    "issue_comment_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues/comments{/number}",
    "contents_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/contents/{+path}",
    "compare_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/compare/{base}...{head}",
    "merges_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/merges",
    "archive_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/{archive_format}{/ref}",
    "downloads_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/downloads",
    "issues_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues{/number}",
    "pulls_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/pulls{/number}",
    "milestones_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/milestones{/number}",
    "notifications_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/notifications{?since,all,participating}",
    "labels_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/labels{/name}",
    "releases_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/releases{/id}",
    "deployments_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/deployments",
    "created_at": "2013-06-04T10:45:10Z",
    "updated_at": "2013-06-04T10:45:10Z",
    "pushed_at": "2013-01-12T19:39:03Z",
    "git_url": "git://github.com/mralexgray/Alfred-Google-Translate.git",
    "ssh_url": "git@github.com:mralexgray/Alfred-Google-Translate.git",
    "clone_url": "https://github.com/mralexgray/Alfred-Google-Translate.git",
    "svn_url": "https://github.com/mralexgray/Alfred-Google-Translate",
    "homepage": null,
    "size": 103,
    "stargazers_count": 0,
    "watchers_count": 0,
    "language": "Shell",
    "has_issues": false,
    "has_downloads": true,
    "has_wiki": true,
    "has_pages": false,
    "forks_count": 0,
    "mirror_url": null,
    "open_issues_count": 0,
    "forks": 0,
    "open_issues": 0,
    "watchers": 0,
    "default_branch": "master"
},
"second": {
    "private": false,
    "html_url": "https://github.com/mralexgray/Alfred-Google-Translate",
    "description": "Extension for Alfred that will do a Google translate for you",
    "fork": true,
    "url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate",
    "forks_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/forks",
    "keys_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/keys{/key_id}",
    "collaborators_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/collaborators{/collaborator}",
    "teams_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/teams",
    "hooks_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/hooks",
    "issue_events_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues/events{/number}",
    "events_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/events",
    "assignees_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/assignees{/user}",
    "branches_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/branches{/branch}",
    "tags_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/tags",
    "blobs_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/blobs{/sha}",
    "git_tags_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/tags{/sha}",
    "git_refs_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/refs{/sha}",
    "trees_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/trees{/sha}",
    "statuses_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/statuses/{sha}",
    "languages_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/languages",
    "stargazers_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/stargazers",
    "contributors_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/contributors",
    "subscribers_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/subscribers",
    "subscription_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/subscription",
    "commits_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/commits{/sha}",
    "git_commits_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/commits{/sha}",
    "comments_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/comments{/number}",
    "issue_comment_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues/comments{/number}",
    "contents_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/contents/{+path}",
    "compare_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/compare/{base}...{head}",
    "merges_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/merges",
    "archive_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/{archive_format}{/ref}",
    "downloads_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/downloads",
    "issues_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues{/number}",
    "pulls_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/pulls{/number}",
    "milestones_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/milestones{/number}",
    "notifications_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/notifications{?since,all,participating}",
    "labels_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/labels{/name}",
    "releases_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/releases{/id}",
    "deployments_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/deployments",
    "created_at": "2013-06-04T10:45:10Z",
    "updated_at": "2013-06-04T10:45:10Z",
    "pushed_at": "2013-01-12T19:39:03Z",
    "git_url": "git://github.com/mralexgray/Alfred-Google-Translate.git",
    "ssh_url": "git@github.com:mralexgray/Alfred-Google-Translate.git",
    "clone_url": "https://github.com/mralexgray/Alfred-Google-Translate.git",
    "svn_url": "https://github.com/mralexgray/Alfred-Google-Translate",
    "homepage": null,
    "size": 103,
    "stargazers_count": 0,
    "watchers_count": 0,
    "language": "Shell",
    "has_issues": false,
    "has_downloads": true,
    "has_wiki": true,
    "has_pages": false,
    "forks_count": 0,
    "mirror_url": null,
    "open_issues_count": 0,
    "forks": 0,
    "open_issues": 0,
    "watchers": 0,
    "default_branch": "master"
   }
}
sql
json
oracle
plsql
oracle12c
EN

回答 2

Stack Overflow用户

发布于 2017-01-04 21:42:49

如果您的varchar2(90)对于返回值来说太小，它可能返回null。有关参考，请参阅子句。

票数 0
EN

Stack Overflow用户

发布于 2017-01-05 20:43:55

我不确定它是否会有所帮助--以您已经展示的示例来说，它没有任何区别，我也不确定其他值中的任何控件或多字节字符是否会造成问题--但我会绑定JSON值，而不是隐式转换为字符串和返回：

代码语言：javascript
复制
l_query := 'select json_value(:json, '''||mypath||''' ERROR ON ERROR)  from dual';
execute Immediate l_query into l_var using l_json;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/41473305

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档