Snipt.net is closing on December 31st, 2016

For more information, please visit this blog post.

snipt

Ctrl+h for KB shortcuts

PL/pgSQL

Select contacts with more then one email address

1
2
3
4
5
select contact_id, count(info) 
from contact_info 
where info like '%@%' 
group by contact_id
having count(info) > 1
https://snipt.net/embed/e02c3f59b479af5d1607749358643d79/
https://snipt.net/raw/e02c3f59b479af5d1607749358643d79/
e02c3f59b479af5d1607749358643d79
plpgsql
PL/pgSQL
5
2016-11-26T18:47:34
True
False
False
Jul 26, 2016 at 07:26 PM
/api/public/snipt/147470/
select-contacts-with-more-then-one-email-address
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a> <a href="#L-5">5</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">select</span> <span class="n">contact_id</span><span class="p">,</span> <span class="n">count</span><span class="p">(</span><span class="n">info</span><span class="p">)</span> </span><span id="L-2"><a name="L-2"></a><span class="k">from</span> <span class="n">contact_info</span> </span><span id="L-3"><a name="L-3"></a><span class="k">where</span> <span class="n">info</span> <span class="k">like</span> <span class="s1">&#39;%@%&#39;</span> </span><span id="L-4"><a name="L-4"></a><span class="k">group</span> <span class="k">by</span> <span class="n">contact_id</span> </span><span id="L-5"><a name="L-5"></a><span class="k">having</span> <span class="n">count</span><span class="p">(</span><span class="n">info</span><span class="p">)</span> <span class="o">&gt;</span> <span class="mf">1</span> </span></pre></div> </td></tr></table>
Oracle, PLSQL

PL/pgSQL

Select data within a date range

1
2
3
4
SELECT *
FROM campaign_activity
WHERE activity > TO_DATE('07/APR/16 01:00:00', 'DD/MON/YY HH:MI:SS')
AND activity   < TO_DATE('12/APR/16 12:00:00', 'DD/MON/YY HH:MI:SS')
https://snipt.net/embed/930b69e1d3b137bc94afd3ddac8f6c44/
https://snipt.net/raw/930b69e1d3b137bc94afd3ddac8f6c44/
930b69e1d3b137bc94afd3ddac8f6c44
plpgsql
PL/pgSQL
4
2016-12-02T06:01:41
True
False
False
Jul 25, 2016 at 08:40 PM
/api/public/snipt/147466/
select-data-within-a-date-range
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="o">*</span> </span><span id="L-2"><a name="L-2"></a><span class="k">FROM</span> <span class="n">campaign_activity</span> </span><span id="L-3"><a name="L-3"></a><span class="k">WHERE</span> <span class="n">activity</span> <span class="o">&gt;</span> <span class="n">TO_DATE</span><span class="p">(</span><span class="s1">&#39;07/APR/16 01:00:00&#39;</span><span class="p">,</span> <span class="s1">&#39;DD/MON/YY HH:MI:SS&#39;</span><span class="p">)</span> </span><span id="L-4"><a name="L-4"></a><span class="k">AND</span> <span class="n">activity</span> <span class="o">&lt;</span> <span class="n">TO_DATE</span><span class="p">(</span><span class="s1">&#39;12/APR/16 12:00:00&#39;</span><span class="p">,</span> <span class="s1">&#39;DD/MON/YY HH:MI:SS&#39;</span><span class="p">)</span> </span></pre></div> </td></tr></table>
Oracle, PLSQL

PL/pgSQL

Selecting data based on > or < todays date minus days

SELECT CAMPAIGN_ID FROM CAMPAIGN_STATS_V WHERE SENT > SYSDATE-30
https://snipt.net/embed/13400929289ea344066a897bc9ccfa76/
https://snipt.net/raw/13400929289ea344066a897bc9ccfa76/
13400929289ea344066a897bc9ccfa76
plpgsql
PL/pgSQL
1
2016-11-27T05:51:08
True
False
False
/api/public/snipt/147465/
selecting-data-based-on-or-todays-date-minus-days
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="n">CAMPAIGN_ID</span> <span class="k">FROM</span> <span class="n">CAMPAIGN_STATS_V</span> <span class="k">WHERE</span> <span class="n">SENT</span> <span class="o">&gt;</span> <span class="n">SYSDATE</span><span class="o">-</span><span class="mf">30</span> </span></pre></div> </td></tr></table>
Oracle, PLSQL

SQL

Find Grants in tables and views in Oracle

1
2
3
select * from table_privileges 
where grantor = 'schema-name' or grantor = 'schema-name' 
order by table_name asc
https://snipt.net/embed/ec83bb0727088a263737e26ea900b6a8/
https://snipt.net/raw/ec83bb0727088a263737e26ea900b6a8/
ec83bb0727088a263737e26ea900b6a8
sql
SQL
3
2016-12-04T02:41:10
True
False
False
Nov 11, 2012 at 11:32 PM
/api/public/snipt/50058/
find-grants-in-tables-and-views-in-oracle
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="n">table_privileges</span> </span><span id="L-2"><a name="L-2"></a><span class="k">where</span> <span class="n">grantor</span> <span class="o">=</span> <span class="s1">&#39;schema-name&#39;</span> <span class="k">or</span> <span class="n">grantor</span> <span class="o">=</span> <span class="s1">&#39;schema-name&#39;</span> </span><span id="L-3"><a name="L-3"></a><span class="k">order</span> <span class="k">by</span> <span class="k">table_name</span> <span class="k">asc</span> </span></pre></div> </td></tr></table>
Oracle

SQL

See all Oracle errors

select * from all_errors;
https://snipt.net/embed/2e897cd212002978bf945cf925da1946/
https://snipt.net/raw/2e897cd212002978bf945cf925da1946/
2e897cd212002978bf945cf925da1946
sql
SQL
1
2016-12-04T02:41:48
True
False
False
Nov 11, 2012 at 11:31 PM
/api/public/snipt/50057/
see-all-oracle-errors
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="n">all_errors</span><span class="p">;</span> </span></pre></div> </td></tr></table>
Oracle

SQL

Find tables and views by name

1
2
3
select table_name from user_tables;

select view_name from user_views;
https://snipt.net/embed/36ae02198111240cf0b6c2937deb548e/
https://snipt.net/raw/36ae02198111240cf0b6c2937deb548e/
36ae02198111240cf0b6c2937deb548e
sql
SQL
3
2016-12-04T02:42:23
True
False
False
Nov 11, 2012 at 11:31 PM
/api/public/snipt/50056/
find-tables-and-views-by-name
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">select</span> <span class="k">table_name</span> <span class="k">from</span> <span class="n">user_tables</span><span class="p">;</span> </span><span id="L-2"><a name="L-2"></a> </span><span id="L-3"><a name="L-3"></a><span class="k">select</span> <span class="n">view_name</span> <span class="k">from</span> <span class="n">user_views</span><span class="p">;</span> </span></pre></div> </td></tr></table>
Oracle

SQL

Find a text occurrence in Oracle PL/SQL code

1
2
3
SELECT name, line, text
  FROM user_source
 WHERE text LIKE '%someText1%' OR text LIKE '%someText2%';
https://snipt.net/embed/b620a78449db869f5fa16425623bdbd0/
https://snipt.net/raw/b620a78449db869f5fa16425623bdbd0/
b620a78449db869f5fa16425623bdbd0
sql
SQL
3
2016-12-04T02:42:43
True
False
False
Nov 11, 2012 at 11:30 PM
/api/public/snipt/50055/
find-a-text-occurrence-in-oracle-plsql-code
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="n">name</span><span class="p">,</span> <span class="n">line</span><span class="p">,</span> <span class="nb">text</span> </span><span id="L-2"><a name="L-2"></a> <span class="k">FROM</span> <span class="n">user_source</span> </span><span id="L-3"><a name="L-3"></a> <span class="k">WHERE</span> <span class="nb">text</span> <span class="k">LIKE</span> <span class="s1">&#39;%someText1%&#39;</span> <span class="k">OR</span> <span class="nb">text</span> <span class="k">LIKE</span> <span class="s1">&#39;%someText2%&#39;</span><span class="p">;</span> </span></pre></div> </td></tr></table>
Oracle
Copyrighted, illegal, or inappropriate content? Email [email protected].