Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Need to different ways using Perl to merge files into one!! One way on Unix, the other Desktop??

Avatar of 7Souls
7SoulsFlag for United States of America asked on
Unix OSOracle DatabasePerl
19 Comments1 Solution915 ViewsLast Modified:
I modified the Sprepsql.sql from Statspack to only produce the following output:

Enter the Begin SnapShot ID#: 32
Enter the End SnapShot ID#: 33

Enter the Hash Value from Statspack report: 3755685678
Enter a output file name if you choose <Return> the default file name will be 'sp_32_33_3755685678'

SQL Text
~~~~~~~~
select psptp, psmth, psref, pscsr from ORCOM_CEMC.uphss where pstr#=:1 and pspdt=:2

Executions: 2270212     Cost: 4     Overall Cost: 9080848
---------------------------------------------------------------------------------------------------------------------
|  Operation                                 |  PHV/Object Name                    |  Rows        |  Bytes     |  Cost      |
---------------------------------------------------------------------------------------------------------------------
|Select Statement                       |------- 1631145577 ---------------|                   |             |           4      |
|Table  Acess By Index Rowid  |UPHSS                                        |            1     |        39 |           4      |
| Index Range Scan                   |UPHSS_PSTR_IDX01                  |             1    |             |           3      |
----------------------------------------------------------------------------------------------------------------------

End of Report

My question is 'If I have my manager or users look at the statspack report and pick out the SQL and hash values for the Sql and create multiple reports how can I merge them either using perl or some editor script using the prefix sp_*?

I would like to run this merge on both the Unix Aix from the /oracle/rdbms/admin directory where the statspack sqls are located or from a SQLplus session that spools the file to local directory on a users desktop for example 'C:\Sql_Explain_plan_Output\'.

I haven't written or run 'Perl' before and I feel since I can produce the report for one SQL then I can run it and produce many reports and merge them into one. This will make it easy on me and my manager and I don't need to try to produce this in PL/SQL cursor loop with no means of producing the same output or moving the output from PL/SQL to SQL at least I haven't found one yet.

Any Ideas, I need help real soon.

Thanks,
7Souls        
ASKER CERTIFIED SOLUTION
Avatar of Adam314
Commented:
This problem has been solved!
Unlock 1 Answer and 19 Comments.
See Answers