Home » Developer & Programmer » JDeveloper, Java & XML » how can me write into xml file ? (merged) (oracle 11g , windows7)
how can me write into xml file ? (merged) [message #496217] Fri, 25 February 2011 05:20 Go to next message
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
Re: how can me write into xml file ? [message #496221 is a reply to message #496217] Fri, 25 February 2011 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
ocean9
Messages: 17
Registered: February 2011
Location: syria
Junior Member

Thanks Michel
I'll try it

Regards
how write results of select statement into xml file ? [message #496367 is a reply to message #496217] Sun, 27 February 2011 04:47 Go to previous messageGo to next message
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"
Re: how write results of select statement into xml file ? [message #496368 is a reply to message #496367] Sun, 27 February 2011 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: how write results of select statement into xml file ? [message #496369 is a reply to message #496368] Sun, 27 February 2011 05:01 Go to previous messageGo to next message
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
Re: how write results of select statement into xml file ? [message #496370 is a reply to message #496369] Sun, 27 February 2011 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have nothing to do, SQL*Plus does it... if it can.

Regards
Michel
Re: how write results of select statement into xml file ? [message #496371 is a reply to message #496370] Sun, 27 February 2011 05:10 Go to previous messageGo to next message
ocean9
Messages: 17
Registered: February 2011
Location: syria
Junior Member

can you write example??
thank you very much
Re: how write results of select statement into xml file ? [message #496372 is a reply to message #496371] Sun, 27 February 2011 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: how write results of select statement into xml file ? [message #496394 is a reply to message #496372] Sun, 27 February 2011 12:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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>

Re: how write results of select statement into xml file ? [message #496515 is a reply to message #496394] Mon, 28 February 2011 13:18 Go to previous messageGo to next message
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??
Re: how write results of select statement into xml file ? [message #496516 is a reply to message #496515] Mon, 28 February 2011 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
ocean9
Messages: 17
Registered: February 2011
Location: syria
Junior Member

Why ?????
Re: how write results of select statement into xml file ? [message #496519 is a reply to message #496518] Mon, 28 February 2011 13:58 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why what???
Re: how write results of select statement into xml file ? [message #496520 is a reply to message #496516] Mon, 28 February 2011 14:02 Go to previous messageGo to next message
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
Re: how write results of select statement into xml file ? [message #496523 is a reply to message #496520] Mon, 28 February 2011 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
ocean9
Messages: 17
Registered: February 2011
Location: syria
Junior Member

please Barbara Boehmer you help me

regards
ocean9
Re: how write results of select statement into xml file ? [message #496534 is a reply to message #496527] Mon, 28 February 2011 15:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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

Re: how write results of select statement into xml file ? [message #496866 is a reply to message #496534] Wed, 02 March 2011 15:57 Go to previous message
ThomasG
Messages: 3211
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.

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


Current Time: Tue Jul 27 23:23:59 CDT 2021