Re: [Q] sql loader problem while load record more than one line???
You may want to add CONTINUEIF clause and try the load again ... infile 'data.asc' CONTINUEIF NEXT (1) != '' into table test ... For complete reference, see this URL: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch05.htm#1005518 -- Thanks, Krishna ~~ NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ~~ dba1 mcc wrote: We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishna Kakatur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Problem with jobs
Mauricio, Check the Oracle version. We had similar problems with 8.1.7.2. They got disappeared after we upgraded to 8.1.7.4 -- Thanks, Krishna ~~ NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ~~ Jared Still wrote: Have you checked DBA_JOBS for the last/next execution times? Is the job broken? Please check DBA_JOBS. Jared On Mon, 2004-01-26 at 06:04, Mauricio Vlez wrote: Hi, This is the situation: I'm woriking on NT and there are two 8i databases on it One database can execute jobs normally, but the other one not execute any job. I proved submitting the same procedure to both databases and worked on the first one but not on the second one. If I manually execute this:(On the database that have the problem): Exec dbms_job.run(job_number); Then the job is successful executed (without any error) and is programmed to the next interval but after that its not executed any more. And It doesnt appear broken (Its like the queue process were not working). I know all jobs are not executed because the column last_date on dba_jobs its not updated on any job (so next_date column its not updated) and the procedure of each job are not doing what they must do. Its happens since one week ago and before that they were working well. When I submit the Job I issued commit and I dont receive any error message So the Jobs doesnt appear broken (Its like the queue process were not working) But the initialization parameter are job_queue_processes = 4 job_queue_interval = 10 in init_SID.ora file So I repeat, the jobs were working well until one week ago, and I havent change nothing special on database. the view dba_job_running appear empty all the time. I removed all jobs and I recreated them again and it didnt resolve the problem. Im not using Oracle Enterprise Management, so I cant see diagnostic error messages and in the alert file it doesnt appear nothing related with the jobs. So early at morning I have to run the jobs manually Im thinking on shutdown down database but I want It to be the last option. I hope you can help me Thanks Mauricio -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishna Kakatur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: [Q] create tablespace with different block size error???
You may want to define DB_16K_CACHE_SIZE in init.ora, or use ALTER SYSTEM to set this value. HTH, Krishna ~~ NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ~~ dba1 mcc wrote: I tried to create tablespace with 16K (default on my init.ora blocksize =8k). I have error happen. The database version is 9.2.0.4. SQL create tablespace index1 logging datafile '/u0/oradata/leg92/index1.dbf' size 20m 2 blocksize 16384 3 autoextend on 4 next 1280k 5 maxsize unlimited 6 extent management local 7 segment space management auto 8 uniform size 128k; autoextend on * ERROR at line 3: if I take out blocksize 16384, then it work fine. SQL create tablespace index1 logging datafile '/u0/oradata/leg92/index1.dbf' size 20m 2 reuse autoextend on 3 next 1280k 4 maxsize unlimited 5 extent management local 6 segment space management auto 7 uniform size 128k; Tablespace created. Does anyone know why?? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishna Kakatur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Anyone run into this strange ORA-00904 error ??
Did you check grants on the table? This happens sometimes, with some oracle versions, in the following situation: step1: user1 creates tab1 and grants all to user2. (without grant option) step2: user2 creates view1 based on tab1 and grants all to user3. Now, when user3 tries to access view1 he gets this kind of error. This can be resolved by issuing the foll sql in step1 GRANT ALL ON tab1 TO user2 WITH ADMIN OPTION; -- Thanks, Krishna ~~ NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ~~ [EMAIL PROTECTED] wrote: We started having a weird problem that looks like some kind of data dictionary corruption. My first choice is to run catalog / catproc. This did nothing to resolve the problem. Why am I able to describe an object, but get ORA-00904 when I try to select from the table... SQL desc ispownre3.individual_names; Name Null?Type - -- INTERNAL_IDENTIFIERNUMBER(12) TITLE_CD NUMBER(3) . . . SQL select * from ispownre3.individual_names; select * from ispownre3.individual_names * ERROR at line 1: ORA-00904: invalid column name -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishna Kakatur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL*Plus errors... how to hide?
See the Code below. It worked fine for me.
Probably you have a raise_exception_error somewhere in the procedure.
SQL create procedure RELEASE_PO_B_H ( in_number varchar2 ) is
2v_number number;
3 begin
4v_number := in_number;
5 exception
6when value_error then
7 dbms_output.put_line('You have entered an invalid number! Exiting
program...');
8 end;
9 /
Procedure created.
$ cat RELEASE_PO_B_H.csh
#!/bin/csh
sqlplus -s DB String EOSQL
set feedback off
set serveroutput on
exec RELEASE_PO_B_H ('yrugis');
EOSQL
$ RELEASE_PO_B_H.csh
You have entered an invalid number! Exiting program...
--
Thanks,
Krishna
~~
NOTICE: This email message is for the sole use of the intended
recipient(s) and may contain confidential and privileged
information. Any unauthorized review, use, disclosure or
distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and destroy
all copies of the original message.
~~
Saira Somani-Mendelin wrote:
List,
I have a shell script that executes a sql*plus script (which executes a
procedure) based on user input.
But what if the user inputs an invalid datatype? The exception section
handles the error and displays a user-friendly message but I still get
an error stack. I want to hide this from the user. How can I do this? I
have set feedback and echo options off. See output below:
PO Reconciliation Batch Release
Enter batch number to be released:
yrugis
You have entered an invalid number! Exiting program...
**[I want to suppress the errors below]**
BEGIN RELEASE_PO_B_H('yrugis'); END;
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at TRAIN65D.RELEASE_PO_B_H, line 16
ORA-06512: at line 1
Thanks,
Saira
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Saira Somani-Mendelin
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Krishna Kakatur
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re:
How about using incremental exports/ imports? HTH, Krishna - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 16, 2003 11:19 AM Use the query= feature of export. This will work if there is a timestamp column where you can determine which are the 'new' records. -Original Message- Sent: Thursday, January 16, 2003 11:49 AM To: Multiple recipients of list ORACLE-L how about, database link -Original Message- Sent: 17 January 2003 01:46 To: Multiple recipients of list ORACLE-L replication -Original Message- Sent: quinta-feira, 16 de Janeiro de 2003 15:09 To: Multiple recipients of list ORACLE-L List, I have to move data on a regular basis between two instances of 8.1.7 on two different UNIX servers. The schema is exactly the same. There are 543 tables to be refreshed. It is only the new data on one of the servers to move to the other. What is the best and easiest way to do this? thanks, David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishna Kakatur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Datafile with no extension using sqlldr
Hi, Oracle 8.1.7 on SUN Solaris 5.8 I want to load a datafile with no extension into Oracle using SQL*Loader. But it looks for a file with a .dat extension. This is the exact error message I got: SQL*Loader-500: Unable to open file (emp.dat)SQL*Loader-553: file not foundSQL*Loader-509: System error: The system cannot find the file specified. I have tried a number of ways but in vain. I understand I can rename the file. But, is there any other way to load a datafile with no extension? TIA,Krishna
OCM
Hi there! Wondering if it is worth becoming Oracle Certified Master, considering the investment for Oracle University courses and Fee for the Practicum. I estimate 2*$1500 for courses and $2000 for practicum totaling to $5000. Any opinions? Also, I have two questions: (1) There are pre-requisite courses for the Advanced DBA courses - For example, if we want to take "Oracle 9i: Security", we should have attended "Oracle 9i DBA Fundamentals I" and "Oracle 9i DBA Fundamentals II". But, when I called Oracle Support, and they have told that these are not mandatory. Did I understand correctly? (2) Do you know of any discounts available for Oracle University courses (for OTN members, or otherwise) Any help is highly appreciated. HTH, Krishna
Can we capture all errors through SQLERRM ?
Hi, How can I capture the second error message (ORA-06512: at line 4) in Exception handler? I want to capture all the messages and insert them into Error Log table. -- SQL declare 2 n number; 3 begin 4 n := 'Not a Number'; 5 end; 6 / declare * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 4 -- SQL declare 2 n number; 3 begin 4 n := 'Not a Number'; 5 exception 6 when others then 7 dbms_output.put_line (SQLCODE ||' '|| SQLERRM); 8 end; 9 / -6502 ORA-06502: PL/SQL: numeric or value error PL/SQL procedure successfully completed. -- TIA, Krishna _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Krishna Kakatur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OCP Model Questions
Hi, I have got this response from many and checked that the site is OK. May be the problem is with your proxy. I too can't access any site from my office which has an underscore within url, not the case when I access from my home. I think this is something to do with Proxy settings. Please check if you can change proxy settings/ try alternate solutions. -- Krishna From: Viraj Luthra [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: OCP Model Questions Date: Mon, 04 Jun 2001 00:10:21 -0800 the url does not work On Sun, 03 Jun 2001 22:10:45 Krishna Kakatur wrote: Hi All, I have compiled some model questions for OCP DBA Test at http://www.oraref.com (mirrored at http://smart_and_handsome.tripod.com/oracle.html). If you have more, I will be happy to add them to the Web Page. -- Krishna _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Krishna Kakatur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OCP Model Questions
Hi All, I have compiled some model questions for OCP DBA Test at http://www.oraref.com (mirrored at http://smart_and_handsome.tripod.com/oracle.html). If you have more, I will be happy to add them to the Web Page. -- Krishna _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Krishna Kakatur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
