5 Replies Latest reply on Apr 12, 2020 1:21 PM by Paavo

    json.value output in sqldeveloper/sqlcl vs. sqlplus

    Paavo

      Hi

       

      How to get the same result in sqldeveloper like in sqlplus?

      sqlplus shows

       

      SQL> SELECT JSON_VALUE(data, '$' RETURNING t_emp_row) AS employee FROM   json_documents;
      
      EMPLOYEE(EMPNO, ENAME, JOB)
      --------------------------------------------------------------------------------
      T_EMP_ROW(7839, 'KING', 'PRESIDENT')
      T_EMP_ROW(7698, 'BLAKE', 'MANAGER')
      T_EMP_ROW(7782, 'CLARK', 'MANAGER')
      T_EMP_ROW(7566, 'JONES', 'MANAGER')
      T_EMP_ROW(7788, 'SCOTT', 'ANALYST')
      T_EMP_ROW(7902, 'FORD', 'ANALYST')
      T_EMP_ROW(7369, 'SMITH', 'CLERK')
      T_EMP_ROW(7499, 'ALLEN', 'SALESMAN')
      T_EMP_ROW(7521, 'WARD', 'SALESMAN')
      T_EMP_ROW(7654, 'MARTIN', 'SALESMAN')
      T_EMP_ROW(7844, 'TURNER', 'SALESMAN')
      
      EMPLOYEE(EMPNO, ENAME, JOB)
      --------------------------------------------------------------------------------
      T_EMP_ROW(7876, 'ADAMS', 'CLERK')
      T_EMP_ROW(7900, 'JAMES', 'CLERK')
      T_EMP_ROW(7934, 'MILLER', 'CLERK')
      T_EMP_ROW(8020, 'JOHNNIE', 'TRIBESMAN')
      T_EMP_ROW(8021, 'LIZZIE', 'VILLAIN')
      T_EMP_ROW(8022, 'HUGO', 'VILLAIN')
      
      17 rows selected.
      
      

       

      and sqldeveloper shows:

      While sqlcl shows:

      >SELECT JSON_VALUE(data, '$' RETURNING t_emp_row) AS employee FROM   json_documents;
                           EMPLOYEE 
      _____________________________ 
      oracle.sql.STRUCT@3dd1dc90    
      oracle.sql.STRUCT@abf688e     
      oracle.sql.STRUCT@478ee483    
      oracle.sql.STRUCT@1a7288a3    
      oracle.sql.STRUCT@2974f221    
      oracle.sql.STRUCT@58fe0499    
      oracle.sql.STRUCT@686449f9    
      oracle.sql.STRUCT@665df3c6    
      oracle.sql.STRUCT@68b6f0d6    
      oracle.sql.STRUCT@4044fb95    
      oracle.sql.STRUCT@aa549e5     
      oracle.sql.STRUCT@36f48b4     
      oracle.sql.STRUCT@5c00384f    
      oracle.sql.STRUCT@3b7ff809    
      oracle.sql.STRUCT@1bb564e2    
      oracle.sql.STRUCT@62e6b5c8    
      oracle.sql.STRUCT@3f792b9b    
      
      
      17 rows selected.
      

       

      rgrds Paavo

        • 1. Re: json.value output in sqldeveloper/sqlcl vs. sqlplus
          Paavo

          Hi

           

          and yes, in single record view the row can be seen in sqldeveloper:

           

          rgrds Paavo

          • 2. Re: json.value output in sqldeveloper/sqlcl vs. sqlplus
            thatJeffSmith-Oracle

            It's an option

             

            1 person found this helpful
            • 3. Re: json.value output in sqldeveloper/sqlcl vs. sqlplus
              Paavo

              Hi Jeff

               

              It worked,

               

              I will place the same question to sqlcl json.value output in sqlcl vs. sqlplus - so you will get more community points in answering how to get this done in sqlcl. as well

              rgrds Paavo

              • 4. Re: json.value output in sqldeveloper/sqlcl vs. sqlplus
                thatJeffSmith-Oracle

                give me a reproducible scenario, table ddl/data, and i'll have a got at it in sqlcl

                1 person found this helpful
                • 5. Re: json.value output in sqldeveloper/sqlcl vs. sqlplus
                  Paavo

                  Hi Jeff

                   

                  Should we move this to sqlcl ?

                  I was walking Tim's easy-to-follow steps https://oracle-base.com/articles/19c/mapping-of-json-data-to-and-from-sql-object-types-19c

                   

                   

                  And yes it seems that my "dockered" sqlcl is somehow different.

                   

                  sqlcl from dockered sqlcl:

                  SQLcl: Release 19.4 Production on Sun Apr 12 13:10:32 2020

                   

                  Copyright (c) 1982, 2020, Oracle.  All rights reserved.

                   

                  Last Successful login time: Sun Apr 12 2020 13:10:33 +00:00

                   

                  Connected to:

                  Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

                  Version 19.6.0.0.0

                   

                  login.sql found in the CWD. DB access is restricted for login.sql.

                  Adjust the SQLPATH to include the path to enable full functionality.

                   

                  >SELECT JSON_VALUE(data, '$' RETURNING t_emp_row) AS employee FROM   json_documents;

                                       EMPLOYEE

                  _____________________________

                  oracle.sql.STRUCT@55493582  

                  oracle.sql.STRUCT@1a20270e  

                  oracle.sql.STRUCT@6b88ca8c  

                  oracle.sql.STRUCT@336f1079  

                  oracle.sql.STRUCT@2f16c6b3  

                  oracle.sql.STRUCT@34158c08  

                  oracle.sql.STRUCT@19e4fcac  

                  oracle.sql.STRUCT@52c3cb31  

                  oracle.sql.STRUCT@4b79ac84  

                  oracle.sql.STRUCT@53941c2f  

                  oracle.sql.STRUCT@6dab9b6d  

                  oracle.sql.STRUCT@21fd5faa  

                  oracle.sql.STRUCT@2766ca9d  

                  oracle.sql.STRUCT@5460cf3a  

                  oracle.sql.STRUCT@65a15628  

                  oracle.sql.STRUCT@2e6a5539  

                  oracle.sql.STRUCT@3fc9504b  

                   

                   

                  17 rows selected.

                   

                  sqlcl from commandline

                  SQLcl: Release 19.4 Production on Sun Apr 12 16:09:31 2020

                   

                  Copyright (c) 1982, 2020, Oracle.  All rights reserved.

                   

                  Last Successful login time: Sun Apr 12 2020 16:09:32 +03:00

                   

                  Connected to:

                  Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

                  Version 19.6.0.0.0

                   

                   

                  SQL> SELECT JSON_VALUE(data, '$' RETURNING t_emp_row) AS employee FROM   json_documents;

                   

                  EMPLOYEE(EMPNO, ENAME, JOB)                                                                                                                                                                                                                                                 

                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                  T_EMP_ROW(7839, 'KING', 'PRESIDENT')                                                                                                                                                                                                                                        

                  T_EMP_ROW(7698, 'BLAKE', 'MANAGER')                                                                                                                                                                                                                                         

                  T_EMP_ROW(7782, 'CLARK', 'MANAGER')                                                                                                                                                                                                                                         

                  T_EMP_ROW(7566, 'JONES', 'MANAGER')                                                                                                                                                                                                                                         

                  T_EMP_ROW(7788, 'SCOTT', 'ANALYST')                                                                                                                                                                                                                                         

                  T_EMP_ROW(7902, 'FORD', 'ANALYST')                                                                                                                                                                                                                                          

                  T_EMP_ROW(7369, 'SMITH', 'CLERK')                                                                                                                                                                                                                                           

                  T_EMP_ROW(7499, 'ALLEN', 'SALESMAN')                                                                                                                                                                                                                                        

                  T_EMP_ROW(7521, 'WARD', 'SALESMAN')                                                                                                                                                                                                                                         

                  T_EMP_ROW(7654, 'MARTIN', 'SALESMAN')                                                                                                                                                                                                                                       

                  T_EMP_ROW(7844, 'TURNER', 'SALESMAN')                                                                                                                                                                                                                                       

                  T_EMP_ROW(7876, 'ADAMS', 'CLERK')                                                                                                                                                                                                                                           

                  T_EMP_ROW(7900, 'JAMES', 'CLERK')                                                                                                                                                                                                                                           

                  T_EMP_ROW(7934, 'MILLER', 'CLERK')                                                                                                                                                                                                                                          

                  T_EMP_ROW(8020, 'JOHNNIE', 'TRIBESMAN')                                                                                                                                                                                                                                     

                  T_EMP_ROW(8021, 'LIZZIE', 'VILLAIN')                                                                                                                                                                                                                                        

                  T_EMP_ROW(8022, 'HUGO', 'VILLAIN')                                                                                                                                                                                                                                          

                   

                  17 rows selected.

                   

                  SQL>

                   

                   

                  sqlplus version:

                  SQL> SELECT JSON_VALUE(data, '$' RETURNING t_emp_row) AS employee  FROM   json_documents;

                   

                  EMPLOYEE(EMPNO, ENAME, JOB)

                  --------------------------------------------------------------------------------

                  T_EMP_ROW(7839, 'KING', 'PRESIDENT')

                  T_EMP_ROW(7698, 'BLAKE', 'MANAGER')

                  T_EMP_ROW(7782, 'CLARK', 'MANAGER')

                  T_EMP_ROW(7566, 'JONES', 'MANAGER')

                  T_EMP_ROW(7788, 'SCOTT', 'ANALYST')

                  T_EMP_ROW(7902, 'FORD', 'ANALYST')

                  T_EMP_ROW(7369, 'SMITH', 'CLERK')

                  T_EMP_ROW(7499, 'ALLEN', 'SALESMAN')

                  T_EMP_ROW(7521, 'WARD', 'SALESMAN')

                  T_EMP_ROW(7654, 'MARTIN', 'SALESMAN')

                  T_EMP_ROW(7844, 'TURNER', 'SALESMAN')

                   

                  EMPLOYEE(EMPNO, ENAME, JOB)

                  --------------------------------------------------------------------------------

                  T_EMP_ROW(7876, 'ADAMS', 'CLERK')

                  T_EMP_ROW(7900, 'JAMES', 'CLERK')

                  T_EMP_ROW(7934, 'MILLER', 'CLERK')

                  T_EMP_ROW(8020, 'JOHNNIE', 'TRIBESMAN')

                  T_EMP_ROW(8021, 'LIZZIE', 'VILLAIN')

                  T_EMP_ROW(8022, 'HUGO', 'VILLAIN')

                   

                  17 rows selected.

                   

                  SQL>

                   

                   

                  rgrds Paavo