How to backup Oracle SQL Developer connection list?

Backup connection.xml file from SQL Developer Setup folder
e.g.
Oracle SQL Developer Version 17.3.1.279.0537

-> C:\Users\%USERNAME%\AppData\Roaming\SQL Developer\system17.3.1.279.0537\o.jdeveloper.db.connection\connections.xml

char vs byte

e.g.
SQL> 
CREATE TABLE exp_table
(
   col1   CHAR (1 CHAR),
   col2   CHAR (1 BYTE),
   col3   CHAR (1)
)
-> col1 can store 4 byte at most (if db charset is AL32UTF8)
-> col1 can store 3 byte at most (if db charset is UTF8)
-> col3 stores string according to NLS_LENGTH_SEMANTICS parameter
SQL>
SELECT * FROM  v$nls_parameters WHERE parameter = 'NLS_LENGTH_SEMANTICS';

Check db charset
SQL>
SELECT * FROM nls_database_parameters WHERE parameter like '%SET%';

-> NLS_CHARACTERSET set for CHAR, VARCHAR2 and CLOB data types
-> NLS_NCHAR_CHARACTERSET set for NCHAR, NVARCHAR2 and NCLOB (N, National) data types

e.g.
NLS_NCHAR_CHARACTERSET         UTF8
NLS_CHARACTERSET               WE8ISO8859P9

Ref: https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch6unicode.htm

How to install/use rlwrap?

Installation
e.g.
# rpm -ivh rlwrap-0.42-1.el6.x86_64.rpm  

Usage
Add alias to .bash_profile file (or .bashrc, etc.)
alias sqlplus='rlwrap sqlplus'

$ sqlplus hr/hr
SQL> select 1 from dual;

Key trick is that while in sqlplus command line, use up or down keys to navigate sql history.

How to define "Code Templates" in SQL Developer?

e.g.
SQL DEVELOPER -> TOOLS -> PREFERENCES -> DATABASE -> SQL EDITOR CODE TEMPLATES -> Click "ADD TEMPLATE" button -> Input Id as "sl", Template as "select * from " -> OK

Usage:
Press "sl" and press "ctrl+space"

Output:
select * from 

ORA-00900, ORA-00911 solution

SQL
DECLARE
   sql_text   VARCHAR (1000);
BEGIN
   sql_text := 'exec P_COMPILE_INVALID_OBJECTS()';

   DBMS_OUTPUT.PUT_LINE (sql_text);

   EXECUTE IMMEDIATE sql_text;
END;

Error
ORA-00900: invalid SQL statement
ORA-06512: at line 8

Solution
Use 'CALL' instead of 'EXEC' keyword.

DECLARE
   sql_text   VARCHAR (100);
BEGIN
   sql_text := 'call P_COMPILE_INVALID_OBJECTS()';

   DBMS_OUTPUT.PUT_LINE (sql_text);

   EXECUTE IMMEDIATE sql_text;
END;

SQL
DECLARE
   sql_text   VARCHAR (1000);
BEGIN
   sql_text := 'select sysdate from dual;';

   DBMS_OUTPUT.PUT_LINE (sql_text);

   EXECUTE IMMEDIATE sql_text;
END;

Error
ORA-00911: invalid character
ORA-06512: at line 8

Solution
Do not put comma (;) at the end of the sql statement.

DECLARE
   sql_text   VARCHAR (100);
BEGIN
   sql_text := 'select sysdate from dual';

   DBMS_OUTPUT.PUT_LINE (sql_text);

   EXECUTE IMMEDIATE sql_text;
END;