OraFAQ Forum: JDeveloper, Java & XML » how can me write into xml file ? (merged)

Home » Developer & Programmer » JDeveloper, Java & XML » how can me write into xml file ? (merged) (oracle 11g , windows7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how can me write into xml file ? (merged) [message #496217]

Fri, 25 February 2011 05:20

ocean9
Messages: 17
Registered: February 2011
Location: syria

Junior Member

hi experts

SQL> desc res;
Name Null? Type
----------------------------------------- -------- ----------------------------
RESULT PUBLIC.XMLTYPE

SQL> select * from res;

RESULT
--------------------------------------------------------------------------------
<fine No="2"><stdNo>2</stdNo><value>300</value><reason>breaks keyboard</reason><
date>2011-10-03</date></fine>

how can me write results of select statement into xml file instead of show them on screen??
please help me

regards
ocean9

Report message to a moderator

Re: how can me write into xml file ? [message #496221 is a reply to message #496217]

Fri, 25 February 2011 05:35

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

set termout off
set long 1000000000
set trimspool on
spool myxml
select ...
spool off

Put it in a script and execute the script.

Regards
Michel

[Updated on: Fri, 25 February 2011 05:36]

Report message to a moderator

Re: how can me write into xml file ? [message #496226 is a reply to message #496221]

Fri, 25 February 2011 06:06

ocean9
Messages: 17
Registered: February 2011
Location: syria

Junior Member

Thanks Michel
But I am new in this area, so please type the full code

Regards
ocean9

[Updated on: Fri, 25 February 2011 06:07]

Report message to a moderator

Re: how can me write into xml file ? [message #496229 is a reply to message #496226]

Fri, 25 February 2011 06:26

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I already put the whole code, what do you want more?

sqlplus usr/psw @myscript

Regards
Michel

[Updated on: Fri, 25 February 2011 06:26]

Report message to a moderator

Re: how can me write into xml file ? [message #496232 is a reply to message #496229]

Fri, 25 February 2011 06:42

ocean9
Messages: 17
Registered: February 2011
Location: syria

Junior Member

Thanks Michel
I'll try it

Regards

Report message to a moderator

how write results of select statement into xml file ? [message #496367 is a reply to message #496217]

Sun, 27 February 2011 04:47

ocean9
Messages: 17
Registered: February 2011
Location: syria

Junior Member

hi experts

SQL> desc res;
Name Null? Type
----------------------------------------- -------- ----------------------------
RESULT PUBLIC.XMLTYPE

SQL> select * from res;

RESULT
--------------------------------------------------------------------------------
<fine No="2"><stdNo>2</stdNo><value>300</value><reason>breaks keyboard</reason><
date>2011-10-03</date></fine>

how can me write results of select statement into xml file instead of show them on screen??
please help me

i tried :
set termout off
set long 1000000000
set trimspool on
spool myxml
select ...
spool off

and i got this error
SQL>can not create spool file "myxml"

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496368 is a reply to message #496367]

Sun, 27 February 2011 04:52

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why do you start a new topic for the continuation of a previous one?

If SQL*Plus cannot create the file this is because either:
- the target directory does not exist
- you have not the privilege to write into
- the spool file already exists and you have not the privilege to overwrite it.

Regards
Michel

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496369 is a reply to message #496368]

Sun, 27 February 2011 05:01

ocean9
Messages: 17
Registered: February 2011
Location: syria

Junior Member

I'm sorry Michel
You wrote " spool myxml "
How do I create myxml
( i log in as system )
I'm really new to this area

Regards
ocean9

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496370 is a reply to message #496369]

Sun, 27 February 2011 05:04

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have nothing to do, SQL*Plus does it... if it can.

Regards
Michel

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496371 is a reply to message #496370]

Sun, 27 February 2011 05:10

ocean9
Messages: 17
Registered: February 2011
Location: syria

Junior Member

can you write example??
thank you very much

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496372 is a reply to message #496371]

Sun, 27 February 2011 05:14

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Read my previous posts.

Your code is correct, only:

Quote:

If SQL*Plus cannot create the file this is because either:
- the target directory does not exist
- you have not the privilege to write into
- the spool file already exists and you have not the privilege to overwrite it.

Specify a spool file in a directory you are allowed to write:
Quote:

spool <directory I have right to write into>\myxml.lst

Regards
Michel

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496394 is a reply to message #496372]

Sun, 27 February 2011 12:53

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

If you have this:

SCOTT@orcl_11gR2> create table res
  2    (result	xmltype)
  3  /

Table created.

SCOTT@orcl_11gR2> insert into res values (xmltype (
  2  '<?xml version="1.0"?>
  3   <fine No="2">
  4  	<stdNo>2</stdNo>
  5  	<value>300</value>
  6  	<reason>breaks keyboard</reason>
  7  	<date>2011-10-03</date>
  8   </fine>'))
  9  /

1 row created.

SCOTT@orcl_11gR2> desc res
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RESULT                                             PUBLIC.XMLTYPE

SCOTT@orcl_11gR2> select * from res
  2  /

RESULT
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<fine No="2">
  <stdNo>2</stdNo>
  <value>300</value>
  <reason>breaks keyboard</reason>
  <date>2011-10-03</date>
</fine>


1 row selected.

SCOTT@orcl_11gR2>

and you do this, substituting some directory path on your system that you can write to for c:\my_oracle_files\:

store set saved_settings replace
set echo off 
set feedback off 
set heading off 
set long 1000000000 
set pagesize 0 
set termout off 
set trimspool on 
set verify off
spool c:\my_oracle_files\myxml.txt
select * from res
/
spool off
start saved_settings
ed c:\my_oracle_files\myxml.txt

-- then you should end up in an edit screen of your file myxml.txt in your directory and see this:

<?xml version="1.0"?>
<fine No="2">
  <stdNo>2</stdNo>
  <value>300</value>
  <reason>breaks keyboard</reason>
  <date>2011-10-03</date>
</fine>

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496515 is a reply to message #496394]

Mon, 28 February 2011 13:18

ocean9
Messages: 17
Registered: February 2011
Location: syria

Junior Member

Hi all
i tried :

Enter user-name: system
Enter password:

Connected to:
Personal Oracle Database 11g Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> store set saved_settings replace
SP2-0606: Cannot create STORE file "saved_settings.sql"
SQL>

what do for this problem??

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496516 is a reply to message #496515]

Mon, 28 February 2011 13:39

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Sun, 27 February 2011 12:14

Read my previous posts.

Your code is correct, only:

Quote:
If SQL*Plus cannot create the file this is because either:
- the target directory does not exist
- you have not the privilege to write into
- the spool file already exists and you have not the privilege to overwrite it.

...

Until you resolved this part, you will still have this problem.

[Updated on: Mon, 28 February 2011 13:39]

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496518 is a reply to message #496516]

Mon, 28 February 2011 13:43

ocean9
Messages: 17
Registered: February 2011
Location: syria

Junior Member

Why ?????

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496519 is a reply to message #496518]

Mon, 28 February 2011 13:58

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why what???

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496520 is a reply to message #496516]

Mon, 28 February 2011 14:02

ocean9
Messages: 17
Registered: February 2011
Location: syria

Junior Member

...[/quote]

Until you resolved this part, you will still have this problem.

[/quote]

why ???
please help me i need do this

regards

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496523 is a reply to message #496520]

Mon, 28 February 2011 14:12

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I donn't understand what you don't understand what I said 3 or 4 times in this topic:

Quote:

If SQL*Plus cannot create the file this is because either:
- the target directory does not exist
- you have not the privilege to write into
- the spool file already exists and you have not the privilege to overwrite it.

Regards
Michel

[Updated on: Mon, 28 February 2011 14:14]

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496527 is a reply to message #496523]

Mon, 28 February 2011 14:27

ocean9
Messages: 17
Registered: February 2011
Location: syria

Junior Member

please Barbara Boehmer you help me

regards
ocean9

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496534 is a reply to message #496527]

Mon, 28 February 2011 15:31

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Apparently you do not have access to your default directory, so you need to specify a directory to store saved_settings to. You will need to substitute the directory path of a directory that you have write privileges to on your system for c:\my_oracle_files\ eveywhere in the below code. You can skip the lines with saved_settings in them if you like. They are not necessary, just convenient. They will save and restore your previous settings, but this can be done by exiting the session, then reconnecting.

-- run this:

store set c:\my_oracle_files\saved_settings replace
set echo off 
set feedback off 
set heading off 
set long 1000000000 
set pagesize 0 
set termout off 
set trimspool on 
set verify off
spool c:\my_oracle_files\myxml.txt
select * from res
/
spool off
start c:\my_oracle_files\saved_settings
ed c:\my_oracle_files\myxml.txt

-- or just run this, then reconnect to restore your settings:

set echo off 
set feedback off 
set heading off 
set long 1000000000 
set pagesize 0 
set termout off 
set trimspool on 
set verify off
spool c:\my_oracle_files\myxml.txt
select * from res
/
spool off
ed c:\my_oracle_files\myxml.txt

Report message to a moderator

Re: how write results of select statement into xml file ? [message #496866 is a reply to message #496534]

Wed, 02 March 2011 15:57

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

And just to add one comment: Don't do it as system user.

The system user is a special user that should only be used for database administration tasks.

Don't use it to do anything regarding to application logic. The system user works completely different from other "normal" users in certain places.

Don't create user objects in the system schema either, create objects in a normal user account.

Report message to a moderator

Previous Topic:	How To Control <Af:panelspliter> At Runtime
Next Topic:	Query XML Data (2 Merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 17 20:24:33 CST 2024