Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Help in extracting fields from a file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help in extracting fields from a file
# 29  
Old 11-22-2011
Quote:
Originally Posted by ahamed101
Try to run the query alone with hardcoded values in the script and see if the output file is fine. Substitute all the variables with actual value!
Code:
#!/bin/bash
sqlplus /nolog << EOF
    connect $db_user/$db_pwd@$db_sid;
    SPOOL output.txt;
    set pagesize 0 feedback off verify off heading off echo off linesize 100 
    select *  from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and event_data_text like '%"$var_ack_party_name"%';
EOF

--ahamed
I have done it.
I ran the query in sql,the query is fine.
I also replaced the variable names with the actual values.But this also resulted me the sameSmilie
# 30  
Old 11-22-2011
Remove this line and see... echo off? what does that do?... Like I said I am not an expert in sqlplus!
Code:
set pagesize 0 feedback off verify off heading off echo off linesize 100

--ahamed
# 31  
Old 11-22-2011
Quote:
Originally Posted by ahamed101
Remove this line and see... echo off? what does that do?... Like I said I am not an expert in sqlplus!
Code:
set pagesize 0 feedback off verify off heading off echo off linesize 100

--ahamed
The problem here is the output.txt file is not getting created in time.This file contains the output of sql query.
so as it is not finding the file in time it is throwing error.
what have to be done here is to wait until the execution of query is completed and file is created.
I manually created output.txt file in the directory and tested it.It is working fine.
All the other part is working fine.
Echo off,heading off commands etc are used to filter the output so that it does not contain column names etc of the table.

---------- Post updated at 08:03 AM ---------- Previous update was at 07:18 AM ----------

Quote:
Originally Posted by ahamed101
Remove this line and see... echo off? what does that do?... Like I said I am not an expert in sqlplus!
Code:
set pagesize 0 feedback off verify off heading off echo off linesize 100

--ahamed
I have removed echo off and executed,it also didnt result successSmilie
# 32  
Old 11-22-2011
Are you getting the output on the screen? You need to get this working first.
Please update the variables with actual value
Code:
#!/bin/bash
sqlplus /nolog << EOF
    connect $db_user/$db_pwd@$db_sid;
    select *  from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and event_data_text like '%"$var_ack_party_name"%';
EOF

# 33  
Old 11-23-2011
Quote:
Originally Posted by ahamed101
Are you getting the output on the screen? You need to get this working first.
Please update the variables with actual value
Code:
#!/bin/bash
sqlplus /nolog << EOF
    connect $db_user/$db_pwd@$db_sid;
    select *  from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and event_data_text like '%"$var_ack_party_name"%';
EOF

I ran the above with variables replaced by actual values and the output is displayed on the screen.
output is like:
Code:
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 23 00:26:04 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> Connected.
SQL> 
BUS_EVENT_SEQ_NBR DET_PARTY_NAME
----------------- ----------------------------------------
ACK_PARTY_NAME                           EVENT_DT EVENT_TM
---------------------------------------- -------- --------
EVENT_ID                                    VER_NBR PERSON_ID       E
---------------------------------------- ---------- --------------- -
ACK_PARTY_CD ACK_EVEN ACK_EVEN
------------ -------- --------
EVENT_DATA_TEXT
--------------------------------------------------------------------------------
LAST_UPD LAST_UPD SEARCH_TEXT               E
-------- -------- ------------------------- -
           203969 OrdAcctCycChg

# 34  
Old 11-23-2011
Now add the SPOOL and set line and check if the output comes in the output.txt file.

Code:
#!/bin/bash
sqlplus /nolog << EOF
    connect $db_user/$db_pwd@$db_sid;
    SPOOL output.txt;
    set pagesize 0 feedback off verify off heading off echo off linesize 100 
    select *  from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and event_data_text like '%"$var_ack_party_name"%';
    SPOOL OFF;
    EXIT;
EOF

--ahamed
# 35  
Old 11-23-2011
Quote:
Originally Posted by ahamed101
Now add the SPOOL and set line and check if the output comes in the output.txt file.

Code:
#!/bin/bash
sqlplus /nolog << EOF
    connect $db_user/$db_pwd@$db_sid;
    SPOOL output.txt;
    set pagesize 0 feedback off verify off heading off echo off linesize 100 
    select *  from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and event_data_text like '%"$var_ack_party_name"%';
    SPOOL OFF;
    EXIT;
EOF

--ahamed
Yes I have got the output in output.txt file:
contents of output.txt file is :
Code:
more output.txt
SQL>     set pagesize 0 feedback off verify off heading off echo off linesize 100
SQL>     select *  from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and event_data_text like '%0030063224%';
           203969 OrdAcctCycChg                            MOVE_USAGE_DAEMON1                       
20101001 07091693 BILLING_CYCLE_CHANGE                              2 SKORUKON                      
20101001 07092076 ACCT_NBR|0030063224|prev_CYCLE_CD|VA|new_CYCLE_CD|27|ACCT_CYCLE_EFF_DT|20101001|  
07092076 20101001                           P                                                       
                                                                                                    
           204001 OrdAcctCycChg                            MOVE_USAGE_DAEMON1                       
20101001 07181709 BILLING_CYCLE_CHANGE                              2 SKORUKON                      
20101001 07182021 ACCT_NBR|0030063224|prev_CYCLE_CD|27|new_CYCLE_CD|VA|ACCT_CYCLE_EFF_DT|20101027|  
07182021 20101001                           P                                                       
                                                                                                    
SQL>     SPOOL OFF;
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Extracting specific fields from an XML file

Hello All, I have a requirement to split the input.xml file different files and i have tried using earlier examples(where i have posted in the forum), but still no luck Here is my input.xml <jms-system-resource> <name>UMSJMSSystemResource</name> ... (4 Replies)
Discussion started by: Siv51427882
4 Replies

2. Shell Programming and Scripting

UNIX extracting fields

I have one file A.txt which is comma separated and I want to extract first 4 field's in a file and also I want to add one more column in output A.txt in output for all records. A.txt should not be hard coded since I do not filename it may be any file. (1 Reply)
Discussion started by: vamsi.valiveti
1 Replies

3. Shell Programming and Scripting

need help in writing a script for extracting fields

Hi, I need to extract last character of the field retrieved from the database using select command. eg: select event,text from event_data; o/p: Event1,text1 But I need to extract only '1' from the fields...similarly '2' from Event2,text2 and '3' from Event3,text3 etc., and need to pass... (6 Replies)
Discussion started by: Rajesh Putnala
6 Replies

4. Shell Programming and Scripting

Extracting Some fields from current file to another file

Hi, I have multiple files in a directory all I am trying to do is to read the files in the directory and extract data from 2nd field till 10th field and put it in a new files. The files are pipe delimited. The new file will have the same name as the old file but the prefix of PRE_oldfilename. ... (1 Reply)
Discussion started by: simi28
1 Replies

5. Shell Programming and Scripting

Extracting fields from file

I am need to extract a number of values from a file, put have now clue how to do this. The file looks like this: # My file Dest=87;CompatibleSystemSoftwareVersion=2.5300-; Dest=87;ImageVersion=000061f3;SystemSoftwareVersion=2.5300;CDN=http://my.backup.com/download.txt;CDN_Timeout=30; I... (3 Replies)
Discussion started by: MagicDude4Eva
3 Replies

6. Shell Programming and Scripting

Removing LF and extracting two fields

I need some assistance, I am writing a script in bash. I want to do two things: 1/. I want to replace the LF at the end of the RFH  Ø  ¸MQSTR ¸ so I can process the file record by record using a while loop. 2/. I want to extract two fields from each record, they are identified with... (1 Reply)
Discussion started by: gugs
1 Replies

7. Shell Programming and Scripting

Extracting records with unique fields from a fixed width txt file

Greetings, I would like to extract records from a fixed width text file that have unique field elements. Data is structured like this: John A Smith NY Mary C Jones WA Adam J Clark PA Mary Jones WA Fieldname / start-end position Firstname 1-10... (8 Replies)
Discussion started by: sitney
8 Replies

8. Shell Programming and Scripting

extracting fields

Hi, i have a line with several fields (indefinite number of - count varies) separated by colon. Now, i need to pick each field (except the first one) and have it assigned to variable within a loop. In other words, in the first iteration of the loop, the variable must be assigned with 2nd... (2 Replies)
Discussion started by: prvnrk
2 Replies

9. UNIX for Dummies Questions & Answers

Extracting information from text fields.

Dear friends, I'm a novice Unix user and I'm trying to learn the ropes. I have a big task I have to accomplish and I'm convinced Unix can get the job done, I just haven't figured out how. I recently posted on the topic of cutting text between unique text patterns and somebody helped me a great... (24 Replies)
Discussion started by: spindoctor
24 Replies

10. Shell Programming and Scripting

Extracting fields from an output 8-)

I am getting a variable as x=2006/01/18 now I have to extract each field from it. Like x1=2006, x2=01 and x3=18. Any idea how? Thanks a lot for help. Thanks CSaha (6 Replies)
Discussion started by: csaha
6 Replies
Login or Register to Ask a Question