Sqlplus 2

[TheEntertainer]

Hello,

Just a quick question, we've got Enterprise XI and I've just added sqlplus in as a program object is it possible to specify a script to run and send the output to a particular network drive?

Oracle 9 client is installed on my machine, does it need to be installed on CE XI server?

Thanks

 

[Turkbear]

Hi,
The Oracle client would need to be on any machine that will be connecting to Oracle..
Not sure what advantage having that kind of setup has, however, over a standard SqlPlus installation on the client PC - can you describe what you plan to do with it?






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

I've not used it in this fashion, I would just schedule a job in Oracle for this.

Anyway, if the job is scheduled to run on the server then it might work providing the server has the Oracle client and permission.

Again, an odd way to do this sort of thing, as it's not a report, rather a recordset from Oracle.

-k

 

[TheEntertainer]

Thanks,

For the replies. -k you are right in the sense that it is a recordset. If I explain what I'm after then you guys could probably point me in the right direction.

I've got a report which contains around 18 months worth of data which produces 3 million plus records. The sql starts returning data within a few minutes, but because of the volumn it takes a few hours to read all the records into crystal, then crystal creates a couple of crosstabs from the data, with various formula this and the formating of the page adds more time, so it takes a good few hours before it actual shows you the report.

I've been playing with the idea of basing the report on a text file instead which will run off once a week, so that when they access the report, they will not atleast have to wait for the database to execute the query and return records.

What I wanted to do is schedule the query and then schedule the report to run straight after and then they can simply view it.

I've had a look at dbms_jobs, but I'm not sure of how to use it and how I can just set it to run in a minute's time for example so that I can check that it's working. Do you need to provide logon info at the time the job runs?

I could also set up a cron job, but I would like to keep it in the database and besides then you've got the problem of converting the file from unix2dos, which I know there is a utility for but the less conversion I have to do the better.

Thanks Guys

 

[Turkbear]

Hi,
You could try using a view in the database to preprocess the data in a form that would be easier for Crystal to load and manipulate - Perhaps some of the formulas could be done at the Databae side as well in a SP or Function - then use this in the View..

Asking Crystal to manipulate several million records at the 'report' end of things is never going to be very efficient and should be avoided if at all possible..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[TheEntertainer]

Turkbear,

Thanks for your response on this. I've followed your suggestion and I've managed to get the database to do most of the calculations. Reduced the number of records from 3 million plus to 19! (With 48 columns). BOXI is still timing out as the database has got to do some churning before it returns, but atleast the return time is MUCH shorter (atleast when run through Crystal Reports).



And I've learnt a few more things about BOXI Enterprise and (PL/)SQL. Will still look in DBMS_JOBS as -k suggested, although I'm not sure that I have ability to do so as I can't set my init.ora, as you are supposed to get it to work.

Cheers

 

[Turkbear]

Hi,
You can increase the Idle Job timeout in the CMC - Servers section .
( Or, if you are not the CE admin, ask that it be done..)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[TheEntertainer]

Yeah,

I saw that in another tread, but I'm fairly new with Enterprise and when I went to change the setting it said that the server had to re-boot and I chicken out until I'm sure that it will not affect anything else

Thanks to you both for your help.

Cheers