How to find creation date for Rollback segment
Is there any way to find out created time stamp for a rollback segment apart from looking into alert.log. TIA Kranti This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. -- 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).
RE: how to find elapsed time for a query in oracle 8.1.7 Database
Stephane, LAST_CALL_ET lets u know that user is idle for so long (correct me id i am wrong) and I want to know which queries are taking long time. Unfortunately I cannot use Oracle trace for it. Regards, Kranti Pushkarna "Stephane Faroult" <[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >cc: Sent by: Subject: RE: how to find elapsed time for a query in oracle 8.1.7 Database [EMAIL PROTECTED] 03/07/2003 04:28 PM Please respond to ORACLE-L >Hi Gurus, > Is there any way to find out which queries >are taking say more than >20 min in oracle 8.1.7 Database. >Regards, >Kranti Pushkarna > Switch your mobile phone on :-). Unless you put your database in trace mode, it is fairly difficult to get this information. Something which comes to my mind but is not very good is to have a small program which queries V$SESSION and gets max(last_call_et) for all the ACTIVE sessions, and checks again (20 * 60 - the value precedently found) seconds later. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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). This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. -- 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).
how to find elapsed time for a query in oracle 8.1.7 Database
Hi Gurus, Is there any way to find out which queries are taking say more than 20 min in oracle 8.1.7 Database. Regards, Kranti Pushkarna This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. -- 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).
Re: Urgent - Help ....
u need to increase the length of the field USERNO and make corresponding changes in the application where you are accessing this field. Syntax will be something like this Alter table modify ; HTH Kranti Harsh Agrawal <[EMAIL PROTECTED]> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: Urgent - Help 02/20/2003 02:28 PM Please respond to ORACLE-L Hi Gurus, I need a urgent help. I have a table e.g. USER_DETAILS with a column NUMBER(9), populated by a sequence. [ The column name is USERNO ] This table is quite heavy and the current situation is USERNO have reached it's maximum value i.e. 9. No more records can be inserted Problem !!! Can any one suggest a solution with NO change is the software and minor change in the software. TIA Regards, Harsh Agrawal The information contained in this message is proprietary of Amdocs, protected from disclosure, and may be privileged. The information is intended to be conveyed only to the designated recipient(s) of the message. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, use, distribution or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. -- 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).
Migration from 8.1.7 to 9.0.2
Hi all, Can anyone give me estimate time required for migrating a DB on 8.1.7 to 9.0.2. Size of the DB is 10GB. RAM is 1 GB. It is a dual CPU system on windows NT. Thanx Kranti Pushkarna This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. -- 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).
Re: passing parameters to a stored procedure from command prompt
Thanx alo Yechiel Adart!! It worked. Kranti "Yechiel Adar" <[EMAIL PROTECTED]>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: Re: passing parameters to a stored procedure from command prompt 12/11/2002 03:39 PM Please respond to ORACLE-L Use &1 &2 for the parameters passed from the command line. If you had (test.sql) a script like: select &1 from dual; exit; and you write on the command line: sqlplus Scott/tiger@db @test.sql 'kuku' It will become: select 'kuku' from dual; Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Wednesday, December 11, 2002 10:39 AM > > Hi all, > I am running a batch file through which I am opening SQLPLUS session > and calling a stored procedure. I want to pass parameters to this procedure > from the command prompt. Is it possible? Can anyone on this list will help > me. > Environment is Oracle 8.1.7 and OS Win2k. > > Thanx, > Kranti Pushkarna > > > > > This message is for the designated recipient only and may contain > privileged, proprietary, or otherwise private information. If you have > received it in error, please notify the sender immediately and delete the > original. Any other use of the email by you is prohibited. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > 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.com -- Author: Yechiel Adar 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). This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
passing parameters to a stored procedure from command prompt
Hi all, I am running a batch file through which I am opening SQLPLUS session and calling a stored procedure. I want to pass parameters to this procedure from the command prompt. Is it possible? Can anyone on this list will help me. Environment is Oracle 8.1.7 and OS Win2k. Thanx, Kranti Pushkarna This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
RE: Re[2]: change a database connection in a stored procedure?
Hi Alexandre, I am writing a procedure to create a user, grant privilleges to the user nad then connect as that user and create schema in that user. I want this whole process to be automated. That is why I want to change a database connection. Anyway I have done it using batch file. Thanks for your response Rgds Kranti -Original Message- Sent: Tuesday, May 07, 2002 3:49 PM To: Multiple recipients of list ORACLE-L Hi Kranti, First of all, think about what you wanna do. This looks like logical mistake if you need session change in PL/SQL. When you change the session, what happens with the first one? Session is establised by client requesting the server, so you can only do it from client. If you just want to perform some action as other user, then you may run procedure from that user scheme declared with AUTHID DEFINER (which is by default). Another solution may be creating database link to itself as another user but that doesn't make much sense. Note that you will make new session with database link from server (as client) to itself as server. Alexandre Gorbatchev Oracle DBA/Developer, OCP [EMAIL PROTECTED] +49 (0) 540 / 550 5177 Avermann Maschinenfabrik GmbH & Co. KG http://www.avermann.de - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, May 07, 2002 10:08 AM > Hello kranti, > > I made a mistake. You can't change your database connection at all > from stored procedure > (there is no such SQL command "connect" it's a sqlplus directive). > When I gave the answer I thought about database link. > You can drop and create it using dynamic SQL. > > Tuesday, May 07, 2002, 2:08:27 PM, you wrote: > > kp> Hi Sergey, > kp> I am using dynamic SQL but it is returing error for connect statement. Can u > kp> give me some example code. > > kp> Rgds > kp> Kranti > kp> -Original Message- > kp> Sent: Tuesday, May 07, 2002 7:38 AM > kp> To: Multiple recipients of list ORACLE-L > > > kp> Hello kranti, > > kp> Use dynamic SQL. > > kp> Monday, May 06, 2002, 8:23:29 PM, you wrote: > > kp>> Hi List, > kp>> Can someone tell me is it possible to change a database > kp> connection > kp>> in a stored procedure? if so how? > kp>> TIA > kp>> Kranti > > > > > > > -- > Best regards, > Sergeymailto:[EMAIL PROTECTED] > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Sergey V Dolgov > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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: change a database connection in a stored procedure?
Hi Sergey, I am using dynamic SQL but it is returing error for connect statement. Can u give me some example code. Rgds Kranti -Original Message- Sent: Tuesday, May 07, 2002 7:38 AM To: Multiple recipients of list ORACLE-L Hello kranti, Use dynamic SQL. Monday, May 06, 2002, 8:23:29 PM, you wrote: kp> Hi List, kp> Can someone tell me is it possible to change a database connection kp> in a stored procedure? if so how? kp> TIA kp> Kranti -- Best regards, Sergeymailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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).
change a database connection in a stored procedure?
Hi List, Can someone tell me is it possible to change a database connection in a stored procedure? if so how? TIA Kranti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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: IN() question
Try this SELECT COUNT(*) from leisure_plan_master_temp where membership_class = 'D' AND (PAY_METHOD IS NULL or pay_METHOD NOT IN ('C','P')) ; -Original Message- Sent: Monday, May 06, 2002 12:18 PM To: Multiple recipients of list ORACLE-L Hello Lisa, Null values are excluded from search list for "in" statement. Saturday, May 04, 2002, 6:43:38 AM, you wrote: KL> Slap me if this is a dumb question. KL> Here's my pay methods SQL>> SELECT ASCII(PAY_METHOD), PAY_METHOD, COUNT(*) KL> 2 FROM LEISURE_PLAN_MASTER_TEMP KL> 3 WHERE MEMBERSHIP_CLASS = 'D' KL> 4 GROUP BY PAY_METHOD; KL> ASCII(PAY_METHOD) P COUNT(*) KL> - - -- KL>67 C 42955 KL>80 P 34373 KL> 11786 KL> I expected this statement to return the 11,786 records that have null KL> values. However, it doesn't: SQL>> SELECT COUNT(*) KL> 2 from leisure_plan_master_temp KL> 3 where membership_class = 'D' KL> 4 AND pay_METHOD NOT IN ('C','P'); KL> COUNT(*) KL> -- KL> 0 KL> But when I do this, I get the answer I expect. SQL>> SELECT COUNT(*) KL> 2 from leisure_plan_master_temp KL> 3 where membership_class = 'D' KL> 4 AND PAY_METHOD IS NULL; KL> COUNT(*) KL> -- KL> 11786 KL> This isn't exactly correct. There may be other values in this field, and if KL> they show up I need to include them, not just records where this field is KL> null. What am I missing? Is it because the value is NULL that Oracle KL> excludes it from the IN() statement, because of the classic definition of KL> NULL (can't be defined, therefore can't be sure it's not a C or a P)? KL> This is easy enough to fix, I'll change my data load to populate the null KL> values with my own code. But still? Have I got the WHY correct? KL> Thanks for any light someone can shed on this stupid question. KL> Lisa Koivu KL> Oracle Database Monkey Mama KL> Fairfield Resorts, Inc. KL> 5259 Coconut Creek Parkway KL> Ft. Lauderdale, FL, USA 33063 -- Best regards, Sergeymailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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: stored procedure status
I am doing the same but I don't know why stored procedure's( the called proc) error is not handled in my block. It is coming out of PL/sql block sayin that my procedure is invalid. STAARSHIP TECHNOLOGIES www.staarship.com ____ Kranti Pushkarna Project Leader Tel: +91-22-6931557 __ " Failure to prepare is preparing to fail " __ -Original Message- Sent: Wednesday, May 01, 2002 2:28 PM To: Multiple recipients of list ORACLE-L Use Exceptions... Ex. Begin StroedProc1 StoredProc2 Exception When Others Then Error Raised .. Aborting... End; HTH Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 Live to learn... forget... and learn again. -Original Message- Sent: Wednesday, May 01, 2002 11:43 AM To: Multiple recipients of list ORACLE-L Hi list, I have writeen a stored procedure to execute a set of stored procedures. I want to know is there any way to stop the procedure after any of stoed procedure( from the set) returns an error. In simple words, is it possible to get a status whether the called procedure was successful or not. TIA Kranti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ganesh Raja 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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).
stored procedure status
Hi list, I have writeen a stored procedure to execute a set of stored procedures. I want to know is there any way to stop the procedure after any of stoed procedure( from the set) returns an error. In simple words, is it possible to get a status whether the called procedure was successful or not. TIA Kranti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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: Sql Question
Thanx Stephane . I did the same STAARSHIP TECHNOLOGIES www.staarship.com Kranti Pushkarna Project Leader Tel: +91-22-6931557 __ " Failure to prepare is preparing to fail " __ -Original Message- Sent: Tuesday, April 30, 2002 2:13 PM To: Multiple recipients of list ORACLE-L It cannot. You have to write a PL/SQL function which returns a VARCHAR for that. >- Original Message - >From: kranti pushkarna ><[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Mon, 29 Apr 2002 23:48:20 > >Hi List, >Can someone give a SQL query to retuen >all values in paricular >column in comma separed format. >e.g. suppose I fire "select deptno from dept" the >output would be like > >Deptno >10 >20 >30 >40 > >I want the output like 10,20,30,40. > >I am just wondering can it be done in a single >query. > > >TIA >Kranti > Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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).
Sql Question
Hi List, Can someone give a SQL query to retuen all values in paricular column in comma separed format. e.g. suppose I fire "select deptno from dept" the output would be like Deptno 10 20 30 40 I want the output like 10,20,30,40. I am just wondering can it be done in a single query. TIA Kranti
RE:
F1 -Original Message-From: Ravindra B [mailto:[EMAIL PROTECTED]]Sent: Tuesday, April 30, 2002 3:09 AMTo: Multiple recipients of list ORACLE-LSubject: HELP
RE: Curious question about flushing the Pool
ACT 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.com -- Author: Tim Gorman 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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: Constraints
it should not contain any null values too other than being unique. Kranti -Original Message- Sent: Wednesday, April 24, 2002 5:39 PM To: Multiple recipients of list ORACLE-L Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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: One sql statement
you can use decode statement like this for credit return the "amount" and for debit return amount with negative value group by name or id select sum(decode(balance,'C',amount,(amount*-1))),name from temp group by name; hope that help you. -Original Message- Sent: Tuesday, April 02, 2002 11:28 AM To: Multiple recipients of list ORACLE-L Suppose I have table structure like this: amount Numeric(10) balance VARCHAR(1) the balance column can only have one of 'D' or 'C' I want to sum the amount of Debet and Credit on ONE SQL statement. How my SQL statement should be? Ferry Situmorang: Using Oracle 8.1.7 Designer 6i R4 PT Perkebunan Nusantara XIII (Persero) Pontianak-Indonesia http://www.ptpn13.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ferry Situmorang 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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).
error 997:overlapped I/O process is in progress
Hi all, I am getting error 997:overlapped I/O process is in progress when I am starting oracle management server service. My system is using DHCP. Can anyone give me idea what to do in this case. Thanx in advance Kranti Pushkarna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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).
how to grant selct on all the ables in schema
Hi all, Is there anyway of granting select on all the tables in a schema in one shot. Regards STAARSHIP TECHNOLOGIES www.staarship.com _ Kranti Pushkarna Project Leader Tel: +91-22-6931557
RE: Weid exp/imp problem
Title: Weid exp/imp problem This error is coming because at the time of adding foreign key constraint , referencing primary key is not available. You can avoid this error by creating a similar schema in the user in which you are taking the import and then import data with parameter "ignore=Y". Kranti -Original Message-From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]Sent: Wednesday, December 19, 2001 1:55 PMTo: Multiple recipients of list ORACLE-LSubject: Weid exp/imp problem Hi! I'm experiencing a weird problem here... I'm about to move one user's object from the development box to a test box. The user's rights on both boxes are identical. What I do is this: - export user (using exp) from development. Works flawlessly. - import user into the other box (user setup and tablespaces are identical) An I get the following errors which doesn't make a lot of sense to me... ... . . importing table "TABELLEN" 37 rows imported . . importing table "TABELLEN_ZUORDNUNGEN" 28 rows imported . . importing table "TMP$TEST" 1 rows imported . . importing table "TMP_FUNKTIONS_PARAMETER" 0 rows imported . . importing table "TMP_FUNKTIONS_SPALTEN" 0 rows imported . . importing table "USEREXIT" 5 rows imported . . importing table "USEREXIT_TYPE" 3 rows imported . . importing table "ZYKLUS" 7 rows imported IMP-00017: following statement failed with ORACLE error 2270: "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_BNGR_FK" FOREIGN" " KEY ("BNGR_ID") REFERENCES "BENUTZER_GRUPPEN" ("ID") ENABLE NOVALIDATE" IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list IMP-00017: following statement failed with ORACLE error 2270: "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_OW_FK" FOREIGN K" "EY ("OW_ID") REFERENCES "OWNER" ("ID") ENABLE NOVALIDATE" IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list ... Any ideas why this is happening? This is 8.1.7 on Sun Solaris. Thanks, Helmut
RE: which view for hidden objects
Title: RE: which view for hidden objects If u mean X$ tables it is V$fixed_table. -Original Message- From: Seema Singh [SMTP:[EMAIL PROTECTED]] Sent: 28 ÑãÖÇä, 1422 12:11 AM To: Multiple recipients of list ORACLE-L Subject: which view for hidden objects Which view is used to see hidden tables? Thanks -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh 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
command for other information (like > subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: CHAN Chor Ling Catherine (CSC) > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mr. Clark 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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: Change sysdate in database to a future date
change the date of the system on which the database resides -Original Message- Sent: Friday, December 07, 2001 9:15 AM To: Multiple recipients of list ORACLE-L Hi Gurus, I need to change the sysdate in the database to a future date. Does anyone know the command to do it ? Please advise. Thanks. Regds, New Bee -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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: ORA - 12560: TNS: protocol adapter error
Title: How to read trace file just start the oracle service in the control panel Kranti -Original Message-From: Saurabh Sharma [mailto:[EMAIL PROTECTED]]Sent: Tuesday, November 27, 2001 3:35 PMTo: Multiple recipients of list ORACLE-LSubject: Re: ORA - 12560: TNS: protocol adapter error check to see whether listener settings are done on the system. check for tnsnames.ora and listener.ora files on home\network\admin folder cheers. saurabh - Original Message - From: S.Jyotinarayan To: Multiple recipients of list ORACLE-L Sent: Monday, November 12, 2001 3:00 PM Subject: ORA - 12560: TNS: protocol adapter error Hi, I have installed your Oracle 8i EE trial software. When i am trying to connect through SQLPLUS with username: SCOTT andpassword: TIGER i am getting the following error :- "ORA - 12560: TNS: protocol adapter error".Could you tell why i am getting this error? What other information should i provide you?Thanx in advanceJyotinarayan
RE: NT SCheduler !!
Hi Saroj, As per my knowledge you cannot specify interval in at command . but you can add multiple entries in at command like at \\machinename 8:00 /interactive /every:M,T,W,Th,F,S at \\machinename 9:00 /interactive /every:M,T,W,Th,F,S hope it will solve ur prblm. Kranti -Original Message- Sent: Tuesday, October 30, 2001 1:50 PM To: Multiple recipients of list ORACLE-L Hello Guru, I want to run one batch in Windowns NT using At scheduler. Thw question is : a) There will be a start time & end time ...say 7PM & 12 PM b) Want to run the batch say in every hour (Have a fixed Interval time ) c)want to repeat step a & b everyday I am facing the problem in AT command how to specify the interval time i.e every 1 hr the batch will be run. Please help me. Regards, Saroj. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dash, Saroj (CAP,CEF) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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: Move table to another tablespace
alter table move ; -Original Message- Sent: Thursday, October 25, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Hallo, Can you please tell how to move one table from one tablespace to another? I have data inthistable. Thanks in advance Roland Sköldblom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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: schema documentation
Hi, You can use TOAD 6.4 for that. There is option for exporting table scripts. Thanx Kranti -Original Message- Sent: Thursday, October 18, 2001 9:00 PM To: Multiple recipients of list ORACLE-L Hi, is anyone aware of a tool that will reverse engineer a database and generate appropriate documentation for it (i.e. tables, columns, column types, type definitions, package procedures,parameters, etc, etc) in fancy HTML (or something similar) OraSnap performs something like this (in the Database Objects section) but the output is not detailed enough (since this is not the goal of OraSnap) thanx, Marin "When someone is seeking, it happens quite easily that he only sees the thing that he is seeking; that he is unable to find anything, unable to absorb anything, because he is only thinking of the thing he is seeking, because he is obsessed with his goal. Seeking means: to have a goal; but finding means: to be free, to be receptive, to have no goal. ..." Herman Hesse, "Siddhartha" -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna 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).