[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

help on shell script

Posted on 2013-06-12
4
Medium Priority
?
334 Views
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 :

Tables=SYS.Test1,SYS.Test2,SYS.Test3,SYS.Test4,SYS.Test5,SYS.Test6,SYS.Test7
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"

Thanks
0
Comment
Question by:Raju1983
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 20

Accepted Solution

by:
flow01 earned 1000 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
MISSING FIELD VALUES ARE NULL
(
 text          
)
        )
        location ('export.par')  
      )
/

-- check data
select * from change_export_par
/

-- convert lines
with pardata
as
(
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
from
(
select linenr,
'Query=' || table_name || ':"' || table_where || '"' newparline
from pardata where table_where is not null
union
select  0 linenr ,
   'TABLE=' ||
   rtrim (xmlagg (xmlelement (e,table_name || ',')).extract ('//text()'), ',') newparline
from
   pardata
   order by linenr
)
order by linenr
/

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

Assisted Solution

by:simon3270
simon3270 earned 1000 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
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

649 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