Oracle report converter

denzil_cactus has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Oracle report converter by CountZero (Bishop) on Jun 25, 2007 at 06:09 UTC
You first need to parse the `.rpt`-file to extract the variables and the queries. Translate these into standard SQL you then feed to the DBI/DBD-modules and obtain the resultset, which you will then format into a pretty report, perhaps through the use of a templating system. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity. - The Tao of Programming, 4.1 - Geoffrey James	[reply] [d/l]
Re^2: Oracle report converter by denzil_cactus (Sexton) on Jun 25, 2007 at 07:28 UTC
ok I got ..thnx for the precious reply but please guide me if the file can have lot of commands like `- .define - .declare - #nc - #t - #s - .ifnull - .print - .. - #dt - .set - .&[something] - .execute - #cl - # - .if - [chars] - #r - .[defined] - .stop - .commit - #cen - #cs - .report - #n - ( [stuff] ) - .goto - #te` [download] So Do I need to parse each and every line of the file?	[reply] [d/l]
Re^3: Oracle report converter by CountZero (Bishop) on Jun 25, 2007 at 19:40 UTC
(Assuming the above is part of the `.rpt` file) Yes, you will have to go through the whole of the file unless you can tell beforehand that certain parts can be skipped as they contain no information you need (such as comments and the like). Searching on the WWW, I found the following in an Oracle Glossary: RPT / RPF Report writing and formatting tools provided with older desupported releases of Oracle. The RPT process queried the database, producing rows of data with embedded formatting commands recognized by RPF. What you are actually trying to do is to re-invent the functionality of the Oracle RPT / RPF process. I know nothing of Oracle, but it looks to be a tall order to me! CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply] [d/l]
Re: Oracle report converter by GrandFather (Saint) on Jun 25, 2007 at 04:56 UTC
A small sample of the data and example of the expected result may help. Some code showing what you have tried would help even more. DWIM is Perl's answer to Gödel	[reply]
Re^2: Oracle report converter by denzil_cactus (Sexton) on Jun 25, 2007 at 06:19 UTC
Well I havent tried the code yet... The .rpt file contains these sort of lines `.declare myID 99 .declare myNote 9999999 .declare rateCount 9999 .define get_in select key, key1, rate_pr,Nt into booking_seq, f_no, print_rates, myNote from ed_tab where ed_tab.tag = 'BKNG' and ed_tab.key = 'PRN' and ed_tab.user_id = usr` [download] I just want to know ..how to do this..I mean how to read this type of file which contains declared variables and sql query...If I open the file and read line by line how could I know on which line variable is declared and on which line query is written..because after reading line by line ,I will have generate the report..... Need your guidance only....	[reply] [d/l]
Re^3: Oracle report converter by GrandFather (Saint) on Jun 25, 2007 at 08:01 UTC
My guess is that that could be a pretty big job. To parse the file you probably want to look at something like Parse::RecDescent then use the output from that to dispatch a series of DB accesses using DBI to retrieve the required report data. Possibly you would then use Template::Toolkit to generate the formated report. DWIM is Perl's answer to Gödel	[reply]