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 Go to next message
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 #314827 is a reply to message #314581] Thu, 17 April 2008 23:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Since you are using APEX, in the Utilities there is a very simple to follow menu-driven load/upload option. It allows you to specify which table, which columns, a query to determine which rows, and other options. However, this seems like an odd requirement to repeatedly remove data to a text file, delete it, then put it back.

Re: temporary ascii files [message #314891 is a reply to message #314827] Fri, 18 April 2008 03:34 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 #315051 is a reply to message #314891] Fri, 18 April 2008 14:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The free_apex demo lots its formatting, even with the code tags, so here is a better view.

/forum/fa/4162/0/
  • Attachment: free_apex.jpg
    (Size: 63.93KB, Downloaded 2199 times)

[Updated on: Fri, 18 April 2008 14:27]

Report message to a moderator

Re: temporary ascii files [message #315061 is a reply to message #314891] Fri, 18 April 2008 14:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
For some reason, I can't seem to connect to Tom Kyte's site right now. When you are home search http://asktom.oracle.com for dump_csv and load_data. Dump_csv is a pl/sql function that outputs the result of any query to a text file and load_data is a pl/sql function that loads the data from a text file to a database table.
Re: temporary ascii files [message #315067 is a reply to message #314891] Fri, 18 April 2008 15:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 #315074 is a reply to message #315067] Fri, 18 April 2008 19:46 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member

THANKS.... and thanks a lot

yours
dr.s.raghunathan
Re: temporary ascii files [message #326119 is a reply to message #315067] Tue, 10 June 2008 04:35 Go to previous messageGo to next message
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 Go to previous message
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>
Previous Topic: browser settings while using apex 3.0
Next Topic: cursor bulk collect
Goto Forum:
  


Current Time: Fri Mar 29 07:10:47 CDT 2024