Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2005
    Posts
    116

    Unanswered: Concatenate all fields with '|' without specifying field name

    Hi guys,
    I have a question here. Lets say i want to select out data from
    tableA and add '|' between each field, without specifying the
    field name, is it possible?

    Meaning lets say tableA has 320 fields, i dont want to do this

    select fields1 || '|' || field2 || '|' ...

    Im aware that we can select all values by doing so
    select * from tableA, but how do i add '|' between
    each value in the column without specifying field name?

    So the output will be
    field1|field2|field3...

    thanks for any help..

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Sql> Set Colsep '|'

  3. #3
    Join Date
    Feb 2005
    Posts
    116
    Quote Originally Posted by Littlefoot
    Sql> Set Colsep '|'
    thanks..
    Just wanted to clarify one thing..i plan to export out data into
    a text file from table. Based on your opinion, which method is
    more practical and efficient in terms of the performance..
    1) using utl_file
    2) spool data

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Simple spooling is easier (spool on, spool off) - UTL_FILE requires a little bit more typing, so - if you are not exporting huge amount of data, I'd rather use spooling.

  5. #5
    Join Date
    Feb 2005
    Posts
    116
    Quote Originally Posted by Littlefoot
    Simple spooling is easier (spool on, spool off) - UTL_FILE requires a little bit more typing, so - if you are not exporting huge amount of data, I'd rather use spooling.
    thanks ..appreciate your feedback

  6. #6
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Additioinally: utl_file will only operate on files on the server, whereas with spool you can create the file on the client

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by shammat
    Additioinally: utl_file will only operate on files on the server, whereas with spool you can create the file on the client
    Yeah, and it's slowwer, can't be automated an will blow out your buffers..do it the right way, every time, don't fall in to bad habits

    Right Blind dude?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by Brett Kaiser
    Yeah, and it's slowwer, can't be automated an will blow out your buffers..do it the right way, every time, don't fall in to bad habits

    Right Blind dude?
    I think that you are thinking about dbms_output. I have read/written file over a gig in size using utl_file.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm saying to not get used to using spool
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Didnt quite get your coment about spooling from sqlplus. Whats so bad about it?

    Alan

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by AlanP
    Didnt quite get your coment about spooling from sqlplus. Whats so bad about it?

    Alan
    It's like a hack, can't be moved to production, is slow, eats up buffer, can't do larger volums of data and takes time away from practicing with packages and procedures to do it the correct way and increase your skill set.....what else....using it as a crutch....there's got to be more reasons
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I have to disagree to a certain extent, it's not perfect but it can be quite useful even in a production environment.

    It is actually quite efficient as it is a C/OCI app. So you can and I have seen it in a number of places being used to dump quite large quantities of data (many GBs). I am using it currently to transfer large quantities of data as csv files mainly because it is very quick and easy to setup and it is very fast at dumping out data. You will actually find it a faster than using procedures with utl_file (if your comparing like with like i.e. both running on the db server). Your comment about buffers doesnt really make much sense to me, if you mean OS/file system buffering, any app which dump out to a file will use these to the same extent as sqlplus.

    It can be automated, in UNIX it is quite easy to call sqlplus from a shell script and thus it can be called from a cron job.

    Finally it is actually quite important to get familiar with sqlplus as it is the one and only app you can pretty much rely on being installed at any Oracle site you visit.

    Alan

  13. #13
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I think the problem they might have is the screen display while spooling. This will really slow up the spooling. If you have set termout off before you spool, the spooling will go VERY fast.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  14. #14
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here's a little script that I just used to pull the data, delimited by | and spools to a text file ... and yes it sets termout off ... spooling is a great tool ... while it does have some issues, I've used it throughout the years and has been invaluable !!!

    Save the file ... Call it in sqlplus as I show at the top ... pass the table_name you are looking to extract ...

    HTH
    Gregg

    -----------------------------------------------------------------------

    --
    --
    -- @C:\DBA\Oracle9\Maintenance\Delimited_Build_Column _List.sql
    --
    --
    set term off
    store set sqlplus_settings.sql replace
    set term on feed off
    set pagesize 0
    set linesize 1000
    set heading off
    SET VERIFY OFF

    set serverout on size 1000000 autoprint off

    var tablename VARCHAR2(30)
    exec :tablename := UPPER('&&TBLNAME')
    SPOOL C:\PSA_Clients\Creform\Conversion\Scripts\&&TBLNAM E..SQL
    DECLARE
    k_fullname CONSTANT VARCHAR2(50) := :tablename;
    v_tablename VARCHAR2(30);
    v_owner VARCHAR2(30);

    CURSOR c_columns
    ( cp_tablename VARCHAR2
    , cp_owner VARCHAR2 DEFAULT NULL )
    IS
    SELECT owner
    , table_name
    , LOWER(column_name) column_name
    FROM all_tab_columns
    WHERE table_name = cp_tablename
    AND ( owner =
    ( SELECT table_owner
    FROM all_synonyms
    WHERE synonym_name = cp_tablename
    AND ( (cp_owner IS NULL AND owner IN (user, 'PUBLIC' ))
    OR owner = cp_owner ) )
    OR ( cp_owner IS NULL
    AND owner = user
    AND NOT EXISTS
    ( SELECT 1
    FROM all_synonyms
    WHERE synonym_name = cp_tablename
    AND owner IN (user, 'PUBLIC') )
    )
    OR owner = cp_owner
    )
    ORDER BY column_id;

    v_delimiter VARCHAR2(8) := ' ';
    BEGIN
    IF k_fullname LIKE '%.%' THEN
    v_owner := SUBSTR(k_fullname,1,INSTR(k_fullname,'.') -1);
    -- DBMS_OUTPUT.PUT_LINE('Owner: ' || v_owner);

    v_tablename := SUBSTR(k_fullname,INSTR(k_fullname,'.') +1);
    ELSE
    v_tablename := k_fullname;
    END IF;

    FOR r IN c_columns(v_tablename, v_owner)
    LOOP
    IF c_columns%ROWCOUNT = 1 THEN
    DBMS_OUTPUT.PUT_LINE('set term OFF feed off PAGESIZE 0 LINESIZE 2500 HEADING OFF trimspool on long 2000');
    DBMS_OUTPUT.PUT_LINE('SPOOL C:\\Conversion\Data\&&TBLNAME..TXT');
    DBMS_OUTPUT.PUT_LINE('select ');
    END IF;

    DBMS_OUTPUT.NEW_LINE;
    DBMS_OUTPUT.PUT(v_delimiter || r.column_name );
    v_delimiter := '||' || ''''||'|'||'''' || '||';
    END LOOP;

    DBMS_OUTPUT.PUT(' from '||v_tablename||';');
    DBMS_OUTPUT.NEW_LINE;
    DBMS_OUTPUT.PUT_LINE('SPOOL OFF');
    DBMS_OUTPUT.PUT_LINE('@sqlplus_settings');
    END;
    /
    UNDEF TBLNAME
    SPOOL OFF
    @sqlplus_settings
    set term on

  15. #15
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hello, Gregg! Nice to see you again! Where have you been all this time?

Posting Permissions

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