RE: Producing .mdb output from sqlplus or SQL or PL/SQL
Patrice, Use the utility Tom Kyte has on his site called 'owa_sylk'. It will generate SLK files that Excel opens automatically. We use it for lots of end user reports. See the following link: http://asktom.oracle.com/pls/ask/f?p=4950:8:1962318::NO::F4950_P8_DISPLAYID, F4950_P8_CRITERIA:769425837805,%7Bowa_sylk%7D http://asktom.oracle.com/pls/ask/f?p=4950:8:1962318::NO::F4950_P8_DISPLAYID ,F4950_P8_CRITERIA:769425837805,%7Bowa_sylk%7D Greg -Original Message- Sent: Monday, January 27, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Patrice, You can certainly spool a file that Excel will open automatically. Simply create a tab-delimited output file, and make sure the extension is .xls . Excel will open it and automatically have everything in the correct columns. You can create a tab-delimited file by selecting the following: set feedback off set lines 200 set trimspool on set pages 1000 spool data.xls select column_1||chr(9)||column_2||chr(9)||column_3 from any_table; exit; I do this all the time. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, January 27, 2003 7:29 AM To: Multiple recipients of list ORACLE-L I would like the ability to create a file which, when double-clicked upon, will open in Access or Excel automatically. (I know that Access can import other formats, I just want it to be automatic for users who don't know / don't want to bother importing files.) Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Friday, January 24, 2003 6:40 PM To: Multiple recipients of list ORACLE-L .mdb files are Microsoft Access database files. Best best is import the data from the database into Access using ODBC connection or to create a flat file. Access can work directly with the flat file (hey Access has been doing this for years, Oracle just got this feature!) or you can import the flat file. -Original Message- Sent: Friday, January 24, 2003 3:34 PM To: Multiple recipients of list ORACLE-L .xls is possible ... using owa_sylk package ... (check asktom.oracle.com for more info). no clue what .mdb is ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Friday, January 24, 2003 4:19 PM To: Multiple recipients of list ORACLE-L I am wondering if it is possible to produce .mdb or .xls format files from inside Oracle. Can this be done? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com 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: Mirsky, Greg 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: HELP..URGENT....NOT
Was this it? http://www.orakle.com/ Greg -Original Message- Sent: Thursday, January 16, 2003 5:29 AM To: Multiple recipients of list ORACLE-L A while back I posted a link to a website that has hundreds of Oracle links. It's a site that is good for Unix and Windoze bigots, pick your flavor. Well, I lost my hard drive and my internet favorites. If anyone has that link that I posted I would greatly appreciate it if you can now post it cuz I cannot remember the name of it. Thanks Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Farnsworth, Dave 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: Mirsky, Greg 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: Regular Expressions in SQL
Try: http://asktom.oracle.com/pls/ask/f?p=4950:8:1745425::NO::F4950_P8_DISPLAYID, F4950_P8_CRITERIA:2200894550208,%7Bregular%7D%20and%20%7Bexpressions%7D Ask Tom has whole write up on it. Greg -Original Message- Sent: Wednesday, December 18, 2002 2:04 PM To: Multiple recipients of list ORACLE-L What's the easiest way to do regular expressions in an SQL query? If the answer is creating a function, does anyone have one they wouldn't mind sharing? Thanks, Chris Christopher Beckley OCPDBA, MCDBA, MCSD, EIEIO ThirdParadigm LLC [EMAIL PROTECTED] -- 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: Mirsky, Greg 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).
Returning a Java object to PL/SQL
I'm on Oracle 9iR2 and after crawling through the documentation I am beginning to think this can not be done. Can your return a Java object back to a PL/SQL procedure/package? I have a class that has a method that will return an extended version of java.io.FileOutputStream. I won't need to do anything with the object in the PL/SQL except to pass it to another Java method along with some data obtained and manipulated from the PL/SQL procedure. The java object would be used as you would utilize file_type in sys.utl_file as a file pointer. Creating a Java method that would take as input a SQL results set and then make the calls to the other Java methods and handling the java.io.FileOutputStream object for me is probably out of the question since I am most likely going to be forced to deal with dynamic SQL. Anyone have suggestions, or condolences? ;-) greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mirsky, Greg 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: password
If you are too cheap for $4.00 then there is this one http://www.trantechnologies.com/pass_cracker.zip I found it in the comments for Oracle Password Cracker 1.6 on www.download.com Greg -Original Message- Sent: Tuesday, December 17, 2002 1:14 PM To: Multiple recipients of list ORACLE-L oh this is very scary especially that price did you try out the demo? I'm still in catch-up, deal with crises mode so I haven't had a chance Rachel --- [EMAIL PROTECTED] wrote: Hmm... Well maybe you *can* crack oracle passwords. I've just ordered the full version of this product. ( $4, I don't think I need to bother the purchasing department ). I'll let you know how it works. Jared Mark Leith [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/17/2002 06:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: password Yes, you can do this, but it still doesn't tell you the users *current* password does it? Has anyone tried: http://home.earthlink.net/~adamshalon/oracle_password_cracker/ ? Mark -Original Message- Sent: 17 December 2002 13:59 To: Multiple recipients of list ORACLE-L And you can use it to change it to your convenience and later get this encrypted password IN without the knowledge of the user.. Regards Jai Paulo Gomes [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/17/02 06:08 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: password nope u can get the encripted password from the oracle dictionáry -Original Message- Sent: terça-feira, 17 de Dezembro de 2002 11:34 To: Multiple recipients of list ORACLE-L Check the post-it note on their monitor? :) -Original Message- Sent: 17 December 2002 10:55 To: Multiple recipients of list ORACLE-L he can't but he can change it to a new one and then put the old back on -Original Message- Sent: terça-feira, 17 de Dezembro de 2002 4:09 To: Multiple recipients of list ORACLE-L how can a dba see the password of a user. The new MSN 8: smart spam protection and 2 months FREE* -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: faisal ahmad 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: 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Mirsky, Greg 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
RE: Mass updates to production tables (NULL to non-NULL)
What about creating a view with nvl(column_name,chr(32)) for the columns involved? Greg -Original Message- Sent: Monday, November 25, 2002 4:04 PM To: Multiple recipients of list ORACLE-L I've got a real hot project (8.1.7.2 on HP/UX 11.0) that needs to have NULLs converted to spaces on three different columns. Each is a CHAR, so I shouldn't need to worry about chaining, since that column's full size has already been allocated in the block, right? But the first column has 1.2M NULLs out of 1.45M rows. My first test was to just UPDATE mytable SET mycol = ' ' WHERE mycol IS NULL, after removing the index on that column. Seeing as there were many more rows updated than I had anticipated, I was going to test the UPDATE using a cursor, and committing at every 10K rows (~120 total commits) to reduce rollback and locking issues. Thoughts? Since this table is used for time-and-attendance and directly affects payroll, downtime isn't possible. TIA! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: Mirsky, Greg 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: Oracle on MVS able to submit JCL ??
You could FTP to 127.0.0.1 127.0.0.1 is the universal IP address of the system you are on. That is you would FTP to yourself and then send the FTP file, the JCL, to the JES reader. Give it a try it seems to work on our installation. Greg -Original Message- Sent: Wednesday, November 20, 2002 6:49 PM To: Multiple recipients of list ORACLE-L Thanks but EVERYTHING is on the mainframe (including Oracle). As far as I know there is no such thing as PIPEs and daemon processes on the mainframe. I can place skeleton JCL in a proclib. The problem is getting Oracle on the mainframe to somehome invoke the internal reader to submit the JCL. -Original Message- Greg Sent: Monday, November 18, 2002 10:04 AM To: Multiple recipients of list ORACLE-L Babette, If you are on a UNIX platfrom you can ftp the JCL directly to the JES reader to submit a job on the mainframe. You need to set the parameter site filetype=JES. On my web site (http://www.oracle-developer.us/code.htm) I have code posted to allow Oracle 8i or greater to FTP directly from Oracle. Here is an example using just UNIX: HOW TO SUBMIT A MAINFRAME BATCH JOB FROM UNIX STEP ONE: Create your jcl in a unix file. remember to use all upper case! EXAMPLE: iefbr14.jcl //M33TEST1 JOB (HRP,TEST),M33,CLASS=V,MSGCLASS=X,NOTIFY=M33 //STEP01 EXEC PGM=IEFBR14 //DD1 DD DSN=M33.TEST.FILE2, // UNIT=HRSDA, // SPACE=(CYL,(1,1),RLSE), // DCB=(RECFM=FB,LRECL=080,BLKSIZE=27920,DSORG=PS), // DISP=(NEW,CATLG,DELETE) //SYSPRINT DD SYSOUT=* //SYSOUT DD SYSOUT=* //* STEP TWO: Create a job script to FTP your JCL to the mainframe. 192.6.1.79 is the address of the mainframe for this example. 'uid' should be replaced with your user id. 'password' should be replaced with your password. 'iefbr14.jcl' is the file that we created in step one. Remember to grant execute rights to the job script using chmod. ### ftp a job to be submitted on the mainframe ftp -vn 192.6.1.79 END_OF_FTP_INPUT user uid password site filetype=JES put iefbr14.jcl bye STEP THREE: Run your job (as illustrated by the output). $ ftpjesjob.job Connected to 192.6.1.79. 220-FTPSRVR IBM MVS V3R2 at IBMMVS01, 12:07:39 on 2000/08/03 220 Connection will close if idle for more than 2 minutes. Remote system type is MVS. 331 Send password please. 230 M33 is logged on. Working directory is M33.. 200 Site command was accepted 200 Port request OK. 125 Sending Job to JES Internal Reader FIXrecfm 80 250-It is known to JES as JOB00676 250 Transfer completed successfully. 349 bytes sent in 0.00 seconds (1002.41 Kbytes/s) 221 Quit command received. Goodbye. $ Hope that helps you out Greg -- [snipped] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babette Turner-Underwood 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: Mirsky, Greg 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: Oracle on MVS able to submit JCL ??
Babette, If you are on a UNIX platfrom you can ftp the JCL directly to the JES reader to submit a job on the mainframe. You need to set the parameter site filetype=JES. On my web site (http://www.oracle-developer.us/code.htm) I have code posted to allow Oracle 8i or greater to FTP directly from Oracle. Here is an example using just UNIX: HOW TO SUBMIT A MAINFRAME BATCH JOB FROM UNIX STEP ONE: Create your jcl in a unix file. remember to use all upper case! EXAMPLE: iefbr14.jcl //M33TEST1 JOB (HRP,TEST),M33,CLASS=V,MSGCLASS=X,NOTIFY=M33 //STEP01 EXEC PGM=IEFBR14 //DD1 DD DSN=M33.TEST.FILE2, // UNIT=HRSDA, // SPACE=(CYL,(1,1),RLSE), // DCB=(RECFM=FB,LRECL=080,BLKSIZE=27920,DSORG=PS), // DISP=(NEW,CATLG,DELETE) //SYSPRINT DD SYSOUT=* //SYSOUT DD SYSOUT=* //* STEP TWO: Create a job script to FTP your JCL to the mainframe. 192.6.1.79 is the address of the mainframe for this example. 'uid' should be replaced with your user id. 'password' should be replaced with your password. 'iefbr14.jcl' is the file that we created in step one. Remember to grant execute rights to the job script using chmod. ### ftp a job to be submitted on the mainframe ftp -vn 192.6.1.79 END_OF_FTP_INPUT user uid password site filetype=JES put iefbr14.jcl bye STEP THREE: Run your job (as illustrated by the output). $ ftpjesjob.job Connected to 192.6.1.79. 220-FTPSRVR IBM MVS V3R2 at IBMMVS01, 12:07:39 on 2000/08/03 220 Connection will close if idle for more than 2 minutes. Remote system type is MVS. 331 Send password please. 230 M33 is logged on. Working directory is M33.. 200 Site command was accepted 200 Port request OK. 125 Sending Job to JES Internal Reader FIXrecfm 80 250-It is known to JES as JOB00676 250 Transfer completed successfully. 349 bytes sent in 0.00 seconds (1002.41 Kbytes/s) 221 Quit command received. Goodbye. $ Hope that helps you out Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mirsky, Greg 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: PIC 9(9)V99 ??
Decimal point is implied the number would physically look like 12345678999. -Original Message- Sent: Tuesday, November 12, 2002 8:14 PM To: Multiple recipients of list ORACLE-L Totally off Oracle topic. I am studying the mainframe layout specs for a flat file, and came across this datatype, and I just want to confirm my interpretation. PIC 9(9)V99 So that's 9 digits before the decimal point? And then two digits after the decimal point? And the actual data would have a decimal point? So in this case, for a total of 12 characters? 123456789.99 ? TIA!!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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: Mirsky, Greg 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: Oracle 10i features
Are you trying to KILL the product with MS bloat-ware? -Original Message- Sent: Tuesday, November 12, 2002 11:34 AM To: Multiple recipients of list ORACLE-L MS Word as SQL editor? MS Excel as sqlplus? Outlook as Instance Manager? :-) On Tuesday 12 November 2002 15:58, you wrote: .NET? -Original Message- From: Boivin, Patrice J [mailto:BoivinP;mar.dfo-mpo.gc.ca] Sent: Tuesday, November 12, 2002 7:59 AM To: Multiple recipients of list ORACLE-L Subject: Oracle 10i features Any rumours about what might be included in 10i? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J 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). -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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: Mirsky, Greg 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: Oracle to Excel
Laura, Check out Tom Kyte's site. He has posted the source code for a PL/SQL package called OWA_SYLK that will dump a SQL query to a *.SLK file (*.SLK files can be directly opened by Excel). Plug in the SQL and then go... send the output file to the user and they do the rest. Here is the link: http://asktom.oracle.com/pls/ask/f?p=4950:8:1556583::NO::F4950_P8_DISPLAYID, F4950_P8_CRITERIA:828426949078,%7Bowa_sylk%7D http://asktom.oracle.com/pls/ask/f?p=4950:8:1556583::NO::F4950_P8_DISPLAYID ,F4950_P8_CRITERIA:828426949078,%7Bowa_sylk%7D We use it constantly! Greg -Original Message- Sent: Wednesday, November 06, 2002 4:00 PM To: Multiple recipients of list ORACLE-L Thank you for your response Tom. I received other responses as well, and I know that I can comma delimit a .txt file but I wanted to know if I could do it all at one time, which is why I tried your suggestion first. It works except that each field is in a separate row instead of a column. Below is an example (1 record is shown): 53-041-02 0 2 AL POWER CO 51271 27-Nov-01 173.52 447-57 D412 173.52 226 DONNELL 43000 00014.04.D412 It looks like it thinks it is a carriage return instead of a tab. Any ideas? Laura -Original Message- Sent: Wednesday, November 06, 2002 1:24 PM To: Multiple recipients of list ORACLE-L Laura, lots of tools do this for you automatically. Oracle discoverer will export the results of a query directly into Excel format. You can do this yourself as follows: in SqlPLus select col1||chr(10)||col2||chr(10) from table. the chr(10) is the tab character. spool the results to a output.xls open the file using excel. the tab character is the default column delimiter. You should see all your data in cells. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 1:49 PM To: Multiple recipients of list ORACLE-L I think I have seen traffic concerning the extracting of data from Oracle into an Excel spreadsheet. We now have a need for this. Could anyone enlighten me? Thank you in advance. Laura -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mirsky, Greg 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 Brain Teaser Challenge
How about... SELECT t.id , t.parentid , t.nodeorder , t.description FROM treenode t CONNECT BY t.parentid = PRIOR t.id START WITH t.description = 'top folder' ORDER BY NVL ( TRIM ( TRANSLATE ( LOWER (t.description) , 'abcdefghijklmnopqrstuvwxyz' , ' ' ) ) , 0 ); Regards! Greg -Original Message- Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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: Mirsky, Greg 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: Trigger/character search
Something like this???
DECLARE
--x VARCHAR2 (128) := 'abcdefg';
x VARCHAR2 (128) := 'abcdefg123';
BEGIN
DBMS_OUTPUT.ENABLE (100);
IF SUBSTR (x, LENGTH (x) - 1, 1) IN
('1', '2', '3', '4', '5', '6', '7', '8',
'9', '0')
THEN
DBMS_OUTPUT.put_line ('Ends in a number... ');
ELSE
DBMS_OUTPUT.put_line ('Ends in a letter... ');
END IF;
END;
-Original Message-
Sent: Friday, October 25, 2002 3:39 PM
To: Multiple recipients of list ORACLE-L
Is there any way to do pattern matching in a PL/SQL trigger? In other
words,
if I had two strings and one ended with a character (ie. g) and one ended
with a number, is there a way to search for it so that it would flag if it
is
only a number?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mike Sardin
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: Mirsky, Greg
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).
ORA-02070: database does not support operator SYS_CONTEXT in this
For what it is worth... I've been battling this problem on Oracle 9i Release 2 and finally got it into Oracle as a bug. ORA-02070: database does not support operator SYS_CONTEXT in this context. (Oracle BUG Reference 2624342) when using an object that contains SYS_CONTEXT. I don't know if anyone has encountered this problem but I figured I would try to save someone from utter fustration trying to debug it. The bug and the work around code can be found in a script file on my web site http://www.oracle-developer.us/code.htm (go to the bottom of the page). I'll give an update when Oracle comes up with a fix. Gregory N. Mirsky Project Manager Financial Systems / HRMS Payroll The Estee Lauder Companies, Inc. (Estée Lauder, Aramis, Clinique, Prescriptives, Origins, M·A·C, Bobbi Brown essentials, Tommy Hilfiger Toiletries, jane, Donna Karan, Crème de la Mer, Aveda, Stila, Kate Spade fragrances and Jo Malone) 7 Corporate Center Drive Melville, NY 11747 Telephone: 1-631-531-1807 FAX: 1-631-531-1886 E-Mail: [EMAIL PROTECTED] Personal E-Mail: [EMAIL PROTECTED] or [EMAIL PROTECTED] or [EMAIL PROTECTED] Web: www.coin-collector.us www.oracle-developer.us Feed an opportunity. Starve a problem. ~ Peter F. Drucker -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mirsky, Greg 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: oracle or mssql
If he really wants to go cheap tell him to go with mySQL! At least it will be an open solution. Greg -Original Message- Sent: Wednesday, October 23, 2002 11:09 AM To: Multiple recipients of list ORACLE-L No it doesn't. MicroSoft is a card-carrying member of the Axis Of Evil. Last I heard they were developing nuclear weapons, probably in a huge bunker under Bill Gates' house. --Walt Weaver Bozeman, Montana -Original Message- Sent: Wednesday, October 23, 2002 8:54 AM To: Multiple recipients of list ORACLE-L It depends on your companies needs. On Tue, 22 Oct 2002 [EMAIL PROTECTED] wrote: Hi list Please input why my boss must invest into oracle rather than the cheaper mssql. His opinion is that most features are almost the same but mssql does that at half the price as oracle does. So why he should not choose mssql is the question g.g. kor rdw ict groningen -- 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: Alex 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: Weaver, Walt 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: Mirsky, Greg 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: Theory v Practice
Wow, Life is stranger than fiction! First of all buy stock in your hardware vendors since every query will result in a table scan of every table and if you get more than an intermittant workload on the system, it may very well come down onto its knee's. indeed they are making it a very expensive file system -- and inefficient! Second, how are they going to prevent duplication? VIA the front-end only? Keep laughing at them until you turn blue. Wait till they get a user who figures out they can do what they want through any ODBC product and create havoc with duplicate records, etc. etc. Third, work on your resume or ask for a transfer. This application sound like a real Cluster-F@#$ My apologies for being bluntly honest, but I speak from experience from my very early days with RDBMS applications. Greg -Original Message- Sent: Wednesday, October 23, 2002 1:45 PM To: Multiple recipients of list ORACLE-L The developers working on our new VB app are also responsible for setting up the Oracle DB behind it. The app is for an order entry/despatch/warehouse system with 5 million customers and 1000 orders per day. We have nearly 400 tables. They are not planning on using primary keys/secondary keys, as they say they will handle all the constraints via VB. I only have a theoretical knowledge of database design, which says this is very wrong. Is the Oracle system being used as anything more than an expensive file system? In real world scenarios, is this a common practice? Regards Craig Healey ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Craig Healey 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: Mirsky, Greg 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 zip a file on From Unix shell script
try this... cat ! To: [EMAIL PROTECTED] mailto:someone;anywhere.com [EMAIL PROTECTED] mailto:someone2;anywhere.com Mime-Version: 1.0 Content-Type: multipart/mixed; boundary=DMW.Boundary.605592468 Attached you can find the files. ! uuencode file001 uuencode file002 uuencode file003 uuencode file004 )| /usr/sbin/sendmail [EMAIL PROTECTED] mailto:someone;anywhere.com [EMAIL PROTECTED] mailto:someone2;anywhere.com -Original Message- Sent: Thursday, October 17, 2002 12:34 PM To: Multiple recipients of list ORACLE-L I'm curious how you get sendmail/mailx to send a file as an attachment? I suppose it can be done. http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mirsky, Greg 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 question : use of SUBSTR/INSTR functions
Anybody have a quick and dirty to parse the 4 octets of a typical IP address How about this... FUNCTION f_ip_to_number ( p_ipNVARCHAR2 ) RETURN NUMBER IS v_ip_segment1 NUMBER := SUBSTR (p_ip, 1, INSTR (p_ip, '.') - 1); v_ip_segment2 NUMBER := SUBSTR ( p_ip , INSTR (p_ip, '.', 1, 1) + 1 , INSTR (p_ip, '.', 1, 2) - INSTR (p_ip, '.', 1, 1) - 1 ); v_ip_segment3 NUMBER := SUBSTR ( p_ip , INSTR (p_ip, '.', 1, 2) + 1 , INSTR (p_ip, '.', 1, 3) - INSTR (p_ip, '.', 1, 2) - 1 ); v_ip_segment4 NUMBER := SUBSTR (p_ip, INSTR (p_ip, '.', -1) + 1); BEGIN RETURN ( ( (v_ip_segment1 * 256 + v_ip_segment2) * 256 ) + v_ip_segment3 ) * 256 + v_ip_segment4; END f_ip_to_number; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mirsky, Greg 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).
