RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS
Title: RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS
Need
bind variables, can't fix them yes will go with global temporary tables but
understand that this is going to place I/O on system that may not be
necessary.
-Original Message-From: Lord, David - CSG
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 13, 2002 11:03
AMTo: Multiple recipients of list ORACLE-LSubject: 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:19To: Multiple recipients of list ORACLE-LSubject:
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 individualother 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.**
RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS
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
AMTo: Multiple recipients of list ORACLE-LSubject: 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:19To: Multiple recipients of list ORACLE-LSubject:
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 individualother 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.**
RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS
Title: 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:19To: Multiple recipients of list ORACLE-LSubject: 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. **
RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS
Title: 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.
