pl/sql formatter and bulk collect

[rbrooker]

Hi,

i just tried for format this code:

DECLARE
    -- Non-scalar parameters require additional processing
    pi_attrs fadssproc.test.t_attrchanges;
BEGIN
    -- Call the procedure
    SELECT object_name
          ,object_id
          ,status BULK COLLECT
    INTO pi_attrs
    FROM dba_objects
    WHERE rownum < 101;
    fadssproc.test.display_records(pi_attrs => pi_attrs);
END;

and as you can see, the bulk collect is in the wrong place. Can this be fixed please?

 

[Marco Kalter]

We'll fix it!

 

[rbrooker]

thank you `

while you are at it : what about this one?

SELECT a.cdts
      ,nvl(b.count_122, 0) count_122
      ,nvl(b.count_290, 0) count_290
      ,nvl(b.count_123, 0) count_123
      ,nvl(b.count_302, 0) count_302
      ,nvl(b.count_293, 0) count_293
FROM (SELECT trunc(SYSDATE) - LEVEL cdts
      FROM dual
      CONNECT BY LEVEL < (trunc(SYSDATE) - add_months(trunc(SYSDATE), -1)) + 1) a
LEFT OUTER JOIN(WITH tbl AS (SELECT nav_id
                                   ,nav_name
                             FROM explorer_hierarchy
                             WHERE nav_type = 'SERVER')
SELECT a.view_date
      ,SUM(decode(id_application, 122, a.view_count, 0)) count_122
      ,SUM(decode(id_application, 290, a.view_count, 0)) count_290
      ,SUM(decode(id_application, 123, a.view_count, 0)) count_123
      ,SUM(decode(id_application, 302, a.view_count, 0)) count_302
      ,SUM(decode(id_application, 293, a.view_count, 0)) count_293
FROM data__apex_stats_daily@sar a
INNER JOIN tbl b
ON (a.apex_server = b.nav_name)
WHERE b.nav_id = 4
AND id_workspace LIKE 843119617946219
GROUP BY view_date) b
ON (a.cdts = b.view_date)
ORDER BY cdts DESC

it appears that the with-select is in the wrong place when used as an inline view.

thanks again ...

 

[Marco Kalter]

We'll check that out too.