help on shell script

Posted on 2013-06-12
Last Modified: 2014-01-13
I am currently struck with one shell script which i am not aware of how to create it..

I have file with the following contents,

export SYS.Test1;
export SYS.Test2;
export SYS.Test3;
export SYS.Test4;
export SYS.TesT5 where emp=0;
export SYS.Test6 where emp=1;
export SYS.Test7 where emp=3;

I need the output as :

Query=SYS.TesT5:"where emp=0"
Query=SYS.Test6:"where emp=1"
Query=SYS.Test7:"where emp=3"

This is just a sample and i have more than a "60000" tables with these type of conditions and it is very difficult to create a par file with all these complex conditions.. Some tables are full export and some are partial tables.. We have a program which generates these tables with these conditions. I need to create a par file for this and export this using expdp.

Tables="should include all the tables next to export command"
Query="should include the tables which has where condition"

Question by:Raju1983
LVL 20

Accepted Solution

flow01 earned 250 total points
ID: 39241765
"We have a program which generates these tables with these conditions"
does it generate first file type or second ?
If first, you can also alter the program  instead of the file.
if second what is the problem ?

For conversion of format 1 to 2 I would use oracle because i am more familiar with it:

create external table  >>  example   (ext is a oracle-directory defined , the input file has to be in the fysical directory where ext refers to)

CREATE TABLE change_export_par (
  text                 varchar2(4000)
      organization external
      ( default directory ext
        access parameters
        ( records delimited by newline
fields terminated by ';' optionally enclosed by '"' RTRIM
        location ('export.par')  

-- check data
select * from change_export_par

-- convert lines
with pardata
select rownum  linenr,
case instr(text,' ',1,2)
when 0 then  trim(substr(text,instr(text,' ',1,1) + 1))
else         trim(substr(text,(instr(text,' ',1,1) + 1), instr(text,' ',1,2) - instr(text,' ',1,1)))
end table_name
case instr(text,' ',1,2)
when 0 then  null
else         trim(substr(text,(instr(text,' ',1,2) + 1)))
end table_where
from    change_export_par
select newparline
select linenr,
'Query=' || table_name || ':"' || table_where || '"' newparline
from pardata where table_where is not null
select  0 linenr ,
   'TABLE=' ||
   rtrim (xmlagg (xmlelement (e,table_name || ',')).extract ('//text()'), ',') newparline
   order by linenr
order by linenr

You can create a sqlscript taking the filename as argument  and  use the argument in the locationr
LVL 19

Assisted Solution

simon3270 earned 250 total points
ID: 39242165
Or, in shell script
: parameter of this script is the original file - modified file written to stdout
awk -F'[ ;]*' '/^export/{tabs=tabs "," $2}END{printf "Tables=%s\n", substr(tabs,2)} $1
awk -F'[ ;]*' '/^export.*where/{printf "Query=%s:\"%s %s\"\n", $2, $3, $4}' $1

Open in new window

This assumes that the "where" clause is exactly one item with no spaces - let me know if it is more comple.

Use the script as
   sh script_name input_file_name > output_file_name

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question