Re: How to pass string with a ' to PL/SQL
(CALLDATE,''MM/DD/'') as p_date, '||
'TO_CHAR(CALLDATE,''MM/DD/ HH24:MI'') as d_date, '||=20
'DECODE(OPERATORID,NULL,'' '',OPERATORID) as dxc_rep_id, '||
'CASE WHEN VERIFIED =3D ''Y'' THEN ''good sale'' ELSE ''no sale'' =
END as status_txt, '||=20
'SALESPERSONID as tsr_id, SALESPERSONNAME as tsr_name, '||=20
'DECODE(SPARE6,NULL,'' '',SPARE6) as call_card, '||
'DECODE(SPARE7,NULL,'' '',SPARE7) as center_id, '||=20
'DECODE(SPARE11,NULL,'' '',SPARE11) as campaign, '||
'DECODE(SPARE3,NULL,'' '',SPARE3) as serv_code, '||=20
'CASE WHEN BTN IS NULL THEN ''00'' ELSE BTN END as btn, '|=
|
'DECODE(FULLNAME,'' '',FULLNAME) as auth_name, '||
'CUSTOMERINFOID as custinfoid, '||
'CASE WHEN fu_getntid(CALL_ID) =3D ''NF'' THEN ''NOT FOUND'' ELSE =
fu_getntid(CALL_ID) END as ver_code '||=20
'FROM CUSTOMERINFO '||
'WHERE CALLDATE between TO_DATE(:a,''-MM-DD HH24:MI:SS'') AND =
'||
' TO_DATE(:b ,''-MM-DD HH24:MI:SS'') ';
IF ( in_whereclause IS NOT NULL ) THEN=20
v_sqlbeg :=3D v_sqlbeg || in_orderbyclause ||' ';=20
END IF;
v_sqlend :=3D
'AND SPARE7 =3D :c AND ACCOUNT_NO =3D :d '||
'AND CALL_ID ''0'' OR CALL_ID IN ( ' ||
' SELECT CALL_ID FROM AUDIT_TABLE WHERE EVENT_TYPE=3D3 '||
' AND EVENT_ID between TO_DATE(:e,''-MM-DD HH24:MI:SS'') AND =
'||
' TO_DATE(:f,''-MM-DD HH24:MI:SS'') AND CALL_ID IN ( '||=20
' SELECT CALL_ID FROM CUSTOMERINFO WHERE '||
'CALLDATE BETWEEN TO_DATE(:g,''-MM-DD HH24:MI:SS'') AND '|=
|
' TO_DATE(:h,''-MM-DD HH24:MI:SS'') AND SPARE7=3D:i AND '|=
|
' ACCOUNT_NO=3D:j AND CALL_ID ''0'') '||
')';
IF ( in_orderbyclause IS NOT NULL ) THEN=20
v_sqlend :=3D v_sqlend || in_orderbyclause ||' ';=20
END IF;
v_sqlstatement :=3D v_sqlbeg || v_sqlend;=20
=20
OPEN rt_custinfo FOR v_sqlstatement USING in_begdate,in_enddate,in_cent=
er,in_acct, in_begdate,in_enddate,in_begdate,in_enddate, in_center,in_acc=
t; =20
RETURN rt_custinfo;
EXCEPTION=20
WHEN OTHERS THEN return rt_custinfo;
END fu_sales_analysis;
FUNCTION fu_sales_analysis_address (in_customerinfoid IN NUMBER, in_addr=
essType IN NUMBER)
RETURN pkg_cursors.ref_cursor IS rt_addressinfo pkg_cursors.ref_cursor;
v_sqlstatement VARCHAR2(3000);
BEGIN
v_sqlstatement :=3D 'SELECT FULLNAME as bill_name, ' ||
'ADDRESS1 ||'' '' || ADDRESS2 as street, '||=20
'DECODE(CITY,NULL,'' '',CITY) as city, '||
'DECODE(POSTALCODE,NULL,'' '',POSTALCODE) as zip, '||
'DECODE(REGION,null,'' '',REGION) as state FROM ADDRESSES '||
'WHERE CUSTOMERINFOID =3D :a AND ADDRESSTYPE=3D :b ';
OPEN rt_addressinfo FOR v_sqlstatement USING in_customerinfoid, in_add=
ressType; =20
RETURN rt_addressinfo;
EXCEPTION=20
WHEN OTHERS THEN return rt_addressinfo;
END fu_sales_analysis_address;
FUNCTION fu_sales_analysis_wtn (in_customerinfoid IN NUMBER)
RETURN pkg_cursors.ref_cursor IS rt_wtn pkg_cursors.ref_cursor;
v_sqlstatement VARCHAR2(500);
BEGIN=20
v_sqlstatement :=3D 'SELECT DECODE(wtn,''00'','' '',WTN) AS WT=
N ' ||
'FROM WTNS WHERE CUSTOMERINFOID=3D :a';
OPEN rt_wtn FOR v_sqlstatement USING in_customerinfoid; =20
RETURN rt_wtn;
EXCEPTION=20
WHEN OTHERS THEN return rt_wtn;
END fu_sales_analysis_wtn;
END pkg_reports_dynamiccti;
/
-- Sampele test runs
var a refcursor
set serveroutput on size 3
execute :a :=3D pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:0=
0:00','2003-06-02 00:00:00','20','864240103',null,null);
execute :a :=3D pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:0=
0:00','2003-06-02 00:00:00','20','864240103','order by calldate',null);
execute :a :=3D pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:0=
0:00','2003-06-02 00:00:00','20','864240103','order by calldate desc',nul=
l);
var a refcursor
var b varchar2(100);
begin
:b :=3D 'and sub_account_no=3D' || CHR(39) || '864240103=92 || CHR(39)=
;
execute :a :=3D pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 0=
0:00:00','2003-06-02 00:00:00','20','864240103','order by calldate desc',=
:b);
end;
/
execute :a :=3D pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:0=
0:00','2003-06-02 00:00:00','20','864240103','order by calldate desc','an=
d sub_account_no=3D''864240103'' ');
execute :a :=3D pkg_reports_Dynamiccti.fu_sales_analysis_address(114,2);
execute :a :=3D pkg_reports_Dynamiccti.fu_sales_analysis_wtn(600);
--print a
--0-724909865-1055276223=:79494--
--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Pat Hildebrand
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web
Re: Where is the sql stored for a stored procedure?
It is also a good idea to include the type or you can end up with such
confusing things as the text of a package mixed with the text of the
package body.
Pat
SELECT TEXT
FROM DBA_SOURCE
WHERE NAME = 'object_name'
and owner='owner name'
order by line
just in case two people own objects with the same name. And while it
MIGHT do the select in line order, it doesn't hurt to ensure that you
are reading the lines of code in the proper order :)
--- Bob Metelsky [EMAIL PROTECTED] wrote:
Thanks for all the replies...
A summary
{user|all|dba}_source
LOCDB SQL desc dba_source;
Name
---
OWNER
NAME
TYPE
LINE
TEXT
LOCDB SQL select distinct type from dba_source ;
TYPE
FUNCTION
PACKAGE
PACKAGE BODY
PROCEDURE
TYPE
SELECT TEXT FROM DBA_SOURCE WHERE NAME = 'object_name'
bob
Im mucking about in the user_ views...
Im seeing dba_objects type = 'procedure'; but need to find the sql
Thanks
bob
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bob Metelsky
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! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
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.net
--
Author: Pat Hildebrand
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: DBMS_JOB
use a function: check if 15 minutes after current time is in the don't run interval if it is return the first time after the interval if not return 15 minutes later Pat Hi All, Does anybody know a way of to get DBMS_JOB to run a job every 15 minutes, unless the time of day is between X and Y (for example 00:00am 03:00am)? All help appreciated! Pointers to RTFM more than welcome! ;) Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pat Hildebrand 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: Restricting the range of values in a field
Sounds made to order for a check constraint on the column of interest. Pat Is there any way you can specify that the only permissible values (is it called a domain?) that can be entered in varchar2 field in an Oracle table to, for example, A, B and C? We can restrict what values users can enter at the application level, but it would be nice to be able to also restrict what can be entered at the database level, in case other means of entering data are ever used or if the application layer fails, for whatever reason, to trap an unwanted value. Thanks -- Aidan Whitehall [EMAIL PROTECTED] Macromedia ColdFusion Developer -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pat Hildebrand 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: To Anyone involved in Web apps
We have a single Oracle user for connecting to Oracle but that doesn't mean that everyone has the same access. Users must authenticate before reaching an application and via this we know who they are so what they can see and do is individualized. Pat I have a question for any of you involved in Web applications. I would like to know how many of you go for the single Oracle user for everyone approach, and how many of you create Oracle schemas for each user, and if you can, what was the major reason for choosing that approach. Any opinions you wish to contribute will be helpful. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pat Hildebrand 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: Orawomen
I have been resisting responding to this thread for a number of reasons including my experiences may be considered outdated (my kids are through with their degree getting days and mine are even further back) but there are some things that seem to be coming through that have changed my mind. 1. The idea of fun. At one point I was involved in putting on a program of teaching kids math activities so that they could then teach their classmates. The kids enjoyed it but parents felt if they were having fun they couldn't be learning math. As to Girl Scouts, when I commented on some activities that I thought scouts might enjoy and help to interest them in science my sister who has a degree in engineering came back with the idea that scouting was supposed to be fun. 2. Studies. There have been studies showing that girls can do math but at about Junior High they turn off. If my experience is any guide to why that is about when the subtle and sometimes not so subtle things come into play when choices of what courses to take are made. Along the same lines there was a study on the critical filter role of math suggesting that not taking sufficient math closed doors to all but traditionally feminine fields. 3. Who or what is responsible. In reading there is something called the literate environment - kids whose parents enjoy reading and read to them have the easiest time learning to read. I tried to find something like that for math. As part of my studies I tested kids on math and asked their parents some questions. There was a correlation between the kids achievement and the parents enjoyment of math. There were also a few surprises in the parents answers about the need for math and how easy it was preceived to be - easy for girls but they had no need for it. I still don't have a good handle on this but the no need if subtled conveyed can turn girls from math and shut them out of things requiring the math. I saw and still see a lot of teaching for tests which to me is a way to turn kids off. If they really understand the test shouldn't be a problem but if they have to do the same thing over and over until they and all of their classmates are conditioned to respond just as the test maker expected they will get bored and turn off. I could go on but just on more thing to get a little more on topic. In a database class when I said that relational databases made sense to me the responses was you're a mathematician, aren't you? Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pat Hildebrand 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).
OAS Mac IE with secure server
I have been trying to track down the source of a problem with an application here where I can't do much more than search the web - I have seen so many things talking about bugs that are security holes in IE that I'm ready to say let's not let anyone us IE but that is not an option. At this point I'm wondering if anyone has encountered anything like this. The situation is as follows: The application is written in PL/SQL with some JavaScript. The Oracle is 8.1.6 on solaris 7. It is OAS 4 (I know it is old and that probably doesn't help) using secure Appache. Of late we have been receiving complaints that going back either from the browser toolbar or a button in the application using onClick=window.history.back() does not work with IE on a Mac (various versions of IE and the OS). The only Mac I have access to works just fine, but it is old and although we put on the latest IE we could that is still old give the old OS. I found a setting on a Win2K version of IE that can cause the same failure. I couldn't find anything corresponding on the Mac. Now to complicate things, I asked someone else on campus who uses a Mac and has access to our application to look for the corresponding setting on his Mac. His response was something of a surprise. He can't find anything like the setting I found under Win2k and while he does have the problems when accessing our application, he doesn't for any other applications using a secure server. He also has a lot of whitespace at the top of the first page with the Mac that he doesn't have with a Windows machine. One other thing that may or may not be relevant. The error raised is one of the negative ones that have plagued us from time to time and the rest of the message is for the entry page (minus the token) rather than whatever page one is trying to get to - certainly sounds like not everything is being cached. We have not heard of anyone having this problem with Netscape (any OS) and with the setting I found, it appears that it is just an IE configuration problem if it occurs under Windows with IE. As a result, I thought it was a Mac IE configuration problem. Now, it would appear that Mac IE doesn't have the ability to be configured on this. Expanding the search for a cause I have to include OAS which is why I'm asking here. If it is not an OAS configuration problem am sorry for what could turn out to be an off topic post. Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Anyone else had a Virus alert for Stephane Faroult's email ?
It probably is not coming from where it appears. We have an alias for a group of us that field questions on a particular application. Every once in awhile we get a message that this virus has been found in a message that we have sent (supposedly from this alias). Since we are not sending under that alias we know that it is coming from somewhere else. Apparently when a machine is infected it sends out messages randomly using addresses from the addressbook for the to, from, and reply to. I'm guessing that if it is using an actual message for the basis of this it could also be using the from as well as the subject of that message. Pat Martin Kendall wrote: My Norton Antivirus reports that Stephane's email re. How to pass value from SQL*Plus to Unix scripts? contains the W32.Klez.H@mm virus in an item called stars-bkgrd. Anyone else had same alert ? Martin Name: smime.p7s smime.p7sType: PKCS7 Signature (application/x-pkcs7-signature) Encoding: 7bit Sorry for the trouble, folks, but I have trouble understanding. When I am answering the list, it is either from a Linux machine (and netscape) - not infected by W32.Klez.H@mm according to the Symantec site - or from a mail web interface. I sent the e-mail mentioned above on October 22nd or October 23rd (two answers to the same question) more than one month ago, and from Linux. I hope that somebody is not harvesting the list ... If anybody has any hint about how to check for viruses on a Linux machine, BTW ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: mixed case text
The initcap function can work pretty well in a lot of cases but those
exceptions can be major. I remember there being discussions on this
list about how does one handle names starting with Mc or Mac. At the
time the original post came in I was dealing with some strings that
included the degrees MD and PhD and I know of no way initcap will
handle those correctly.
Pat
Do you know initcap function?
SELECT INITCAP('the soap') Capitals FROM DUAL;
Capitals
-
The Soap
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Pat Hildebrand
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: oracle sysdate problem ??
Your format is asking for the month instead of the minutes Hi all, I did a select sysdate from dual, and got 04:09:39 pm, but my machine system time is 4:47pm, which is right. SQL select to_char(sysdate,'mm/dd/yy hh:mm:ss am') ^^ should be mi from dual; Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: DBA tools on NT - PERL
I'm not responding to any single message here but a few general comments about perl. First of all I don't care what language you are talking about if you have never used it you probably will see something that you don't understand if someone shows you a program. Therefore, not understanding Jared's small program doesn't mean that perl is hard. After all if you have used || to mean or, seeing it used in SQL queries or PL/SQL to mean concatenation is going to cause some confusion until you have some idea of its meaning in this context. Although I have not had an opportunity to do much with perl recently, I was one who got people looking at perl when we moved off a mainframe and onto UNIX about ten years ago. Why? Because of what I was able to do with it. I was able to come up with things in a short period of time that might or might not be needed again. Some of these quick fixes became more than that - correcting a bad translation of a couple of characters when reading those IMB standard label tapes into an ASCII file was easy for perl and important to the eventual users of the file. Even then I didn't consider myself a perl expert - it was just very easy to pick up and extremely useful. I think it might be relevent to mention how I heard about perl in the first place. It was not from a techie but rather from a librarian. At that point I was more of the exception than the rule in a position of dealing with large machine readable datasets that our faculty and graduate students use for secondary research - in a lot of schools at that time the data was looked at as being infomation that belonged in the library but not much had been done about library provisions for using the data. I have a technical background so handling the data was not a problem but standards for cataloging it were. It was in this context that I heard about perl. Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: NOT IN performance problem
Hello, I have encountered a performance problem. I use Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production. I have two tables. phonenumber and person, each person has none, one or many phonenumbers referenced to him. I'm not sure what you want since your query doesn't correspond to what you are saying you want. Therefore no sample just a general statement, use minus. Pat The phonenumber-table is structured like: phonenumber.personid phonenumber.phonenumber The person-table is structured like: person.personid person.name person.address I wan't to know which persons that does NOT have any phonenumber(s). I can write the query as: SELECT personid FROM phonenumber WHERE personid NOT IN ( SELECT personid FROM person); However, since my tables are quite large, it takes forever to run my query. In the real database both (or atleast one) of person or phonenumber are views. To figure out who _does_ have phonenumbers is SIGNIFICANTLY faster. (SELECT DISTINCT person.personid FROM person, phonenumber WHERE person.personid=phonenumber.personid) I'm wondering how I could restructure or rewrite my query (who doesn't have any phoinenumbers?) to run faster, or if there is anything else I can do to optimize the query? Any suggestions? -- /Nils Höglund, Naqua KB E-mail: [EMAIL PROTECTED] Web:http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)736 51 74 58 Address:Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= 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: Pat Hildebrand 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: Datawarehousing help
Rachel, I haven't used their warehouse stuff and it has been a few years since I had to deal with people doing statistical analysis with SAS (by the way very good for that so if the reason considering this is the need for heavy duty statistical analysis that is a big plus) but the one thing that I remember from someone having SQL problems with SAS might be worth taking a look at. SAS's implementation of SQL was more restricted than Oracle's. You might want to check on what current differences are and if they are relavent to your situation. I can't remember the specifics but think it was not a matter of can't do it but rather have to do it another way. Pat have you used their tools? We are trying to decide whether or not to use them, so if anyone has had recent experience with them, I'd appreciate your thoughts on ease of use, understandability, quality of the product, etc Thanks Rachel -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: Dropping table space where name is lower case
try putting double quotes around the name of the tablespace Pat I am unable to drop a tablespace that I created. I created the tablespace with a lower case name, when I do a select * from dba_tablespaces the name is lowercase as opposed to uppercase. When I drop tablespace spatial oracle gives the following error message : ORA-00959: tablespace 'SPATIAL' does not exist This is oracle 8.1.7.2.? Thanks Darren -- Darren Browett P.Eng This message was transmitted Data Administratorusing 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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: Pat Hildebrand 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: Formatting of listserv messages
Comes over unformated for me and I generally don't even try to read things like that as with completely html messages - I just don't have the time. Pat Naveen and others, Does this email appear to be completely unformatted to anyone else? Just wondering if it was formatted when sent, or if this happened along the way, or is it just me? I'm not likely to respond to something this difficult to read, and I'm just wondering if there's a remedy to this. I've noticed at times that I may send a carefully formatted post only to have it appear rather messy on the list. This most often happens to email I send from work using Lotus Notes. Here at home I use KDE Kmail, which is much easier to configure than Lotus. Sorry for the intrusion, but this appeared to be an interesting topic, and it's rather tedious to format these so I can read them. I can't be the only one that recieves mail looking like this. Thanks, Jared On Saturday 02 February 2002 02:50, Naveen Nahata wrote: Type - - CDL_CDL_ID NOT NULL NUMBER(10) DESCRIPTION NOT NULL VARCHAR2(50) DOCUMENT_LINE NOT NULL NUMBER(2) DATE_RECEIVED NOT NULL DATE DATE_RETURNED DATE INSERTED_BY NOT NULL VARCHAR2(30) INSERT_DATE NOT NULL DATE LAST_CHANGED_BY NOT NULL VARCHAR2(30) LAST_CHANGE_DATE NOT NULL DATEDESCRIPTION column will have relatively few distinct values compared to the number of rows so a Bitmap Index will be considered.Following are the 3 queries which immediately come to my mind ...Query 1:SELECT DISTINCT cdl.description FROM cdl_documents cdl WHERE description NOT IN (SELECT description FROM cdl_documents WHERE cdl_cdl_id = 5);Query 2:SELECT DISTINCT cdl.description FROM cdl_documents cdl WHERE NOT EXISTS (SELECT 1 FROM cdl_documents WHERE cdl_cdl_id = 5 AND cdl.description = description);Query 3:SELECT DISTINCT description FROM cdl_documentsMINUSSELECT DISTINCT description FROM cdl_documents WHERE cdl_cdl_id = 5;One more thing -It is generally not advisable to use a NOT IN or NOT EQUAL TO clause for an indexed column bcoz the optimizer will go for FULL TABLE SCAN rather than INDEX SCAN. But what if i have a Bitmap Index? Will an INDEX SCAN still be avoided? If yes, why?Regards, Naveen - Do You Yahoo!? Yahoo! Auctions Great stuff seeking new owners! Bid now! Content-Type: text/html; charset=us-ascii; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: Pat Hildebrand 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: Why is this code not working?
I think you are confusing the logical and with the fact that you want both columns. Try using or in place of and (column_name = 'REGISTRATION_NUMBER' or column_name = 'DOCUMENT_NUMBER') Pat When I run this code both columns I get no rows returned. When I do a desc on one of the tables I see both columns. So, why am I not getting any data? select table_name, column_name from dba_tab_columns where (column_name = 'REGISTRATION_NUMBER' and column_name = 'DOCUMENT_NUMBER') Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: Pat Hildebrand 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: Create excelfile from pl/sql
I'm not sure what you mean by going through the import step. If this means you want to write something other than a comma seperated file, I don't have an answer. If you can use a csv file what we do is write a file with the extension .csv. Because it is part of an application with many things going on before writing the line we build up the line concatenating with commas between and quoting those values that contain commas. Clicking on the filename opens the file in Excel (or several other spread sheets that our users requested) when the .csv extension has been associated with that application. Pat Hallo, How can I create a excelfile with the UTL_FILE packet, I wantthe file to be able to opened directly in excel, without goingthrough the import step when I open the file. Give me an example, please. Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: New York/East Coast !!!!
Is there anyway we can submit names of family members in NY You might try the Red Cross. That is what has been on the news in Philadelphia. I don't know if that is general or just here. Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: listener supports no services
We have found our problem. It was a patch that didn't link properly and just coincidence that it appeared that the machine could no longer be found when the ip-address was changed. Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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).
listener supports no services
Has anyone run across this problem and come up with a way around it? This morning we had a change of IP-addresses on a cluster of machines. Our production server is now returning The listener supports no services whenever we try to start the listener. We have tried creating a new listener (using a new name and new port) but we still get this message. This is 8.1.6.2 on SunOS 5.7. The OS is 64 bit and Oracle is 32 bit. The applications are all web based so if there is a work around for OAS that in some way can access the database without going through the listener (we can access the database via sqlplus as long as it is the sqlplus on the same machine) it would help us out until we can solve this. I have temporarily brought up one application on the test instance since most of the data is via database links. However, we don't have the space to move other apps that store their data locally. A TAR has been opened with Oracle but at this point we looking for all suggestions. TIA Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: How do they get the answer?
This is a poorly written question. I would guess that since it is the teacher table the assumption is that the ID is unique, even a primary key (it is not null), but none of this is stated so it doesn't have to be the case. Although I can't see it making any sense to repeat the salary (if indeed this is the salary for the teacher rather than the course), if a teacher can be in more than once that block could be used an example of an update not doing what was expected. The update is done on subject_id and it is the record with that subject_id. If a teacher is teaching more than one course and the salary if for the teacher rather than the course, we end up with the possibility of different salaries for the same teacher and if the teacher happened to be teaching both and therefore was to get two raises what happens but those two records are now consistent at the wrong value. Of course with what info is given in the question there are other possibilities for what the table really represents and what the PL/SQL block is supposed to do. I would think an essay question on what is going on would be more revealing of the individual's knowledge than a flawed multiple guess question. I don't think I have ever heard of it for anything other than true-false but justifying the selection might make it a little more useful. Pat Examine the structure of the TEACHER table: Name Null? Type IDNOT NULLNUMBER(9) SALARYNUMBER (7,2) SUBJECT_IDNOT NULLNUMBER(3) SUBJECT_DESCRIPTION VARCHAR2(2) There are 200 teachers and 15 subjects. Each subject is taught by at least 2 teachers. Evaluate this PL/SQL block: DECLARE v_pct_raise number := 1.10; BEGIN UPDATE teacher SET salary = salary * 1.10 WHERE subject_id IN (102, 105); COMMIT; END; Which result will the PL/SQL block provide? (A) Only two teachers will receive a 10% salary increase. (B) All of the teachers will receive a 10% salary increase. (C) At least four teachers will receive a 10% salary increase. (D) A syntax error will occur. Answer: (C) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: Converting EBCDIC to ASCII
If you export the IMS data out to flat textfiles, and then transfer them via ftp in ASCII mode, the conversion will be done automagically for you. Alternatively if on Unix the dd utility will do the conversion for you. One problem that we encountered with using dd to convert EBCDIC to ASCII (in a completely different situation so it might not apply here) is that brackets came out as the characters having octal values 342 and 343 in ASCII. Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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
select table_name from dba_tables where table_name like 'EDL_WORK_%' and I received the following TABLE_NAME - EDL_WORKSTATION_LOCATION EDL_WORK_AR_ENTITIES Why would I be receiving the first record (EDL_WORKSTATION) if I try EDL_WORK_A% I get the correct result. Is there something about the _% combination ? _ is the wild card for matching any single character. Therefore, as long as there is at least one character _% and % will return the same thing. Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: OT -- suggestions to ensure nerdiness in potential IT staff
I can't really tell if no more answers are coming in or things are just very slow but I'll try to fill in the answers to my questions that have not been answered. Looks like the questions about the floppy drive (or should I say drives as I had the luxury of dual drives) are the ones that need some answers. The manufacture (now gone) was Micropolis and they later made hard drives that we used on our UNIX boxes when we moved off the main frame about ten years ago. The hard sectored disks had holes to separate the sectors (the holes were much to large to separate tracks). Micropolis used a 16 hard sector floppy and then soft sectored each hard sector into two. They had an ad about never so many tracks that showed railroad tracks. Yes, Logo was based on LISP. It was very bad practice to use loops in APL. They were avoided by using vectors. I calculated some very actuate statistical tables for my husband on a DEC-10 using APL and picking the exact (rather than approximated values) out of the vector in about the time frame that this discussion started with - some time in the early 80s. We also had APL on the CP/M machine (a Vector Graphics if anyone is curious) but it didn't have precision of the DEC-10 and later got a DOS version. I know it was the thing to try to do everything in one line but it was also possible to write readable code (as long as you knew the symbols) and of course there was always the lamp for comments. Speaking of on the CP/M machine and later a DOS version, I would think that the word processor that was being asked about was probably WordStar. We had that one on the CP/M machine (it was considered THE word processor at that time) and then DOS before WordPerfect. Actually had a strange experience installing the DOS version. I can't remember how much memory we had on that machine but it was a lot for it's day and WordStar gave me an error that I had insufficient memory. After checking everything I could think of, I installed a RAM disk to cut the memory down to a size mentioned in the documentation and low and behold the WordStar worked. IBM standard label tapes could actually be cartridges (3480/3490 or are those obsolute now?). The thing about them (besides EBCDIC which no one not on an IBM used) is that they had three files for one logical file. The header and footer(?) contained documention. In addition to the format stuff they could contain: a file name, the operating system under which the file was written, the date, and I think even some sort of machine ID. If the first file, the header also had a volumn ID. I did help to set up a system in perl under AIX that would compare the information in a header with information that we had other places including an Oracle database, did some other things, and put information into the database. It would have been a lot harder without those standard label tapes. Yes, I have used punch cards. The first computer I used was an IBM/1620. My first work with stat packages, SAS, SPSS, BMDP, was on a remote machine where the job was submitted on punch cards and if I remember correctly the jobs were not run when submitted but at a later time so that an out of order card or incorrectly punched one could make things a very slow process. Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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).
OT (fwd) Looking to resurrect .ZZZ archive file from Kaypro/CPM days
Since I don't think this got a usable reply on list it came from and it sounds like some people on this list had a Kaypro, I decided to see if we are geeky enough that someone has an answer. The closest I got to finding a solution at the time was someone who said it sounded familiar - didn't have a Kaypro and my CP/M machine is long gone. Most of the people on the list it came from probably would not consider themselves geeks and I'm sure have wondered what I'm good for when I displayed such tendencies. Pat I have a user (professor) who, way back, archived some work in some files with a .ZZZ extension. He has succeeded in transferring them from his old Kaypro (running the CPM OS) to his Windows machine, but he can't find a program that will unzip them (e.g. WinZip won't do it). He doesn't recall the name of the program that created the file - memory fades... For all I know, .ZZZ may have been just what he decided to designate as the file extension, rather than the result of a standard naming convention. But I thought I'd see if the .ZZZ file extension rings any bells with anyone out there. Searching on the web yields lots of documents containing ZZZ in them, but, wading through these, I have been unable to find anything that seems relevant to my question. (This matter of .ZZZ archive files - if it really is a matter at all - seems to fall outside the range of history covered on the web.) I would be grateful if anyone out there could offer some answeres, insights, or suggestions regarding this matter. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: OT -- suggestions to ensure nerdiness in potential IT staff
On Thu, 2 Aug 2001,Boivin, Patrice J scribbled on the wall in glitter crayon: -Was CP/M the OS that Bill Gates bought just before making his big deal with -IBM? Can't remember. - -Regards, -Patrice Boivin -Systems Analyst (Oracle Certified DBA) - --Original Message- -CP/M [or is it CPM i've seen it written both ways.] - - AFAIK, no. it was an OS from a small local company called QDOS [Quick and Dirty Operating System] if i'm not suffering from too much bit rot. CP/M was the other OS in contention. And the DEC rainbow shipped with both DOS and CP/M. Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: OT -- suggestions to ensure nerdiness in potential IT staff
I think this is very dependent on one's age and maybe geographic location. Okay, I'll start off by showing my age. In 1981 I wasn't in high school - my older son was. I wasn't a ham but back, I think as a pre-teen, we used to make telegraphs out of a piece of scrap wood, a flashlight bulb, a battery, a few nails, a little wire, and a strip of metal cut from a tin can to practice Morse code (I don't remember any of it now). I had to pass a slide rule test in college but in those days so did everyone else at Harvey Mudd. When I saw the question about boot disks I figured that the answer would be very dependent on the computer because I know there were computers at that time that didn't use disks. However, the computer that I got in 1981 required a boot disk. In fact that computer brings to mind a few other questions from that time. Before the introduction of the IBM PC, what was considered the operating system for business micro computers? What manufacturer made a 5 1/4 floppy drive that used a format with 100 tracks per inch (they actually had more data on a single sided disk than IBM had on their double sided disk)? What is the difference between hard sectored and soft sectored floppies? What did the above mentioned drive use? Some other questions that come to mind that are not so tied into a specific machine are: What does APL stand for? What was very bad practice in APL that other languages use? What was used instead? What is the language that uses a turtle for teaching children about computers? What language was it based on? Or how about something from the days of main frame tapes that has come back to haunt me lately. What is an IBM standard label tape? What about it might help in identifying what is on the tape and where it came from? Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: How to search all tables for a particular field?
I am looking for a particular field if it exists in a database. How to search all tables for a particular field? If you really mean all select table_name from dba_tab_columns where column_name = 'FIELD'; Be sure that the field you are looking for is in caps. If you mean all that a particular user can use replace dba with all and run query as that user. Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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).
virus question
I think I must have something confused here. If this list removes attachments to avoid viruses, how does a virus in an attachment come through the list? I would think that it might be sent out directly. This makes some sense as it seems that not everyone is receiving the same attachment. At this point I haven't received any but since I frequently receive messages out of order that does prove anything. Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: job offer from SAUDI ARABIA
Since it is Friday, I'll share something that all the talk of going back to where you came from brought to mind. When the 1990 US census forms came out we got a call from our younger son where he said on the order of the following: I got the long form. They are asking me for ethnicity and I don't think they would be amused by Dad's Middle American Mongrel. What one thing can I put down? Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: strange problem
hi gurus,
I am able to select from every hting from v$session at command line.
but when i write in procedure it fails why.
SQL select osuser from v$session where username = 'IMAGE_INDEX' and rownum
= 1;
OSUSER
--
MLOBO
this is ok...
CREATE OR REPLACE PROCEDURE n2 IS
tmpVar varchar2(100);
BEGIN
select osuser into tmpvar from v$session where username = 'MLOBO'
and rownum = 1;
dbms_output.put_line ('name '||tmpvar);
EXCEPTION
WHEN NO_DATA_FOUND THEN
Null;
WHEN OTHERS THEN
Null;
END n2;
this is failing says sys.V_$session must be declared.
what could be reason ?
How was select on v$session granted to user who owns the procedure?
If via a role this is the expected result.
Pat
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Pat Hildebrand
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: Mean/Median
Any scripts (sql or pl/sql) out there to compute various statistical things in Oracle? Would depend on just what you are looking for. A quick look at Oracle's SQL functions showed avg, st. deviation, and variance although no median but that should be easier to write than some of the others. Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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).
Oracle error -20001
Has anyone any idea what this means? It is coming up when trying to use a procedure in a PL/SQL package to display a web page. With some negative errors we are able to remove them by reloading all for the application in the OAS manager but not this one. Searching MetaLink I come up with suggestions to patch Applications 11i or upgrade Developer 6 - neither of which are in use here. I also came up with cause not determined. Environment: Sun OS 5.7 Oracle8i Enterprise Edition release 8.1.6.0.0 OAS 4.0 - we are using a secure server Both the package and package body are valid. Any help in unraveling this would be greaterly appreciated. Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: Input truncated to 1 characters
You are missing a newline after the / at the end of the procedure. Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: Select won't work for view create
I have a select that works fine. However, it doesn't work fine when I try to create a view of the result set. There is nothing particularly fancy about the query, just a plethora of outer joins from the main table to the secondary tables. I get an error back about one of the tables not existing, but like I said, it works as a select, just not as a create view as ... I had a problem like this and it turned out that it had nothing to do with the tables. I had accidently put the file name in the create script instead of the view name. :( Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: questions: security on the unix
Thanks Brian. It is very strang. I have two database on 2 nodes. db1, one instance all 640 for datafile. db2 2 databases. that I have change the test database for 640. the error message said the datafile premittion error. owner is oracle:dba Mitchell Are you set uid all the way down to and including the mount point to be able to run as oracle and therefore have the necessary permissions? Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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: Last Fri. of the Month ?
Given Any Date , How to Deduce the Date for the Last Fri. of the SAME Month ? how about next_day(last_day(date) - 7, 'Friday') where date is the given date? for example to find the last Friday of the month select next_day(last_day(sysdate) - 7, 'Friday') from dual; Pat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Hildebrand 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).
