DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Export to txt file

  1. #1
    Join Date
    Nov 2002
    Location
    Argentina
    Posts
    24

    Export to txt file

    I have this table structure and I need to extract all data in a txt file. Is there some simple way to extract it separeted with commas or '|'

    Table definition
    Empresa char(2) Not null,
    escp_def escp_def_array_type

    escp_def_array_type array definition
    escp_def_type [10]


    escp_def_type object definition
    escp_defecto varchar2(4)
    Marcelo Quesada
    :)

  2. #2
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    use spool
    did u try this ??

    spool test.txt
    select Empresa || ',' || escp_def from table ;
    spool off
    Last edited by prakashs43; 12-02-2002 at 10:44 AM.

  3. #3
    Join Date
    Nov 2002
    Location
    Argentina
    Posts
    24
    I already tried to use spool but i got the following error:
    select Empresa || ',' || escp_def from sad_desbastes
    *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected NUMBER got ADAS.ESCP_DEF_ARRAY_TYPE
    Marcelo Quesada
    :)

  4. #4
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    try this
    select Empresa || ',' || to_char(escp_def) from table ;

  5. #5
    Join Date
    Nov 2002
    Location
    Argentina
    Posts
    24
    is not working .. the same error
    Marcelo Quesada
    :)

  6. #6
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Is this what you have?
    Code:
    SQL> CREATE TYPE escp_def_array_type AS VARRAY(3) OF varchar2(4);
      2  /
    Type created.
    
    SQL> CREATE table sad_desbastes (
      2  Empresa char(2) Not null, 
      3  escp_def escp_def_array_type)
      4  ;
    
    Table created.
    
    SQL> INSERT INTO  sad_desbastes VALUES ('AA', escp_def_array_type('How','are','you'));
    1 row created.
    SQL> INSERT INTO  sad_desbastes VALUES ('BB', escp_def_array_type(' I','am','fine'));
    1 row created.
    
    SQL> commit;
    Commit complete.
    
    SQL> spool c:\spool.txt
    SQL> 
    SQL> SELECT a.Empresa||',', b.*   
      2  FROM sad_desbastes a, table (a.escp_def) b; 
    
    A.E COLU
    --- ----
    AA, How
    AA, are
    AA, you
    BB,  I
    BB, am
    BB, fine
    
    6 rows selected.
    SQL> spool off

  7. #7
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    i think the trick is USING ALIASES for user defined types .
    thanks sameer.

  8. #8
    Join Date
    Nov 2002
    Location
    Argentina
    Posts
    24
    I have this ..

    CREATE TYPE "ADAS"."ESCO_DEF_TYPE" AS OBJECT ( "ESCP_DEFECTO"
    VARCHAR2(4) )

    CREATE TYPE "ADAS"."ESCP_DEF_ARRAY_TYPE" AS VARRAY (10) OF
    "ADAS"."ESCP_DEF_TYPE"

    CREATE table sad_desbastes (
    Empresa char(2) Not null,
    escp_def escp_def_array_type)

    and i want to create a txt file to insert data using sqlloader for example .
    I hope someone can help me. Im using Oracle 9.2
    Last edited by mquesada; 12-02-2002 at 03:47 PM.
    Marcelo Quesada
    :)

  9. #9
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    I guess still it works and spools .txt file comma seperated... Its on O9i R2 which can be used for SQL*Loader. :-)

    Code:
    SQL> CREATE TYPE ESCO_DEF_TYPE AS OBJECT (ESCP_DEFECTO VARCHAR2(6));
      2  /
    
    Type created.
    
    SQL> CREATE TYPE ESCP_DEF_ARRAY_TYPE AS VARRAY(10) OF ESCO_DEF_TYPE;
      2  /
    
    Type created.
    
    SQL> CREATE table sad_desbastes (
      2  Empresa char(2) Not null,
      3  escp_def escp_def_array_type);
    
    Table created.
    
    SQL> INSERT INTO sad_desbastes VALUES
      2  ('01',
      3  ESCP_DEF_ARRAY_TYPE(ESCO_DEF_TYPE('Ein'),
      4  ESCO_DEF_TYPE('Zwei'),
      5  ESCO_DEF_TYPE('Drei'),
      6  ESCO_DEF_TYPE('Vier'),
      7  ESCO_DEF_TYPE('Fünf'),
      8  ESCO_DEF_TYPE('Sechs'),
      9  ESCO_DEF_TYPE('Sieben'),
     10  ESCO_DEF_TYPE('Acht'),
     11  ESCO_DEF_TYPE('Neun'),
     12  ESCO_DEF_TYPE('Zehn')));
    
    1 row created.
    
    SQL> INSERT INTO sad_desbastes VALUES
      2  ('02',
      3  ESCP_DEF_ARRAY_TYPE(ESCO_DEF_TYPE('Ein'),
      4  ESCO_DEF_TYPE('Zwei'),
      5  ESCO_DEF_TYPE('Drei'),
      6  ESCO_DEF_TYPE('Vier'),
      7  ESCO_DEF_TYPE('Fünf'),
      8  ESCO_DEF_TYPE('Sechs'),
      9  ESCO_DEF_TYPE('Sieben'),
     10  ESCO_DEF_TYPE('Acht'),
     11  ESCO_DEF_TYPE('Neun'),
     12  ESCO_DEF_TYPE('Zehn')));
    
    1 row created.
    
    SQL> INSERT INTO sad_desbastes VALUES
      2  ('03',
      3  ESCP_DEF_ARRAY_TYPE(ESCO_DEF_TYPE('Ein'),
      4  ESCO_DEF_TYPE('Zwei'),
      5  ESCO_DEF_TYPE('Drei'),
      6  ESCO_DEF_TYPE('Vier'),
      7  ESCO_DEF_TYPE('Fünf'),
      8  ESCO_DEF_TYPE('Sechs'),
      9  ESCO_DEF_TYPE('Sieben'),
     10  ESCO_DEF_TYPE('Acht'),
     11  ESCO_DEF_TYPE('Neun'),
     12  ESCO_DEF_TYPE('Zehn')));
    
    1 row created.
    
    SQL> spool c:\output.txt
    SQL> 
    SQL> SELECT a.Empresa||',', b.*
      2  FROM sad_desbastes a, table (a.escp_def) b;
    
    A.E ESCP_D                                                                      
    --- ------                                                                      
    01, Ein                                                                         
    01, Zwei                                                                        
    01, Drei                                                                        
    01, Vier                                                                        
    01, Fünf                                                                        
    01, Sechs                                                                       
    01, Sieben                                                                      
    01, Acht                                                                        
    01, Neun                                                                        
    01, Zehn                                                                        
    02, Ein                                                                         
    02, Zwei                                                                        
    02, Drei                                                                        
    02, Vier                                                                        
    02, Fünf                                                                        
    02, Sechs                                                                       
    02, Sieben                                                                      
    02, Acht                                                                        
    02, Neun                                                                        
    02, Zehn                                                                        
    03, Ein                                                                         
    03, Zwei                                                                        
    03, Drei                                                                        
    03, Vier                                                                        
    03, Fünf                                                                        
    03, Sechs                                                                       
    03, Sieben                                                                      
    03, Acht                                                                        
    03, Neun                                                                        
    03, Zehn                                                                        
    
    30 rows selected.
    
    SQL> spool off;
    HTH

    Sameer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width