Rhenium Readme

This extension contains several tools for working with JSON and Oracle PL/SQL.

Features

After selecting text in the editor, you will find several extra options in de context menu:

• PL/SQL Tools
  • PCK_TS_LOG to PCK_LOG_RS
    This will replace every call to PCK_TS_LOG with its counterpart in PCK_LOG_RS making the code much more compact.
  • PL/SQL to lowercase
    This will make all code lowercase except the string literals and the single and multiline comments.
• JSON Tools
  • JSON to JSON_(OBJECT/ARRAY)
    Convert the selected json to a JSON_OBJECT / JSON_TABLE select statent. You can use this to select data from the database in a JSON format.
  • JSON to JSON_TABLE
    Convert the select json (part) to a JSON_TABLE cursor. You can use this to create PL/SQL code to process the json.

Requirements

None

Known Issues

None

Release Notes

See changelog

Examples

PL/SQL Tools: PCK_TS_LOG to PCK_LOG_RS

This is not usefull for people outside my work place. It's used to convert the old logger to the new logger.

PL/SQL Tools: PL/SQL to lowercase

Converts the SQL or PL/SQL code to lowercase without changing the strings and comments

/*
Available online in file 'Sample1'
*/
DECLARE
  x NUMBER := 100;
BEGIN
  FOR i IN 1..10 LOOP
    IF MOD(i,2) = 0 THEN -- I is Even
        INSERT INTO temp VALUES (i, x, 'I is Even');
    END IF;
    x := x + 100;
  END LOOP;
  COMMIT;
END;

is converted into

/*
Available online in file 'Sample1'
*/
declare
  x number := 100;
begin
  for i in 1..10 loop
    if mod(i,2) = 0 then -- I is Even
        insert into temp values (i, x, 'I is Even');
    end if;
    x := x + 100;
  end loop;
  commit;
end;

JSON Tools: JSON to JSON_(OBJECT/ARRAY)

Convert the selected json to a JSON_OBJECT / JSON_TABLE select statent. You can use this to select data from the database in a JSON format. Example:

{
  "id": 10,
  "name": "doggie",
  "category": {
    "id": 1,
    "name": "Dogs"
  },
  "tags": [
    {
      "id": 0,
      "name": "string"
    }
  ]
}

is converted into

select
  json_object(
    'id' value 10,
    'name' value 'doggie',
    'category' value json_object(
      'id' value 1,
      'name' value 'Dogs'
      absent on null returning clob strict with unique keys
    ),
    'tags' value json_array(
      json_object(
        'id' value 0,
        'name' value 'string'
        absent on null returning clob strict with unique keys
      )
      absent on null returning clob strict
    )
    absent on null returning clob strict with unique keys
  )
from dual;

JSON Tools: JSON to JSON_TABLE

Convert the select json (part) to a JSON_TABLE cursor. You can use this to create PL/SQL code to process the json. Examples:

{
  "id": 10,
  "name": "doggie",
  "category": {
    "id": 1,
    "name": "Dogs"
  },
  "tags": [
    {
      "id": 0,
      "name": "string"
    }
  ]
}

is converted into

for rec_object in
select jt.*
from json_table(i_json, '$'
  null on error
  columns (
    id number path '$.id',
    name varchar2 path '$.name',
    category_id number path '$.category.id',
    category_name varchar2 path '$.category.name',
    tags_id number path '$.tags[0].id',
    tags_name varchar2 path '$.tags[0].name'
  )
) jt
loop
  l_dummy := null;
  l_dummy.id := rec_object.id;
  l_dummy.name := rec_object.name;
  l_dummy.category_id := rec_object.category_id;
  l_dummy.category_name := rec_object.category_name;
  l_dummy.tags_id := rec_object.tags_id;
  l_dummy.tags_name := rec_object.tags_name;
  l_dummy.status := rec_object.status;
end loop;

• Tip: It is possible to also select the name of the object or array to get a named cursor. In that case the selected part is enclosed in {selected_part} in the plugin. Don't select the extra , at the end.

"tags": [
  {
    "id": 0,
    "name": "string"
  }
]

is converted to

for rec_tags in
select jt.*
from json_table(i_json, '$.tags[*]'
  null on error
  columns (
    id number path '$.id',
    name varchar2 path '$.name'
  )
) jt
loop
  l_dummy := null;
  l_dummy.id := rec_tags.id;
  l_dummy.name := rec_tags.name;
end loop;