How to Import data From Oracle table to CSV file or txt file?

1) Write a procedure like below......

connect as scott user and create below procedure.


---------------------------------------------------------------
CREATE OR REPLACE PROCEDURE PR_Oracle_table_to_csv_or_txt (
p_tname IN VARCHAR2,
p_dir IN VARCHAR2,
p_filename IN VARCHAR2
)
IS
l_output UTL_FILE.file_type;
l_thecursor INTEGER DEFAULT DBMS_SQL.open_cursor;
l_columnvalue VARCHAR2 (4000);
l_status INTEGER;
l_query VARCHAR2 (1000) DEFAULT 'select * from ' || p_tname;
l_colcnt NUMBER := 0;
l_separator VARCHAR2 (1);
l_desctbl DBMS_SQL.desc_tab;
BEGIN
l_output := UTL_FILE.fopen (p_dir, p_filename, 'w');

EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

DBMS_SQL.parse (l_thecursor, l_query, DBMS_SQL.native);
DBMS_SQL.describe_columns (l_thecursor, l_colcnt, l_desctbl);

FOR i IN 1 .. l_colcnt
LOOP
UTL_FILE.put (l_output,
l_separator || '"' || l_desctbl (i).col_name || '"'
);
DBMS_SQL.define_column (l_thecursor, i, l_columnvalue, 4000);
l_separator := ',';
END LOOP;

UTL_FILE.new_line (l_output);
l_status := DBMS_SQL.EXECUTE (l_thecursor);

WHILE (DBMS_SQL.fetch_rows (l_thecursor) > 0)
LOOP
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 := ',';
END LOOP;

UTL_FILE.new_line (l_output);
END LOOP;

DBMS_SQL.close_cursor (l_thecursor);
UTL_FILE.fclose (l_output);

EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' ';
EXCEPTION
WHEN OTHERS
THEN
EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' ';

RAISE;
END;

-------------------------------------------------------

2) Grant create directory privileze to scott


SQL> grant CREATE ANY DIRECTORY to scott ;


3) Create a directory

create directory DIR_SCOTT as 'G:\DUMP'


4) then execute the procedure as desire file extension.


exec dump_table_to_csv( 'emp', 'DIR_SCOTT', 'emp_in_csv.csv' );


exec dump_table_to_csv( 'emp', 'DIR_SCOTT', 'emp_in_csv.txt' );

How to import/insert data from csv/excel file into a table by using Oracle forms (6i,8i,9i,10g)?

This is very easy, here i have attached a 6i forms and excel file and read me file
you can Download this (zip) form and excel file and follow the steps for this.

or you can download separately




[you can migrate this form to 8i,9i,10g forms easily, just need to compile]

1) create temp table to scott schema

2) then connect to oracle 6i form with scott/tiger user then run the form.

3) locate the csv file via browse option of the forms.

4) then click the "process to insert temp table" .

-----cheers----

How to Change current time in linux or solaris or unix

change the with "date" command .

given date format is [MMDDhhmm[[CC]YY][.ss]]

like belows

[root@RAC1 etc]#

[root@RAC1 etc]# date 052917012012

---(month 05, date 29 ,17 hour, 01 minute ,2012 year) [.12 second]

Tue May 29 17:01:00 BDT 2012

[root@RAC1 etc]#

How to Change TimeZone Using /etc/localtime File in redhat linux

---showing current time and timezone
# date

---Remove current localtime zone file
# cd /etc
# rm localtime

---Find out your time Zone information or name
# ls /usr/share/zoneinfo/Asia/

---Create an link to file localtime
# cd /etc
# ln -s /usr/share/zoneinfo/US/Pacific localtime

---at last show date and timezone again
# date

ORA-00470: LGWR process terminated with error, ora-00600 [3700]

Today i am seeing following message in my SMS Gateway sever's alert log file.
its seems problem in the file system of this server, log file can't work properly.....
so, i do a temprary solution for immediatelly solve the problem.



Sun May 27 10:31:35 2012
Shutting down instance (abort)
Sun May 27 10:31:35 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =97
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.3.0.
System parameters with non-default values:
processes = 800
sga_max_size = 734003200
__shared_pool_size = 255852544
__large_pool_size = 4194304
__java_pool_size = 12582912
__streams_pool_size = 0
sga_target = 734003200
control_files = E:\ORACLE\PRODUCT\10.2.0\ORADATA\SMSDB\CONTROL01.CTL, E:\ORACLE\PRODUCT\10.2.0\ORADATA\SMSDB\CONTROL02.CTL, E:\ORACLE\PRODUCT\10.2.0\ORADATA\SMSDB\CONTROL03.CTL
db_block_size = 8192
__db_cache_size = 452984832
compatible = 10.2.0.3.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = E:\oracle\product\10.2.0\flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=smsdbXDB)
job_queue_processes = 20
audit_file_dest = E:\ORACLE\PRODUCT\10.2.0\ADMIN\SMSDB\ADUMP
background_dump_dest = E:\ORACLE\PRODUCT\10.2.0\ADMIN\SMSDB\BDUMP
user_dump_dest = E:\ORACLE\PRODUCT\10.2.0\ADMIN\SMSDB\UDUMP
core_dump_dest = E:\ORACLE\PRODUCT\10.2.0\ADMIN\SMSDB\CDUMP
db_name = smsdb
open_cursors = 300
pga_aggregate_target = 314572800
aq_tm_processes = 2
PMON started with pid=2, OS id=2776
PSP0 started with pid=3, OS id=3404
MMAN started with pid=4, OS id=4088
DBW0 started with pid=5, OS id=4092
LGWR started with pid=6, OS id=1916
CKPT started with pid=7, OS id=448
SMON started with pid=8, OS id=444
RECO started with pid=9, OS id=868
CJQ0 started with pid=10, OS id=1688
MMON started with pid=11, OS id=1576
Sun May 27 10:31:35 2012
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=3276
Sun May 27 10:31:35 2012
starting up 1 shared server(s) ...
Sun May 27 10:31:35 2012
ALTER DATABASE MOUNT
Sun May 27 10:31:39 2012
Setting recovery target incarnation to 2
Sun May 27 10:31:39 2012
Successful mount of redo thread 1, with mount id 113714055
Sun May 27 10:31:39 2012
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Sun May 27 10:31:39 2012
ALTER DATABASE OPEN
Sun May 27 10:31:40 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_lgwr_1916.trc:
ORA-00600: internal error code, arguments: [3700], [1], [15], [3], [3], [], [], []

Sun May 27 10:31:40 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_lgwr_1916.trc:
ORA-00600: internal error code, arguments: [3700], [1], [15], [3], [3], [], [], []

Sun May 27 10:31:40 2012
LGWR: terminating instance due to error 470
Sun May 27 10:31:40 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_dbw0_4092.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_pmon_2776.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_reco_868.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_smon_444.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_mman_4088.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_psp0_3404.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:42 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_ckpt_448.trc:
ORA-00470: LGWR process terminated with error

Instance terminated by LGWR, pid = 1916




===========================================================================
=============================solution======================================
===========================================================================

For thid i do the follwing steps:

SQL> CONN / AS SYSDBA
SQL> startup mount
SQL> recover database until cancel;
SQL> alter database open resetlogs;


like.........


SQL*Plus: Release 10.2.0.3.0 - Production on Sun May 27 10:28:15 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 272631316 bytes
Database Buffers 452984832 bytes
Redo Buffers 7094272 bytes
Database mounted.
ORA-03113: end-of-file on communication channel



SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup force
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 276825620 bytes
Database Buffers 448790528 bytes
Redo Buffers 7094272 bytes
Database mounted.
ORA-03113: end-of-file on communication channel


SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL>
SQL>
SQL> conn / as sysdba
Connected to an idle instance.
SQL>
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 281019924 bytes
Database Buffers 444596224 bytes
Redo Buffers 7094272 bytes
Database mounted.
SQL>
SQL>
SQL> recover database until cancel ;
Media recovery complete.
SQL>
SQL>
SQL> alter database open resetlogs ;

Database altered.

SQL>
SQL>
SQL> $exp smsgtway1/smsgtway2 file=D:/smsgtway2_27052012.dmp owner=smsgtway1 ;

Export: Release 10.2.0.3.0 - Production on Sun May 27 10:42:06 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SMSGTWAY1
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SMSGTWAY1
About to export SMSGTWAY1's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SMSGTWAY1's tables via Conventional Path ...
. . exporting table SMS_ALL_TRAN 66131 rows exported
. . exporting table SMS_ALL_TRAN_BA 63166 rows exported
. . exporting table SMS_ALL_TRAN_REJECT 101 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

SQL>