#Oracle #SQLcl #quicktip

If you’re not following Jeff Smith and Kris Rice, you should be. These are two really smart guys, who are designing the tools we use everyday to do our jobs.

If you’re like me, 99% of your data is in relational tables. So how do you get the data out of your relational tables into a JSON format. SQLcl has a command for that.

Getting data out of your relational tables and into JSON format is pretty easy; only, up until now, it was not easy to read. 

set sqlformat json-formattedSQL> select * from hr.employees  2* where rownum = 1;{“results”:[{“columns”:[{“name”:”EMPLOYEE_ID”,”type”:”NUMBER”},{“name”:”FIRST_NAME”,”type”:”VARCHAR2″},{“name”:”LAST_NAME”,”type”:”VARCHAR2″},{“name”:”EMAIL”,”type”:”VARCHAR2″},{“name”:”PHONE_NUMBER”,”type”:”VARCHAR2″},{“name”:”HIRE_DATE”,”type”:”DATE”},{“name”:”JOB_ID”,”type”:”VARCHAR2″},{“name”:”SALARY”,”type”:”NUMBER”},{“name”:”COMMISSION_PCT”,”type”:”NUMBER”},{“name”:”MANAGER_ID”,”type”:”NUMBER”},{“name”:”DEPARTMENT_ID”,”type”:”NUMBER”}],”items”:[{“employee_id”:100,”first_name”:”Steven”,”last_name”:”King”,”email”:”SKING”,”phone_number”:”515.123.4567″,”hire_date”:”17-JUN-87″,”job_id”:”AD_PRES”,”salary”:24000,”department_id”:90}]}]}

json-formatted is new in SQLcl 19.2 this gives a pretty print of the JSON data.

<code>
SQL> set sqlformat json-formatted
SQL> select * from hr.employees where rownum = 1;
{
  “results” : [
    {
      “columns” : [
        {
          “name” : “EMPLOYEE_ID”,
          “type” : “NUMBER”
        },
        {
          “name” : “FIRST_NAME”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “LAST_NAME”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “EMAIL”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “PHONE_NUMBER”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “HIRE_DATE”,
          “type” : “DATE”
        },
        {
          “name” : “JOB_ID”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “SALARY”,
          “type” : “NUMBER”
        },
        {
          “name” : “COMMISSION_PCT”,
          “type” : “NUMBER”
        },
        {
          “name” : “MANAGER_ID”,
          “type” : “NUMBER”
        },
        {
          “name” : “DEPARTMENT_ID”,
          “type” : “NUMBER”
        }
      ],
      “items” : [
        {
          “employee_id” : 100,
          “first_name” : “Steven”,
          “last_name” : “King”,
          “email” : “SKING”,
          “phone_number” : “515.123.4567”,
          “hire_date” : “17-JUN-87”,
          “job_id” : “AD_PRES”,
          “salary” : 24000,
          “commission_pct” : “”,
          “manager_id” : “”,
          “department_id” : 90
        }
      ]
    }
  ]
}
</code>

Update to my earlier #quicktip on setting #plsql scope and warnings.

I noticed a error in my code for setting PLSCOPE_SETTINGS and PLSQL_WARNINGS. QuicTip Logon.sql What I did was get the instance name out of v$instance to figure out if I was connecting to a production environment or one of the lower environments. The problem with this is, not everyone is going to have permissions to select on sys.v_$instance. The better way to do this is to use sys_context to get the instance name. This way, you won’t have to chase down additional privileges from your DBA.

<code>

select sys_context('userenv','instance_name')
 into sInst
 from dual;

</code>

Here is the corrected code for my logon.sql

<code>

DECLARE
 sInst varchar2(1);
 BEGIN
 -- rlockard: 2018/02/23 commented out getting instance name from v$instance.
 -- used the more apporiate sys_context('userenv','instance_name')

--select upper(SUBSTR(instance_name, 1,1))
 --INTO sInst
 --FROM SYS.V_$INSTANCE;

select sys_context('userenv','instance_name')
 into sInst
 from dual;

-- test to see if this is a production instance
 -- all production instances start with P so ...
 -- if it's not a production instance set up
 -- session properties approiate for dev / test / sandbox.
 IF sInst != 'P' THEN
 execute immediate 'ALTER SESSION SET PLSCOPE_SETTINGS=' || '''IDENTIFIERS:ALL''';
 execute immediate 'ALTER SESSION SET PLSQL_WARNINGS=' || '''ENABLE:ALL''';
 END IF;
 END;
 /

</code>