Oracle-L: RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS

From: Lord, David - CSG <David.Lord_at_hayscsg.com>
Date: Tue, 13 Aug 2002 07:03:26 -0800
Message-ID: <F001.004B2C32.20020813070326@fatcity.com>

Paula

I don't know Crystal reports so I'm not sure what you are trying to achieve, but...

If you're using 8i and can fix the number of bind variables (or don't need to use them at all) then native dynamic sql (NDS) is faster and easier to code than DBMS_SQL.
In either, I don't know of any restrictions on the type of sql you can use.
If you're using 8i and want to do the thing with the table, consider using global temporary tables. They look like normal tables but the data is stored in the user's sort area (or thereabouts) so you don't need the session_id. Each session only sees the data it has inserted.

Regards
David Lord

-----Original Message-----
Sent: 13 August 2002 15:19
To: Multiple recipients of list ORACLE-L

Guys,

User apparently can only execute one stored proc. from a Crystal Reports. There are lots of different types of queries and he would like to cut-down on the number of Crystal Reports he would need. He wishes to create a table with session i.d. as primary key that will be used to insert aggregate records (rolling averages, etc.) so that he doesn't have to do this. I am concerned about even just 12 concurrent users (maybe?), 67 rows, primary key and index inserts and deletes are unnecessarily incurring I/O. I am suggesting using DBMS_SQL but don't know if there are limitations to the type of SQL you can write - we have inline views/hints/etc.? I just think it would be a more efficient way to do the same thing. He is saying he has been burned before by code generators. I am trying to say that this is a supplied package and not really a code generator and in my experience quite flexible. Help!!!! Am I making too much of this? What about metadata tables? In otherwords, keep report name, columns involved or even store query and view name then query actually based on the metadata. Probably what the ad hoc query tools do anyway.

This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at email.helpdesk_at_hays.plc.uk
Any information, statements or opinions contained in this message
(including any attachments) are given by the author. They are not
given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays.

A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Lord, David - CSG
  INET: David.Lord_at_hayscsg.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Tue Aug 13 2002 - 10:03:26 CDT