| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Custom Function Help Needed?
In article <13e4506b.0695a1ed_at_usw-ex0107-055.remarq.com>,
gdas <gdas1NOgdSPAM_at_yahoo.com.invalid> wrote:
> I am trying to create a function that will essentially "SUM" a
> string. Unfortunately the built in SUM function works only on
> numeric datatypes. I can't simply concat || values because the
> values I'm trying to aggregate aren't within a single row, but
> rather span multiple rows in a column.
>
> As an example, imagine a query that is:
>
> select region, sum(sales_rep) from foo group by region
>
> I'd like this to return each region and the next cell should be
> a comma delimited list of all sales_reps in that particular
> region.
>
> Region sum(sales_rep)
> ------ --------------
> WEST Joe, Bill, Bob
> East Lisa, Steve, Mary
> etc...
> I created a custom function that basically fed in the "grouping"
> parameters (Region) and then that function loops through a
> cursor to return the appropriate value. no problems there.
>
> Only problem is that everything needs to be hardcoded in the
> function, particularly the input parameters. I'd like to
> simulate a dynamic group by here based on whatever other columns
> are in the select list for the query? Does anyone have a
> creative idea on this?
>
> One idea I had (if this is possible) was that if I could
> dynamically send as a parameter to my custom function the full
> text of the select statement...then inside of the function, I
> could parse that statement to determine the appropriate level of
> grouping. But in order to do this (again, it has to be dynamic
> because I can't hard code the statement as I would like to use
> this function in different scenarios), I need some kind of
> keyword or variable that will allow me to feed in the sql
> statement as a parameter to the function) Additionally, the
> application is end-user driven...so I have no idea what the
> users will be querying (it's a decision support app where people
> will be drilling up/down and all around) .
>
> If anyone has any creative ideas to this dilemma, I'd very much
> appreciate it if you could share your thoughts.
>
> Thanks in advance,
> Gavin
>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion
Network *
> The fastest and easiest way to search and participate in Usenet -
Free!
>
>
Gavin,
You present an interesting problem for discussion, and provide very little information to facilitate solving it. You've generated a specific solution with your current function; now you wish to generalize the solution to any table, any column or set of columns and return similar results. Hmmmm...
Your idea of parsing the actual SQL text is a good one. What you need to now accomplish is dynamic generation of the 'select (column) from (table) order by (group by parameter)' in your function. Dynamic SQL can help you there. Parse the incoming SQL for the sum() parameter and the group by parameter then do something similar to the following script (this alters the PCTUSED and PCTFREE values on a table as well as deleting and inserting rows -- the first steps in correcting chained rows in a table):
set serveroutput on
declare
source_cursor integer;
destination_cursor integer;
rows_processed integer;
rows_returned integer;
err_pos integer;
pfree integer;
pused integer;
cursor get_chained_tbl is
select distinct table_name
from chained_rows
order by table_name;
cursor get_chain_data is
select table_name, head_rowid
from chained_rows
order by table_name, head_rowid;
cursor get_pfree (tname in varchar2) is
select pct_free from user_tables
where table_name = tname;
begin
dbms_output.enable(1000000);
--
-- Create the necessary temporary tables
--
for chain_tbl_rec in get_chained_tbl loop
source_cursor := dbms_sql.open_cursor;
dbms_sql.parse(source_cursor,
'create table '||chain_tbl_rec.table_name||'_TEMP as
select * from '||chain_tbl_rec.table_name||' where 0 = 1', 2);
rows_processed := dbms_sql.execute(source_cursor);
err_pos := dbms_sql.last_error_position;
dbms_sql.close_cursor(source_cursor);
if err_pos <> 0 then
dbms_output.put_line('Error on table create for
'||chain_tbl_rec.table_name||'_TEMP');
exit;
else
dbms_output.put_line('Table
'||chain_tbl_rec.table_name||'_TEMP created');
end if;
end loop;
--
-- Insert the offending row or rows into the associated temp
table
--
for chain_data_rec in get_chain_data loop
source_cursor := dbms_sql.open_cursor;
dbms_sql.parse(source_cursor,
'insert into '||chain_data_rec.table_name||'_TEMP select
* from '||chain_data_rec.table_name||' where rowid =
'''||chain_data_rec.head_rowid||'''', 2);
rows_processed := dbms_sql.execute(source_cursor);
err_pos := dbms_sql.last_error_position;
dbms_sql.close_cursor(source_cursor);
if err_pos <> 0 then
dbms_output.put_line('Error on insert into
'||chain_data_rec.table_name||'_TEMP');
exit;
else
commit;
end if;
--
-- Delete the chained row or rows from the source table
--
source_cursor := dbms_sql.open_cursor;
dbms_sql.parse(source_cursor,
'delete from '||chain_data_rec.table_name||' where rowid
= '''||chain_data_rec.head_rowid||'''', 2);
rows_processed := dbms_sql.execute(source_cursor);
err_pos := dbms_sql.last_error_position;
dbms_sql.close_cursor(source_cursor);
if err_pos <> 0 then
dbms_output.put_line('Error on delete from
'||chain_data_rec.table_name);
exit;
else
commit;
end if;
end loop;
for chain_tbl_rec in get_chained_tbl loop
--
-- Alter the PCTUSED and PCTFREE values for the current source
table
-- based upon the current value of PCTFREE
--
-- This should correct row chaining, however sometimes a 10
percent
-- increase is not sufficient
--
-- This script can be run as often as warranted, with each run
increasing
-- the PCTFREE value by 10 percent
--
-- The first change brings the PCTFREE up to 50
-- The maximum allowable PCTFREE by this script is 90
--
-- If row chaining is still a problem after increasing the
PCTFREE value
-- for a table to 90, rebuild the database with a larger block
size
--
open get_pfree(chain_tbl_rec.table_name);
fetch get_pfree into pfree;
close get_pfree;
dbms_output.put_line('Old PCTFREE value: '||pfree);
if pfree < 90 then
if pfree < 50 then
pfree := 50;
else
pfree := pfree + 10; -- Up PCTFREE by
10 percent
end if;
end if;
pused := 100 - pfree; -- PCTUSED is difference between
100 and new PCTFREE
dbms_output.put_line('New PCTFREE value: '||pfree);
source_cursor := dbms_sql.open_cursor;
dbms_sql.parse(source_cursor,
'alter table '||chain_tbl_rec.table_name||' PCTUSED
'||pused||' PCTFREE '||pfree, 2);
rows_processed := dbms_sql.execute(source_cursor);
err_pos := dbms_sql.last_error_position;
dbms_sql.close_cursor(source_cursor);
if err_pos <> 0 then
dbms_output.put_line('Error on alter table for
'||chain_tbl_rec.table_name);
exit;
end if;
--
-- Insert the deleted rows into the source table from
-- the associated temp table
--
source_cursor := dbms_sql.open_cursor;
dbms_sql.parse(source_cursor,
'insert into '||chain_tbl_rec.table_name||' select *
from '||chain_tbl_rec.table_name||'_TEMP', 2);
rows_processed := dbms_sql.execute(source_cursor);
err_pos := dbms_sql.last_error_position;
dbms_sql.close_cursor(source_cursor);
if err_pos <> 0 then
dbms_output.put_line('Error on insert into
'||chain_tbl_rec.table_name);
exit;
else
commit;
dbms_output.put_line('Table:
'||chain_tbl_rec.table_name||' Rows processed: '||rows_processed);
end if;
source_cursor := dbms_sql.open_cursor;
dbms_sql.parse(source_cursor,
'drop table '||chain_tbl_rec.table_name||'_TEMP', 2);
rows_processed := dbms_sql.execute(source_cursor);
err_pos := dbms_sql.last_error_position;
dbms_sql.close_cursor(source_cursor);
if err_pos <> 0 then
dbms_output.put_line('Error on table drop for
'||chain_tbl_rec.table_name||'_TEMP');
exit;
end if;
end loop;
exception
when others
then
dbms_output.put_line('Error '||to_char(SQLCODE) ||' :
'|| SQLERRM);
-- -- This ends the chained row corrections -- end; / The areas you should concentrate on in the above script are the dbms_sql calls -- cut and paste as necessary, then change the literal text in the dbms_sql.parse statements to text that you would use. The dbms_sql package dynamically generates SQL statements with literal text and variables, then can execute the statements generated. Explanatory documentation for the dbms_sql package can be found in the dbmssql.sql script under $ORACLE_HOME/rdbms/admin; the documentation explains quite well the functionality and use of the package to generate SQL and retrieve the data. If this package is not installed have the DBA install it for you (if you are not in possession of a DBA account). The package should be installed as SYS or via svrmgrl (connect internal). I hope this helps somewhat. David Fitzjarrell Oracle Certified DBA Sent via Deja.com http://www.deja.com/ Before you buy.Received on Wed Jun 07 2000 - 00:00:00 CDT
 |
 |