I am working on a similar situation with dynamic in clauses
and I am trying to implement what you are already doing. Can
you give me some more information on
"sf_sdba_csvstr2tbl". This looks more elegant than
what I have come up with so far.
Thanks
Robert Lindberg
Database Administrator
McLane Advanced Technologies
-----Original Message-----
Sent: Wednesday, September 24, 2008 12:31 PM
To: Multiple recipients of list ODTUG-SQLPLUS-L
Hi,
Here's the situation:
We have a database with information from log files. The log
files are generally FTP logs - which files got downloaded,
where they went, how big they are, etc - and SQL*Loader logs
- rows read, rows rejected, start time, etc.
We are building a web page front end for the database that
allows users to query the logs.
So, for instance, we have a query page for the SQL*Loader
logs which allows the user to choose a database and/or
choose a schema and/or choose a table and/or choose a log
file and/or choose a date, then query the database for those
parameters.
The web developer has drop-downs for each of the possible
parameters. He initializes each drop-down when loading the
page, so that the database drop-down contains all the
possible databases, for example. Each drop-down has a
default value of "all".
Here's the question:
If the user selects "all" on a given drop down,
the web developer takes all the values in the array he uses
to populate the drop down, parses them into a
comma-delimited string, passes that into stored procedure
via a VARCHAR2 variable, then uses a function to put the
delimited string into an array which is cast to an object
and used in an IN clause in the query. An example follows at
the end of the message.
This screams "scalability problems" at me every
time I look at it. We're running the query against the
development database which returns small result sets for the
databases, schemas, tables, and logs. If we go into
production and a lot of people start putting log files into
the database, we're looking at possibly hundreds of
databases, thousands of schemas, tens of thousands of
tables, and millions of log files.
Do you agree with me? Or is the developer taking the correct
approach?
If you agree with me, what would be your approach?
Here's the procedure, with query:
PROCEDURE reporting_sqlldrlog (
p_rc_metrics OUT rc_generic,
p_str_database IN VARCHAR,
p_str_schema IN VARCHAR,
p_str_instance_id IN VARCHAR
)
IS
BEGIN
DBMS_APPLICATION_INFO.set_action
('reporting_sqlldrlog');
OPEN p_rc_metrics FOR
SELECT e.lmc_metr_ins_file_name, a.cpu_time,
a.elapsed_time,
a.lmc_db_name, a.lmc_db_schema_name,
a.lmc_db_table_name,
a.nbr_rows_data_errors,
a.nbr_rows_discarded,
a.nbr_rows_loaded, a.nbr_rows_read,
a.nbr_rows_rejected,
a.nbr_rows_skipped, a.run_begin,
a.run_end,
a.lmc_metrics_instance_id
FROM vw_lmc_sqlldr_metrics a,
(SELECT TO_CHAR (COLUMN_VALUE) AS
in_database
FROM THE
(SELECT CAST
(sf_sdba_csvstr2tbl (p_str_database) AS tt_sdba_csvstr2tab
)
FROM DUAL
)) b,
(SELECT TO_CHAR (COLUMN_VALUE) AS
in_schema
FROM THE
(SELECT CAST
(sf_sdba_csvstr2tbl (p_str_schema) AS tt_sdba_csvstr2tab
)
FROM DUAL
)) c,
(SELECT TO_NUMBER (COLUMN_VALUE) AS
in_instance_id
FROM THE
(SELECT CAST
(sf_sdba_csvstr2tbl
(p_str_instance_id) AS tt_sdba_csvstr2tab
)
FROM DUAL
)) d,
lmc_metrics_instances e
WHERE a.lmc_db_name = b.in_database
AND a.lmc_db_schema_name = c.in_schema
AND a.lmc_metrics_instance_id =
d.in_instance_id
AND a.lmc_metrics_instance_id =
e.lmc_metrics_instance_id
ORDER BY e.lmc_metr_ins_date DESC,
a.lmc_db_table_name ASC;
DBMS_APPLICATION_INFO.set_action (NULL);
END reporting_sqlldrlog;
Tom Berthoff
Enterprise Technology
x1024
484-562-1024
tom.berthoff sig.com
IMPORTANT: The information contained in this email and/or
its attachments is confidential. If you are not the intended
recipient, please notify the sender immediately by reply and
immediately delete this message and all its attachments. Any
review, use, reproduction, disclosure or dissemination of
this message or any attachment by an unintended recipient is
strictly prohibited. Neither this message nor any attachment
is intended as or should be construed as an offer,
solicitation or recommendation to buy or sell any security
or other financial instrument. Neither the sender, his or
her employer nor any of their respective affiliates makes
any warranties as to the completeness or accuracy of any of
the information contained herein or that this message or any
of its attachments is free of viruses.
--
For more information on this topic or to become a member,
visit our Web site at http://www.ODTUG.com
Join ODTUG for The PL/SQL and The APEX Expert Gathering of
the Year! OPP2008 and APEXposed! 2008 will be held on
October 29-30, 2008 at the Wyndham O'Hare, Chicago. Visit
www.odtugopp.com or www.odtugapextraining.com for more
details.
--
Author: Berthoff, Tom
INET: Tom.Berthoffsig.com
Fat City Hosting, San Diego, California -- http://www.fatcity.com
------------------------------------------------------------
---------
To REMOVE yourself from this mailing list, send an E-Mail
message
to: ListGurufatcity.com (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB
ODTUG-SQLPLUS-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).
--
For more information on this topic or to become a member,
visit our Web site at http://www.ODTUG.com
Join ODTUG for The PL/SQL and The APEX Expert Gathering of
the Year! OPP2008 and APEXposed! 2008 will be held on
October 29-30, 2008 at the Wyndham O'Hare, Chicago. Visit
www.odtugopp.com or www.odtugapextraining.com for more
details.
--
Author: Robert Lindberg
INET: Robert.Lindbergmclaneat.com
Fat City Hosting, San Diego, California -- http://www.fatcity.com
------------------------------------------------------------
---------
To REMOVE yourself from this mailing list, send an E-Mail
message
to: ListGurufatcity.com (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB
ODTUG-SQLPLUS-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).
|
