PL/SQL IntelliSense - package members #32

mickeypearce · 2017-11-29T09:26:53Z

Hello,
Would it be possible to provide package members autocomplition?
(For example when I write package name and dot it would list package members to choose from)
That would be perfect.

zabel-xyz · 2017-11-29T20:52:21Z

Like I say in issue #9, it would be great to have auto-completion, but there is no db connection yet...

My first priority is to finish issue #8, and perhaps after that.

mickeypearce · 2017-11-30T07:25:35Z

I have in mind autocomplition with procedures and functions from package files (./src, not from DB). But having conn with db would be even better.

MadeManMax · 2018-04-08T13:29:44Z

Also would love this functionality.
Basically it could be limited to workspace packages.
The use of DB connection might be slow.

Danieleeee · 2018-05-15T13:07:15Z

Basically it could be limited to workspace packages.
The use of DB connection might be slow.

I do not think that the best solution is to have limited workspace packages.
In order to have the functionality with all the packages/objects I would have to download all the DB, of all the schema, locally.
Oracle is a payable DB with good performance, I do not think waiting times are long...

MadeManMax · 2018-05-16T05:26:01Z

Yes, you are right, Oracle should execute that query fairly quickly but getting all that data may take several seconds. This would be no problem if it happens in background. Nevertheless I would see the need to also pars the workspace packages. I don't want to connect to customer DB and in some cases I don't have that connection or it requires VPN. Best regards Mateusz 2018-05-15 15:07 GMT+02:00 Danieleeee <[email protected]>:

…

Basically it could be limited to workspace packages. The use of DB connection might be slow. I do not think this is the best solution have limited workspace packages. In order to have the functionality with all the packages I would have to download all the DB, of all the schema, locally. Oracle is a payable DB with enough performance, I do not think waiting times are long... — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#32 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AIUSCQTOLYp8RbJg5pa8YedJ8KZOZEf2ks5tytMEgaJpZM4QupvG> .

-- Pozdrawiam Mateusz Żurek

bmarkovic17 · 2018-05-16T06:47:01Z

Hi guys, just wanted to let you know that 5 months ago I implemented the Go to Symbol in Workspace functionality which parses all packages in a workspace (using ctags), but my pull request was never reviewed. This functionality could also be upgraded to implement IntelliSense since I already have list of all packages and symbols inside it.

Kind regards,
Boris

Danieleeee · 2018-05-16T08:53:26Z

Hi @MadeManMax ,
there are many extensions (SQLTolls, mssql,...) that do what I told to you and don't have perfomance problems.
Parserizing all workspace packages would cause more perfermance problems than direct searching in the DB.
Having the DB in local seems to me a workaround.
I do not say that you have to connect to the customer's db, but programming without having a db of development seems to me a strange way to proceed.

zabel-xyz · 2018-05-16T20:19:27Z

Hello everyone,
There is no DB connection yet (as I said, this is the next feature I would like to implement).
Auto-completion with procedures and functions from package files (no DBs) can be a useful feature (when there is no connection).
It's not difficult to implement, so I have already added this feature. I finish some tests and will publish something this weekend.

MadeManMax · 2018-05-16T20:20:04Z

Hi @Danieleeee,
You are probably right.
Your approach would be best.
If we assume that DB connection is workspace related it could work for me.

@bmarkovic17 - that would be nice.

Best regards

MadeManMax · 2018-05-16T20:21:16Z

Hi @zabel-xyz ,
Can't wait to test it.
Thank you

Danieleeee · 2018-05-17T07:42:33Z

Hi @zabel-xyz,
I will wait for the db connection to be implemented.
I can not understand how a db can be related to a pc folder.

Thanks.

Danieleeee · 2018-05-17T09:07:17Z

Hi @zabel-xyz
In plsql.completion.json can you add "tables" ?
We could populate this json with the objects of our DB, without having to transfer all the objects from the DB to the computer folder.

zabel-xyz · 2018-05-20T10:47:44Z

version 1.3.0:

Auto-completion from package files
Custom completion can be defined in a file plsql.completion.json (see doc)

Danieleeee · 2018-05-20T21:04:10Z

Hi,
I tested completion, works great.
There is a small bug, the completion is case sensitive.
I have configured plsql.completion.json with my tables.
If I write table name (SERVIZ) in uppercase it works well:

but in lowercase (serviz) no:

Danieleeee · 2018-05-20T21:06:58Z

Is possible to have another param in plsql.completion.json for method "description"?
You can show it instead of "plsql.completion":

For to enable code completion I have to push ctrl+space, there is a way to aneble it by default?

Thanks

MadeManMax · 2018-05-21T06:41:20Z

Hi,
I've tested "Auto-completion from package files" and it works grate.
I have few remarks:

the package name is not listed. It have to be fully written. It would be grate if package name would be listed in auto compleat
the procedures and functions are presented with the same icon as constants or globals
there is not information about procedure/function parameters or any hint (something like popup you get when you hit Ctrl and hover over procedure name)

Best regards

zabel-xyz · 2018-05-21T07:23:10Z

@Danieleeee

I see, there is a bug with case, I'll fix it.
You can use documentation like I use in test (it's missing in documentation)
You can use character . or ctrl+space for auto-completion

zabel-xyz · 2018-05-21T07:34:13Z

@MadeManMax

Don't sure I understand exactly what you want...
I see, there is a bug with completionItemKind, I'll fix it
hoverProvider is not implemented yet (see issue HoverProvider #24)

MadeManMax · 2018-05-21T08:15:42Z

Hi @zabel-xyz ,

Don't sure I understand exactly what you want...
OK. I might write it to specifically.
For example: you start to write PL/SQL package code and you want to use function xyz from package zxysomething but you don't remember the package name but it is in your workspace. So you start to write zxy and hit ctrl + space and you'd assume that you will see it on IntelliSense but it is not presented.

I don't know if this is part of this functionality.

mickeypearce · 2018-05-21T08:56:00Z

I agree with @MadeManMax, autocompletion of package name itself would be super useful. Thanks @zabel-xyz.

Danieleeee · 2018-05-21T09:07:13Z

Hi @zabel-xyz ,
can you try to increase performance of completion for searching in plsql.completion.json?
I have 158000 rows in plsql.completion.json and it hangs loading "members":

zabel-xyz · 2018-05-21T09:26:27Z

@MadeManMax, @mickeypearce
What you like, is auto-completion for packageName.
To do that I must parse all files of workspace (workpaceSymbol), (I need also to implement a cache).
I suggest you to open a new issue to do that.

zabel-xyz · 2018-05-21T10:03:22Z

@Danieleeee
I don't know what to do... I load the file once in a JS object completion.members and then I use completion.members[text]

Maybe is not a performance issue, but when it's fail, I notice it hangs loading...
I notice this when there is no workspace. In your case I don't know what goes wrong !?

Or it takes time the first times only when loading the file. I can move it at another moment (eg extension activation)

Danieleeee · 2018-05-21T10:35:05Z

@zabel-xyz
I have the workspace, the problem is it.
When I have many packages in workspace and have object in plsql.completion.json it hangs.
With only plsql.completion.json and empty workspace, it works.

Danieleeee · 2018-05-22T08:32:36Z

Yes, you can.
You can assign "kind" values using this list:

class
color
constant
constructor
enum
enumMember
event
field
file
folder
function
interface
keyword
method
module
operator
property
reference
snippet
struct
text
typeParameter
unit
value
variable

MadeManMax · 2018-05-22T08:37:54Z

Thank you @Danieleeee

Danieleeee · 2018-05-22T08:37:56Z

@MadeManMax ,
now I have done two scripts to import the tables and packages (+ procedures and functions) from the DB.
You could incorporate this functionality in your extension in order to have the list of object and tables always updated and not having to download all the objects physically from the DB every time.

MadeManMax · 2018-05-22T09:05:39Z

@Danieleeee ,
Yes I was thinking about similar solution.
If you can please share your scripts.

Thank you.

Danieleeee · 2018-05-22T09:21:35Z

I create a table prova_dr with a clob column where to put results to copy in file "plsql.compleation.json"
As you can see I used 3 schemes (UTE, SIU_INT, ETADBA), so it would be useful to give the possibility to go on more than one scheme at the same time
Update tables :

declare
  v_app              varchar2(1000);
  stringa_superiore  varchar2(32000):= '';
  stringa_colonna    clob:= '';
  log_clob           clob;
  --
  cursor c is Select t.TABLE_NAME,
                '"'||t.TABLE_NAME||'":{ 
                    "kind": "struct",
                    "documentation": "'||c.comments||'",
                       "members": [' stringa_superiore,
       '{"label": "'||t.COLUMN_NAME||'", "kind": "field","documentation":"'||data_type||'('||data_length||')"}' stringa_colonna
                from dba_tab_columns t, all_tab_comments c
               where t.owner in ('UTE', 'SIU_INT','ETADBA') 
               and  t.TABLE_NAME = c.table_name 
             order by TABLE_NAME;
begin
  dbms_lob.createtemporary(log_clob, true);
  for cur in c loop 
     if v_app = cur.TABLE_NAME then  
        stringa_colonna := stringa_colonna||','||CHR(10)||replace(replace (cur.stringa_colonna,CHR(10),'\n'),'"','\"');
     else
       if dbms_lob.getlength(stringa_colonna)>3 then 
          if dbms_lob.getlength(stringa_colonna)>30000 then     
             dbms_lob.writeappend(log_clob, 30000, dbms_lob.substr( stringa_colonna, 30000, 1 ));
             dbms_lob.writeappend(log_clob, dbms_lob.getlength(dbms_lob.substr( stringa_colonna, dbms_lob.getlength(stringa_colonna), 30001 )||CHR(10)||']},'||CHR(10)), dbms_lob.substr( stringa_colonna, dbms_lob.getlength(stringa_colonna), 30001 ) ||CHR(10)||']},'||CHR(10));          
          else
             dbms_lob.writeappend(log_clob, dbms_lob.getlength(stringa_colonna||CHR(10)||']},'||CHR(10)), stringa_colonna||CHR(10)||']},'||CHR(10));  
          end if;
       end if;
       v_app := cur.TABLE_NAME;
       stringa_superiore := cur.stringa_superiore;
       dbms_lob.writeappend(log_clob, length(stringa_superiore), stringa_superiore||CHR(10));
       stringa_colonna := replace(replace (cur.stringa_colonna,CHR(10),'\n'),'"','\"');
     end if;
  end loop;  
  --
  if length(stringa_colonna)>3 then 
     dbms_lob.writeappend(log_clob, length(stringa_colonna||CHR(10)||']}'||CHR(10)), stringa_colonna||CHR(10)||']}'||CHR(10));
  end if;
  --
  insert into prova_dr (parametrizzazione) values (log_clob);
  commit;
  --
  dbms_lob.freetemporary(log_clob);
 dbms_session.free_unused_user_memory;
exception
  when others then
     rollback;
     dbms_lob.freetemporary(log_clob);
 dbms_session.free_unused_user_memory;
     dbms_output.put_line('Errore: '||sqlerrm);
end;

Package (no parameter desc, go to ALL_ARGUMENTS):

declare
  v_app              varchar2(1000);
  stringa_superiore  varchar2(14000):= '';
  stringa_colonna    varchar2(32000):= ''; 
  v_list_procedure   varchar2(32000):= ''; 
  log_clob           clob;
  
  cursor c is Select object_name,
                '"'||object_name||'":{ 
                    "kind": "class",
                    "documentation": "Package '||owner||'\n-----\nlista_di_procedure",
                       "members": [' stringa_superiore,
       '{"label": "'||PROCEDURE_NAME||'", "kind": "property", "documentation": ""}' stringa_colonna
                from all_procedures ui
  where owner in ('UTE','SIU_INT','ETADBA')
  and object_type = 'PACKAGE'    
  order by object_name;
  
begin
  dbms_lob.createtemporary(log_clob, true);
  
  for cur in c loop 
     
     if v_app = cur.object_name then 
        stringa_colonna := stringa_colonna||','||CHR(10)||cur.stringa_colonna;
     else
       if length(stringa_colonna)>3 then 
          dbms_lob.writeappend(log_clob, length(stringa_colonna||CHR(10)||']},'||CHR(10)), stringa_colonna||CHR(10)||']},'||CHR(10));
       end if;
       
       v_app := cur.object_name;
       stringa_superiore := cur.stringa_superiore; 
       
       SELECT RTRIM(XMLAGG(XMLELEMENT(E,PROCEDURE_NAME,'\n').EXTRACT('//text()') ORDER BY PROCEDURE_NAME).GetClobVal(),'\n') AS LIST 
         into v_list_procedure
         FROM all_procedures
        where object_name = cur.object_name 
         and object_type = 'PACKAGE'; 
       
       stringa_superiore := replace(stringa_superiore, 'lista_di_procedure', v_list_procedure);   
       dbms_lob.writeappend(log_clob, length(stringa_superiore), stringa_superiore||CHR(10));
       stringa_colonna := cur.stringa_colonna;
     end if;
     
  end loop;
  --
   dbms_lob.writeappend(log_clob, length(stringa_colonna||CHR(10)||']}'||CHR(10)), stringa_colonna||CHR(10)||']}'||CHR(10));
  --
  insert into prova_dr (parametrizzazione) values (log_clob);
  commit;
  --
  dbms_lob.freetemporary(log_clob);
-- libera tutta la memoria non più utilizzata
 dbms_session.free_unused_user_memory;
exception
  when others then
     rollback;
     dbms_lob.freetemporary(log_clob);
-- libera tutta la memoria non più utilizzata
 dbms_session.free_unused_user_memory;
     dbms_output.put_line('Errore: '||sqlerrm);
end;

Danieleeee · 2018-05-22T09:34:55Z

Fix for table update:

declare
  v_app              varchar2(1000);
  stringa_superiore  varchar2(32000):= '';
  stringa_colonna    clob:= '';
  log_clob           clob;
  --
  cursor c is Select t.TABLE_NAME,
                '"'||t.TABLE_NAME||'":{ 
                    "kind": "struct",
                    "documentation": "'|| replace(replace (c.comments,CHR(10),'\n'),'"','\"')||'",
                       "members": [' stringa_superiore,
       '{"label": "'||t.COLUMN_NAME||'", "kind": "field","documentation":"'||data_type||'('||data_length||')"}' stringa_colonna
                from dba_tab_columns t, all_tab_comments c
               where t.owner in ('UTE', 'SIU_INT','ETADBA') 
               and  t.TABLE_NAME = c.table_name 
             order by TABLE_NAME;
begin
  dbms_lob.createtemporary(log_clob, true);
  for cur in c loop 
     if v_app = cur.TABLE_NAME then  
        stringa_colonna := stringa_colonna||','||cur.stringa_colonna;
     else
       if dbms_lob.getlength(stringa_colonna)>3 then 
          if dbms_lob.getlength(stringa_colonna)>30000 then     
             dbms_lob.writeappend(log_clob, 30000, dbms_lob.substr( stringa_colonna, 30000, 1 ));
             dbms_lob.writeappend(log_clob, dbms_lob.getlength(dbms_lob.substr( stringa_colonna, dbms_lob.getlength(stringa_colonna), 30001 )||CHR(10)||']},'||CHR(10)), dbms_lob.substr( stringa_colonna, dbms_lob.getlength(stringa_colonna), 30001 ) ||CHR(10)||']},'||CHR(10));          
          else
             dbms_lob.writeappend(log_clob, dbms_lob.getlength(stringa_colonna||CHR(10)||']},'||CHR(10)), stringa_colonna||CHR(10)||']},'||CHR(10));  
          end if;
       end if;
       v_app := cur.TABLE_NAME;
       stringa_superiore := cur.stringa_superiore;
       dbms_lob.writeappend(log_clob, length(stringa_superiore), stringa_superiore||CHR(10));
       stringa_colonna := cur.stringa_colonna;
     end if;
  end loop;  
  --
  if length(stringa_colonna)>3 then 
     dbms_lob.writeappend(log_clob, length(stringa_colonna||CHR(10)||']}'||CHR(10)), stringa_colonna||CHR(10)||']}'||CHR(10));
  end if;
  --
  insert into prova_dr (parametrizzazione) values (log_clob);
  commit;
  --
  dbms_lob.freetemporary(log_clob);
 dbms_session.free_unused_user_memory;
exception
  when others then
     rollback;
     dbms_lob.freetemporary(log_clob);
 dbms_session.free_unused_user_memory;
     dbms_output.put_line('Errore: '||sqlerrm);
end;

MadeManMax · 2018-05-22T09:55:27Z

My control query looks like one below

  SELECT t.TABLE_NAME
        ,   '"'
         || t.TABLE_NAME
         || '":{ 
                    "kind": "struct",
                    "documentation": "'
         || c.comments
         || '",
                       "members": ['
         || REPLACE(
                ( SELECT RTRIM(
                              XMLAGG(
                                  XMLELEMENT(
                                      e
                                     ,   TRIM('{"label": "'
                                      || CASE
                                             WHEN cc.Column_name LIKE '% %'
                                             THEN
                                                 '\"' || cc.Column_name || '\"'
                                             ELSE
                                                 cc.Column_name
                                         END
                                      || '", "kind": "field", "documentation": "'
                                      || cc.data_type
                                      || '('
                                      || cc.data_length
                                      || '). '
                                      || com.comments)
                                      || '"}'
                                     ,',').EXTRACT('//text()')).GetClobVal()
                             ,',')
                     FROM all_tab_columns cc, all_col_comments com
                    WHERE cc.column_name = com.column_name
                      AND cc.table_name = com.table_name
                      AND cc.table_name = t.table_name
                      AND cc.Owner = com.owner
                      AND cc.owner = t.owner)
               ,'&quot;'
               ,'"')
         || ']}'
    FROM all_tables t, all_tab_comments c
   WHERE t.table_name = c.table_name
     AND t.owner = c.owner
     AND t.owner IN ('UTE', 'SIU_INT', 'ETADBA')
ORDER BY t.TABLE_NAME;

MadeManMax · 2018-05-22T13:18:20Z

Hi,
I've modified My query so that it returns one clob with all tables, their columns and comments.
I replace few characters so that returned data is valid JSON.
It takes a minute to execute and it may work longer on bigger database.
In my case it returns around 3K tables

SELECT '{'||rtrim(REPLACE(XMLAGG(XMLELEMENT(a,datarow,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),',')||'}' FROM (
  SELECT t.TABLE_NAME
         ,'"'
         || t.TABLE_NAME
         || '":{ 
                    "kind": "struct",
                    "documentation": "'
         ||replace(replace(REPLACE(c.comments,'\','\\'),'"','\"'),CHR(10),'\n')
         || '",
                       "members": ['
         || REPLACE(
                  RTRIM(
                              XMLAGG(
                                  XMLELEMENT(
                                      e
                                     ,   TRIM('{"label": "'
                                      || CASE
                                             WHEN cc.Column_name LIKE '% %'
                                             THEN
                                                 '\"' || cc.Column_name || '\"'
                                             ELSE
                                                 cc.Column_name
                                         END
                                      || '", "kind": "field", "documentation": "'
                                      || cc.data_type
                                      || '('
                                      || cc.data_length
                                      || '). '
                                      || replace(replace(replace(com.comments,'\','\\'),'"','\"'),CHR(10),'\n'))
                                      || '"}'
                                     ,',').EXTRACT('//text()')).GetClobVal()
                             ,','),'&quot;','"')
         || ']}' datarow
    FROM all_tables t, all_tab_comments c, all_tab_columns cc, all_col_comments com
   WHERE t.table_name = c.table_name
     AND t.owner = c.owner
     and cc.column_name = com.column_name
                      AND cc.table_name = com.table_name
                      AND cc.table_name = t.table_name
                      AND cc.Owner = com.owner
                      AND cc.owner = t.owner
     AND t.owner IN ('UTE', 'SIU_INT', 'ETADBA')
     group by t.table_name,c.comments
ORDER BY t.TABLE_NAME)

Danieleeee · 2018-05-22T15:11:09Z

Hi @MadeManMax ,
you select works good!
I made some fix like ' , tab character, CHR(13), indentation of json....
I think @mickeypearce can use this.

SELECT '{'||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,datarow,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||'}' FROM (
  SELECT t.TABLE_NAME
         ,'"'
         || t.TABLE_NAME
         || '":{ 
                    "kind": "struct",
                    "documentation": "'
         ||replace(replace(REPLACE(c.comments,'\','\\'),'"','\"'),CHR(10),'\n')
         || '",
                       "members": ['
         || REPLACE(REPLACE(
                  RTRIM(
                              XMLAGG(
                                  XMLELEMENT(        
                                      e
                                     ,   TRIM('{"label": "'
                                      || CASE
                                             WHEN cc.Column_name LIKE '% %'
                                             THEN
                                                 '\"' || cc.Column_name || '\"'
                                             ELSE
                                                 cc.Column_name
                                         END
                                      || '", "kind": "field", "documentation": "'                                           
                                      || cc.data_type
                                      || '('                      
                                      || cc.data_length             
                                      || ')\n'
                                      || replace(replace(replace(replace(replace(com.comments,'\','\\'),'"','\"'),CHR(10),'\n'),CHR(13),'\r'),'     ','\t')) 
                                      || '"}'
                                     ,','||CHR(10)).EXTRACT('//text()')).GetClobVal()
                             ,','||CHR(10)),'&quot;','"'),'&apos;','''')
         || ']}'||CHR(10) datarow
    FROM all_tables t, all_tab_comments c, all_tab_columns cc, all_col_comments com
   WHERE t.table_name = c.table_name
     AND t.owner = c.owner
     and cc.column_name = com.column_name
                      AND cc.table_name = com.table_name
                      AND cc.table_name = t.table_name
                      AND cc.Owner = com.owner
                      AND cc.owner = t.owner
     AND t.owner IN ('UTE', 'SIU_INT', 'ETADBA')
     group by t.table_name,c.comments
ORDER BY t.TABLE_NAME);

MadeManMax · 2018-05-22T19:32:03Z

Hi @Danieleeee ,
Thank you.
I think that this query needs one additional touch.
Since you are taking data from multiple schema you probably need to add owner to group by statement so that same table name from different schema is not grouped to one row.
maybe the schema name should be added to documentation part.

In my case I'm basically using one schema so I don't have such problem.

Best regards

Danieleeee · 2018-05-23T09:52:38Z

Done,
there are still some problems with duplicate rows.

SELECT '{'||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,datarow,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||'}' 
 FROM (
  SELECT '"'
         || t.TABLE_NAME
         || '":{ 
                    "kind": "struct",
                    "documentation": "'||t.owner||'.'||t.TABLE_NAME||'\n'
         ||replace(replace(REPLACE(c.comments,'\','\\'),'"','\"'),CHR(10),'\n')
         || '",
                       "members": ['
         || REPLACE(REPLACE(
                  RTRIM(
                              XMLAGG(
                                  XMLELEMENT(        
                                      e
                                     ,   TRIM('{"label": "'
                                      || CASE
                                             WHEN cc.Column_name LIKE '% %'
                                             THEN
                                                 '\"' || cc.Column_name || '\"'
                                             ELSE
                                                 cc.Column_name
                                         END
                                      || '", "kind": "field", "documentation": "'                                           
                                      || cc.data_type
                                      || '('                      
                                      || cc.data_length             
                                      || ')\n'                                                                                           
                                      || replace(replace(replace(replace(replace(com.comments,'\','\\'),'"','\"'),CHR(10),'\n'),CHR(13),'\r'),CHR(09),'\t')) 
                                      || '"}'
                                     ,','||CHR(10)).EXTRACT('//text()')).GetClobVal()
                             ,','||CHR(10)),'&quot;','"'),'&apos;','''')
         || ']}'||CHR(10) datarow
    FROM all_tables t, all_tab_comments c, all_tab_columns cc, all_col_comments com
   WHERE t.table_name = c.table_name
     AND t.owner = c.owner
     and cc.column_name = com.column_name
     AND cc.table_name = com.table_name
     AND cc.table_name = t.table_name
     AND cc.Owner = com.owner
     AND cc.owner = t.owner
     AND t.owner IN ('UTE', 'SIU_INT', 'ETADBA')
     group by c.comments,t.owner,t.table_name
ORDER BY t.TABLE_NAME,t.owner);

MadeManMax · 2018-05-23T10:39:36Z

Hi @Danieleeee ,
Are your duplicated rows related to different schema?
You can always prefix table name with owner and then check if you have duplicates.
I test it using below query

SELECT owner,TABLE_NAME, count(*)
 FROM (
  SELECT '"'
         || t.TABLE_NAME
         || '":{ 
                    "kind": "struct",
                    "documentation": "'||t.owner||'.'||t.TABLE_NAME||'\n'
         || replace(replace(replace(replace(replace(c.comments,'\','\\'),'"','\"'),CHR(10),'\n'),CHR(13),'\r'),CHR(09),'\t')
         || '",
                       "members": ['
         || REPLACE(REPLACE(
                  RTRIM(
                              XMLAGG(
                                  XMLELEMENT(        
                                      e
                                     ,   TRIM('{"label": "'
                                      || CASE
                                             WHEN cc.Column_name LIKE '% %'
                                             THEN
                                                 '\"' || cc.Column_name || '\"'
                                             ELSE
                                                 cc.Column_name
                                         END
                                      || '", "kind": "field", "documentation": "'                                           
                                      || cc.data_type
                                      || '('                      
                                      || cc.data_length             
                                      || ')\n'                                                                                           
                                      || replace(replace(replace(replace(replace(com.comments,'\','\\'),'"','\"'),CHR(10),'\n'),CHR(13),'\r'),CHR(09),'\t')
                                      ) 
                                      || '"}'
                                     ,','||CHR(10)).EXTRACT('//text()')).GetClobVal()
                             ,','||CHR(10)),'&quot;','"'),'&apos;','''')
         || ']}'||CHR(10) datarow,t.table_name,t.owner
    FROM all_tables t, all_tab_comments c, all_tab_columns cc, all_col_comments com
   WHERE t.table_name = c.table_name
     AND t.owner = c.owner
     and cc.column_name = com.column_name
     AND cc.table_name = com.table_name
     AND cc.table_name = t.table_name
     AND cc.Owner = com.owner
     AND cc.owner = t.owner
     AND t.owner IN ('UTE', 'SIU_INT', 'ETADBA')
     group by t.owner,t.table_name,c.comments
ORDER BY t.owner,t.TABLE_NAME)
group by owner,TABLE_NAME
having count(*) > 1

Below is slightly modified query. The comments column used for table documentation part also needs to handle special characters.
I also changed order of columns in group by and order by. It might now have any influence on result but I think it is more readable.

SELECT '{'||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,datarow,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||'}' 
 FROM (
  SELECT '"'
         || t.TABLE_NAME
         || '":{ 
                    "kind": "struct",
                    "documentation": "'||t.owner||'.'||t.TABLE_NAME||'\n'
         || replace(replace(replace(replace(replace(c.comments,'\','\\'),'"','\"'),CHR(10),'\n'),CHR(13),'\r'),CHR(09),'\t')
         || '",
                       "members": ['
         || REPLACE(REPLACE(
                  RTRIM(
                              XMLAGG(
                                  XMLELEMENT(        
                                      e
                                     ,   TRIM('{"label": "'
                                      || CASE
                                             WHEN cc.Column_name LIKE '% %'
                                             THEN
                                                 '\"' || cc.Column_name || '\"'
                                             ELSE
                                                 cc.Column_name
                                         END
                                      || '", "kind": "field", "documentation": "'                                           
                                      || cc.data_type
                                      || '('                      
                                      || cc.data_length             
                                      || ')\n'                                                                                           
                                      || replace(replace(replace(replace(replace(com.comments,'\','\\'),'"','\"'),CHR(10),'\n'),CHR(13),'\r'),CHR(09),'\t')
                                      ) 
                                      || '"}'
                                     ,','||CHR(10)).EXTRACT('//text()')).GetClobVal()
                             ,','||CHR(10)),'&quot;','"'),'&apos;','''')
         || ']}'||CHR(10) datarow
    FROM all_tables t, all_tab_comments c, all_tab_columns cc, all_col_comments com
   WHERE t.table_name = c.table_name
     AND t.owner = c.owner
     and cc.column_name = com.column_name
     AND cc.table_name = com.table_name
     AND cc.table_name = t.table_name
     AND cc.Owner = com.owner
     AND cc.owner = t.owner
     AND t.owner IN ('UTE', 'SIU_INT', 'ETADBA')
     group by t.owner,t.table_name,c.comments
ORDER BY t.owner,t.TABLE_NAME);

Danieleeee · 2018-05-23T12:32:21Z

Yes the problem of duplicate rows is with owner.
Make attention, with duplicate rows completion of colums don't work for that table.
I think we have done with tables.
Now we have to do a good query for package.procedures/function.
I have done this, but has goup error (too many XMLAGG):

SELECT '{'||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,datarow,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||'}' 
 FROM (
  SELECT '"'
         || t.object_name
         || '":{ 
                    "kind": "struct",
                    "documentation": "'||t.owner||'.'||t.object_name||'\n'
        -- || replace(replace(replace(replace(replace(c.comments,'\','\\'),'"','\"'),CHR(10),'\n'),CHR(13),'\r'),CHR(09),'\t')
         || '",
                       "members": ['
         || REPLACE(REPLACE(RTRIM( XMLAGG(XMLELEMENT( e, '{"label": "'||a.object_name || '", "kind": "field", "documentation": "'||
              REPLACE(REPLACE(RTRIM( XMLAGG(XMLELEMENT( e, TRIM((a.argument_name||'     '||a.in_out||'  '||a.data_type))
                                     ,','||CHR(10)).EXTRACT('//text()')).GetClobVal()    
                             ,','||CHR(10)),'&quot;','"'),'&apos;','''')||'"},'||CHR(10)).EXTRACT('//text()')).GetClobVal()),'&quot;','"'),'&apos;','''')||CHR(10)
         || ']}'||CHR(10) datarow
    FROM all_procedures t, ALL_ARGUMENTS a
   WHERE t.object_type = 'PACKAGE'
     AND t.owner IN ('UTE')
     and a.OBJECT_ID = t.object_id    
     and a.package_name = t.object_name    
     and a.argument_name is not null
     and a.OBJECT_NAME = t.PROCEDURE_NAME
     and a.OWNER = t.owner
   group by t.owner, t.object_name
ORDER BY t.owner,t.object_name)

Danieleeee · 2018-05-23T15:36:06Z

This works:

SELECT '{'||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,datarow,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||'}' 
 FROM (
  SELECT '"'
         || t.object_name
         || '":{ 
                    "kind": "struct",
                    "documentation": "'||t.owner||'.'||t.object_name||'\n'
        -- || replace(replace(replace(replace(replace(c.comments,'\','\\'),'"','\"'),CHR(10),'\n'),CHR(13),'\r'),CHR(09),'\t')
         || '",
                       "members": ['
         || REPLACE(REPLACE(RTRIM( XMLAGG(XMLELEMENT( e, '{"label": "'||t.PROCEDURE_NAME || '", "kind": "field", "documentation": "'||
              (SELECT REPLACE(REPLACE(RTRIM( XMLAGG(XMLELEMENT( e, TRIM((a.argument_name||'     '||a.in_out||'  '||a.data_type))
                                     ,','||CHR(10)).EXTRACT('//text()')).GetClobVal()    
                             ,','||CHR(10)),'&quot;','"'),'&apos;','''')||'"},'||CHR(10)
                 FROM ALL_ARGUMENTS a  
                where a.package_name = t.object_name                                                    
                  and a.OBJECT_NAME = t.PROCEDURE_NAME              
                  and a.OBJECT_ID = t.object_id             
                  and argument_name is not null
                  and a.OWNER = t.owner
                  group by a.object_name)
             ).EXTRACT('//text()')).GetClobVal()),'&quot;','"'),'&apos;','''')||CHR(10)
         || ']}'||CHR(10) datarow
    FROM all_procedures t
   WHERE t.object_type = 'PACKAGE'
     AND t.owner IN ('UTE')  
   group by t.owner, t.object_name
ORDER BY t.owner,t.object_name);

MadeManMax · 2018-05-24T07:04:59Z

Hi @Danieleeee ,
I've prepared My version of query based on yours.
It should handle most of record type and returns valid JSON.

SELECT '{'||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,procdata,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||'}' FROM (
SELECT '"' || a.package_name || '":{"kind":"class","documentation": "' || a.OWNER||'.'||a.PACKAGE_NAME ||'",'
|| '"members":[' ||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,datarow,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||']}' procdata
 FROM (SELECT a.owner, a.package_name, '{"label": "' || a.object_name || '", "kind": "method", "documentation": "' || 

REPLACE(REPLACE(
                  RTRIM(
                              XMLAGG(
                                  XMLELEMENT(
                                      e
                                     ,CASE WHEN a.ARGUMENT_NAME IS NULL AND IN_OUT = 'OUT'
                                              THEN 'RETURN ' ||
                                                   CASE WHEN a.DATA_TYPE IN ('TABLE','UNDEFINED','OBJECT')
                                                        THEN  RTRIM(a.TYPE_OWNER || CASE WHEN a.TYPE_OWNER       IS NOT NULL THEN '.' ELSE NULL END 
                                                                   || a.TYPE_NAME  || CASE WHEN a.TYPE_NAME        IS NOT NULL THEN '.' ELSE NULL END 
                                                                   || a.TYPE_SUBNAME  || CASE WHEN a.TYPE_SUBNAME  IS NOT NULL THEN '.' ELSE NULL END,'.')
                                                        ELSE CASE WHEN a.DATA_TYPE = 'PL/SQL BOOLEAN' THEN a.PLS_TYPE ELSE a.DATA_TYPE END
                                                    END
                                           WHEN a.ARGUMENT_NAME IS NOT NULL 
                                           THEN a.argument_name||'     '||a.in_out||'  '|| 
                                                CASE WHEN a.DATA_TYPE IN ('TABLE','OBJECT')
                                                     THEN  RTRIM(a.TYPE_OWNER || CASE WHEN a.TYPE_OWNER       IS NOT NULL THEN '.' ELSE NULL END 
                                                                   || a.TYPE_NAME  || CASE WHEN a.TYPE_NAME        IS NOT NULL THEN '.' ELSE NULL END 
                                                                   || a.TYPE_SUBNAME  || CASE WHEN a.TYPE_SUBNAME  IS NOT NULL THEN '.' ELSE NULL END,'.')
                                                     ELSE CASE WHEN a.DATA_TYPE = 'PL/SQL BOOLEAN' THEN a.PLS_TYPE ELSE a.DATA_TYPE END
                                                 END
                                       end
                                     ,','||'\n').EXTRACT('//text()')).GetClobVal()
                             ,','||'\n'),'&quot;','"'),'&apos;','''')|| '"}'||CHR(10) datarow
FROM (SELECT * FROM ALL_ARGUMENTS a
WHERE a.package_name is NOT null
AND a.OWNER = 'UTE'
and a.POSITION = CASE WHEN a.ARGUMENT_NAME IS NULL THEN 0 ELSE a.POSITION END
order by a.package_name, a.object_name, a.SEQUENCE, a.POSITION) a
group by a.owner,a.package_name , a.object_name
) a group by a.owner,a.package_name ) z

Danieleeee · 2018-05-24T07:59:59Z

It works great.
With big DB it produces:

Probably we need a procedure with bulk collect.

MadeManMax · 2018-05-24T08:14:44Z

In this case you can go one step back in this query and try to write script that will compose clobs return by below query. It is returning separate clobs for packages

SELECT '"' || a.package_name || '":{"kind":"class","documentation": "' || a.OWNER||'.'||a.PACKAGE_NAME ||'",'
|| '"members":[' ||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,datarow,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||']}' procdata
 FROM (SELECT a.owner, a.package_name, '{"label": "' || a.object_name || '", "kind": "method", "documentation": "' ||

REPLACE(REPLACE(
                  RTRIM(
                              XMLAGG(
                                  XMLELEMENT(
                                      e
                                     ,CASE WHEN a.ARGUMENT_NAME IS NULL AND IN_OUT = 'OUT'
                                              THEN 'RETURN ' ||
                                                   CASE WHEN a.DATA_TYPE IN ('TABLE','UNDEFINED','OBJECT')
                                                        THEN  RTRIM(a.TYPE_OWNER || CASE WHEN a.TYPE_OWNER       IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_NAME  || CASE WHEN a.TYPE_NAME        IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_SUBNAME  || CASE WHEN a.TYPE_SUBNAME  IS NOT NULL THEN '.' ELSE NULL END,'.')
                                                        ELSE CASE WHEN a.DATA_TYPE = 'PL/SQL BOOLEAN' THEN a.PLS_TYPE ELSE a.DATA_TYPE END
                                                    END
                                           WHEN a.ARGUMENT_NAME IS NOT NULL
                                           THEN a.argument_name||'     '||a.in_out||'  '||
                                                CASE WHEN a.DATA_TYPE IN ('TABLE','OBJECT')
                                                     THEN  RTRIM(a.TYPE_OWNER || CASE WHEN a.TYPE_OWNER       IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_NAME  || CASE WHEN a.TYPE_NAME        IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_SUBNAME  || CASE WHEN a.TYPE_SUBNAME  IS NOT NULL THEN '.' ELSE NULL END,'.')
                                                     ELSE CASE WHEN a.DATA_TYPE = 'PL/SQL BOOLEAN' THEN a.PLS_TYPE ELSE a.DATA_TYPE END
                                                 END
                                       end
                                     ,','||'\n').EXTRACT('//text()')).GetClobVal()
                             ,','||'\n'),'&quot;','"'),'&apos;','''')|| '"}'||CHR(10) datarow
FROM (SELECT * FROM ALL_ARGUMENTS a
WHERE a.package_name is NOT null
AND a.OWNER = 'UTE'
and a.POSITION = CASE WHEN a.ARGUMENT_NAME IS NULL THEN 0 ELSE a.POSITION END
order by a.package_name, a.object_name, a.SEQUENCE, a.POSITION) a
group by a.owner,a.package_name , a.object_name
) a group by a.owner,a.package_name

Danieleeee · 2018-05-24T09:23:54Z

If I execute this select, have the same error.

MadeManMax · 2018-05-24T10:10:01Z

I've try to execute query for all owners on my DB and I don't have this problem.
My biggest package have 3262 arguments.

SELECT owner,package_name, count(*) FROM all_arguments group by owner,package_name
order by count(*)  desc

I have around 3000 packages and generated file contain 32000 lines and around 9M characters.

In google I've found some comment mentioning use of temporary table but I'm not sure that it will help you.
https://community.oracle.com/thread/2354697

MadeManMax · 2018-05-24T10:24:57Z

I've extended the list of data types and I've added handling of overloaded objects

SELECT '{'||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,procdata,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||'}' FROM (
SELECT '"' || a.package_name || '":{"kind":"class","documentation": "' || a.OWNER||'.'||a.PACKAGE_NAME ||'",'
|| '"members":[' ||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,datarow,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||']}' procdata
 FROM (SELECT a.owner, a.package_name, '{"label": "' || a.object_name || '", "kind": "method", "documentation": "' ||
 CASE WHEN a.OVERLOAD IS NOT NULL THEN 'Overloaded ['||a.OVERLOAD||']\n'ELSE NULL END ||
REPLACE(REPLACE(
                  RTRIM(
                              XMLAGG(
                                  XMLELEMENT(
                                      e
                                     ,CASE WHEN a.ARGUMENT_NAME IS NULL AND IN_OUT = 'OUT'
                                              THEN 'RETURN ' ||
                                                   CASE WHEN a.DATA_TYPE IN ('TABLE','OBJECT','PL/SQL TABLE','UNDEFINED','PL/SQL RECORD','VARRAY')
                                                        THEN  RTRIM(a.TYPE_OWNER || CASE WHEN a.TYPE_OWNER       IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_NAME  || CASE WHEN a.TYPE_NAME        IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_SUBNAME  || CASE WHEN a.TYPE_SUBNAME  IS NOT NULL THEN '.' ELSE NULL END,'.')
                                                        ELSE CASE WHEN a.DATA_TYPE = 'PL/SQL BOOLEAN' THEN a.PLS_TYPE ELSE a.DATA_TYPE END
                                                    END
                                           WHEN a.ARGUMENT_NAME IS NOT NULL
                                           THEN a.argument_name||'     '||a.in_out||'  '||
                                                CASE WHEN a.DATA_TYPE IN ('TABLE','OBJECT','PL/SQL TABLE','UNDEFINED','PL/SQL RECORD','VARRAY')
                                                     THEN  RTRIM(a.TYPE_OWNER || CASE WHEN a.TYPE_OWNER       IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_NAME  || CASE WHEN a.TYPE_NAME        IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_SUBNAME  || CASE WHEN a.TYPE_SUBNAME  IS NOT NULL THEN '.' ELSE NULL END,'.')
                                                     ELSE CASE WHEN a.DATA_TYPE = 'PL/SQL BOOLEAN' THEN a.PLS_TYPE ELSE a.DATA_TYPE END
                                                 END
                                       end
                                     ,','||'\n').EXTRACT('//text()')).GetClobVal()
                             ,','||'\n'),'&quot;','"'),'&apos;','''')|| '"}'||CHR(10) datarow
FROM (SELECT * FROM ALL_ARGUMENTS a
WHERE a.package_name is NOT null
AND a.OWNER = 'UTE'
and a.POSITION = CASE WHEN a.ARGUMENT_NAME IS NULL THEN 0 ELSE a.POSITION END
order by a.package_name,a.SUBPROGRAM_ID, a.object_name,TO_NUMBER(a.OVERLOAD), a.SEQUENCE, a.POSITION) a
group by a.owner,a.package_name ,a.SUBPROGRAM_ID, a.object_name, a.OVERLOAD
) a group by a.owner,a.package_name ) z

Danieleeee · 2018-05-24T12:27:12Z

Result of my count is 12855

SELECT owner,package_name, count(*) FROM all_arguments group by owner,package_name
order by count(*)  desc

Danieleeee · 2018-05-24T12:38:39Z

The problem is with the amount of parameters of each procedure.
I inserted a 3500 substr to prevent the 4000 character limit, now it works:

SELECT '{'||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,procdata,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||'}' FROM (
SELECT '"' || a.package_name || '":{"kind":"class","documentation": "' || a.OWNER||'.'||a.PACKAGE_NAME ||'",'
|| '"members":[' ||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,datarow,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||']}' procdata
 FROM (SELECT a.owner, a.package_name, '{"label": "' || a.object_name || '", "kind": "method", "documentation": "' ||
 CASE WHEN a.OVERLOAD IS NOT NULL THEN 'Overloaded ['||a.OVERLOAD||']\n'ELSE NULL END ||
REPLACE(REPLACE(
                  RTRIM(
                             DBMS_LOB.substr(  XMLAGG(
                                  XMLELEMENT(
                                      e
                                     ,CASE WHEN a.ARGUMENT_NAME IS NULL AND IN_OUT = 'OUT'
                                              THEN 'RETURN ' ||
                                                   CASE WHEN a.DATA_TYPE IN ('TABLE','OBJECT','PL/SQL TABLE','UNDEFINED','PL/SQL RECORD','VARRAY')
                                                        THEN  RTRIM(a.TYPE_OWNER || CASE WHEN a.TYPE_OWNER       IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_NAME  || CASE WHEN a.TYPE_NAME        IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_SUBNAME  || CASE WHEN a.TYPE_SUBNAME  IS NOT NULL THEN '.' ELSE NULL END,'.')
                                                        ELSE CASE WHEN a.DATA_TYPE = 'PL/SQL BOOLEAN' THEN a.PLS_TYPE ELSE a.DATA_TYPE END
                                                    END
                                           WHEN a.ARGUMENT_NAME IS NOT NULL
                                           THEN a.argument_name||'     '||a.in_out||'  '||
                                                CASE WHEN a.DATA_TYPE IN ('TABLE','OBJECT','PL/SQL TABLE','UNDEFINED','PL/SQL RECORD','VARRAY')
                                                     THEN  RTRIM(a.TYPE_OWNER || CASE WHEN a.TYPE_OWNER       IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_NAME  || CASE WHEN a.TYPE_NAME        IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_SUBNAME  || CASE WHEN a.TYPE_SUBNAME  IS NOT NULL THEN '.' ELSE NULL END,'.')
                                                     ELSE CASE WHEN a.DATA_TYPE = 'PL/SQL BOOLEAN' THEN a.PLS_TYPE ELSE a.DATA_TYPE END
                                                 END
                                       end
                                     ,','||'\n').EXTRACT('//text()')).GetClobVal(),3500)
                             ,','||'\n'),'&quot;','"'),'&apos;','''')|| '"}'||CHR(10) datarow
FROM (SELECT * FROM ALL_ARGUMENTS a
WHERE a.package_name is NOT null
AND a.OWNER = 'UTE'
and a.POSITION = CASE WHEN a.ARGUMENT_NAME IS NULL THEN 0 ELSE a.POSITION END
order by a.package_name,a.SUBPROGRAM_ID, a.object_name,TO_NUMBER(a.OVERLOAD), a.SEQUENCE, a.POSITION) a
group by a.owner,a.package_name ,a.SUBPROGRAM_ID, a.object_name, a.OVERLOAD
) a group by a.owner,a.package_name ) z

Danieleeee · 2018-05-24T12:47:56Z

I think that with this tow select we don't need workspace parser completion.
Now we have completion for all DB object with good performance.

DB Object:

SELECT '{'||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,procdata,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||'}' FROM (
SELECT '"' || a.package_name || '":{"kind":"class","documentation": "' || a.OWNER||'.'||a.PACKAGE_NAME ||'",'
|| '"members":[' ||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,datarow,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||']}' procdata
 FROM (SELECT a.owner, a.package_name, '{"label": "' || a.object_name || '", "kind": "method", "documentation": "' ||
 CASE WHEN a.OVERLOAD IS NOT NULL THEN 'Overloaded ['||a.OVERLOAD||']\n'ELSE NULL END ||
REPLACE(REPLACE(
                  RTRIM(
                             DBMS_LOB.substr(  XMLAGG(
                                  XMLELEMENT(
                                      e
                                     ,CASE WHEN a.ARGUMENT_NAME IS NULL AND IN_OUT = 'OUT'
                                              THEN 'RETURN ' ||
                                                   CASE WHEN a.DATA_TYPE IN ('TABLE','OBJECT','PL/SQL TABLE','UNDEFINED','PL/SQL RECORD','VARRAY')
                                                        THEN  RTRIM(a.TYPE_OWNER || CASE WHEN a.TYPE_OWNER       IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_NAME  || CASE WHEN a.TYPE_NAME        IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_SUBNAME  || CASE WHEN a.TYPE_SUBNAME  IS NOT NULL THEN '.' ELSE NULL END,'.')
                                                        ELSE CASE WHEN a.DATA_TYPE = 'PL/SQL BOOLEAN' THEN a.PLS_TYPE ELSE a.DATA_TYPE END
                                                    END
                                           WHEN a.ARGUMENT_NAME IS NOT NULL
                                           THEN a.argument_name||'     '||a.in_out||'  '||
                                                CASE WHEN a.DATA_TYPE IN ('TABLE','OBJECT','PL/SQL TABLE','UNDEFINED','PL/SQL RECORD','VARRAY')
                                                     THEN  RTRIM(a.TYPE_OWNER || CASE WHEN a.TYPE_OWNER       IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_NAME  || CASE WHEN a.TYPE_NAME        IS NOT NULL THEN '.' ELSE NULL END
                                                                   || a.TYPE_SUBNAME  || CASE WHEN a.TYPE_SUBNAME  IS NOT NULL THEN '.' ELSE NULL END,'.')
                                                     ELSE CASE WHEN a.DATA_TYPE = 'PL/SQL BOOLEAN' THEN a.PLS_TYPE ELSE a.DATA_TYPE END
                                                 END
                                       end
                                     ,','||'\n').EXTRACT('//text()')).GetClobVal(),3500)
                             ,','||'\n'),'&quot;','"'),'&apos;','''')|| '"}'||CHR(10) datarow
FROM (SELECT * FROM ALL_ARGUMENTS a
WHERE a.package_name is NOT null
AND a.OWNER in ('UTE','SIU_INT','ETADBA')
and a.POSITION = CASE WHEN a.ARGUMENT_NAME IS NULL THEN 0 ELSE a.POSITION END
order by a.package_name,a.SUBPROGRAM_ID, a.object_name,TO_NUMBER(a.OVERLOAD), a.SEQUENCE, a.POSITION) a
group by a.owner,a.package_name ,a.SUBPROGRAM_ID, a.object_name, a.OVERLOAD
) a group by a.owner,a.package_name ) z

DB Table:

SELECT '{'||rtrim(REPLACE(REPLACE(XMLAGG(XMLELEMENT(a,datarow,',').EXTRACT('//text()')).GetClobVal(),'&quot;','"'),'&apos;',''''),',')||'}' 
 FROM (
  SELECT '"'
         || t.TABLE_NAME
         || '":{ 
                    "kind": "struct",
                    "documentation": "'||t.owner||'.'||t.TABLE_NAME||'\n'
         || replace(replace(replace(replace(replace(c.comments,'\','\\'),'"','\"'),CHR(10),'\n'),CHR(13),'\r'),CHR(09),'\t')
         || '",
                       "members": ['
         || REPLACE(REPLACE(
                  RTRIM(
                              XMLAGG(
                                  XMLELEMENT(        
                                      e
                                     ,   TRIM('{"label": "'
                                      || CASE
                                             WHEN cc.Column_name LIKE '% %'
                                             THEN
                                                 '\"' || cc.Column_name || '\"'
                                             ELSE
                                                 cc.Column_name
                                         END
                                      || '", "kind": "field", "documentation": "'                                           
                                      || cc.data_type
                                      || '('                      
                                      || cc.data_length             
                                      || ')\n'                                                                                           
                                      || replace(replace(replace(replace(replace(com.comments,'\','\\'),'"','\"'),CHR(10),'\n'),CHR(13),'\r'),CHR(09),'\t')
                                      ) 
                                      || '"}'
                                     ,','||CHR(10)).EXTRACT('//text()')).GetClobVal()
                             ,','||CHR(10)),'&quot;','"'),'&apos;','''')
         || ']}'||CHR(10) datarow
    FROM all_tables t, all_tab_comments c, all_tab_columns cc, all_col_comments com
   WHERE t.table_name = c.table_name
     AND t.owner = c.owner
     and cc.column_name = com.column_name
     AND cc.table_name = com.table_name
     AND cc.table_name = t.table_name
     AND cc.Owner = com.owner
     AND cc.owner = t.owner
     AND t.owner IN ('UTE', 'SIU_INT', 'ETADBA')
     group by t.owner,t.table_name,c.comments
ORDER BY t.owner,t.TABLE_NAME);

@mickeypearce , can you use this with oradew-vscode extension for update plsql.completion.json?
So we don't need to launch select every week.

zabel-xyz added In progress enhancement and removed In progress labels Nov 29, 2017

zabel-xyz added the In progress label May 12, 2018

zabel-xyz added a commit that referenced this issue May 13, 2018

Add completion for package member #32

0703c34

zabel-xyz added the bug label May 21, 2018

Danieleeee mentioned this issue May 22, 2018

bug completion with workspace + plsql.completion.json #44

Closed

zabel-xyz removed In progress bug labels May 24, 2018

zabel-xyz closed this as completed May 24, 2018

Danieleeee mentioned this issue May 26, 2018

Import DB object metadata mickeypearce/oradew-vscode#2

Closed

PL/SQL IntelliSense - package members #32

PL/SQL IntelliSense - package members #32

Comments

mickeypearce commented Nov 29, 2017

zabel-xyz commented Nov 29, 2017

mickeypearce commented Nov 30, 2017

MadeManMax commented Apr 8, 2018

Danieleeee commented May 15, 2018 • edited Loading

MadeManMax commented May 16, 2018 via email

bmarkovic17 commented May 16, 2018

Danieleeee commented May 16, 2018

zabel-xyz commented May 16, 2018

MadeManMax commented May 16, 2018

MadeManMax commented May 16, 2018

Danieleeee commented May 17, 2018

Danieleeee commented May 17, 2018 • edited Loading

zabel-xyz commented May 20, 2018

Danieleeee commented May 20, 2018 • edited Loading

Danieleeee commented May 20, 2018 • edited Loading

MadeManMax commented May 21, 2018

zabel-xyz commented May 21, 2018

zabel-xyz commented May 21, 2018

MadeManMax commented May 21, 2018

mickeypearce commented May 21, 2018 • edited Loading

Danieleeee commented May 21, 2018 • edited Loading

zabel-xyz commented May 21, 2018

zabel-xyz commented May 21, 2018 • edited Loading

Danieleeee commented May 21, 2018

Danieleeee commented May 22, 2018

MadeManMax commented May 22, 2018

Danieleeee commented May 22, 2018

MadeManMax commented May 22, 2018

Danieleeee commented May 22, 2018 • edited Loading

Danieleeee commented May 22, 2018 • edited Loading

MadeManMax commented May 22, 2018 • edited Loading

MadeManMax commented May 22, 2018

Danieleeee commented May 22, 2018 • edited Loading

MadeManMax commented May 22, 2018

Danieleeee commented May 23, 2018

MadeManMax commented May 23, 2018 • edited Loading

Danieleeee commented May 23, 2018 • edited Loading

Danieleeee commented May 23, 2018 • edited Loading

MadeManMax commented May 24, 2018 • edited Loading

Danieleeee commented May 24, 2018 • edited Loading

MadeManMax commented May 24, 2018

Danieleeee commented May 24, 2018

MadeManMax commented May 24, 2018 • edited Loading

MadeManMax commented May 24, 2018 • edited Loading

Danieleeee commented May 24, 2018

Danieleeee commented May 24, 2018

Danieleeee commented May 24, 2018 • edited Loading

Danieleeee commented May 15, 2018 •

edited

Loading

Danieleeee commented May 17, 2018 •

edited

Loading

Danieleeee commented May 20, 2018 •

edited

Loading

Danieleeee commented May 20, 2018 •

edited

Loading

mickeypearce commented May 21, 2018 •

edited

Loading

Danieleeee commented May 21, 2018 •

edited

Loading

zabel-xyz commented May 21, 2018 •

edited

Loading

Danieleeee commented May 22, 2018 •

edited

Loading

Danieleeee commented May 22, 2018 •

edited

Loading

MadeManMax commented May 22, 2018 •

edited

Loading

Danieleeee commented May 22, 2018 •

edited

Loading

MadeManMax commented May 23, 2018 •

edited

Loading

Danieleeee commented May 23, 2018 •

edited

Loading

Danieleeee commented May 23, 2018 •

edited

Loading

MadeManMax commented May 24, 2018 •

edited

Loading

Danieleeee commented May 24, 2018 •

edited

Loading

MadeManMax commented May 24, 2018 •

edited

Loading

MadeManMax commented May 24, 2018 •

edited

Loading

Danieleeee commented May 24, 2018 •

edited

Loading