Is it possible to retrieve content only with 'sqlformat json output', i.e. not include metadata?

3272816

Are there any settings, or combination of settings, in sqlci that would eliminate metadata {"results"} - providing content only?

test case

drop table example;

create table example (pk numeric, description varchar2(50));

insert into example values (1, 'First');

insert into example values (2, 'Second');

commit;

set sqlformat json

select * from example;

What sqlci returns:

{"results":[{"columns":[{"name":"PK","type":"NUMBER"},{"name":"DESCRIPTION","type":"VARCHAR2"}],"items":

[

{"pk":1,"description":"First"}

,{"pk":2,"description":"Second"}

]}]}

Desired output:

{"pk":1,"description":"First"}

,{"pk":2,"description":"Second"}

Regards

Gaz in Oz

Here's a workaround...

On Linux, using sed, for example:

$ cat json.sqlset sqlformat jsonset feedback offwith example (pk, description) as (   select 1, 'Level '||level from dual connect by level <10)select pk,       descriptionfrom   example/exit;$ ./sql -s gaz/[email protected] @json.sql | sed -e '1,2d;$d' -e '$d'{"pk":"1","description":"Level 1"},{"pk":"1","description":"Level 2"},{"pk":"1","description":"Level 3"},{"pk":"1","description":"Level 4"},{"pk":"1","description":"Level 5"},{"pk":"1","description":"Level 6"},{"pk":"1","description":"Level 7"},{"pk":"1","description":"Level 8"},{"pk":"1","description":"Level 9"}$

Here's a windows cmd command-line option, using the same example json.sql, making use of "more +2" and "find -v" to suppress 1st 2 lines and last line:

F:\Oracle>sql -s gaz/[email protected] @json.sql | more +2 | find /v "]}]}"{"pk":1,"description":"Level 1"},{"pk":1,"description":"Level 2"},{"pk":1,"description":"Level 3"},{"pk":1,"description":"Level 4"},{"pk":1,"description":"Level 5"},{"pk":1,"description":"Level 6"},{"pk":1,"description":"Level 7"},{"pk":1,"description":"Level 8"},{"pk":1,"description":"Level 9"}F:\Oracle>

Glen Conway

It appears the answer is no.  Logically, I thought "sqlformat json" might respect the standard "set heading off" to suppress that metadata portion of the output, but that still does not work as of SQLcl 18.3.

Someone suggested it long ago (SQLcl - customize sqlformat json? ) but no action yet.  You might try adding a Feature Request in the SQL Developer Exchange (http://sqldeveloper.oracle.com).

Cheers

Gaz in Oz

Here's a workaround...

On Linux, using sed, for example:

$ cat json.sqlset sqlformat jsonset feedback offwith example (pk, description) as (   select 1, 'Level '||level from dual connect by level <10)select pk,       descriptionfrom   example/exit;$ ./sql -s gaz/[email protected] @json.sql | sed -e '1,2d;$d' -e '$d'{"pk":"1","description":"Level 1"},{"pk":"1","description":"Level 2"},{"pk":"1","description":"Level 3"},{"pk":"1","description":"Level 4"},{"pk":"1","description":"Level 5"},{"pk":"1","description":"Level 6"},{"pk":"1","description":"Level 7"},{"pk":"1","description":"Level 8"},{"pk":"1","description":"Level 9"}$

Here's a windows cmd command-line option, using the same example json.sql, making use of "more +2" and "find -v" to suppress 1st 2 lines and last line:

F:\Oracle>sql -s gaz/[email protected] @json.sql | more +2 | find /v "]}]}"{"pk":1,"description":"Level 1"},{"pk":1,"description":"Level 2"},{"pk":1,"description":"Level 3"},{"pk":1,"description":"Level 4"},{"pk":1,"description":"Level 5"},{"pk":1,"description":"Level 6"},{"pk":1,"description":"Level 7"},{"pk":1,"description":"Level 8"},{"pk":1,"description":"Level 9"}F:\Oracle>

3272816

perfect.  Thank you very much for the workaround.

3272816

I had not seen that earlier question, and good suggestion.  Thanks Glen.