Skip to Main Content
Forums

SQL Developer

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!

sqldeveloper-4.0.3.16.84-x64 does not open

Minoo.LApr 20 2015 — edited Apr 21 2015

Hi There

I've downloaded  sqldeveloper-4.0.3.16.84-x64.zip for Windows 64-bit - zip file includes the JDK 7.

unziped the file, but when I click on sqldeveloper.exe, a window will open and ask is it verified for run..I click on run button, but nothing will get opened.

Could anyone help me how can i start sql developer?
This post has been answered by Gary Graham-Oracle on Apr 20 2015
Jump to Answer

Comments

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.

Post Details

Locked on May 19 2015
Added on Apr 20 2015
#sql-developer
2 comments
956 views