Conectarse con SQLcl

Oracle SQL Developer Command Line (SQLcl) es una herramienta que viene a reemplazar a SQL*plus.

Está construido sobre Java. Si deseamos usar una implementación específica, creamos un symbolik link.

<span>cd</span> <span>sqlcl</span><span>-</span><span>22</span>.1.0.90.1611
<span>mkdir</span> <span>jdk</span>
<span>mklink</span> <span>/d </span>.\jdk\jre <span>C</span>:\java17runtime
<span>cd</span> <span>sqlcl</span><span>-</span><span>22</span>.1.0.90.1611
<span>mkdir</span> <span>jdk</span>
<span>mklink</span> <span>/d </span>.\jdk\jre <span>C</span>:\java17runtime
cd sqlcl-22.1.0.90.1611
mkdir jdk
mklink /d .\jdk\jre C:\java17runtime

Enter fullscreen mode Exit fullscreen mode

Indicamos la ruta del archivo tnsnames.ora. Nos conectamos y ejecutamos scripts SQL.

<span>$</span><span>env</span><span>:</span><span>TNS_ADMIN</span><span>=</span><span>'C:\oracle\product\11.2.0\server\network\ADMIN'</span><span> </span><span>.</span><span>\sqlcl\bin\sql.exe</span><span> </span><span>/NOLOG</span><span> </span><span>@</span><span>CONN_LOCAL</span><span> </span><span>011</span><span> </span>
<span>$</span><span>env</span><span>:</span><span>TNS_ADMIN</span><span>=</span><span>'C:\oracle\product\11.2.0\server\network\ADMIN'</span><span> </span><span>.</span><span>\sqlcl\bin\sql.exe</span><span> </span><span>/NOLOG</span><span> </span><span>@</span><span>CONN_LOCAL</span><span> </span><span>011</span><span> </span>
$env:TNS_ADMIN='C:\oracle\product\11.2.0\server\network\ADMIN' .\sqlcl\bin\sql.exe /NOLOG @CONN_LOCAL 011 

Enter fullscreen mode Exit fullscreen mode

Podemos crear ALIAS como abreviaciones de comandos.

<span>set</span> <span>highlight</span> <span>on</span>
<span>ALIAS</span> <span>recompile</span><span>=</span>
<span>exec</span> <span>dbms_utility</span><span>.</span><span>compile_schema</span><span>(</span><span>'PTOVENTA'</span><span>,</span><span>false</span><span>);</span>
<span>ALIAS</span> <span>invalids</span><span>=</span>
<span>SELECT</span> <span>*</span>
<span>FROM</span> <span>ALL_OBJECTS</span>
<span>WHERE</span> <span>STATUS</span> <span>!=</span> <span>'VALID'</span>
<span>AND</span> <span>OWNER</span> <span>=</span> <span>'PTOVENTA'</span><span>;</span>
<span>set</span> <span>highlight</span> <span>on</span>

<span>ALIAS</span> <span>recompile</span><span>=</span>
<span>exec</span> <span>dbms_utility</span><span>.</span><span>compile_schema</span><span>(</span><span>'PTOVENTA'</span><span>,</span><span>false</span><span>);</span>

<span>ALIAS</span> <span>invalids</span><span>=</span>
<span>SELECT</span> <span>*</span>
<span>FROM</span> <span>ALL_OBJECTS</span>
<span>WHERE</span> <span>STATUS</span> <span>!=</span> <span>'VALID'</span>
<span>AND</span> <span>OWNER</span> <span>=</span> <span>'PTOVENTA'</span><span>;</span>
set highlight on

ALIAS recompile=
exec dbms_utility.compile_schema('PTOVENTA',false);

ALIAS invalids=
SELECT *
FROM ALL_OBJECTS
WHERE STATUS != 'VALID'
AND OWNER = 'PTOVENTA';

Enter fullscreen mode Exit fullscreen mode

Una ventaja es conectarnos a una base de datos mediante la IP.

<span>CONN</span> <span>usrdesarrollo</span><span>/</span><span>usrdesarrollo</span><span>@<</span><span>IP_SERVIDOR_CENTRAL</span><span>></span><span>:</span><span>1521</span><span>/</span><span>ADMBD</span>
<span>VAR</span> <span>IP_LOCAL</span> <span>VARCHAR2</span><span>(</span><span>100</span><span>);</span>
<span>BEGIN</span>
<span>SELECT</span> <span>IP_SERVIDOR_LOCAL</span>
    <span>INTO</span> <span>:</span><span>IP_LOCAL</span>
<span>FROM</span> <span>APPS</span><span>.</span><span>PBL_LOCAL</span>
<span>WHERE</span> <span>COD_LOCAL</span> <span>=</span> <span>'&1'</span><span>;</span>    
<span>END</span><span>;</span>
<span>/</span>
<span>column</span> <span>SERVIDOR</span> <span>new_value</span> <span>IP</span> <span>noprint</span>
<span>select</span> <span>:</span><span>IP_LOCAL</span> <span>SERVIDOR</span> <span>from</span> <span>dual</span><span>;</span>
<span>CONN</span> <span>usrdesarrollo</span><span>/</span><span>usrdesarrollo</span><span>@&</span><span>IP</span><span>:</span><span>1521</span><span>/</span><span>XE</span>
<span>CONN</span> <span>usrdesarrollo</span><span>/</span><span>usrdesarrollo</span><span>@<</span><span>IP_SERVIDOR_CENTRAL</span><span>></span><span>:</span><span>1521</span><span>/</span><span>ADMBD</span>

<span>VAR</span> <span>IP_LOCAL</span> <span>VARCHAR2</span><span>(</span><span>100</span><span>);</span>

<span>BEGIN</span>
<span>SELECT</span> <span>IP_SERVIDOR_LOCAL</span>
    <span>INTO</span> <span>:</span><span>IP_LOCAL</span>
<span>FROM</span> <span>APPS</span><span>.</span><span>PBL_LOCAL</span>
<span>WHERE</span> <span>COD_LOCAL</span> <span>=</span> <span>'&1'</span><span>;</span>    
<span>END</span><span>;</span>
<span>/</span>

<span>column</span> <span>SERVIDOR</span> <span>new_value</span> <span>IP</span> <span>noprint</span>
<span>select</span> <span>:</span><span>IP_LOCAL</span> <span>SERVIDOR</span> <span>from</span> <span>dual</span><span>;</span>

<span>CONN</span> <span>usrdesarrollo</span><span>/</span><span>usrdesarrollo</span><span>@&</span><span>IP</span><span>:</span><span>1521</span><span>/</span><span>XE</span>
CONN usrdesarrollo/usrdesarrollo@<IP_SERVIDOR_CENTRAL>:1521/ADMBD

VAR IP_LOCAL VARCHAR2(100);

BEGIN
SELECT IP_SERVIDOR_LOCAL
    INTO :IP_LOCAL
FROM APPS.PBL_LOCAL
WHERE COD_LOCAL = '&1';    
END;
/

column SERVIDOR new_value IP noprint
select :IP_LOCAL SERVIDOR from dual;

CONN usrdesarrollo/usrdesarrollo@&IP:1521/XE

Enter fullscreen mode Exit fullscreen mode

Documentación

https://www.oracle.com/tools/downloads/sqlcl-downloads.html

原文链接：Conectarse con SQLcl