{"action":"created","issue":{"url":"https://api.github.com/repos/jOOQ/jOOQ/issues/330","repository_url":"https://api.github.com/repos/jOOQ/jOOQ","labels_url":"https://api.github.com/repos/jOOQ/jOOQ/issues/330/labels{/name}","comments_url":"https://api.github.com/repos/jOOQ/jOOQ/issues/330/comments","events_url":"https://api.github.com/repos/jOOQ/jOOQ/issues/330/events","html_url":"https://github.com/jOOQ/jOOQ/issues/330","id":5764920,"number":330,"title":"Add support for PL/SQL data-types","user":{"login":"lukaseder","id":734593,"avatar_url":"https://avatars.githubusercontent.com/u/734593?v=3","gravatar_id":"","url":"https://api.github.com/users/lukaseder","html_url":"https://github.com/lukaseder","followers_url":"https://api.github.com/users/lukaseder/followers","following_url":"https://api.github.com/users/lukaseder/following{/other_user}","gists_url":"https://api.github.com/users/lukaseder/gists{/gist_id}","starred_url":"https://api.github.com/users/lukaseder/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lukaseder/subscriptions","organizations_url":"https://api.github.com/users/lukaseder/orgs","repos_url":"https://api.github.com/users/lukaseder/repos","events_url":"https://api.github.com/users/lukaseder/events{/privacy}","received_events_url":"https://api.github.com/users/lukaseder/received_events","type":"User","site_admin":false},"labels":[{"id":9935133,"url":"https://api.github.com/repos/jOOQ/jOOQ/labels/C:%20DB:%20Oracle","name":"C: DB: Oracle","color":"0000aa","default":false},{"id":9935166,"url":"https://api.github.com/repos/jOOQ/jOOQ/labels/P:%20Medium","name":"P: Medium","color":"ffaaaa","default":false},{"id":9935128,"url":"https://api.github.com/repos/jOOQ/jOOQ/labels/T:%20Enhancement","name":"T: Enhancement","color":"cccccc","default":false}],"state":"open","locked":false,"assignee":{"login":"lukaseder","id":734593,"avatar_url":"https://avatars.githubusercontent.com/u/734593?v=3","gravatar_id":"","url":"https://api.github.com/users/lukaseder","html_url":"https://github.com/lukaseder","followers_url":"https://api.github.com/users/lukaseder/followers","following_url":"https://api.github.com/users/lukaseder/following{/other_user}","gists_url":"https://api.github.com/users/lukaseder/gists{/gist_id}","starred_url":"https://api.github.com/users/lukaseder/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lukaseder/subscriptions","organizations_url":"https://api.github.com/users/lukaseder/orgs","repos_url":"https://api.github.com/users/lukaseder/repos","events_url":"https://api.github.com/users/lukaseder/events{/privacy}","received_events_url":"https://api.github.com/users/lukaseder/received_events","type":"User","site_admin":false},"assignees":[{"login":"lukaseder","id":734593,"avatar_url":"https://avatars.githubusercontent.com/u/734593?v=3","gravatar_id":"","url":"https://api.github.com/users/lukaseder","html_url":"https://github.com/lukaseder","followers_url":"https://api.github.com/users/lukaseder/followers","following_url":"https://api.github.com/users/lukaseder/following{/other_user}","gists_url":"https://api.github.com/users/lukaseder/gists{/gist_id}","starred_url":"https://api.github.com/users/lukaseder/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lukaseder/subscriptions","organizations_url":"https://api.github.com/users/lukaseder/orgs","repos_url":"https://api.github.com/users/lukaseder/repos","events_url":"https://api.github.com/users/lukaseder/events{/privacy}","received_events_url":"https://api.github.com/users/lukaseder/received_events","type":"User","site_admin":false}],"milestone":{"url":"https://api.github.com/repos/jOOQ/jOOQ/milestones/125","html_url":"https://github.com/jOOQ/jOOQ/milestones/Version%203.9.0","labels_url":"https://api.github.com/repos/jOOQ/jOOQ/milestones/125/labels","id":1397345,"number":125,"title":"Version 3.9.0","description":"","creator":{"login":"lukaseder","id":734593,"avatar_url":"https://avatars.githubusercontent.com/u/734593?v=3","gravatar_id":"","url":"https://api.github.com/users/lukaseder","html_url":"https://github.com/lukaseder","followers_url":"https://api.github.com/users/lukaseder/followers","following_url":"https://api.github.com/users/lukaseder/following{/other_user}","gists_url":"https://api.github.com/users/lukaseder/gists{/gist_id}","starred_url":"https://api.github.com/users/lukaseder/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lukaseder/subscriptions","organizations_url":"https://api.github.com/users/lukaseder/orgs","repos_url":"https://api.github.com/users/lukaseder/repos","events_url":"https://api.github.com/users/lukaseder/events{/privacy}","received_events_url":"https://api.github.com/users/lukaseder/received_events","type":"User","site_admin":false},"open_issues":379,"closed_issues":211,"state":"open","created_at":"2015-11-06T09:31:19Z","updated_at":"2016-10-26T21:18:29Z","due_on":null,"closed_at":null},"comments":8,"created_at":"2012-07-22T13:39:38Z","updated_at":"2016-10-27T20:23:24Z","closed_at":null,"body":"Oracle allows for declaring types (RECORD, OBJECT, VARRAY, etc) inside a package specification. Those types can then be used in the package's stored procedures.\n\nAdd support for those local types.\n\nThe following query helps discover such types and their actual data types:\n\n``` sql\nWITH plscope_hierarchy\n        AS (SELECT line\n                 , col\n                 , name\n                 , TYPE\n                 , usage\n                 , usage_id\n                 , usage_context_id\n              FROM all_identifiers\n             WHERE     owner = USER\n                   AND object_name = 'PKG'\n                   AND object_type = 'PACKAGE')\nSELECT    LPAD (' ', 3 * (LEVEL - 1))\n       || TYPE\n       || ' '\n       || name\n       || ' ('\n       || usage\n       || ')'\n          identifier_hierarchy\n  FROM plscope_hierarchy\nSTART WITH usage_context_id = 0\nCONNECT BY PRIOR usage_id = usage_context_id\nORDER SIBLINGS BY line, col\n```\n\nIf executed against this package spec here:\n\n``` sql\ncreate or replace package pkg as \n  const1 constant char := 'A';\n  const2 constant char := 'B';\n\n  type rec1 is record (a number(7), b varchar2(50));\nend pkg;\n```\n\nwe'll get:\n\n```\nPACKAGE PKG (DECLARATION)\n   CONSTANT CONST1 (DECLARATION)\n      CONSTANT CONST1 (ASSIGNMENT)\n      SUBTYPE CHAR (REFERENCE)\n   CONSTANT CONST2 (DECLARATION)\n      CONSTANT CONST2 (ASSIGNMENT)\n      SUBTYPE CHAR (REFERENCE)\n   RECORD REC1 (DECLARATION)\n      VARIABLE A (DECLARATION)\n         NUMBER DATATYPE NUMBER (REFERENCE)\n      VARIABLE B (DECLARATION)\n         CHARACTER DATATYPE VARCHAR2 (REFERENCE)\n```\n\nAnother use-case would be to materialise PL/SQL subtypes in this way:\nhttp://docs.oracle.com/database/121/LNPLS/datatypes.htm#LNPLS99933\n\nSee also discussion with Joe Wildish:\nhttps://twitter.com/joewildish/status/547424492495335424\n---\n\nOn a side-note: Oracle 12c seems to allow for using PL/SQL types in SQL (finally!) and thus for serialising them via JDBC:\n\nhttp://stackoverflow.com/a/27562054/521799\n"},"comment":{"url":"https://api.github.com/repos/jOOQ/jOOQ/issues/comments/256758272","html_url":"https://github.com/jOOQ/jOOQ/issues/330#issuecomment-256758272","issue_url":"https://api.github.com/repos/jOOQ/jOOQ/issues/330","id":256758272,"user":{"login":"lukaseder","id":734593,"avatar_url":"https://avatars.githubusercontent.com/u/734593?v=3","gravatar_id":"","url":"https://api.github.com/users/lukaseder","html_url":"https://github.com/lukaseder","followers_url":"https://api.github.com/users/lukaseder/followers","following_url":"https://api.github.com/users/lukaseder/following{/other_user}","gists_url":"https://api.github.com/users/lukaseder/gists{/gist_id}","starred_url":"https://api.github.com/users/lukaseder/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lukaseder/subscriptions","organizations_url":"https://api.github.com/users/lukaseder/orgs","repos_url":"https://api.github.com/users/lukaseder/repos","events_url":"https://api.github.com/users/lukaseder/events{/privacy}","received_events_url":"https://api.github.com/users/lukaseder/received_events","type":"User","site_admin":false},"created_at":"2016-10-27T20:23:24Z","updated_at":"2016-10-27T20:23:24Z","body":"This technique can also resolve `TABLE OF xxx` but not yet `TABLE OF xxx INDEX BY yyy` (specifically, the index type cannot be resolved).\r\n\r\nIn the latter case, we can still resort to the original query, which yields:\r\n\r\n```\r\n   INDEX TABLE PLS_ASSOC_I_N (DECLARATION)\r\n      NUMBER DATATYPE NUMBER (REFERENCE)\r\n         SUBTYPE PLS_INTEGER (REFERENCE)\r\n   INDEX TABLE PLS_ASSOC_I_V (DECLARATION)\r\n      CHARACTER DATATYPE VARCHAR2 (REFERENCE)\r\n         SUBTYPE PLS_INTEGER (REFERENCE)\r\n   ASSOCIATIVE ARRAY PLS_ASSOC_V_N (DECLARATION)\r\n      NUMBER DATATYPE NUMBER (REFERENCE)\r\n         CHARACTER DATATYPE VARCHAR2 (REFERENCE)\r\n   ASSOCIATIVE ARRAY PLS_ASSOC_V_V (DECLARATION)\r\n      CHARACTER DATATYPE VARCHAR2 (REFERENCE)\r\n         CHARACTER DATATYPE VARCHAR2 (REFERENCE)\r\n```"}}
{"actor":{"display_login":"lukaseder"}}