unix script, help

[svk]

okay, so you are talking @ actual columns and not the records in the table. I thought your example of selecting from USER_TAB_COLUMNS was just an example.

So, if you spool to a file :

awk '{ printf("%s,",$1) }END{ printf("\n")}' your_spool_file

should do the job.

[jm]

thanks SVK, it works.

but I still need your help.

The result:
cust_id,cust_name,cust_descp,updateuser,

How I eliminate the last ",".

thanks, again

[svk]

Once you execute the awk, you can used sed to eliminate the last ','.

Or, you can use a simple logic in awk to check for the record number (NR) and don't print the ',' for the first record and print a ',' before the actual field for all other records. Simple.

[jm]

svk,
I am not good at either one. I try to use MAN to figure out how but no answer. Could you please give me the syntax.

thanks.

[tekion]

Hi, I am not quite sure what you want, but under the assumption that you are only interested in the second field for every 5 line, here's a script that might do just that. It could be shorter, but here it is:

script:
#!/bin/sh

#!/bin/sh

awk '{
if($2 != "") {printf "%s," ,$2; }
if(getline) {printf "%s," ,$2;}
if(getline) {printf "%s," ,$2;}
if(getline) {printf "%s," ,$2;}
if(getline) {printf "%s\n" ,$2;}
}' < punt

[svk]

Lets get this completed :

Create a .sql file as follows :

spool /tmp/x
set pages 0
set lines 200
set feedback off
set head off
select table_name,column_name,data_type,column_id
from user_tab_columns
order by 1,4;
spool off

Executing this file script will create a spool file /tmp/x.lst that is in the format that you have been mentioning since begining.

Now, create this awk script : (lets call it /tmp/x.awk)

BEGIN {
prev_table="."
}
{
curr_table=$1
if ( curr_table!=prev_table )
{
prev_table=curr_table
printf("\nTable %s : %s",$1,$2)
}
else
{
printf(",%s",$2)
}
}

Execute this awk script as follows :

awk -f /tmp/x.awk /tmp/x.lst

The output will be as follows :

Table ABC : COL1,COL2,COL3
Table DEF$_PROPAGATOR : USERID,USERNAME,CREATED

You can redirect output of the above awk command to a file.
If you do not want the "Table .... : " part in the output, edit the awk script as required.

Does it solve the issue ?


[Edited by svk on 08-28-2001 at 06:38 PM]

[roobaron]

Hi,

So you want table_name,column_name,column_name,...

use a cursor
select table_name,column_name
from dba_tab_columns (OR user_tab_columns)
and loop for each table.
use dbms.output to create the right output.

Hope this helps

[hrishy]

Hello SVK

could you plaese explain your code i am feeling it a bit diffcult to understand it.

regards
hrishy

[jm]

svk,

thanks, it works perfectly.

In fact, last night I use your previous suggestion
at end I use sed to remove the pending ",".

awk '{ printf("%s,",$1) }END{ printf("\n")}' your_spool_file | sed "s/\,$//g"

both works !!! this is not the first time you help me out, thanks.

jm