Oracle Developer Tools for VS Code

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

New Release: 19.3.3. Lots of features! Try it out and let us know what you think

Christian.Shay -OracleOct 1 2020 — edited Oct 8 2020

Please let me know what you think.
We have added SQL*Plus support. See these web pages:
SQL*Plus common commands: https://www.oracle.com/database/technologies/appdev/dotnet/odtvscodesqlplus.html
SQL*Plus Reference:
https://www.oracle.com/database/technologies/appdev/dotnet/odtvscodesqlplusref.html
More features from the changelog:
Changes in version 19.3.3
Connection Dialog enhancements: Set/Change the current schema, improved proxy user connection UI
Improved connection failure detection with an option to reconnect
Support for macOS connections to Oracle Autonomous Database over TLS
Support for database connections using LDAP
Browse other schemas ("Other Users" node) in Oracle Explorer tree control
SQL History and Bookmarks
Limited SQL*Plus support
Detection and warning of unsupported SQL*Plus commands in scripts (and child scripts)
SQL*Plus CONNECT command associates the file with a connection
Preservation of all session associated properties from execution to execution
Autocommit On/Off setting
Autocompletion of SQL*Plus commands
Autocompletion of procedure/function parameters
Intellisense/Autocomplete performance enhancements
Syntax coloring for SQL and PL/SQL keywords and SQL*Plus commands/variables
Append new results to existing results window
Toolbar buttons to cancel running query and to clear results window
Setting to automatically clear results window after each execution
Remember previous selections in some UI elements and offer as defaults
Support for REFCURSOR variables and implicit cursors
Visual Studio Code theme support for Light/Dark/High Contrast themes

730428

SQL> with t as (
  2  select xmltype('<?xml version="1.0"?>
  3  <purchase_order>
  4  <po_items>
  5  <item>
  6  <name>#Name#</name>
  7  <quantity>#number#</quantity>
  8  </item>
  9  </po_items>
 10  </purchase_order>') xml from dual)
 11  select x.*
 12  from t, xmltable('/' PASSING t.xml COLUMNS
 13                   name varchar2(20) PATH '/purchase_order/po_items/item/name',
 14                   qty  varchar2(20) PATH '/purchase_order/po_items/item/quantity') x;

NAME                 QTY
-------------------- --------------------
#Name#               #number#

Max
http://oracleitalia.wordpress.com

730428

Answer

If you have multiple item tags:

SQL> with t as (
  2  select xmltype('<?xml version="1.0"?>
  3  <purchase_order>
  4  <po_items>
  5  <item>
  6  <name>#Name#</name>
  7  <quantity>#number#</quantity>
  8  </item>
  9  <item>
 10  <name>#Name2#</name>
 11  <quantity>#number2#</quantity>
 12  </item>
 13  <item>
 14  <name>#Name3#</name>
 15  <quantity>#number3#</quantity>
 16  </item>
 17  </po_items>
 18  </purchase_order>') xml from dual)
 19  select x.*
 20  from t, xmltable('/purchase_order/po_items/item' PASSING t.xml COLUMNS
 21                   name varchar2(20) PATH '/item/name',
 22                   qty  varchar2(20) PATH '/item/quantity') x;

NAME                 QTY
-------------------- --------------------
#Name#               #number#
#Name2#              #number2#
#Name3#              #number3#

Max
http://oracleitalia.wordpress.com

Marked as Answer by 754085 · Sep 27 2020

754085

I'm not think this to get in result:

NAME                 QTY
----------------    -----------------
#Name#             #number#

in my result needs

VALUE                PATH
--------------      ------------------------------
#Name#            /purchase_order/po_items/item/name
#number#        /purchase_order/po_items/item/quantity

754085

Massimo Ruocchio do you have some ideas??

730428

In Oracle 10g:

SQL> with t as (
  2  select xmltype('<?xml version="1.0"?>
  3  <purchase_order>
  4  <po_items>
  5  <item>
  6  <name>#Name#</name>
  7  <quantity>#number#</quantity>
  8  </item>
  9  </po_items>
 10  </purchase_order>') xml from dual)
 11  select x.val, 
 12         '/purchase_order/po_items/item/'||xmlquery('for $i in /* return name($i)' 
 13                                                    PASSING x.col RETURNING CONTENT) path
 14  from t, xmltable('/purchase_order/po_items/item/*' PASSING t.xml COLUMNS
 15                   val varchar2(20) PATH '.',
 16                   col xmltype PATH '.') x;

VAL                  PATH
-------------------- --------------------------------------------------
#Name#               /purchase_order/po_items/item/name
#number#             /purchase_order/po_items/item/quantity

In Oracle11g the following should work:

with t as (
select xmltype('<?xml version="1.0"?>
<purchase_order>
<po_items>
<item>
<name>#Name#</name>
<quantity>#number#</quantity>
</item>
</po_items>
</purchase_order>') xml from dual)
select x.val, 
       '/purchase_order/po_items/item/'||x.col path
from t, xmltable('/purchase_order/po_items/item/*' PASSING t.xml COLUMNS
                 val varchar2(20) PATH '.',
                 col varchar2(20) PATH 'name(.)') x;

Max
http://oracleitalia.wordpress.com

MichaelS

Pitty you did not mention your db version.

putting together some functions from FunctX one can write

SQL> with t as (
select xmltype('<?xml version="1.0"?>
<purchase_order>
<po_items>
<item>
<name>#Name#</name>
<quantity>#number#</quantity>
</item>
</po_items>
</purchase_order>') xml from dual
)
select x.* 
  from t t, 
  xmltable('declare function local:index-of-node  ( $nodes as node()* ,  $nodeToFind as node() )  as xs:integer* 
                {
                   for $seq in (1 to count($nodes))  return $seq[$nodes[$seq] is $nodeToFind]
                } ;
               declare function local:path-to-node-with-pos  ( $node as node()? )  as xs:string 
               {
                   fn:string-join( for $ancestor in $node/ancestor-or-self::*
                                          let $sibsOfSameName := $ancestor/../*[fn:name() = fn:name($ancestor)]
                                       return fn:concat(fn:name($ancestor), 
                                                               if (fn:count($sibsOfSameName) <= 1)
                                                               then ""  else fn:concat( "[", local:index-of-node($sibsOfSameName,$ancestor),"]"))
                                                               , "/")
            };
           element e {local:path-to-node-with-pos(//item/name[. = "#Name#"]), element name {//name}},
           element e {local:path-to-node-with-pos(//item/quantity[. = "#number#"]), element name {//quantity}}' 
           passing t.xml
           columns value varchar2(50) path 'name',
                        path varchar2(50) path 'text()'
           ) x

VALUE      PATH                                    
---------- ----------------------------------------
#Name#     purchase_order/po_items/item/name       
#number#   purchase_order/po_items/item/quantity   

2 rows selected.

754085

But there you give a static value #number# and #Name#, but in my case. I need select all names what is in xml and get path of them!

Thanks anyway...it's helpful anyway :)

MichaelS

there you give a static value #number# and #Name#

can be avoided of course:

SQL> with t as (
select xmltype('<?xml version="1.0"?>
<purchase_order>
<po_items>
<quantity>#number#</quantity>
<item>
<price>#Price#</price>
<name>#Name#</name>
<quantity>#number#</quantity>
</item>
</po_items>
</purchase_order>') xml from dual
)
--
--
select x.* 
  from t t, 
  xmltable('declare function local:index-of-node  ( $nodes as node()* ,  $nodeToFind as node() )  as xs:integer* 
                {
                   for $seq in (1 to count($nodes))  return $seq[$nodes[$seq] is $nodeToFind]
                } ;
               declare function local:path-to-node-with-pos  ( $node as node()? )  as xs:string 
               {
                   fn:string-join( for $ancestor in $node/ancestor-or-self::*
                                          let $sibsOfSameName := $ancestor/../*[fn:name() = fn:name($ancestor)]
                                       return fn:concat(fn:name($ancestor), 
                                                               if (fn:count($sibsOfSameName) <= 1)
                                                               then ""  else fn:concat( "[", local:index-of-node($sibsOfSameName,$ancestor),"]"))
                                                               , "/")
            };
           for $i in //po_items//* where ora:matches ($i, "^#\w+#$") return element e {local:path-to-node-with-pos($i), $i}' 
           passing t.xml
           columns value varchar2(50) path '*',
                        path varchar2(50) path 'text()'
           ) x
/
VALUE      PATH                                    
---------- ----------------------------------------
#number#   purchase_order/po_items/quantity        
#Price#    purchase_order/po_items/item/price      
#Name#     purchase_order/po_items/item/name       
#number#   purchase_order/po_items/item/quantity   

4 rows selected.

1 person found this helpful

754085

Thanks a lot. Now I try to get understand the code I use it. I hope it's works fine.

This will works fine:
<p style-name="P1">
- <frame style-name="fr1" name="graphics1" anchor-type="paragraph" width="3.175cm" height="1.879cm" z-index="0">
<image xlink:href="Pictures/100002000000007800000047C659D6E8.gif" xlink:type="simple" xlink:show="embed" xlink:actuate="onLoad" />
</frame>
#DOME#
</p>

it's will select the dome and path of it??

ravikumar.sv

this is simpler... ;-)

WITH tab AS
  (SELECT xmltype('<?xml version="1.0"?>
                  <purchase_order>
                  <po_items>
                  <quantity>#number#</quantity>
                  <item>
                  <price>#Price#</price>
                  <name>#Name#</name>
                  <quantity>#number#</quantity>
                  </item>
                  </po_items>
                  </purchase_order>') col
     FROM dual
  )
SELECT column_value
 FROM tab t,
xmltable(' 
  for $i in $tmp/descendant::*
    where $i/text() != ""
    return concat(string-join($i/ancestor-or-self::*/name(), "/"),"/","#",data($i),"#")
' passing t.col AS "tmp" )

Ravi Kumar

754085

And what it's return in your case??

ravikumar.sv

Gastons21 wrote:
And what it's return in your case??

not so sure what you mean by that. i think you asked me to show the result after executing...

SQL> ed
Wrote file afiedt.buf

  1  WITH tab AS
  2    (SELECT xmltype('<?xml version="1.0"?>
  3                    <purchase_order>
  4                    <po_items>
  5                    <quantity>#number#</quantity>
  6                    <item>
  7                    <price>#Price#</price>
  8                    <name>#Name#</name>
  9                    <quantity>#number#</quantity>
 10                    </item>
 11                    </po_items>
 12                    </purchase_order>') col
 13       FROM dual
 14    )
 15  SELECT nodepath,nodevalue
 16   FROM tab t,
 17  xmltable('
 18    for $i in $tmp/descendant::*
 19      where $i/text() != ""
 20      return <R><P>{string-join($i/ancestor-or-self::*/name(), "/")}</P><V>{$i/text()}</V></R>
 21  ' passing t.col AS "tmp" columns
 22    nodepath varchar2(50) path '//P',
 23*   nodevalue varchar2(50) path '//V')
SQL> /

NODEPATH                                           NODEVALUE
-------------------------------------------------- -------------------------------------------------
purchase_order/po_items/quantity                   #number#
purchase_order/po_items/item/price                 #Price#
purchase_order/po_items/item/name                  #Name#
purchase_order/po_items/item/quantity              #number#

Ravi Kumar

754085

Works like I need, but with one difference. There select all xml elements where is text.

But I need to select path and #variables# only!

for example:

with your code I get in result:

office.text/text:p	L?muma Nr.#numurs#

but I need:

office.text/text:p	 #numurs#

and places where not this #varaible_name# don't select into table!

Thanks for help! :)

Edited by: Gastons21 on Feb 19, 2010 2:30 AM

ravikumar.sv

if i understand you properly...you have a xml..and inside it you want to get a all node paths for a particular text.

SQL> ed
Wrote file afiedt.buf

  1  WITH tab AS
  2    (SELECT xmltype('<?xml version="1.0"?>
  3                    <purchase_order>
  4                    <po_items>
  5                    <quantity>#number#</quantity>
  6                    <item>
  7                    <price>#Price#</price>
  8                    <name>#Name#</name>
  9                    <quantity>#number#</quantity>
 10                    </item>
 11                    </po_items>
 12                    </purchase_order>') col
 13       FROM dual
 14    )
 15  SELECT nodepath,nodevalue
 16   FROM tab t,
 17  xmltable('
 18    for $i in $tmp/descendant::*
 19      where $i/text() = "#number#"
 20      return <R><P>{string-join($i/ancestor-or-self::*/name(), "/")}</P><V>{$i/text()}</V></R>
 21  ' passing t.col AS "tmp" columns
 22    nodepath varchar2(50) path '//P',
 23*   nodevalue varchar2(50) path '//V')
SQL> /

NODEPATH                                           NODEVALUE
-------------------------------------------------- -------------------------------------------------
purchase_order/po_items/quantity                   #number#
purchase_order/po_items/item/quantity              #number#

check for below line especially to be set...

where $i/text() = "#number#"

i shown an example for getting a nodepath for nodevalue #number# for the sample xml

Ravi Kumar

754085

xmltype('<?xml version="1.0"?>
                      <purchase_order>
                      <po_items>
                      <quantity>NUmber of Number: #number#</quantity>
                     <item>
                      <price>Unit price: #Price#</price>
                      <name> Unit name#Name#</name>
                     <quantity>Unit qant: #number#</quantity>
                   </item>
                     </po_items>
                    </purchase_order>') col

there is xml where nodes text ar:
NUmber of Number: #number#
Unit price: #Price#
Unit name#Name#
Unit qant: #number#

but I need get path of these nodes and only #variable_name# values. For example:

PATH                                                 VALUE
--------------------------------------            -----------
purchase_order/po_items/quantity                   #number#
purchase_order/po_items/item/quantity              #Price#
purchase_order/po_items/item/quantity              #Name#
purchase_order/po_items/item/quantity              #number#

I hope you understand me now wright :) And then all these values put in table where I store records :)

Edited by: Gastons21 on Feb 19, 2010 3:20 AM

Edited by: Gastons21 on Feb 19, 2010 3:21 AM

ravikumar.sv

WITH tab AS
  (SELECT xmltype('<?xml version="1.0"?>
                      <purchase_order>
                      <po_items>
                      <quantity>NUmber of Number: #number#</quantity>
                     <item>
                      <price>Unit price: #Price#</price>
                      <name> Unit name#Name#</name>
                     <quantity>Unit qant: #number#</quantity>
                   </item>
                     </po_items>
                    </purchase_order>') col

     FROM dual
  )
SELECT nodepath,regexp_substr(nodevalue,'#.*#') nodevalue
 FROM tab t,
xmltable(' 
  for $i in $tmp/descendant::*
    where $i/text() != ""
    return <R><P>{string-join($i/ancestor-or-self::*/name(), "/")}</P><V>{$i/text()}</V></R>
' passing t.col AS "tmp" columns
  nodepath varchar2(50) path '//P',
  nodevalue varchar2(50) path '//V')

just use a simple regular expression after getting data like above.

regexp_substr(nodevalue,'#.*#')

Scrapping the data is difficult after that we can use our normal oracle functions to modify the data in the manner you want. ;-)

Ravi Kumar

1 person found this helpful

754085

Ok..but there is output in xml, but how can I read datas into table of records??

ravikumar.sv

Gastons21 wrote:
Ok..but there is output in xml, but how can I read datas into table of records??

do you mean putting above selected data in a table??

SQL> create table test(nodepath varchar2(40),nodetext varchar2(40));

Table created.

SQL> ed
Wrote file afiedt.buf

  1  insert into test
  2  WITH tab AS
  3    (SELECT xmltype('<?xml version="1.0"?>
  4                        <purchase_order>
  5                        <po_items>
  6                        <quantity>NUmber of Number: #number#</quantity>
  7                       <item>
  8                        <price>Unit price: #Price#</price>
  9                        <name> Unit name#Name#</name>
 10                       <quantity>Unit qant: #number#</quantity>
 11                     </item>
 12                       </po_items>
 13                      </purchase_order>') col
 14       FROM dual
 15    )
 16  SELECT nodepath,regexp_substr(nodevalue,'#.*#') nodevalue
 17   FROM tab t,
 18  xmltable('
 19    for $i in $tmp/descendant::*
 20      where $i/text() != ""
 21      return <R><P>{string-join($i/ancestor-or-self::*/name(), "/")}</P><V>{$i/text()}</V></R>
 22  ' passing t.col AS "tmp" columns
 23    nodepath varchar2(50) path '//P',
 24*   nodevalue varchar2(50) path '//V')
SQL> /

4 rows created.

SQL> select * from test;

NODEPATH                                 NODETEXT
---------------------------------------- ----------------------------------------
purchase_order/po_items/quantity         #number#
purchase_order/po_items/item/price       #Price#
purchase_order/po_items/item/name        #Name#
purchase_order/po_items/item/quantity    #number#

SQL> drop table test;

Table dropped.

SQL>

Ravi Kumar

1 person found this helpful

754085

Please can you help me little bit more :) how can I put these values into table of record? :)
Theres code:

this is code in package:
TYPE Var_Names IS RECORD
 (
   v_name     VARCHAR2(100),
   XPath      VARCHAR2(500)
 );
 TYPE Var_Name_List IS TABLE OF Var_Names;

 Variables Var_Name_List;

this is code in body package:
  FUNCTION selectVariablesInList(tempXML XMLType) RETURN Var_Name_List
    IS
    BEGIN
      WITH tab AS
      (SELECT tempXML col
        FROM dual
     )
     SELECT nodepath, regexp_substr(nodevalue,'#.*#') nodevalue INTO Variables

      FROM tab t,
     xmltable('
       for $i in $tmp/descendant::*
         where $i/text() != ""
        return <R><P>{string-join($i/ancestor-or-self::*/name(), "/")}</P><V>{$i/text()}</V></R>' 
        passing t.col AS "tmp" columns
      nodepath varchar2(50) path '//P',
      nodevalue varchar2(50) path '//V'); 
    
     RETURN Variables;   
    END;

ravikumar.sv

you need to use sql types not pl/sql records for using inside sql queries within functions...check this it will be useful....replace the code as required for you...

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE type type_node
  2  AS
  3    object
  4    (
  5      nodepath  VARCHAR2(100),
  6*     nodevalue VARCHAR2(500) );
SQL> /

Type created.

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE type type_node_tab
  2  AS
  3*   TABLE OF type_node;
SQL> /

Type created.

SQL> ed
Wrote file afiedt.buf

  1  create or replace
  2  PACKAGE test_ravi AS
  3     function selectVariablesInList (tempXML XMLType) RETURN type_node_tab;
  4* END test_ravi;
SQL> /

Package created.

SQL> ed
Wrote file afiedt.buf

  1  create or replace
  2  PACKAGE BODY test_ravi AS
  3     FUNCTION selectVariablesInList(tempXML XMLType) RETURN type_node_tab
  4      IS
  5      v_type_node_tab type_node_tab;
  6      BEGIN
  7        select cast(multiset(
  8  select nodepath,nodevalue from (
  9  WITH tab AS
 10        (SELECT tempXML col
 11          FROM dual
 12       )
 13       SELECT nodepath,regexp_substr(nodevalue,'#.*#') nodevalue
 14       FROM tab t,
 15       xmltable('
 16         for $i in $tmp/descendant::*
 17           where $i/text() != ""
 18          return <R><P>{string-join($i/ancestor-or-self::*/name(), "/")}</P><V>{$i/text()}</V></R>'
 19          passing t.col AS "tmp" columns
 20        nodepath  VARCHAR2(100) path '//P',
 21        nodevalue VARCHAR2(500) path '//V'))
 22  ) as type_node_tab) into v_type_node_tab from dual;
 23       RETURN v_type_node_tab;
 24      END;
 25* END test_ravi;
SQL> /

Package body created.

SQL> ed
Wrote file afiedt.buf

  1  select * from table(test_ravi.selectvariablesinlist(xmltype('<?xml version="1.0"?>
  2                        <purchase_order>
  3                        <po_items>
  4                        <quantity>NUmber of Number: #number#</quantity>
  5                       <item>
  6                        <price>Unit price: #Price#</price>
  7                        <name> Unit name#Name#</name>
  8                       <quantity>Unit qant: #number#</quantity>
  9                     </item>
 10                       </po_items>
 11*                     </purchase_order>')))
SQL> /

NODEPATH
----------------------------------------------------------------------------------------------------
NODEVALUE
----------------------------------------------------------------------------------------------------
purchase_order/po_items/quantity
#number#

purchase_order/po_items/item/price
#Price#

purchase_order/po_items/item/name
#Name#


NODEPATH
----------------------------------------------------------------------------------------------------
NODEVALUE
----------------------------------------------------------------------------------------------------
purchase_order/po_items/item/quantity
#number#


SQL>

Ravi Kumar

1 person found this helpful

754085

can't save the values into record based table???

754085

If I make object, then it's not saves into package but into type folder. I want all in one place!

ravikumar.sv

you can do it...but it's a bit tedious to execute....
and the worst thing is you cannot call a function inside a query(as I shown in the last statement of above post) to obtain data, if you define return types to be pl/sql record types,so better they be sql types.

and also this will be easy method to access data for an input xml...
just a simple query needs to be run ...
instead of running a pl/sql block, retrieving the data,copying into local variables and looping on that data.

Ravi Kumar

1 person found this helpful

754085

But I Don't need from sql get these values in the end. I need this list to replace values from databases and do some work with xml , update it and then return xmltype like blob into database.

MichaelS

this is simpler...

Right. Should simply have searched a bit more intensively to find path-to-node ;)

1 - 25

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 20 2024

Added on Oct 1 2020

#19-3-3, #features, #new-features, #new-release, #oracle-developer-tools-for-vs-code

67 comments

1,702 views

1 mention

Oracle Developer Tools for VS Code

New Release: 19.3.3. Lots of features! Try it out and let us know what you think

Comments

Post Details