Home » Developer & Programmer » Application Express & MOD_PLSQL » create remote file with util_file (Oracle 11)
create remote file with util_file [message #578085] Sun, 24 February 2013 07:10 Go to next message
BLROracle
Messages: 1
Registered: February 2013
Junior Member

Hello,

i´am a newon pl/sql and i have question about util_file.
My environment:

Database is installed on some server, which i have no access.
I have to export some data from this database.

1.At first, i have to create some csv and xml files.
I want to do that with command "create directory"
But it does not work, because i cant no permission on this server.
Because of that i want to create these files remote:

Create directory("\\localmashine_ip\temp");


But it does not work.

How it could be works?
or
What i have to do, to select data from remote database.
Save this data on csv and xml
Transport these csv and xml files





Re: create remote file with util_file [message #578136 is a reply to message #578085] Sun, 24 February 2013 09:19 Go to previous message
Littlefoot
Messages: 19824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you don't have access to the server (meaning: a computer) (by the way, WHY don't you have access to it? Can't you negotiate that with the DBA?), it is useless to even try to fool operating system and Oracle restrictions.

Anyway, an ordinary SELECT statement is capable of creating a CSV file on your own computer - you just need to SPOOL the output into a file:
SQL> set colsep ','
SQL> spool test.csv
SQL> select * from dept;

    DEPTNO,DNAME         ,LOC
----------,--------------,-------------
        10,ACCOUNTING    ,NEW YORK
        20,RESEARCH      ,DALLAS
        30,SALES         ,CHICAGO
        40,OPERATIONS    ,BOSTON

SQL> spool off;
SQL> $type test.csv
SQL> select * from dept;

    DEPTNO,DNAME         ,LOC
----------,--------------,-------------
        10,ACCOUNTING    ,NEW YORK
        20,RESEARCH      ,DALLAS
        30,SALES         ,CHICAGO
        40,OPERATIONS    ,BOSTON

SQL> spool off;

SQL>

Review SQL*Plus Reference book for more details on creating reports in that tool (hint: SET commands):
SQL> help set

 SET
 ---

 Sets a system variable to alter the SQL*Plus environment settings
 for your current session. For example, to:
     -   set the display width for data
     -   customize HTML formatting
     -   enable or disable printing of column headings
     -   set the number of lines per page
 In iSQL*Plus, you can also use the Preferences screen to set
 system variables.

 SET system_variable value

 where system_variable and value represent one of the following clauses:

   APPI[NFO]{OFF|ON|text}                   NEWP[AGE] {1|n|NONE}
   ARRAY[SIZE] {15|n}                       NULL text
   AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}      NUMF[ORMAT] format
   AUTOP[RINT] {OFF|ON}                     NUM[WIDTH] {10|n}
   AUTORECOVERY {OFF|ON}                    PAGES[IZE] {14|n}
   AUTOT[RACE] {OFF|ON|TRACE[ONLY]}         PAU[SE] {OFF|ON|text}
     [EXP[LAIN]] [STAT[ISTICS]]             RECSEP {WR[APPED] |
   BLO[CKTERMINATOR] {.|c|OFF|ON}                    EA[CH]|OFF}
etd.


The same goes with XML.
SQL> select xmlelement("Dname", dname)
  2  from dept;

XMLELEMENT("DNAME",DNAME)
---------------------------------------
<Dname>ACCOUNTING</Dname>
<Dname>RESEARCH</Dname>
<Dname>SALES</Dname>
<Dname>OPERATIONS</Dname>

SQL>


Therefore, you don't really need to have access to the server (unless, for some reason, you do).


Previous Topic: Populating table
Next Topic: Forms in Apex
Goto Forum:
  


Current Time: Fri Nov 28 18:25:51 CST 2014

Total time taken to generate the page: 0.14550 seconds