plsql Development etiketine sahip kayıtlar gösteriliyor. Tüm kayıtları göster
plsql Development etiketine sahip kayıtlar gösteriliyor. Tüm kayıtları göster
prc_table_to_csv
PROCEDURE prc_table_to_csv (p_table_name VARCHAR2,
p_sep_char VARCHAR2 DEFAULT '","',
p_filename VARCHAR2 DEFAULT 'Output.txt',
p_file_location VARCHAR2
)
--
IS
CURSOR cur_table_data
IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = p_table_name;
v_line VARCHAR2(20000);
v_sep VARCHAR2(30);
file_handle UTL_FILE.FILE_TYPE;
TYPE TabCurTyp IS REF CURSOR;
v_TabCurTyp TabCurTyp;
BEGIN
v_sep := '||''' || p_sep_char|| '''||';
dbms_output.put_line(v_sep);
FOR cur_table_data_rec IN cur_table_data
LOOP
v_line := v_line || cur_table_data_rec.column_name || v_sep;
END LOOP;
v_line := SUBSTR(v_line, 1, LENGTH(v_line) - (6 + LENGTH(p_sep_char)) );
IF p_sep_char = '","'
THEN
v_line := ''''||'"'||'''||' || v_line || '||''' || '"' || '''';
dbms_output.put_line(v_line);
END IF;
v_line := 'SELECT ' || v_line || ' FROM ' || p_table_name;
/*
v_line := 'SELECT '||
SUBSTR(v_line, 1, LENGTH(v_line) - (6 + LENGTH(p_sep_char)) )||
' FROM ' || p_table_name ;
*/
dbms_output.put_line(v_line);
OPEN v_TabCurTyp FOR v_line;
file_handle := UTL_FILE.FOPEN(p_file_location, p_filename, 'W');
LOOP
FETCH v_TabCurTyp INTO v_line;
EXIT WHEN v_TabCurTyp%NOTFOUND;
UTL_FILE.PUT_LINE(file_handle,v_line);
dbms_output.put_line(v_line);
END LOOP;
CLOSE v_TabCurTyp;
UTL_FILE.FCLOSE(file_handle);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN
dbms_output.put_line('file location or name was invalid');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INVALID_MODE
THEN
dbms_output.put_line('the open_mode string was invalid');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INVALID_FILEHANDLE
THEN
dbms_output.put_line('not a valid file handle');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INVALID_OPERATION
THEN
dbms_output.put_line('File could not be opened as requested');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.READ_ERROR
THEN
dbms_output.put_line('OS error occurred during read');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.WRITE_ERROR
THEN
dbms_output.put_line('OS error occured during write operation');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INTERNAL_ERROR
THEN
dbms_output.put_line('Internal Error');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN OTHERS
THEN
UTL_FILE.FCLOSE(file_handle);
RAISE;
END prc_table_to_csv;
p_sep_char VARCHAR2 DEFAULT '","',
p_filename VARCHAR2 DEFAULT 'Output.txt',
p_file_location VARCHAR2
)
--
IS
CURSOR cur_table_data
IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = p_table_name;
v_line VARCHAR2(20000);
v_sep VARCHAR2(30);
file_handle UTL_FILE.FILE_TYPE;
TYPE TabCurTyp IS REF CURSOR;
v_TabCurTyp TabCurTyp;
BEGIN
v_sep := '||''' || p_sep_char|| '''||';
dbms_output.put_line(v_sep);
FOR cur_table_data_rec IN cur_table_data
LOOP
v_line := v_line || cur_table_data_rec.column_name || v_sep;
END LOOP;
v_line := SUBSTR(v_line, 1, LENGTH(v_line) - (6 + LENGTH(p_sep_char)) );
IF p_sep_char = '","'
THEN
v_line := ''''||'"'||'''||' || v_line || '||''' || '"' || '''';
dbms_output.put_line(v_line);
END IF;
v_line := 'SELECT ' || v_line || ' FROM ' || p_table_name;
/*
v_line := 'SELECT '||
SUBSTR(v_line, 1, LENGTH(v_line) - (6 + LENGTH(p_sep_char)) )||
' FROM ' || p_table_name ;
*/
dbms_output.put_line(v_line);
OPEN v_TabCurTyp FOR v_line;
file_handle := UTL_FILE.FOPEN(p_file_location, p_filename, 'W');
LOOP
FETCH v_TabCurTyp INTO v_line;
EXIT WHEN v_TabCurTyp%NOTFOUND;
UTL_FILE.PUT_LINE(file_handle,v_line);
dbms_output.put_line(v_line);
END LOOP;
CLOSE v_TabCurTyp;
UTL_FILE.FCLOSE(file_handle);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN
dbms_output.put_line('file location or name was invalid');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INVALID_MODE
THEN
dbms_output.put_line('the open_mode string was invalid');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INVALID_FILEHANDLE
THEN
dbms_output.put_line('not a valid file handle');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INVALID_OPERATION
THEN
dbms_output.put_line('File could not be opened as requested');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.READ_ERROR
THEN
dbms_output.put_line('OS error occurred during read');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.WRITE_ERROR
THEN
dbms_output.put_line('OS error occured during write operation');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INTERNAL_ERROR
THEN
dbms_output.put_line('Internal Error');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN OTHERS
THEN
UTL_FILE.FCLOSE(file_handle);
RAISE;
END prc_table_to_csv;
row count procedure
declare
sql_stmt varchar2(254);
row_count number;
cursor get_tab is
select table_name, num_rows
from dba_tables
where owner=upper('&&1');
begin
dbms_output.put_line('Checking Record Counts for schema &&1 ');
dbms_output.put_line('Log file to numrows_&&1.lst ....');
dbms_output.put_line('....');
FOR get_tab_rec IN get_tab LOOP
BEGIN
sql_stmt := 'select count(*) col1 from &&1.'||get_tab_rec.table_name;
EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table '||rpad(get_tab_rec.table_name,30)
||' '||TO_CHAR(row_count)||' rows (Analyze '||get_tab_rec.num_rows||' rows).');
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error counting rows for table '
||get_tab_rec.table_name);
END;
END LOOP;
END;
sql_stmt varchar2(254);
row_count number;
cursor get_tab is
select table_name, num_rows
from dba_tables
where owner=upper('&&1');
begin
dbms_output.put_line('Checking Record Counts for schema &&1 ');
dbms_output.put_line('Log file to numrows_&&1.lst ....');
dbms_output.put_line('....');
FOR get_tab_rec IN get_tab LOOP
BEGIN
sql_stmt := 'select count(*) col1 from &&1.'||get_tab_rec.table_name;
EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table '||rpad(get_tab_rec.table_name,30)
||' '||TO_CHAR(row_count)||' rows (Analyze '||get_tab_rec.num_rows||' rows).');
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error counting rows for table '
||get_tab_rec.table_name);
END;
END LOOP;
END;
oracle data to xml format
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4061080732051
dbms random value from a table
SELECT * FROM employees
WHERE employee_id IN
(SELECT employee_id FROM
(SELECT employee_id, SYS.DBMS_RANDOM.RANDOM FROM employees ORDER BY 2)
WHERE rownum <= 1 );
Director read write file operations
declare
l_file UTL_FILE.file_type;
fileHandler UTL_FILE.FILE_TYPE;
l_location VARCHAR2(100) := '
l_text VARCHAR2(32767);
mesaj VARCHAR2(32767);
l_filename VARCHAR2(100) := '
l_filename_output VARCHAR2(100) := '
asd VARCHAR2(32767);
begin
mesaj:=' ';
l_file := UTL_FILE.fopen(l_location, l_filename, 'r', 32767);
UTL_FILE.get_line(l_file, l_text, 32767);
--DBMS_OUTPUT.PUT_LINE(l_text);
--mesaj:=mesaj||l_text||', '', '||chr(13) || chr(10)||' ';
END
UTL_FILE.fclose(l_file);
fileHandler := UTL_FILE.FOPEN(l_location,l_filename_output, 'w');
UTL_FILE.PUTF(fileHandler, mesaj);
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN OTHERS THEN
NULL;
end;
Cursor in Cursor
DECLARE
CURSOR cur_log IS SELECT PK_VALUES, COLUMNS_HEADER, COLUMNS_OLD_VALUE,COLUMNS_NEW_VALUE FROM XXTEB_AUDIT_LOG WHERE PK_VALUES=202;
BEGIN
FOR cur_log_rec IN cur_log LOOP
DECLARE CURSOR cur_result IS SELECT h.COLUMNVAL as Header, nv.COLUMNVAL as NewValue, ov.COLUMNVAL as OldValue
FROM TABLE(CAST (XXTEB_STR2TBL (cur_log_rec.COLUMNS_HEADER,'|') AS XXTEB_TABLE)) h
JOIN TABLE(CAST (XXTEB_STR2TBL (cur_log_rec.COLUMNS_NEW_VALUE,'|') AS XXTEB_TABLE)) nv ON nv.ROWNO=h.ROWNO
JOIN TABLE(CAST (XXTEB_STR2TBL (cur_log_rec.COLUMNS_OLD_VALUE,'|') AS XXTEB_TABLE)) ov ON ov.ROWNO=nv.ROWNO
WHERE 1=1;
BEGIN
FOR cur_result_rec IN cur_result LOOP
dbms_output.put_line (cur_result_rec.Header||';'||cur_result_rec.OldValue||';'||cur_result_rec.NewValue);
END LOOP;
END;
END LOOP;
END;
CURSOR cur_log IS SELECT PK_VALUES, COLUMNS_HEADER, COLUMNS_OLD_VALUE,COLUMNS_NEW_VALUE FROM XXTEB_AUDIT_LOG WHERE PK_VALUES=202;
BEGIN
FOR cur_log_rec IN cur_log LOOP
DECLARE CURSOR cur_result IS SELECT h.COLUMNVAL as Header, nv.COLUMNVAL as NewValue, ov.COLUMNVAL as OldValue
FROM TABLE(CAST (XXTEB_STR2TBL (cur_log_rec.COLUMNS_HEADER,'|') AS XXTEB_TABLE)) h
JOIN TABLE(CAST (XXTEB_STR2TBL (cur_log_rec.COLUMNS_NEW_VALUE,'|') AS XXTEB_TABLE)) nv ON nv.ROWNO=h.ROWNO
JOIN TABLE(CAST (XXTEB_STR2TBL (cur_log_rec.COLUMNS_OLD_VALUE,'|') AS XXTEB_TABLE)) ov ON ov.ROWNO=nv.ROWNO
WHERE 1=1;
BEGIN
FOR cur_result_rec IN cur_result LOOP
dbms_output.put_line (cur_result_rec.Header||';'||cur_result_rec.OldValue||';'||cur_result_rec.NewValue);
END LOOP;
END;
END LOOP;
END;
replace char in all database by another char
CREATE OR REPLACE PROCEDURE REPLACE_CHAR(p_tablename in varchar2 default null,
--table name
p_search in varchar2 default null,--search valuep_replace in varchar2 default null--replace value) AS
--this plsql procedure will replace the spqcific char with another specific char in specific table of the schema and in all columns ('VARCHAR2','CHAR','VARCHAR')l_columname user_tab_cols.COLUMN_NAME%type;l_data varchar2(32767);l_rowid rowid;v_sql varchar2(1000);temp varchar2(1000);
type t_data is REF CURSOR;c_data t_data;
cursor c_user_tab_columnsisselect column_namefrom user_tab_colswhere table_name = p_tablenameand data_type in ('VARCHAR2','CHAR','VARCHAR');--cursor declaration
BEGINtemp:='';v_sql:='';
OPEN c_user_tab_columns;loop
--DBMS_output.put_line( 'loop');fetch c_user_tab_columns into l_columname;EXIT WHEN c_user_tab_columns%NOTFOUND;
--DBMS_output.put_line( 'columnsname ' l_columname );
open c_data for 'select rowid,'l_columname' from 'p_tablename;--ref cursor dynamic sql within plsql
loop
fetch c_data into l_rowid,l_data;
EXIT WHEN c_data%NOTFOUND;
temp:=temp'replace(''';temp:=templ_data;temp:=temp''',''';temp:=tempp_search;temp:=temp''',''';temp:=tempp_replace;temp:=temp''')';v_sql:=v_sql'update 'p_tablename' set'l_columname'='temp' whererowid=chartorowid('''(l_rowid)''')';
execute immediate v_sql;
commit;
temp:='';v_sql:='';
--EXECUTE IMMEDIATE 'select 'l_columname' from 'p_tablename into l_data;/*dbms_output.put_line(p_tablename);dbms_output.put_line(l_columname);dbms_output.put_line(l_data);dbms_output.put_line(l_rowid);dbms_output.put_line(p_search);dbms_output.put_line(p_replace);*/
end loop;close c_data;
end loop;close c_user_tab_columns;
EXCEPTION WHEN OTHERS THEN DBMS_output.put_line(' error ' SQLERRM);
END;/
--table name
p_search in varchar2 default null,--search valuep_replace in varchar2 default null--replace value) AS
--this plsql procedure will replace the spqcific char with another specific char in specific table of the schema and in all columns ('VARCHAR2','CHAR','VARCHAR')l_columname user_tab_cols.COLUMN_NAME%type;l_data varchar2(32767);l_rowid rowid;v_sql varchar2(1000);temp varchar2(1000);
type t_data is REF CURSOR;c_data t_data;
cursor c_user_tab_columnsisselect column_namefrom user_tab_colswhere table_name = p_tablenameand data_type in ('VARCHAR2','CHAR','VARCHAR');--cursor declaration
BEGINtemp:='';v_sql:='';
OPEN c_user_tab_columns;loop
--DBMS_output.put_line( 'loop');fetch c_user_tab_columns into l_columname;EXIT WHEN c_user_tab_columns%NOTFOUND;
--DBMS_output.put_line( 'columnsname ' l_columname );
open c_data for 'select rowid,'l_columname' from 'p_tablename;--ref cursor dynamic sql within plsql
loop
fetch c_data into l_rowid,l_data;
EXIT WHEN c_data%NOTFOUND;
temp:=temp'replace(''';temp:=templ_data;temp:=temp''',''';temp:=tempp_search;temp:=temp''',''';temp:=tempp_replace;temp:=temp''')';v_sql:=v_sql'update 'p_tablename' set'l_columname'='temp' whererowid=chartorowid('''(l_rowid)''')';
execute immediate v_sql;
commit;
temp:='';v_sql:='';
--EXECUTE IMMEDIATE 'select 'l_columname' from 'p_tablename into l_data;/*dbms_output.put_line(p_tablename);dbms_output.put_line(l_columname);dbms_output.put_line(l_data);dbms_output.put_line(l_rowid);dbms_output.put_line(p_search);dbms_output.put_line(p_replace);*/
end loop;close c_data;
end loop;close c_user_tab_columns;
EXCEPTION WHEN OTHERS THEN DBMS_output.put_line(' error ' SQLERRM);
END;/
CrossTab
Hi,
I'm glad you asked this question. I have a recipe
for a similar query in my upcoming book and
I was juggling with a few solutions.
Answering it here will give me a chance to get some
real feedback on my proposed solution :)
As always, there's actually a few ways to do this,
here's one approach:
SQL> select * from food;
WHO APPLES PEACHES ORANGES PIZZA
---- ---------- ---------- ---------- ----------
john 5 1 2 15
mary 1 3 4 12
jill 2 1 6 6
/* the key is transforming the columns into rows, you can
do that with a cartesian product then keep only
the rows you need
*/
SQL> select decode(iter.pos,
2 1,'apples',
3 2,'peaches',
4 3,'oranges',
5 4,'pizza' ) fruit,
6 decode(iter.pos,
7 1,food.apples,
8 2,food.peaches,
9 3,food.oranges,
10 4,food.pizza) cnt,
11 food.who,
12 iter.pos
13 from food,
14 (select rownum pos from all_objects) iter
15 where iter.pos <= 4
16 order by who;
FRUIT CNT WHO POS
------- ---------- ---- ----------
apples 2 jill 1
peaches 1 jill 2
oranges 6 jill 3
pizza 6 jill 4
apples 5 john 1
peaches 1 john 2
pizza 15 john 4
oranges 2 john 3
apples 1 mary 1
oranges 4 mary 3
pizza 12 mary 4
peaches 3 mary 2
/* in the query above, I named the inline view that select from
all_objects, ITER, and rownum, POS, to indicate you are
sort of "iterating" until a certain "position". In this
case, there are 4 food columns, so 4 "iterations" (rows) need
to be generated. POS is also used to provide a "ranking"
for WHO and FRUIT - you'll need this later when grouping
*/
/* the next step is to use DECODE to start organizing the data
into groups - in the book I think I use CASE cuz it's universal
- either work fine here
*/
SQL> select who,
2 fruit,
3 decode(who,'john',cnt) john,
4 decode(who,'mary',cnt) mary,
5 decode(who,'jill',cnt) jill,
6 pos
7 from (
8 select decode(iter.pos,
9 1,'apples',
10 2,'peaches',
11 3,'oranges',
12 4,'pizza' ) fruit,
13 decode(iter.pos,
14 1,food.apples,
15 2,food.peaches,
16 3,food.oranges,
17 4,food.pizza) cnt,
18 food.who,
19 iter.pos
20 from food,
21 (select rownum pos from all_objects) iter
22 where iter.pos <= 4
23 )
24 order by who;
WHO FRUIT JOHN MARY JILL POS
---- ------- ---------- ---------- ---------- ----------
jill apples 2 1
jill peaches 1 2
jill oranges 6 3
jill pizza 6 4
john apples 5 1
john peaches 1 2
john pizza 15 4
john oranges 2 3
mary apples 1 1
mary oranges 4 3
mary pizza 12 4
mary peaches 3 2
/* at this point, the last step is to bump the rows for
JOHN and MARY up, to do that you need to get rid of the
NULLS. This is done by grouping by FRUIT and POS while
taking the max for JOHN, MARY, and JILL
*/
SQL> select fruit,
2 max(decode(who,'john',cnt)) john,
3 max(decode(who,'mary',cnt)) mary,
4 max(decode(who,'jill',cnt)) jill
5 from (
6 select decode(iter.pos,
7 1,'apples',
8 2,'peaches',
9 3,'oranges',
10 4,'pizza' ) fruit,
11 decode(iter.pos,
12 1,food.apples,
13 2,food.peaches,
14 3,food.oranges,
15 4,food.pizza) cnt,
16 food.who,
17 iter.pos
18 from food,
19 (select rownum pos from all_objects) iter
20 where iter.pos <= 4
21 )
22 group by fruit,pos
23 order by decode(fruit,'oranges','yoranges','pizza','zizza',fruit);
FRUIT JOHN MARY JILL
------- ---------- ---------- ----------
apples 5 1 2
peaches 1 3 1
oranges 2 4 6
pizza 15 12 6
/* the funky ORDER BY at the end is to ensure the results are
displayed how you had them in your post - there's other
ways to do that as well
*/
Let me know if this has been helpful. If so, this will be the approach
I use for
the Oracle recipe that deals with creating cross tab results when n
columns
are involved ( I will be covering 5 dbms's, not just Oracle ).
Thanks and regards,
Anthony
-----Original Message-----
From: RM via oracle-db-l [mailto:oracle-db-l@Groups.ITtoolbox.com]
Sent: Sunday, February 13, 2005 1:02 AM
To: Anthony Molinaro
Subject: [oracle-db-l] Cross Tab
# Oracle Database 10g: A Revolution in Database Technology
# Read Paper: http://oracle.ITtoolbox.com/r/hdr.asp?r=41933
# View Group Archive: http://oracle.ITtoolbox.com/hrd.asp?i=838
Hi, I have the following table...
apples peaches oranges pizza
john 5 1 2 15
mary 1 3 4 12
jill 2 1 6 6
How is it possible to create a cross-tab (table/view) in sql or plsql
(but I prefer regular sql) so that the data now looks like this...
john mary jill
apples 5 1 2
peaches 1 3 1
oranges 2 4 6
pizza 15 12 6
I'm glad you asked this question. I have a recipe
for a similar query in my upcoming book and
I was juggling with a few solutions.
Answering it here will give me a chance to get some
real feedback on my proposed solution :)
As always, there's actually a few ways to do this,
here's one approach:
SQL> select * from food;
WHO APPLES PEACHES ORANGES PIZZA
---- ---------- ---------- ---------- ----------
john 5 1 2 15
mary 1 3 4 12
jill 2 1 6 6
/* the key is transforming the columns into rows, you can
do that with a cartesian product then keep only
the rows you need
*/
SQL> select decode(iter.pos,
2 1,'apples',
3 2,'peaches',
4 3,'oranges',
5 4,'pizza' ) fruit,
6 decode(iter.pos,
7 1,food.apples,
8 2,food.peaches,
9 3,food.oranges,
10 4,food.pizza) cnt,
11 food.who,
12 iter.pos
13 from food,
14 (select rownum pos from all_objects) iter
15 where iter.pos <= 4
16 order by who;
FRUIT CNT WHO POS
------- ---------- ---- ----------
apples 2 jill 1
peaches 1 jill 2
oranges 6 jill 3
pizza 6 jill 4
apples 5 john 1
peaches 1 john 2
pizza 15 john 4
oranges 2 john 3
apples 1 mary 1
oranges 4 mary 3
pizza 12 mary 4
peaches 3 mary 2
/* in the query above, I named the inline view that select from
all_objects, ITER, and rownum, POS, to indicate you are
sort of "iterating" until a certain "position". In this
case, there are 4 food columns, so 4 "iterations" (rows) need
to be generated. POS is also used to provide a "ranking"
for WHO and FRUIT - you'll need this later when grouping
*/
/* the next step is to use DECODE to start organizing the data
into groups - in the book I think I use CASE cuz it's universal
- either work fine here
*/
SQL> select who,
2 fruit,
3 decode(who,'john',cnt) john,
4 decode(who,'mary',cnt) mary,
5 decode(who,'jill',cnt) jill,
6 pos
7 from (
8 select decode(iter.pos,
9 1,'apples',
10 2,'peaches',
11 3,'oranges',
12 4,'pizza' ) fruit,
13 decode(iter.pos,
14 1,food.apples,
15 2,food.peaches,
16 3,food.oranges,
17 4,food.pizza) cnt,
18 food.who,
19 iter.pos
20 from food,
21 (select rownum pos from all_objects) iter
22 where iter.pos <= 4
23 )
24 order by who;
WHO FRUIT JOHN MARY JILL POS
---- ------- ---------- ---------- ---------- ----------
jill apples 2 1
jill peaches 1 2
jill oranges 6 3
jill pizza 6 4
john apples 5 1
john peaches 1 2
john pizza 15 4
john oranges 2 3
mary apples 1 1
mary oranges 4 3
mary pizza 12 4
mary peaches 3 2
/* at this point, the last step is to bump the rows for
JOHN and MARY up, to do that you need to get rid of the
NULLS. This is done by grouping by FRUIT and POS while
taking the max for JOHN, MARY, and JILL
*/
SQL> select fruit,
2 max(decode(who,'john',cnt)) john,
3 max(decode(who,'mary',cnt)) mary,
4 max(decode(who,'jill',cnt)) jill
5 from (
6 select decode(iter.pos,
7 1,'apples',
8 2,'peaches',
9 3,'oranges',
10 4,'pizza' ) fruit,
11 decode(iter.pos,
12 1,food.apples,
13 2,food.peaches,
14 3,food.oranges,
15 4,food.pizza) cnt,
16 food.who,
17 iter.pos
18 from food,
19 (select rownum pos from all_objects) iter
20 where iter.pos <= 4
21 )
22 group by fruit,pos
23 order by decode(fruit,'oranges','yoranges','pizza','zizza',fruit);
FRUIT JOHN MARY JILL
------- ---------- ---------- ----------
apples 5 1 2
peaches 1 3 1
oranges 2 4 6
pizza 15 12 6
/* the funky ORDER BY at the end is to ensure the results are
displayed how you had them in your post - there's other
ways to do that as well
*/
Let me know if this has been helpful. If so, this will be the approach
I use for
the Oracle recipe that deals with creating cross tab results when n
columns
are involved ( I will be covering 5 dbms's, not just Oracle ).
Thanks and regards,
Anthony
-----Original Message-----
From: RM via oracle-db-l [mailto:oracle-db-l@Groups.ITtoolbox.com]
Sent: Sunday, February 13, 2005 1:02 AM
To: Anthony Molinaro
Subject: [oracle-db-l] Cross Tab
# Oracle Database 10g: A Revolution in Database Technology
# Read Paper: http://oracle.ITtoolbox.com/r/hdr.asp?r=41933
# View Group Archive: http://oracle.ITtoolbox.com/hrd.asp?i=838
Hi, I have the following table...
apples peaches oranges pizza
john 5 1 2 15
mary 1 3 4 12
jill 2 1 6 6
How is it possible to create a cross-tab (table/view) in sql or plsql
(but I prefer regular sql) so that the data now looks like this...
john mary jill
apples 5 1 2
peaches 1 3 1
oranges 2 4 6
pizza 15 12 6
plsql send mail
declare
CRLF CONSTANT varchar2(10) := utl_tcp.CRLF;
BOUNDARY CONSTANT varchar2(256) := '-----7D81B75CCC90D2974F7A1CBD';
FIRST_BOUNDARY CONSTANT varchar2(256) := '--'||BOUNDARY||CRLF;
LAST_BOUNDARY CONSTANT varchar2(256) := '--'||BOUNDARY||'--'||CRLF;
MULTIPART_MIME_TYPE CONSTANT varchar2(256) := 'multipart/mixed; boundary="'||BOUNDARY||'"';
mail_conn utl_smtp.connection;
sender VARCHAR2(200);
mhost VARCHAR2(30);
inRecipient VARCHAR2(100);
inMessage VARCHAR2(100);
mime_type varchar2(255) := 'text/html';
attachment_file_name varchar2(255) := 'abc.txt';
--attachment_file_name varchar2(255) := 'abc.txt';
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(mail_conn, name || ': ' || header || utl_tcp.CRLF);
END;
procedure send_receive_header(name in varchar2, header in varchar2) is
begin
utl_smtp.write_data(mail_conn, name||': '||header||CRLF);
end;
BEGIN
inMessage :='DENEME1';
inRecipient:='ctahtaci@yfas.com.tr';
mhost:='193.193.193.96';
mail_conn := utl_smtp.open_connection(mhost, 25);
sender :='ct_deneme_attach@yfas.com.tr';
utl_smtp.helo(mail_conn, mhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, inRecipient);
utl_smtp.open_data(mail_conn);
send_header('From', 'xxxxx');
send_header('To', '"Recipient"
send_header('Subject', 'Hello1');
send_header('Content-Type',MULTIPART_MIME_TYPE);
--utl_smtp.write_data(c, utl_tcp.CRLF || 'Hello, world!');
-- utl_smtp.data(mail_conn, inMessage);
----------------------------------------
-- Send the main message text
----------------------------------------
-- mime header
utl_smtp.write_data(mail_conn, FIRST_BOUNDARY);
send_receive_header('Content-Type',mime_type);
utl_smtp.write_data(mail_conn, CRLF);
utl_smtp.write_data(mail_conn, 'see attachment');
utl_smtp.write_data(mail_conn, CRLF);
-- add the attachment
utl_smtp.write_data(mail_conn, FIRST_BOUNDARY);
send_receive_header('Content-Type',mime_type);
send_receive_header('Content-Disposition','attachment; filename= '||attachment_file_name);
utl_smtp.write_data(mail_conn, CRLF);
utl_smtp.write_data(mail_conn, 'This is the attachment and this text is written in add attachement section');
utl_smtp.write_data(mail_conn, CRLF);
-- Close the message
utl_smtp.write_data(mail_conn, LAST_BOUNDARY);
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
null;
END;
Kaydol:
Kayıtlar (Atom)
