#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>
This entry was posted in Database Stuff and tagged by rlockard. Bookmark the permalink.

About rlockard

Robert Lockard is a professional Oracle Designer, Developer and DBA working in the world of financial intelligence. In 1987 his boss called him into his office and told him that he is now their Oracle Wizard then handed him a stack of Oracle tapes and told him to load it on the VAX. Sense then, Robert has worked exclusively as an Oracle database designer, developer and Database Administrator. Robert enjoys flying vintage aircraft, racing sailboats, photography, and technical diving. Robert owns and fly’s the “Spirit of Baltimore Hon” a restored 1948 Ryan Navion and lives in Glen Burnie Maryland on Marley Creek

Welcome to oraclewizard