Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » temporary ascii files (Oracle 10 G Apex 3.0)
| temporary ascii files [message #314581] |
Thu, 17 April 2008 03:56  |
dr.s.raghunathan
Messages: 540
Registered: February 2008
|
Senior Member |
|
|
hi
i have one table with more than 10000 rows where in one of the
column is date. I just select no of rows by giving one specific
date. It fetches approximately 8500 rows.
I just want to push these 8500 rows to separate ascii file
under the name given by me. Like spool file... Immediately
i want to delete these rows from the table.
Subsequently whenever i want to retrive the data to my table
i shall be able to give the filename with location. MY table
should populate these data .
This job frequently executable on daily basis.
how can i achieve it..
For those who know dbase.. the following syntax does it
copy to xx.txt sdf
appe from xx
I want to achieve it through pl/sql.. when i try to
use spool command in pl/sql my system behaves crazily...
can any one offer some sort of solution..
yours
dr.s.raghunathan...
post script : My office premises intranet permits only
orafaq.com site. Even if any one suggests any other link,
our system prevents accessing. Hence i have to note down
the link and i can get clarrified only at my residence..
In home, i am a very obedient husband and getting time to
access the site is very cumbersome task and long plan
required.. If possible straight solution is preferred..
yours
dr.s.raghuanthan
|
|
|
|
|
|
| Re: temporary ascii files [message #314891 is a reply to message #314827] |
Fri, 18 April 2008 03:34  |
dr.s.raghunathan
Messages: 540
Registered: February 2008
|
Senior Member |
|
|
agreed...
i also achieved through utility menu option.
I want to achieve through pl/sql through apex page.
Reason for this facility is very peculiar..
I am using free version of apex which has got 4 GB database
limitation. database resources swallow 19% at the time of
installation. I am unable to measure my data storage whether
it exceeds 4 gb or when it can exceed?
since it is a single user and rare data manipulation or
extracts on old data, i am afraid to delete the stored data.
Hence i want to store it through ascii file.
My subordinate who is using no prevliges to access database
directly. he can use the database only through apex pages
he has no control over command other than clicking buttons
otherwise, he has no accesss over application builder,
sql workshop or or any utilities
hope you shall understand my requirement...
Through apex page i want to measure the size of my database?
thanks in advance
yours
dr.s.raghunathan
|
|
|
|
| Re: temporary ascii files [message #315036 is a reply to message #314891] |
Fri, 18 April 2008 13:25 |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Tom Kyte has a script (free.sql) that shows the allocated size of the database, how much is used, and how much is free, by tablespace, with totals. The script uses some SQL*Plus commands for formatting. I made some modifications so that it will run on apex, which does not recognize SQL*Plus commands and saved it as free_apex.sql. I have provided both scripts, with links to the original source, and demos below.
-- free.sql
-- from Tom Kyte's site (http://asktom.oracle.com)
-- link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:285415955510
--------------------------------------------------------
-- free.sql
--
-- This SQL Plus script lists freespace by tablespace
--------------------------------------------------------
column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a16 heading "Tablespace Name"
column Kbytes format 999,999,999 heading "KBytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report
select nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
tablespace_name
from sys.dba_data_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 1
/
-------------------------------------------------------
-- modifications to original:
-- removed ampersand in order by clause
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
-- demo of free.sql
SCOTT@orcl_11g> @free
SCOTT@orcl_11g> -- from Tom Kyte's site (http://asktom.oracle.com)
SCOTT@orcl_11g> -- link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:285415955510
SCOTT@orcl_11g> --------------------------------------------------------
SCOTT@orcl_11g> -- free.sql
SCOTT@orcl_11g> --
SCOTT@orcl_11g> -- This SQL Plus script lists freespace by tablespace
SCOTT@orcl_11g> --------------------------------------------------------
SCOTT@orcl_11g>
SCOTT@orcl_11g> column dummy noprint
SCOTT@orcl_11g> column pct_used format 999.9 heading "%|Used"
SCOTT@orcl_11g> column name format a16 heading "Tablespace Name"
SCOTT@orcl_11g> column Kbytes format 999,999,999 heading "KBytes"
SCOTT@orcl_11g> column used format 999,999,999 heading "Used"
SCOTT@orcl_11g> column free format 999,999,999 heading "Free"
SCOTT@orcl_11g> column largest format 999,999,999 heading "Largest"
SCOTT@orcl_11g> break on report
SCOTT@orcl_11g> compute sum of kbytes on report
SCOTT@orcl_11g> compute sum of free on report
SCOTT@orcl_11g> compute sum of used on report
SCOTT@orcl_11g>
SCOTT@orcl_11g> select nvl(b.tablespace_name,
2 nvl(a.tablespace_name,'UNKOWN')) name,
3 kbytes_alloc kbytes,
4 kbytes_alloc-nvl(kbytes_free,0) used,
5 nvl(kbytes_free,0) free,
6 ((kbytes_alloc-nvl(kbytes_free,0))/
7 kbytes_alloc)*100 pct_used,
8 nvl(largest,0) largest
9 from ( select sum(bytes)/1024 Kbytes_free,
10 max(bytes)/1024 largest,
11 tablespace_name
12 from sys.dba_free_space
13 group by tablespace_name ) a,
14 ( select sum(bytes)/1024 Kbytes_alloc,
15 tablespace_name
16 from sys.dba_data_files
17 group by tablespace_name )b
18 where a.tablespace_name (+) = b.tablespace_name
19 order by 1
20 /
%
Tablespace Name KBytes Used Free Used Largest
---------------- ------------ ------------ ------------ ------ ------------
EXAMPLE 102,400 79,232 23,168 77.4 21,184
SYSAUX 865,344 808,640 56,704 93.4 39,424
SYSTEM 757,760 749,248 8,512 98.9 8,128
UNDOTBS1 435,200 43,328 391,872 10.0 385,984
USERS 1,943,680 1,656,384 287,296 85.2 1,024
------------ ------------ ------------
sum 4,104,384 3,336,832 767,552
SCOTT@orcl_11g> -------------------------------------------------------
SCOTT@orcl_11g> -- modifications to original:
SCOTT@orcl_11g> -- removed ampersand in order by clause
SCOTT@orcl_11g> CLEAR COLUMNS
SCOTT@orcl_11g> CLEAR BREAKS
SCOTT@orcl_11g> CLEAR COMPUTES
-- free_apex.sql
-- free_apex.sql
-- modification of free.sql by Tom Kyte (http://asktom.oracle.com)
-- modified to run on apex without sql*plus commands
-- original free.sql: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:285415955510
--------------------------------------------------------
-- free.sql
--
-- This SQL Plus script lists freespace by tablespace
--------------------------------------------------------
--column dummy noprint
--column pct_used format 999.9 heading "%|Used"
--column name format a16 heading "Tablespace Name"
--column Kbytes format 999,999,999 heading "KBytes"
--column used format 999,999,999 heading "Used"
--column free format 999,999,999 heading "Free"
--column largest format 999,999,999 heading "Largest"
--break on report
--compute sum of kbytes on report
--compute sum of free on report
--compute sum of used on report
select nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
1 AS order_by
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
tablespace_name
from sys.dba_data_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
UNION ALL
select 'TOTALS' AS name,
SUM (kbytes_alloc) kbytes,
SUM (kbytes_alloc-nvl(kbytes_free,0)) used,
SUM (nvl(kbytes_free,0)) free,
NULL AS pct_used,
NULL AS largest,
2 AS order_by
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
tablespace_name
from sys.dba_data_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by order_by, name
/
-------------------------------------------------------
-- demo of free_apex.sql
Application Express HelpLogout
Home Application Builder SQL Workshop Utilities
Home>SQL Workshop>SQL Scripts>Results
Script: free_apex.sql Status: Complete
View:
Detail Summary
Show:
Statement Results Feedback
select nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
1 AS order_by
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
tablespace_name
from sys.dba_data_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
UNION ALL
select 'TOTALS' AS name,
SUM (kbytes_alloc) kbytes,
SUM (kbytes_alloc-nvl(kbytes_free,0)) used,
SUM (nvl(kbytes_free,0)) free,
NULL AS pct_used,
NULL AS largest,
2 AS order_by
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
tablespace_name
from sys.dba_data_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by order_by, name
NAME KBYTES USED FREE PCT_USED LARGEST ORDER_BY
CM 40960 1984 38976 4.84375 38976 1
FLOW_1 10304 5632 4672 54.6583850931677018633540372670807453416 3072 1
FLOW_2 10304 256 10048 2.48447204968944099378881987577639751553 8704 1
FLOW_3 51264 1472 49792 2.8714107365792759051186017478152309613 49728 1
FLOW_4 5184 1728 3456 33.3333333333333333333333333333333333333 3456 1
FLOW_5 102464 1408 101056 1.37414116177389131792629606495940037477 101056 1
FLOW_6 51264 64 51200 .124843945068664169787765293383270911361 40768 1
FLOW_7 10304 64 10240 .621118012422360248447204968944099378882 10240 1
HOME 20480 1408 19072 6.875 19072 1
IMAGING 51200 2752 48448 5.375 48448 1
RHYTHM 51200 17472 33728 34.125 27840 1
SYSAUX 1075200 1059840 15360 98.5714285714285714285714285714285714286 5056 1
SYSTEM 593920 587008 6912 98.8362068965517241379310344827586206897 6912 1
UNDOTBS1 138240 10624 127616 7.68518518518518518518518518518518518519 108480 1
USERS 5120 4352 768 85 704 1
TOTALS 2217408 1696064 521344 - - 2
16 rows selected. 0.23 seconds
Run By BARBARA_ADMIN
Parsing Schema BARBARA
Script Started Friday, April 18, 2008
5 seconds ago
Elapsed time 0.49 seconds
Statements Processed 1
Successful 1
With Errors 0
Language: en-us
Application Express 3.0.0.00.20
Workspace: BARBARAUser: BARBARA_ADMIN
Copyright © 1999, 2007, Oracle. All rights reserved.
|
|
|
|
|
|
|
|
| Re: temporary ascii files [message #315067 is a reply to message #314891] |
Fri, 18 April 2008 15:36 |
|
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I still can't connect to Tom Kyte's site, but I located some copies of the functions and have provided them and a brief demo below.
-- dump_csv for dumping data from table to text file:
-- by Tom Kyte:
create or replace function dump_csv( p_query in varchar2,
p_separator in varchar2
default ',',
p_dir in varchar2 ,
p_filename in varchar2 )
return number
AUTHID CURRENT_USER
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(2000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i,
l_columnValue, 2000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;
dbms_sql.define_column( l_theCursor, 1, l_columnValue,
2000 );
l_status := dbms_sql.execute(l_theCursor);
loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
utl_file.put( l_output, l_separator ||
l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
return l_cnt;
end dump_csv;
/
-- load_data for loading data from text file to table:
-- by Tom Kyte from
create or replace
function load_data( p_table in varchar2,
p_cnames in varchar2,
p_dir in varchar2,
p_filename in varchar2,
p_delimiter in varchar2 default '|' )
return number
is
l_input utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_buffer varchar2(4000);
l_lastLine varchar2(4000);
l_status integer;
l_colCnt number default 0;
l_cnt number default 0;
l_sep char(1) default NULL;
l_errmsg varchar2(4000);
begin
l_input := utl_file.fopen( p_dir, p_filename, 'r' );
l_buffer := 'insert into ' || p_table || ' values ( ';
l_colCnt := length(p_cnames)-
length(replace(p_cnames,',',''))+1;
for i in 1 .. l_colCnt
loop
l_buffer := l_buffer || l_sep || ':b'||i;
l_sep := ',';
end loop;
l_buffer := l_buffer || ')';
dbms_sql.parse(l_theCursor, l_buffer, dbms_sql.native);
loop
begin
utl_file.get_line( l_input, l_lastLine );
exception
when NO_DATA_FOUND then
exit;
end;
l_buffer := l_lastLine || p_delimiter;
for i in 1 .. l_colCnt
loop
dbms_sql.bind_variable
( l_theCursor, ':b'||i,
substr( l_buffer, 1,
instr(l_buffer,p_delimiter)-1 ) ) ;
l_buffer := substr( l_buffer,
instr(l_buffer,p_delimiter)+1 );
end loop;
begin
l_status := dbms_sql.execute(l_theCursor);
l_cnt := l_cnt + 1;
-- exception
-- when others then
-- l_errmsg := sqlerrm;
-- insert into badlog ( errm, data )
-- values ( l_errmsg, l_lastLine );
end;
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_input );
commit;
return l_cnt;
end load_data;
/
-- demo:
SCOTT@orcl_11g> CREATE TABLE test_tab AS SELECT * FROM emp
2 /
Table created.
SCOTT@orcl_11g> SELECT * FROM test_tab
2 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\oracle11g'
2 /
Directory created.
SCOTT@orcl_11g> DECLARE
2 v_sql_query VARCHAR2 (32767);
3 v_rows NUMBER;
4 BEGIN
5 v_sql_query := 'SELECT * FROM test_tab WHERE hiredate < TO_DATE (''01-01-1982'', ''DD-MM-YYYY'')';
6 v_rows := dump_csv (v_sql_query, ',', 'MY_DIR', 'old_emp_data.dat');
7 DBMS_OUTPUT.PUT_LINE (v_rows || ' rows saved to text file; check it before deleting from table');
8 END;
9 /
11 rows saved to text file; check it before deleting from table
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> DELETE FROM test_tab WHERE hiredate < TO_DATE ('01-01-1982', 'DD-MM-YYYY')
2 /
11 rows deleted.
SCOTT@orcl_11g> COMMIT
2 /
Commit complete.
SCOTT@orcl_11g> SELECT * FROM test_tab
2 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SCOTT@orcl_11g> DECLARE
2 v_rows NUMBER;
3 BEGIN
4 v_rows := load_data
5 ('TEST_TAB',
6 'EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO',
7 'MY_DIR',
8 'old_emp_data.dat',
9 ',');
10 DBMS_OUTPUT.PUT_LINE (v_rows || ' rows loaded into table from text file; check before deleting text file');
11 END;
12 /
11 rows loaded into table from text file; check before deleting text file
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> SELECT * FROM test_tab
2 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
14 rows selected.
SCOTT@orcl_11g>
|
|
|
|
|
|
| Re: temporary ascii files [message #326119 is a reply to message #315067] |
Tue, 10 June 2008 04:35 |
dr.s.raghunathan
Messages: 540
Registered: February 2008
|
Senior Member |
|
|
hi,
fantastic solution offered by barbara..
the only problem, with that is
while compiling the function of dump_csv, my Oracle XE Apex 3.0
version shows the error on using the utl_file.file_type
please help me once again to solve this sitution
yours
dr.s.raghunathan
|
|
|
|
| Re: temporary ascii files [message #326147 is a reply to message #314581] |
Tue, 10 June 2008 06:01 |
Martin Eysackers
Messages: 80
Registered: October 2005
Location: Belgium
|
Member |
|
|
can you show us the error message
as it occurs at the first occurance of utl_file within dump_csv
you probably don't have the rights to execute utl_file
try describe utl_file in sql*plus
if it fails have someone with dba privileges do the following :
grant execute on utl_file to <your_scheme>
|
|
|
|
Goto Forum:
Current Time: Tue Jan 07 16:31:49 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
