insert/read Blob from a table
Hi , Does anyone have a sample code to insert/update/delete a BLOB into a table from a stored procedure. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
output parameter/pl-sql table type
I have a package Spec that has a types defined as TYPE FIRSTNAMETABLE IS TABLE OF V_CUS.FIRSTNAME%TYPEINDEX BY BINARY_INTEGER; TYPE LASTNAMETABLE IS TABLE OF V_CUS.LASTNAME%TYPEINDEX BY BINARY_INTEGER; TYPE EMAILADDRESSTABLE IS TABLE OF V_CUS.EMAILADDRESS%TYPE INDEX BY BINARY_INTEGER; TYPE ISGRANTEDMASTERPRIVILEGESTABLE IS TABLE OF V_CUS.ISGRANTEDMASTERPRIVILEGES%TYPE INDEX BY BINARY_INTEGER; TYPE USERNAMETABLE IS TABLE OF CUS_EN.USERNAME%TYPE INDEX BY BINARY_INTEGER; TYPE PASSWORDTABLE IS TABLE OF CUS_EN.PASSWORD%TYPE INDEX BY BINARY_INTEGER; TYPE MSISDNTABLEIS TABLE OF V_CUS.MSISDN%TYPE INDEX BY BINARY_INTEGER; TYPE OSUCCESSTABLE IS TABLE OF CHAR(1) INDEX BY BINARY_INTEGER; TYPE OERRORCODETABLEIS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE OERRORMSGTABLE IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER; TYPE ADDRESS1TABLE IS TABLE OF ADD.ADDRESS1%TYPE INDEX BY BINARY_INTEGER; TYPE ADDRESS2TABLE IS TABLE OF ADD.ADDRESS2%TYPE INDEX BY BINARY_INTEGER; TYPE BILLCITYTABLE IS TABLE OF ADD.CITY%TYPE INDEX BY BINARY_INTEGER; TYPE BILLSTATETABLE IS TABLE OF ADD.STATE%TYPE INDEX BY BINARY_INTEGER; TYPE BILLZIPTABLE IS TABLE OF ADD.ZIPCODE%TYPE INDEX BY BINARY_INTEGER; TYPE BILLFIRSTNAMETABLEIS TABLE OF CUS.FIRSTNAME%TYPE INDEX BY BINARY_INTEGER; TYPE BILLLASTNAMETABLE IS TABLE OF CUS.LASTNAME%TYPE INDEX BY BINARY_INTEGER; TYPE SERVICELEVELTABLE IS TABLE OF CUSCODE.SERVICECODEID%TYPE INDEX BY BINARY_INTEGER; Procedure GETUSER ( pMSISDN IN VSTR_Customer.MSISDN%TYPE, oFirstName OUT FIRSTNAMETABLE, oLastName OUT LASTNAMETABLE, oBillingFirstName OUT BILLFIRSTNAMETABLE, oBillingLastNameOUT BILLLASTNAMETABLE, oEmailAddress OUT EMAILADDRESSTABLE, oMSISDN OUT MSISDNTABLE, oIsGrantedMasterPrivileges OUT ISGRANTEDMASTERPRIVILEGESTABLE, oServiceLevel OUT SERVICELEVELTABLE, oUserName OUT USERNAMETABLE, oPassword OUT PASSWORDTABLE, oAddress1 OUT ADDRESS1TABLE, oAddress2 OUT ADDRESS2TABLE, oCity OUT BILLCITYTABLE, oState OUT BILLSTATETABLE, oZipOUT BILLZIPTABLE, oErrorCode OUT OERRORCODETABLE, oErrorMsg OUT oERRORMSGTABLE ); I want to execute this above procedure by passing the IN paramter and to display the OUT paraeter in SQLPLUS. I tried using..trying to print one of the paramter.I believe i am getting this error paramter of TABLE type.Looked up on metalink,didn't find aything useful declare myFirstname pkg_streamline.Firstnametable; myLASTNAME pkg_streamline.LASTNAMETABLE; myISGRANTEDMASTERPRIVILEGES pkg_streamline.ISGRANTEDMASTERPRIVILEGESTABLE; myUSERNAME pkg_streamline.USERNAMETABLE; myPASSWORD pkg_streamline.PASSWORDTABLE; myMSISDN pkg_streamline.MSISDNTABLE; myOERRORCODE pkg_streamline.OERRORCODETABLE; myOERRORMSG pkg_streamline.OERRORMSGTABLE; myADDRESS1 pkg_streamline.ADDRESS1TABLE; myADDRESS2 pkg_streamline.ADDRESS2TABLE; myBILLCITY pkg_streamline.BILLCITYTABLE; myBILLSTATE pkg_streamline.BILLSTATETABLE; myBILLZIP pkg_streamline.BILLZIPTABLE; myBILLFIRSTNAME pkg_streamline.BILLFIRSTNAMETABLE; myBILLLASTNAME pkg_streamline.BILLLASTNAMETABLE; -- myEMAILADDRESS pkg_streamline.EMAILADDRESSTABLE; myEM pkg_streamline.EMAILADDRESSTABLE; mySERVICELEVEL pkg_streamline.SERVICELEVELTABLE; begin pkg_streamline.GETUSERFOR(77,myFirstName,myLastName,myBillFirstName, myBillLastName,myEM,myMSISDN,myIsGrantedMasterPrivileges,myServiceLevel, myUserName,myPassword,myAddress1,myAddress2,mybillCity,mybillState,mybillZip ,myoErrorCode,myoErrorMsg); dbms_output.put_line(myFirstName); end; / I get the following error SQL / dbms_output.put_line(myFirstName); * ERROR at line 24: ORA-06550: line 24, column 1: PLS-00306: wrong number or types of arguments in call to 'PUT_LINE' ORA-06550: line 24, column 1: PL/SQL: Statement ignored HOW DO I GET THIS WORKING.I TRIED DECLARING VARIABLE OF TABLETYPE AND TO PRINT IN SQLPLUS BUT I STILL THE SAME ERROR PLS-00306. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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
RE: logging oracle error code and message into a table within pro
Title: RE: logging oracle error code and message into a table within procedu When i call the log_errors in the exception block what should i give the aruguments for the procedure.? thanks -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Thursday, July 31, 2003 12:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: logging oracle error code and message into a table within pro sorry should be ... insert into error_log (error_ts, ora_user, error_text, call_stack) values (sysdate, user, szerrors,szcalls); Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Jamadagni, Rajendra Sent: Thursday, July 31, 2003 3:45 PMTo: Multiple recipients of list ORACLE-LSubject: RE: logging oracle error code and message into a table within pro create or replace log_errors (in_errors varchar2, in_calls varchar2) is szerrors varchar2(1000) := dbms_utility.format_error_stack; szCalls varchar2(1000) := dbms_utility.format_call_stack; pragma autonomous_transaction; begin szerrors := insert into error_log (error_ts, ora_user, error_text) values (sysdate, user, szerrors); commit; end; / begin ... exception when others then log_errors; -- for benefit of the program ... raise; end; / something like this should help ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 31, 2003 3:35 PM To: Multiple recipients of list ORACLE-L Subject: logging oracle error code and message into a table within procedu Hi All, I want to log the oracle error code and messages that will be generated within a procedure/trigger into a table. I know i can write this in the exception block.Does anyone has any sample code to help me out. thanks ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
AQ-JMS- SYS.AQ$_JMS_TEXT_MESSAGE
Hi All, We are using AQ with ORacle JMS implementation that uses the object SYS.AQ$_JMS_TEXT_MESSAGE. We have a requirement to grant insert,delete,update on the AQ queue table that has the SYS.AQ$_JMS_TEXT_MESSAGE object.This error throw up.AQ tables are created under database user A using the SYS objects SYS.AQ$_JMS_TEXT_MESSAGE.We are granting insert,update,delete priviliges to user B to use the AQ tables that are created under User A. ERROR at line 1: ORA-01720: grant option does not exist for 'SYS.AQ$_JMS_TEXT_MESSAGE' . Is there a solution for this. thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
logging oracle error code and message into a table within procedu
Hi All, I want to log the oracle error code and messages that will be generated within a procedure/trigger into a table. I know i can write this in the exception block.Does anyone has any sample code to help me out. thanks ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
Oracle AQ monitoring questions
Hi, I am looking at using Oracle AQ to post messages.I know they are just like any other database table but with some limitations.i have some concerns from what I have heard from users who have previously used AQ 1.Are there any serious concerns in using AQ.My purpose of using AQ is just to act like a messager of message and to have JMS features implemented with the Application.Are there any known issues? or any advantages.? 2.Are there any monitoring scripts/features within oracle database to monitor the AQ for their growth/size/length or corruption.? 3.Does AQ get corrupted more often than other oracle tables?How can we find out if AQ is getting corrupted.This question may be related to the previous one . Pls advice. Thanks Ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
Oracle JMS--Lost all my Emaisl...Pls resend if any reply was post
Hi All, I need some help in setting up ORACLE JMS to post and read messages from queue.I am a newbie in this and I ave gone through the Oracle application developers guide for Advanced queuing document but it is too exhaustive. Does anyone have any examples,web links to get started with Oracle JMS. Appreciate any help. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=12
I am seeing WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=12 in the alert log file? there are no blocking locks and at this time the weblogic connection pool cannot get connections to the database but the client connections from sql*client gets connected (sqlplus or toad). What could be the problem?any thoughts? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
Oracle JMS
Hi All, I need some help in setting up ORACLE JMS to post and read messages from queue.I am a newbie in this and I ave gone through the Oracle application developers guide for Advanced queuing document but it is too exhaustive. Does anyone have any examples,web links to get started with Oracle JMS. Appreciate any help. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
Passing array as input to procedure from sqlplus-HELP
Hi All, I want to test a procedure that accepts an array as input.I want to test this script from SQL*PLUS.How can I pass array value from sql*plus Procedure testproc(iarrPID IN PID_VArray). I want to test it this way SQLExec testproc(?) Tried this was SQLEXEC testproc(33); ERROR at line 1: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'testproc' ORA-06550: line 1, column 7: PL/SQL: Statement ignored Pls help Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
Passing array as input to procedure from sqlplus-HELP
Hi All, I want to test a procedure that accepts an array as input.I want to test this script from SQL*PLUS.How can I pass array value from sql*plus Procedure testproc(iarrPID IN PID_VArray). I want to test it this way SQLExec testproc(?) Tried this was SQLEXEC testproc(33); ERROR at line 1: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'testproc' ORA-06550: line 1, column 7: PL/SQL: Statement ignored Pls help Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
Oracle JMS
Hi All, I am an Oracle DBA/Developer.I want to start setting up and using Oracle JMS.How do I get started. I have a database Oracle EE 8.1.7.4.Do I have to install anything,how to configure for oracle jms? Any available documents that gives a tutorial on this .I have checked metalink and didn't find anything good. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
SQL Query Help
I have a table with records like this CID S TO_CHAR(DATECREATED, MESSAGE PID - - -- 2 N 01-feb-1974 19:45:45 service change1* 3 N 01-feb-1974 19:45:45 service change1* 3 N 01-feb-1974 21:45:45 service change1 1 N 01-jan-1974 12:34:45 msisdn change 1* 1 N 01-jan-1974 19:45:45 service change1 2 N 01-jan-1974 19:45:45 service change1 1 N 01-nov-1974 17:45:45 service change1 1 N 01-nov-1974 19:45:45 service change1 I want to display only the records with the *(not a value stored in the database.just used as a marker here). i.e the records which meet the following. 1.earliest date 2.if there are multiple occurances of records with the same cid and pid combination i want only the record for the combination of cid-pid and with the most earliest record(oldest time stamp). i want to achieve this CID S TO_CHAR(DATECREATED, MESSAGE PID - - -- 2 N 01-feb-1974 19:45:45 service change1* 3 N 01-feb-1974 19:45:45 service change1* 1 N 01-jan-1974 12:34:45 msisdn change 1* I need some help in getting the query that can get the results like that. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
Database Auditing
Hi All, How do we turn on statement level auditing (DB and OS Level) to audit the DML/DDL's on the database.Is this possible with out triggers? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
SQL Query Help
i have a query that returns 2 rows with one column being id and the other being date-time stamp. i want to select the row with the latest timestamp among those two records.they have difference id values SELECT ID,LastModDate FROM Tab ID LastModDate -- --- 2 1/20/2003 2:56:18 AM 1 4/23/2003 10:26:42 PM I want to modify the above query to return the row with id=1 which has the latest timestamp I tried this SELECT ID,MAX(LastModDate) FROM Tab getting this error OERR: ORA 937 not a single-group group function how do i get this work. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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 Query Help
I had tried this as per Oracle Metalink Note.When I add the group by ID it will display both the records like 2 1/20/2003 2:56:18 AM 1 4/23/2003 10:26:42 PM but I want to see the only the record with the latest time stamp like 1 4/23/2003 10:26:42 PM -Original Message- Sent: Wednesday, May 28, 2003 6:25 PM To: Multiple recipients of list ORACLE-L Add group by ID; as in: SELECT ID,MAX(LastModDate) FROM Tab group by ID; -Original Message- From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 8:15 PM To: Multiple recipients of list ORACLE-L Subject: SQL Query Help i have a query that returns 2 rows with one column being id and the other being date-time stamp. i want to select the row with the latest timestamp among those two records.they have difference id values SELECT ID,LastModDate FROM Tab IDLastModDate ----- 2 1/20/2003 2:56:18 AM 1 4/23/2003 10:26:42 PM I want to modify the above query to return the row with id=1 which has the latest timestamp I tried this SELECT ID,MAX(LastModDate) FROM Tab getting this error OERR: ORA 937 not a single-group group function how do i get this work. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Murray, Margaret 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: Basavaraja, Ravindra 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 Query Help
05/28/2003 07:14 PM Please respond to ORACLE-L i have a query that returns 2 rows with one column being id and the other being date-time stamp. i want to select the row with the latest timestamp among those two records.they have difference id values SELECT ID,LastModDate FROM Tab ID LastModDate -- --- 2 1/20/2003 2:56:18 AM 1 4/23/2003 10:26:42 PM I want to modify the above query to return the row with id=1 which has the latest timestamp I tried this SELECT ID,MAX(LastModDate) FROM Tab getting this error OERR: ORA 937 not a single-group group function how do i get this work. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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: 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: Basavaraja, Ravindra 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).
how to calculate table size
Hi, Anyone having any formula to calculate table size?Basically to estimate the growth of table over a peroid of time. I have the row_size,db_block_size.How do i get the table size. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
Database sizing and capacity analysis
Hi, I have a requirement to do database sizing for one of our new databases.Does anyone has any templete for database sizing which lists all the aspects that we need to check on .What all should be considered for database sizing and capacity analysis to predict future growth. Also what are the ways in which we can compute database sizing knowing the number of users that we can expect by this year end. Does anyone has any formula to calculate row size(I use TOAD to currently get the row size). Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
recovery
Hi, I have to recover my database back to 23-MAR-03.There was some changes to the data yesterday and cannot be reverted. We want to recover the data as of 23-MAR-03.Database is running on archive log mode and I have the hot backup datafiles of Feb 3 and all the archive logs since then till today.I know how to recover but to make sure that I don't miss anything i am posting this here. 1.Restore the data tablespace datafiles from the Feb 3rd backup. 2.Startup the database in MOUNT mode. 3.recover database until time 2003-03-23-12:00:00 4.All the archive logs will get applied 5.Open the database with reset logs.I should go back by 3 days wrt to data. Am I missing anything. My questions: Should I restore the control files(of FEb 3) before starting the recovery.? Should i restore redo log files?.I donot want the recent changes to be applied to my database after restoring till 23-MAR-03. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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: partitioning
Title: RE: partitioning Hi Jacques, How do I exactly implement this.In the before insert trigger what after I generate the value for the new partition column.How does the records go into that partition. Have you tried this.How is the performance for an insert into a table of 10 records everyday.Executing the trigger for every insert for high volume of data may be costly on the performance..? Can we achieve this or anything closer using HASH partitioning as suggested by others. thanks -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]Sent: Wednesday, March 19, 2003 11:52 AMTo: '[EMAIL PROTECTED]'Cc: '[EMAIL PROTECTED]'Subject: RE: partitioning You could accomplish this with a before insert trigger and a partitioning column that contains the value 0 through 7. e.g. create trigger before insert for each row begin select mod (sequence.nextval, 8) into :new.partition_column from dual ; end ; / Something similar would be achieve by hash partitioning, which is easier to implement. -Original Message- From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]] I am wondering if there is any way to achieve horizontal partitioning in Oracle. Assuming that I have about 8 partitions for a table.When there is INSERT onto this table I want one record to be inserted into each partition i.e 1st record goes into partition 1 2nd record goes into partition 2 3rd record goes into partition 3 . . 8th record goes into partition 8 9th record goes into partition 1. I guess this feature is available in Informix handled by The informix engine.I am not sure if Oracle has something similiar to this OR is it possible to design a logic and embede it ,but what would be the performance effect? Any thoughts or similiar ideas
partitioning
Hi, I am wondering if there is any way to achieve horizontal partitioning in Oracle. Assuming that I have about 8 partitions for a table.When there is INSERT onto this table I want one record to be inserted into each partition i.e 1st record goes into partition 1 2nd record goes into partition 2 3rd record goes into partition 3 . . 8th record goes into partition 8 9th record goes into partition 1. I guess this feature is available in Informix handled by The informix engine.I am not sure if Oracle has something similiar to this OR is it possible to design a logic and embede it ,but what would be the performance effect? Any thoughts or similiar ideas Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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: partitioning
Title: RE: partitioning thanks -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]Sent: Wednesday, March 19, 2003 11:52 AMTo: '[EMAIL PROTECTED]'Cc: '[EMAIL PROTECTED]'Subject: RE: partitioning You could accomplish this with a before insert trigger and a partitioning column that contains the value 0 through 7. e.g. create trigger before insert for each row begin select mod (sequence.nextval, 8) into :new.partition_column from dual ; end ; / Something similar would be achieve by hash partitioning, which is easier to implement. -Original Message- From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]] I am wondering if there is any way to achieve horizontal partitioning in Oracle. Assuming that I have about 8 partitions for a table.When there is INSERT onto this table I want one record to be inserted into each partition i.e 1st record goes into partition 1 2nd record goes into partition 2 3rd record goes into partition 3 . . 8th record goes into partition 8 9th record goes into partition 1. I guess this feature is available in Informix handled by The informix engine.I am not sure if Oracle has something similiar to this OR is it possible to design a logic and embede it ,but what would be the performance effect? Any thoughts or similiar ideas
Demo of PL/SQL returning a REF Cursor to an OCI program
Hi, Anyone with examples of using OCI and ref cursors.Can I get some examples for Demo's of Pl/SQl returning a REf cursor to an OCI program. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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 query: to_date() :ORA-01830: date format picture ends bef
Correction
I am using
select * from cust where
to_char(DATECREATED,'DD-MON-YY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
-Original Message-
From: Basavaraja, Ravindra
Sent: Tuesday, March 11, 2003 12:19 PM
To: 'Multiple recipients of list ORACLE-L'
Subject: sql query: to_date() :ORA-01830: date format picture ends before
converting entire input string
Hi...
I am getting the following error with a query like this
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
What is the problem?
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
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).
sql query: to_date() :ORA-01830: date format picture ends before
Hi...
I am getting the following error with a query like this
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
What is the problem?
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
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).
ref cursor
Hi, I have a procedure that has an ref cursor as an OUT parameter.If for some reason If I have to return NULL values in the ref cursor how do i do that. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
dbms_job
Hi, I have a procedure that submits a dbms_job for immediate processing for onetime.I want to know if the procedure that submits the job will have to wait till the job gets executed to execute the next piece of code after the dbms_job.submit() in the procedure or will the procedure submit the dbms_job and continue executing the next line of code in the procedure. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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: slow insert
-Original Message- Sent: Saturday, February 22, 2003 7:54 AM To: Multiple recipients of list ORACLE-L You can read more abt it in PL/SQL User's Guide and Reference - Chapter 4 - Collections and Records ! anyway, below is the simple code to copy data from emp table. note that, after u fetch from the cursor, u have to check for the notfound condition at the end. say, the emp table has 15 rows and u r fetching 2 rows at a time. during the 1st 7 fetches u will be ok. but during the 8th fetch, the fetch will bring only 1 row and the condition notfound will be true. thus u will never process the last set of rows. hence, check for the condition after u have inserted/updated/deleted the rows. finally, try playing with the limit clause in the fetch and see what number is best for u. considering my hardware etc, i got better performance with 5000 fetches at a time. also, till 8.1.7. u could not have any stmts in the FORALL loop. only 1, i.e. insert/update/delete. but with 9i u can have a pl/sql block any dynamic stmts too. moreover, with 9i u can also trap for individual exception whereas with 8.1.7. if there was an error, entire bulk process would rollback. hope this helps let me know if u need any more help. -- code follows declare cursor c_emp is select empno, ename, hiredate from emp ; type empno_arr_type is table of number(4); v_empno_arr empno_arr_type; type name_arr_type is table of varchar2(10); v_ename_arr name_arr_type; type date_arr_type is table of date; v_hiredate_arr date_arr_type; v_arr_idx binary_integer := 0; v_arr_cnt binary_integer := 0; begin open c_emp; loop fetch c_emp bulk collect into v_empno_arr, v_ename_arr, v_hiredate_arr limit 2; -- DO NOT CHECK for notfound here v_arr_cnt := v_empno_arr.count(); dbms_output.put_line(v_arr_cnt); forall v_arr_idx in 1 .. v_arr_cnt insert into emp_copy(empno, ename, hiredate) values(v_empno_arr(v_arr_idx),v_ename_arr(v_arr_idx),v_hiredate_arr(v_arr_id x)); commit; -- note : the condition has to be checked here ONLY exit when c_emp%notfound; end loop; close c_emp; end; / -Original Message- Ravindra Sent: Friday, February 21, 2003 7:09 PM To: Multiple recipients of list ORACLE-L Yes I am usng 8.1.7 EE.How do I use bulk inserts..Any docs or links on metalink plss? -Original Message- Sent: Friday, February 21, 2003 3:34 PM To: Multiple recipients of list ORACLE-L if u r having oracle 8.1.7 EE or higher, try using bulk inserts ! that surely will speed up ! and of course, u surely might have thought of APPEND hint dropping/disabling indexes etc. -Original Message- Ravindra Sent: Friday, February 21, 2003 3:08 PM To: Multiple recipients of list ORACLE-L hi, I have an insert statement that will insert about 40 records into a table having 43million records.The values for the insert statement are from a select statement that has a join.This query take about 5-10minutes.What are the ways in which we can speed up this process.the statement looks like insert into ... select ... from a,b where a.col1=b.col1 ---index columns and a.col2=x --non index and b.col2=x --non index Thanks -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Basavaraja, Ravindra 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: [EMAIL PROTECTED] 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
RE: slow insert
I tried this way and infact bulk collect took about 1 minutes more than my current case? Any thoughts thanks -Original Message- Sent: Saturday, February 22, 2003 7:54 AM To: Multiple recipients of list ORACLE-L You can read more abt it in PL/SQL User's Guide and Reference - Chapter 4 - Collections and Records ! anyway, below is the simple code to copy data from emp table. note that, after u fetch from the cursor, u have to check for the notfound condition at the end. say, the emp table has 15 rows and u r fetching 2 rows at a time. during the 1st 7 fetches u will be ok. but during the 8th fetch, the fetch will bring only 1 row and the condition notfound will be true. thus u will never process the last set of rows. hence, check for the condition after u have inserted/updated/deleted the rows. finally, try playing with the limit clause in the fetch and see what number is best for u. considering my hardware etc, i got better performance with 5000 fetches at a time. also, till 8.1.7. u could not have any stmts in the FORALL loop. only 1, i.e. insert/update/delete. but with 9i u can have a pl/sql block any dynamic stmts too. moreover, with 9i u can also trap for individual exception whereas with 8.1.7. if there was an error, entire bulk process would rollback. hope this helps let me know if u need any more help. -- code follows declare cursor c_emp is select empno, ename, hiredate from emp ; type empno_arr_type is table of number(4); v_empno_arr empno_arr_type; type name_arr_type is table of varchar2(10); v_ename_arr name_arr_type; type date_arr_type is table of date; v_hiredate_arr date_arr_type; v_arr_idx binary_integer := 0; v_arr_cnt binary_integer := 0; begin open c_emp; loop fetch c_emp bulk collect into v_empno_arr, v_ename_arr, v_hiredate_arr limit 2; -- DO NOT CHECK for notfound here v_arr_cnt := v_empno_arr.count(); dbms_output.put_line(v_arr_cnt); forall v_arr_idx in 1 .. v_arr_cnt insert into emp_copy(empno, ename, hiredate) values(v_empno_arr(v_arr_idx),v_ename_arr(v_arr_idx),v_hiredate_arr(v_arr_id x)); commit; -- note : the condition has to be checked here ONLY exit when c_emp%notfound; end loop; close c_emp; end; / -Original Message- Ravindra Sent: Friday, February 21, 2003 7:09 PM To: Multiple recipients of list ORACLE-L Yes I am usng 8.1.7 EE.How do I use bulk inserts..Any docs or links on metalink plss? -Original Message- Sent: Friday, February 21, 2003 3:34 PM To: Multiple recipients of list ORACLE-L if u r having oracle 8.1.7 EE or higher, try using bulk inserts ! that surely will speed up ! and of course, u surely might have thought of APPEND hint dropping/disabling indexes etc. -Original Message- Ravindra Sent: Friday, February 21, 2003 3:08 PM To: Multiple recipients of list ORACLE-L hi, I have an insert statement that will insert about 40 records into a table having 43million records.The values for the insert statement are from a select statement that has a join.This query take about 5-10minutes.What are the ways in which we can speed up this process.the statement looks like insert into ... select ... from a,b where a.col1=b.col1 ---index columns and a.col2=x --non index and b.col2=x --non index Thanks -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Basavaraja, Ravindra 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Update and Trigger
Hi, I have an update statement that affects 1 record in a table.There is a trigger associated with this update that updates few other tables and not the parent table that will triiger the update trigger. With the trigger enabled the update of 1 record on the parent table takes about 40 seconds and without the trigger the update takes about 1 second. When the updates are operating on different tables(locking not a issue) why do i see this delay in execution of the update statement. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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: slow insert
Thanks for the inputs. Here are some important results. 1)The insert into Original table with 43million records takes about 10minutes and the explain plan as attached in the text file original table insert into original table... select ... from a,b where a.col1=b.col1 ---index columns and a.col2=x --non index and b.col2=x --non index Note:Original table is having a composite primary key of 3 columns 2)I created a temp table similiar to the original table as create table temp1 as select * from originaltable where 1=2; this didn't create any index on the table and the insert got executed in 4 seconds. I am attaching the explain plan in the attachment new table 3)I created a composite primary key of the 3 cols on this new table and the insert took 13 seconds. The execution can be found in the same file new table at the end. pls advice thanks -Original Message- Sent: Monday, February 24, 2003 7:04 PM To: Multiple recipients of list ORACLE-L Ignoring bulk insert, and assuming you are performing a single insert statement have you looked at the following (as already suggested): 1) What, and how many, indexes exist on the destination table. Each index requires updating as records are inserted. If there are indexes not required then remove them. Alternatively you might even consider disabling the indexes before inserting the data then rebuilding them... Although not likely if you are performing a single insert with no other work. 2) Is col2 (in your example query) indexed in either table? What proportion of the table meets this selection criteria? IE: If col2 was gender and your data was evenly spread then selecting male would return ~50% of rows. If col2 was age and your data was evenly spread between 1 and 100 then selecting 20 would return ~1% of rows. If either of the selection returns a relatively low number of rows then look at indexing these columns. 3) Have you looked at an explain plan for this statement? If not, start sql*plus, type set autotrace on, then execute the query. This will show if indexes are being used, etc. We may be able to help further if we know this information and know about your data. 4) Have you tried running the select statement on it's own, or inserting into a table which contains no indexes (and maybe has nologging set). This may give an idea of whether the time is consumed retrieving the data or inserting it into the destination. This will give you a good idea of where to focus your tuning efforts. 5) Have you tried a parallel hint? This may help depending on your physical configuration. Regards, Mark. Basavaraja, Ravindra [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] obile.com cc: Sent by: Subject: RE: slow insert [EMAIL PROTECTED] 25/02/2003 13:14 Please respond to ORACLE-L I tried this way and infact bulk collect took about 1 minutes more than my current case? Any thoughts thanks -Original Message- Sent: Saturday, February 22, 2003 7:54 AM To: Multiple recipients of list ORACLE-L You can read more abt it in PL/SQL User's Guide and Reference - Chapter 4 - Collections and Records ! anyway, below is the simple code to copy data from emp table. note that, after u fetch from the cursor, u have to check for the notfound condition at the end. say, the emp table has 15 rows and u r fetching 2 rows at a time. during the 1st 7 fetches u will be ok. but during the 8th fetch, the fetch will bring only 1 row and the condition notfound will be true. thus u will never
slow insert
hi, I have an insert statement that will insert about 40 records into a table having 43million records.The values for the insert statement are from a select statement that has a join.This query take about 5-10minutes.What are the ways in which we can speed up this process.the statement looks like insert into ... select ... from a,b where a.col1=b.col1 ---index columns and a.col2=x --non index and b.col2=x --non index Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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: slow insert
Yes I am usng 8.1.7 EE.How do I use bulk inserts..Any docs or links on metalink plss? -Original Message- Sent: Friday, February 21, 2003 3:34 PM To: Multiple recipients of list ORACLE-L if u r having oracle 8.1.7 EE or higher, try using bulk inserts ! that surely will speed up ! and of course, u surely might have thought of APPEND hint dropping/disabling indexes etc. -Original Message- Ravindra Sent: Friday, February 21, 2003 3:08 PM To: Multiple recipients of list ORACLE-L hi, I have an insert statement that will insert about 40 records into a table having 43million records.The values for the insert statement are from a select statement that has a join.This query take about 5-10minutes.What are the ways in which we can speed up this process.the statement looks like insert into ... select ... from a,b where a.col1=b.col1 ---index columns and a.col2=x --non index and b.col2=x --non index Thanks -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Basavaraja, Ravindra 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).
slow update and db trigger
I have an update statement executed by a procedure.This update statements fires a trigger which has a set of update statements and also executes 2 procedures. If i execute the parent update statement which will affect a single row from a table which has significantly less number of rows.This statement sometimes is executed very fast and sometimes it takes upto 50 seconds.Does this slowness got to do anything with the triggers that get fired automatically .For me this update is a single piece of transaction.Why do i see the delay in this update statements. Thanks Ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
update slow and EJB timesout
Hi, I have and update statement (called my a procedure which will inturn be invoked by an EJB) that will mark a record for deletion(set a column as Y). Records marked for deletion will not be picked up for display on the JSP page.This will affect only 1 record in the table and takes about 30 seconds to execute as a procedure or just as a single update statement.When called as a procedure from the EJB the EJB will timeout and the jsp page goes blank screen before it gets the response from the procedure(the return paratemter is a code and message).But even when it goes blank the procedure is executed at the database level. Contradictily when I isssue an update statement to revert the record marked for deletion i.e change the value of the column to N it is executed very fast. We have some cases when the transaction to add a record is done on the jsp page which calls the EJB to execute a procedure that will affect quite a few number of records(not too big in number) takes long long time(about 2-3 minutes before EJB timesout).But the procedure gets executed at the database level even if the EJB that invoked the procedure timesout. All the above cases were working earlier.What could be the problem with EJB timing out and the duration the procedure is taking to be executed. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
test - pls Ignore
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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).
test-pls ignore
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Basavaraja, Ravindra 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).
Database Desgin book/migration from informix to oracle
Does anyone know any good book on oracle database design and if it is available to be purchased on internet used or new book is ok with me. Are there any resource available explaining the process of migration from informix database to oracle database. resources like any url or books or anything.pls let me know thanx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Basavaraja, Ravindra 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).
