RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS

[Paula_Stankus]

Title: RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS

Yes, it looks like NDS may be able to be used - Reading Johnathon Lewis' Practical 8i with refcursor.  I think this is a better approach then global temporary table.  I will see if there are limitations to this using Crystal. 

-----Original Message-----
From: Lord, David - CSG [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 13, 2002 11:03 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS

Paula

 

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

 

1) 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.

 

2) In either, I don't know of any restrictions on the type of sql you can use.

 

3) 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-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 13 August 2002 15:19
To: Multiple recipients of list ORACLE-L
Subject: RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS

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 PROTECTED]
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.
**********************************************************************