snipt

Ctrl+h for KB shortcuts

SQL

Insertar xml en Nuestra tabla ficheros(OFICIAL PEISMU)

INSERT INTO FICHEROS F (F.FIC_ID, F.FIC_NODOPRINCIPAL, F.FIC_CONTENIDO, F.CAR_ID) VALUES
(FICHERO_FIC_ID_SEQ.NEXTVAL, 'DICCIONARIO_AGRUPACION_FORMA_FARMA', xmltype(bfilename('DIRECTORIO_XML', 'DICCIONARIO_AGRUPACION_FORMA_FARMA.xml'),nls_charset_id('WE8ISO8859P15')), 1);
https://snipt.net/embed/c99c09c73855b0f598709e733048c45b/
https://snipt.net/raw/c99c09c73855b0f598709e733048c45b/
c99c09c73855b0f598709e733048c45b
sql
SQL
2
2016-09-19T01:22:03
True
False
False
/api/public/snipt/18824/
insertar-xml-en-nuestra-tabla-ficherosoficial-peismu
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">FICHEROS</span> <span class="n">F</span> <span class="p">(</span><span class="n">F</span><span class="p">.</span><span class="n">FIC_ID</span><span class="p">,</span> <span class="n">F</span><span class="p">.</span><span class="n">FIC_NODOPRINCIPAL</span><span class="p">,</span> <span class="n">F</span><span class="p">.</span><span class="n">FIC_CONTENIDO</span><span class="p">,</span> <span class="n">F</span><span class="p">.</span><span class="n">CAR_ID</span><span class="p">)</span> <span class="k">VALUES</span> </span><span id="L-2"><a name="L-2"></a><span class="p">(</span><span class="n">FICHERO_FIC_ID_SEQ</span><span class="p">.</span><span class="n">NEXTVAL</span><span class="p">,</span> <span class="s1">&#39;DICCIONARIO_AGRUPACION_FORMA_FARMA&#39;</span><span class="p">,</span> <span class="n">xmltype</span><span class="p">(</span><span class="n">bfilename</span><span class="p">(</span><span class="s1">&#39;DIRECTORIO_XML&#39;</span><span class="p">,</span> <span class="s1">&#39;DICCIONARIO_AGRUPACION_FORMA_FARMA.xml&#39;</span><span class="p">),</span><span class="n">nls_charset_id</span><span class="p">(</span><span class="s1">&#39;WE8ISO8859P15&#39;</span><span class="p">)),</span> <span class="mi">1</span><span class="p">);</span> </span></pre></div> </td></tr></table>
bbdd

SQL

conectarse a sql plus apuntado a una bbdd remota

1
2
3
//conectarse a sql plus apuntado a una bbdd remota

connect MAPRES_VAD_LOCAL/MAPRES_VAD_LOCAL@192.168.143.33:1521
https://snipt.net/embed/3f84787ecda1bf6a308aebb5ef3addd4/
https://snipt.net/raw/3f84787ecda1bf6a308aebb5ef3addd4/
3f84787ecda1bf6a308aebb5ef3addd4
sql
SQL
3
2016-09-30T10:01:03
True
False
False
/api/public/snipt/18745/
conectarse-a-sql-plus-apuntado-a-una-bbdd-remota
<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 id="L-1"><a name="L-1"></a><span class="o">//</span><span class="n">conectarse</span> <span class="n">a</span> <span class="k">sql</span> <span class="n">plus</span> <span class="n">apuntado</span> <span class="n">a</span> <span class="n">una</span> <span class="n">bbdd</span> <span class="n">remota</span> </span><span id="L-2"><a name="L-2"></a> </span><span id="L-3"><a name="L-3"></a><span class="k">connect</span> <span class="n">MAPRES_VAD_LOCAL</span><span class="o">/</span><span class="n">MAPRES_VAD_LOCAL</span><span class="o">@</span><span class="mi">192</span><span class="p">.</span><span class="mi">168</span><span class="p">.</span><span class="mi">143</span><span class="p">.</span><span class="mi">33</span><span class="p">:</span><span class="mi">1521</span> </span></pre></div> </td></tr></table>
bbdd

SQL

extraer un campo concreto de un xmlType en bbdd

1
2
3
select extractValue(object_value,'/DICCIONARIO_GRUPO_ATC/GRATNOMBRELARGOGRUPOATC') "GRATNOMBRELARGOGRUPOATC: " from diccionario;

//la ruta que le pasemos tiene que existir en el xmlType
https://snipt.net/embed/ffcb3e27c5aab119ad6a6fd364c6c1ba/
https://snipt.net/raw/ffcb3e27c5aab119ad6a6fd364c6c1ba/
ffcb3e27c5aab119ad6a6fd364c6c1ba
sql
SQL
3
2016-08-23T08:27:56
True
False
False
/api/public/snipt/18657/
extraer-un-campo-concreto-de-un-xmltype-en-bbdd
<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 id="L-1"><a name="L-1"></a><span class="k">select</span> <span class="n">extractValue</span><span class="p">(</span><span class="n">object_value</span><span class="p">,</span><span class="s1">&#39;/DICCIONARIO_GRUPO_ATC/GRATNOMBRELARGOGRUPOATC&#39;</span><span class="p">)</span> <span class="ss">&quot;GRATNOMBRELARGOGRUPOATC: &quot;</span> <span class="k">from</span> <span class="n">diccionario</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="o">//</span><span class="n">la</span> <span class="n">ruta</span> <span class="n">que</span> <span class="n">le</span> <span class="n">pasemos</span> <span class="n">tiene</span> <span class="n">que</span> <span class="n">existir</span> <span class="n">en</span> <span class="n">el</span> <span class="n">xmlType</span> </span></pre></div> </td></tr></table>
bbdd

SQL

Sql Loader-->definiciones

To load internal LOBs, Binary Large Objects (BLOBs), Character Large Objects (CLOBs), and National Character Large Object (NCLOBs)
https://snipt.net/embed/afb3fa1a54f555d9e85670994c5a2fac/
https://snipt.net/raw/afb3fa1a54f555d9e85670994c5a2fac/
afb3fa1a54f555d9e85670994c5a2fac
sql
SQL
1
2016-09-18T17:03:04
True
False
False
/api/public/snipt/18508/
sql-loader-definiciones
<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 id="L-1"><a name="L-1"></a><span class="k">To</span> <span class="k">load</span> <span class="n">internal</span> <span class="n">LOBs</span><span class="p">,</span> <span class="nb">Binary</span> <span class="k">Large</span> <span class="n">Objects</span> <span class="p">(</span><span class="n">BLOBs</span><span class="p">),</span> <span class="nb">Character</span> <span class="k">Large</span> <span class="n">Objects</span> <span class="p">(</span><span class="n">CLOBs</span><span class="p">),</span> <span class="k">and</span> <span class="k">National</span> <span class="nb">Character</span> <span class="k">Large</span> <span class="k">Object</span> <span class="p">(</span><span class="n">NCLOBs</span><span class="p">)</span> </span></pre></div> </td></tr></table>
bbdd

SQL

Borrar registros duplicados

1
2
3
4
5
DELETE FROM tabla
 WHERE ROWID NOT IN
            (SELECT MIN(ROWID)
               FROM tabla
              GROUP BY columna1, columna2, columna3...) having count(*) >1;
https://snipt.net/embed/71a8817ae08c4ca55387d3a8814c1880/
https://snipt.net/raw/71a8817ae08c4ca55387d3a8814c1880/
71a8817ae08c4ca55387d3a8814c1880
sql
SQL
5
2016-09-19T08:47:09
True
False
False
/api/public/snipt/18453/
borrar-registros-duplicados
<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 id="L-1"><a name="L-1"></a><span class="k">DELETE</span> <span class="k">FROM</span> <span class="n">tabla</span> </span><span id="L-2"><a name="L-2"></a> <span class="k">WHERE</span> <span class="n">ROWID</span> <span class="k">NOT</span> <span class="k">IN</span> </span><span id="L-3"><a name="L-3"></a> <span class="p">(</span><span class="k">SELECT</span> <span class="k">MIN</span><span class="p">(</span><span class="n">ROWID</span><span class="p">)</span> </span><span id="L-4"><a name="L-4"></a> <span class="k">FROM</span> <span class="n">tabla</span> </span><span id="L-5"><a name="L-5"></a> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">columna1</span><span class="p">,</span> <span class="n">columna2</span><span class="p">,</span> <span class="n">columna3</span><span class="p">...)</span> <span class="k">having</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="o">&gt;</span><span class="mi">1</span><span class="p">;</span> </span></pre></div> </td></tr></table>
bbdd
Copyrighted, illegal, or inappropriate content? Email support@snipt.net.