how to filter only records from output.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: how to filter only records from output.

  1. #1
    Join Date
    Mar 2007
    Posts
    7

    how to filter only records from output.

    HI,

    i have written a script to give the inpuit(tablename) explicitly.so that it will spool the records in a file.the problem is that it dispalys the rownumber also ,that is if its 1st record,it will display 1 and if its there for 45 records it will display 1 to 45.I want to eliminate the row columnwhich displays rownumber.while spooling it it dispalys like

    exec input_table('T24_CACCOUNT');
    1,222222284001,2222222,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    2,222222384001,2222223,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    3,222222484001,2222224,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    4,222222584001,2222225,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    5,222222684001,2222226,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    6,222222284001,2222222,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    7,222222384001,2222223,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    8,222222484001,2222224,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    9,222222584001,2222225,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    10,222222684001,2222226,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    11,222222284001,2222222,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    12,222222384001,2222223,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    13,222222484001,2222224,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    14,222222584001,2222225,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    15,222222684001,2222226,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

    this is my actual output.

    what can be done if i have to get the desired output

    222222284001,2222222,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    222222384001,2222223,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    222222484001,2222224,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    222222584001,2222225,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    222222684001,2222226,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    222222284001,2222222,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    222222384001,2222223,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    222222484001,2222224,6001,,,,,TR,USD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,


    Thanks and Regards,
    Ratheesh.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,004
    You might want to post your script so that people could see what you are doing.
    this space intentionally left blank

  3. #3
    Join Date
    Mar 2007
    Posts
    7
    hi,

    iam attaching the script .pls check it out.

    create or replace procedure input_table(taname in varchar2)
    is
    str varchar2(32767);
    cnt number(2);
    cursor c1(tabname in varchar2)
    is
    select column_name
    from all_tab_columns
    where table_name = tabname
    order by column_id;
    rec c1%rowtype;
    begin
    str:= 'declare '||
    'cursor c2 '||
    'is '||
    'select rownum';
    open c1(taname);
    loop
    fetch c1 into rec;
    exit when c1%notfound;

    str:= str||'||'',''||'||rec.column_name;

    end loop;
    close c1;
    str:= str||' SRC from '||taname||';'|| ' r2 c2%rowtype;'||
    ' begin '||' for r2 in c2'||' loop '||
    ' dbms_output.put_line(r2.SRC);'||
    ' end loop;'||
    ' end;';
    execute immediate(str);
    end;
    /


    here the rownumber is included.i want to remove the rownumber.
    is it possible to write the file in utl_file.

    Thanks,
    Ratheesh

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by rratheesh
    ...that is if its 1st record,it will display 1 and if its there for 45 records it will display 1 to 45.I want to eliminate the row columnwhich displays rownumber
    Rownum is there 'cause you put it there. Just take it out.

    Quote Originally Posted by rratheesh
    ...is it possible to write the file in utl_file.
    Yes. Why not?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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