{"id":611,"date":"2024-11-04T14:34:50","date_gmt":"2024-11-04T14:34:50","guid":{"rendered":"https:\/\/maboc.nl\/?p=611"},"modified":"2024-11-04T14:37:54","modified_gmt":"2024-11-04T14:37:54","slug":"listagg","status":"publish","type":"post","link":"https:\/\/maboc.nl\/?p=611","title":{"rendered":"listagg()"},"content":{"rendered":"<p>Today I needed to create an audit statement with a lot of users who are excepted from auditing. It looks somewhat like :<\/p>\n<pre>audit policy &lt;policy_name&gt; except &lt;comma separated list of users&gt;<\/pre>\n<p>&nbsp;<\/p>\n<p>For my requirement all Oracle Maintained account should be excepted from auditing.<\/p>\n<p>Following statement made it easy:<\/p>\n<pre><span style=\"font-family: 'courier new', courier, monospace;\">spool policy_on.sql<\/span>\r\n<span style=\"font-family: 'courier new', courier, monospace;\">select 'audit policy &lt;policy_name&gt; except '||names||';'<\/span>\r\n<span style=\"font-family: 'courier new', courier, monospace;\">       from (<\/span>\r\n<span style=\"font-family: 'courier new', courier, monospace;\">              select listagg(name,',') names<\/span>\r\n<span style=\"font-family: 'courier new', courier, monospace;\">              from (<\/span>\r\n<span style=\"font-family: 'courier new', courier, monospace;\">                     select username name<\/span>\r\n<span style=\"font-family: 'courier new', courier, monospace;\">                     from dba_users<\/span>\r\n<span style=\"font-family: 'courier new', courier, monospace;\">                     where oracle_maintained='Y'<\/span>\r\n<span style=\"font-family: 'courier new', courier, monospace;\">                   ) names<\/span>\r\n<span style=\"font-family: 'courier new', courier, monospace;\">            );<\/span>\r\n<span style=\"font-family: 'courier new', courier, monospace;\">spool off<\/span>\r\n\r\n<span style=\"font-family: 'courier new', courier, monospace;\">@policy_on.sql<\/span><\/pre>\n<p>&nbsp;<\/p>\n<p>The statement generates a statement like:<\/p>\n<pre>audit policy nskp20a_audit_policy except SYS,SYSTEM,OUTLN,APPQOSSYS,GSMADMIN_INTERNAL,XDB,WMSYS,....;\r\n\r\n\r\n\r\n<\/pre>\n<p>And why do I write this down?\u00a0 Because I did not know the listagg() function, and now I do.<\/p>\n<p>(And yes&#8230;I do know that ther are a lot more possibilities&#8230;this is the one I needed the function for)<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Today I needed to create an audit statement with a lot of users who are excepted from auditing. It looks somewhat like : audit policy &lt;policy_name&gt; except &lt;comma separated list of users&gt; &nbsp; For my requirement all Oracle Maintained account should be excepted from auditing. Following statement made it easy: spool policy_on.sql select &#8216;audit policy [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,49],"tags":[97,24,14],"class_list":["post-611","post","type-post","status-publish","format-standard","hentry","category-oracle","category-sql","tag-listagg","tag-oracle","tag-sql"],"_links":{"self":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/611","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=611"}],"version-history":[{"count":2,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/611\/revisions"}],"predecessor-version":[{"id":613,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/611\/revisions\/613"}],"wp:attachment":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=611"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=611"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=611"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}