-
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.
-
You might want to post your script so that people could see what you are doing.
-
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
-
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.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|