susan@roadrunner.slcs.slb.com (Susan Rosenbaum) (07/26/90)
I have an Oracle database containing address information. What I want to do is print the addresses onto two-column formatted mailing labels. For example, Joe Smith Jane Jones 111 Main St. 222 First St. Yourtown, Tx. 99999 Yourcity, Tx. 88888 Does anyone know the magic incantation from SQL*Plus to format the responses from a database query in this manner? Thanks! Susan Rosenbaum e-mail: rosenbaum@slcs.slb.com Schlumberger Laboratory for Computer Science phone: 1 512-331-3759 Austin, Texas fax: 1 512-331-3760
re4@prism.gatech.EDU (RUSSELL EARNEST) (07/27/90)
In article <8601@linus.SLCS.SLB.COM>, susan@roadrunner.slcs.slb.com (Susan Rosenbaum) writes: > I have an Oracle database containing address information. What I want > to do is print the addresses onto two-column formatted mailing labels. > For example, > Joe Smith Jane Jones > 111 Main St. 222 First St. > Yourtown, Tx. 99999 Yourcity, Tx. 88888 > Does anyone know the magic incantation from SQL*Plus to format the > responses from a database query in this manner? Thanks! I think what you are looking for is in the SQL*Report Writer package not SQL*Plus. Page 4-9 of the Introduction to SQL*Report Writer manual (#19678-0688) describes a mailing label report laid out exactly as you describe. Enjoy. -- Any similarity between this comment and rational thought is purely coincidental. - RUSSELL EARNEST Georgia Institute of Technology, Atlanta Georgia, 30332 uucp: ...!{allegra,amd,hplabs,seismo,ut-ngp}!gatech!prism!re4 ARPA: re4@prism.gatech.edu
carl@eleazar.dartmouth.edu (Carl Pedersen) (07/27/90)
re4@prism.gatech.EDU (RUSSELL EARNEST) writes: >In article <8601@linus.SLCS.SLB.COM>, susan@roadrunner.slcs.slb.com (Susan Rosenbaum) writes: >> I have an Oracle database containing address information. What I want >> to do is print the addresses onto two-column formatted mailing labels. >> For example, >> Joe Smith Jane Jones >> 111 Main St. 222 First St. >> Yourtown, Tx. 99999 Yourcity, Tx. 88888 >> Does anyone know the magic incantation from SQL*Plus to format the >> responses from a database query in this manner? Thanks! >I think what you are looking for is in the SQL*Report Writer package not >SQL*Plus. Well, I suppose SQL*ReportWriter could handle this, but it costs a lot of money. SQL*Report, which you get for free can also do a pretty good job, but you have to learn another language. You CAN do it in plain old SQL*Plus if you are willing to be obscure. Something like the following seems to work for me: SQL> set pagesize 0; SQL> column line2 newline; SQL> column line3 newline; SQL> r 1 select rpad(max(decode(mod(rownum,2),1,name)),30)|| 2 max(decode(mod(rownum,2),0,name)) line1, 3 rpad(max(decode(mod(rownum,2),1,street)),30)|| 4 max(decode(mod(rownum,2),0,street)) line2, 5 rpad(max(decode(mod(rownum,2),1,city_st_zip)),30)|| 6 max(decode(mod(rownum,2),0,city_st_zip)) line3 7 from folks 8* group by trunc((rownum-1)/2) Amazing what you can do with a language that doesn't even have primitive recursion, isn't it? You can also do a reflexive join on rownum to achieve more or less the same thing. Of course, all of this is highly non-ANSII, but most of what you need can be built up using only ANSII constructs if that's a problem. I was a little surprised that ORACLE let me use ROWNUM in a GROUP BY clause. ROWNUM is not stable. Newer versions of ORACLE may not support this. However, it's easy enough to add a sequence number to your table and use that instead.
dalmandm@infonode.ingr.com (Dianne M. Dalman) (02/15/91)
(Please excuse any glaring errors here, this is my first post!) Question: In oracle, is there any "quick and dirty" way to delete a user, and drop all tables owned by that user? I haven't been able to find anything in documentation about how to get rid of an oracle username once it's been created, and it's a hassle to have to go into the system tables to find out all the tables which have been created by a user, drop them one by one, and then just leave that "empty name" hanging out there. Seems messy! (previously I used standard engine INFORMIX, where a simple "rm -r" solves all problems!) ------ (new subject) ------ My (very modest) opinions on various databases: (no flames, please, I'm relatively new at this!) Informix SE: small, quick, friendly, a perfect "one user-one database" RDBMS Informix -ONLINE: Larger (min. 125K blocks), copied ORACLE's "one main db for everyone" concept to be competative in that market. Relatively new but fairly stable, STILL friendly (isql menus, etc.) and quick. Oracle: spacehog, slow, but better than most at networking, somewhat UN-friendly (a programmers database, not for the low-end user) since it needs to be "tuned" and you need to know your SQL . Better (more flexible) report writing capabilities than INFORMIX. more portable. a good large "production " remote database. Ingres: (not too much experience here) large, moderately friendly. (??) --------------------------------------------------------------------- Disclamer: Opinions expressed herein are Strictly my own, and are subject to change at my slightest whim, with no forewarning whatsoever unless it's tuesday and the moon is full and my shoes are on backwards...i.e. Opinions are nearly as stable as a first release of a brand new RDBMS based on previously untested relational theories... ----------------------------------------------------------------------- Opinion, comments, answers >> uucp!ingr!wyle!dianne!dianne gripes, flames >>/dev/null
mmorris@oracle.com (Martin Morris) (02/15/91)
In article <1991Feb14.163452.28616@infonode.ingr.com> dalmandm@infonode.ingr.com (Dianne M. Dalman) writes: >(Please excuse any glaring errors here, this is my first post!) > >Question: In oracle, is there any "quick and dirty" way to delete a user, >and drop all tables owned by that user? I haven't been able to find anything >in documentation about how to get rid of an oracle username once it's been >created, and it's a hassle to have to go into the system tables to find >out all the tables which have been created by a user, drop them one by one, >and then just leave that "empty name" hanging out there. Seems messy! > >(previously I used standard engine INFORMIX, where a simple "rm -r" solves >all problems!) > The following SQL*Plus script will drop all objects belonging to a user: SET PAGES 999 SELECT 'DROP '||OBJECT_TYPE||' '||OBJECT_NAME||';' FROM USER_OBJECTS WHERE OBJECT_TYPE != 'INDEX' SPOOL filename.sql / SPOOL OFF @filename Run this script as the user you wish to remove. After running it you can then CONNECT to SYSTEM (or some other DBA account) and use the REVOKE command to prevent access of the user. Note that this is for a V6 database. For V5 use an equivilent SYSTEM table (I thing the view TAB will do) It is fairly easy to make this script (or similar) generic that just accepts the username as a parameter and does it all. This is left as an exercise to the reader... :-) Martin > >--------------------------------------------------------------------- >Disclamer: Opinions expressed herein are Strictly my own, and are > subject to change at my slightest whim, with no forewarning whatsoever > unless it's tuesday and the moon is full and my shoes are on > backwards...i.e. Opinions are nearly as stable as a first release > of a brand new RDBMS based on previously untested relational theories... >----------------------------------------------------------------------- >Opinion, comments, answers >> uucp!ingr!wyle!dianne!dianne >gripes, flames >>/dev/null ------------------------------------------------------------------------------ Martin Morris SELECT STANDARD_DISCLAIMER "Everybody is lost Oracle UK FROM LEGAL_SPEAKS; but me!" -- Indy
ac@dde.dk (Anders Conrad) (02/20/91)
dalmandm@infonode.ingr.com (Dianne M. Dalman) writes: >Question: In oracle, is there any "quick and dirty" way to delete a user, >and drop all tables owned by that user? I haven't been able to find anything >in documentation about how to get rid of an oracle username once it's been >created, and it's a hassle to have to go into the system tables to find >out all the tables which have been created by a user, drop them one by one, >and then just leave that "empty name" hanging out there. Seems messy! I don't think you are able to delete the name from the user-list. You can get rid of tables belonging to the user by running this little script in SQL*Plus : rem remtab.sql rem DDE 1. August 1989. rem removes all tables from the current user. rem set heading off set termout off set feedback off set echo off set pause off set pagesize 1000 spool drop.sql select 'drop '||tabtype||' '||tname||';' from tab; spool off start drop set heading on set termout on set feedback on set pause on set echo on select * from tab; host rm drop.sql -- Anders Conrad Dansk Data Elektronik A/S SQL Group E-mail: ac@dde.dk or ..!mcsun!dkuug!dde!ac