RE: User with less privileges...
Thanks guys. OK, I can control the access to public synonyms but, I also don't want them to be seen by that new user (the intruder). So, I guess in order to avoid public synonyms, I still have to create all the synonyms for every aplication's users. It seems that I'm a lazy person. Regards iulian ilies -Original Message- Sent: Friday, August 02, 2002 9:08 AM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** I agree on the use of roles - it is the best way to go. However, beware that object privileges granted via a role are NOT in effect inside a definer's rights procedure/package (the default type). This *may* require some investigation and, perhaps, some changes to the application, the privileges of the package owner, the owner of the package, or the package authid, or ... I disagree about granting CONNECT to everyone - grant create session instead. CONNECT is actually a pre-defined role with a number of system privileges that most application users do NOT need (alter session, create table, create cluster, create database link, etc.) in addition to the create session system privilege. Likewise, I would grant explicit tablespace quotas. Granting RESOURCE is again overkill. Most application users don't need tablespace quotas and even if they do it is usually something trivial (e.g. 1-10 MB) in USERS. The system privilege unlimited tablespace (included in the RESOURCE role) is especially dangerous as it includes the SYSTEM tablespace. The easy way out is to just grant *everything* to PUBLIC, but it is a very poor choice from any rational security perspective - as you are now discovering. (Oracle preaches this, but doesn't actually practice it themselves!) You will need to do as Bill suggested: 1) Create a set of application-specific functional roles (e.g. CUST_SVC_REP, CUST_SVC_SUPR, CUST_SVC_ADMIN, ...). 2) Grant privileges to roles as appropriate 3) Grant roles to users as appropriate 4) Revoke all (most?) of the application object privileges (and perhaps some others) from PUBLIC The public synonyms are another issue. The don't carry any intrinsic privilege - SELECT, INSERT, etc. still have to be granted to the user or to a role granted to the user. However, public synonyms can be a performance issue and *may* be undesirable for other reasons. Don Granaman [OraSaurus] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 01, 2002 11:28 AM try this: rather than granting specific privs to PUBLIC, create specific roles for the different types of users you have, and grant appropriate object privs to each role (granting connect also helps :-). then for each user you add, just give that user whatever role is relevent and you're set . . . they will still be able to access public synonyms. only issue with this is that you'll still need to specify TS quotas to the specific users, as they don't inherit these from the roles (unless you grant RESOURCE to the role, which has UNLIMITED TABLESPACE). using roles is easy to maintain, document and manage -bill -Original Message- Sent: Thursday, August 01, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Hi guys. Can you give some ideeas about this problem. I have a schema which contains all the objects for the application. The user owner of the schema is also the application administrator and having more privilleges. The other users can have access to these objects by beeing granted with some special privilleges (like select/update/insert/delete for tables, execute for functionsprocedures) Because the user are deleted or added from time to time, the application author decided to grant the above kind of privilleges to the public and also create some public synonyms with the same names as the originals. BUT, my problem is that now I need to create an user (he does not have any relations with the ordinary application users) which I don't want to have any access to the hrowner objects, or just on few. Is this doable working only on this new user or I have to re-create all those synonyms and grant privilleges to every application user and revoke'em from public? Thank in advance! iulian ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Orange Romania SA is neither liable
User with less privileges...
Hi guys. Can you give some ideeas about this problem. I have a schema which contains all the objects for the application. The user owner of the schema is also the application administrator and having more privilleges. The other users can have access to these objects by beeing granted with some special privilleges (like select/update/insert/delete for tables, execute for functionsprocedures) Because the user are deleted or added from time to time, the application author decided to grant the above kind of privilleges to the public and also create some public synonyms with the same names as the originals. BUT, my problem is that now I need to create an user (he does not have any relations with the ordinary application users) which I don't want to have any access to the hrowner objects, or just on few. Is this doable working only on this new user or I have to re-create all those synonyms and grant privilleges to every application user and revoke'em from public? Thank in advance! iulian ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Orange Romania SA is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Database Analyst Tasks
Hi all! Can you guys, clear me up about what are the tasks of a database analyst. I mean something like a job description. I know they are likely to be different from case to case but just to make an ideea. Thanks in advance. Iulian Ilies ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Orange Romania SA is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Complex Integrity Checking
Title: RE: Complex Integrity Checking
Ok
Richard, this seems to be what I want.
I read
carefully the message but I didn't find the trigger
RHUNTLEY.SINTERVAL
How
did you do that?
Thanks!
iulian
-Original Message-From: Richard Huntley
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, June 05, 2002 7:44
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
Complex Integrity Checking
Iulian, this is what you want, NO? (except this works for date
fields not number fields as you've put in your latest
posts)... This is done using two triggers.
SQL insert into interval values('01-JAN-2002','01-MAR-2002');
2 1 row created.
SQL insert into interval values('03-MAR-2002','26-MAR-2002');
2 1 row created.
SQL insert into interval values('03-FEB-2002','14-MAR-2002');
2 insert into interval
* ERROR at line 1: ORA-2:
date overlap 03-FEB-02 14-MAR-02 ORA-06512: at
"RHUNTLEY.SINTERVAL", line 23 ORA-04088: error during
execution of trigger 'RHUNTLEY.SINTERVAL'
SQL insert into interval values('01-DEC-1999','01-JAN-2002');
2 insert into interval
* ERROR at line 1: ORA-2:
date overlap 03-FEB-02 14-MAR-02 ORA-06512: at
"RHUNTLEY.SINTERVAL", line 23 ORA-04088: error during
execution of trigger 'RHUNTLEY.SINTERVAL'
SQL insert into interval values('05-JAN-2002','01-FEB-2002');
2 insert into interval
* ERROR at line 1: ORA-2:
date overlap 03-FEB-02 14-MAR-02 ORA-06512: at
"RHUNTLEY.SINTERVAL", line 23 ORA-04088: error during
execution of trigger 'RHUNTLEY.SINTERVAL'
-Original Message- From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 05, 2002 10:39 AM To: Multiple recipients of list ORACLE-L Subject: RE: Complex Integrity Checking
-Original Message- Sent:
Wednesday, June 05, 2002 4:53 PM To: Multiple
recipients of list ORACLE-L
**
This email has been tested for viruses by F-Secure
Antivirus administered by IT Network
Department. **
two questions: How many records do you insert into that
table before a commit ?
Is the whole issue simply mutating table error when running
some business logic in an insert/update trigger for
the intervals table?
Regards,
Waleed
I'm sorry bu I can't answer to your questions because I don't
see the point.
Here's a test table: CREATE TABLE
intervals ( start_time NUMBER NOT NULL,
end_time NUMBER
NOT NULL )
Here are some statemens:
INSERT INTO intervals (START_TIME,END_TIME) VALUES (3,5) / INSERT INTO
intervals (START_TIME,END_TIME) VALUES (2,3) /
INSERT INTO intervals (START_TIME,END_TIME) VALUES (7,8)
What I want is that the integrity rule (no overlapped
intervals) be operational even if i insert a new
record or more or update one or more. Think of it the
same way an unique key works. This is a simplified
table for example purpose. In fact my application is a resource scheduler, so I want a resource not to be assigned for more
than 1 client at the same time. Here the start_time and end_time are of number type just for testing,
but of course it'll be of date type.
I'm starting to think that what I want, can be done in a
simple, clean manner but using complex workarounds,
isn't it? Thanks!
iulian
-Original Message- To:
Multiple recipients of list ORACLE-L Sent: 6/5/02 4:33
AM
First of all I want to thank you all for your answers.
Let's take'em one by one:
Attn: Mercadante, Thomas F
[[EMAIL PROTECTED]] - I cannont use "instead
of" trigger because of this error:
ORA-25002: cannot create INSTEAD OF triggers on tables
Cause: Only BEFORE or AFTER triggers can be created on a
table. Action: Change the trigger type to BEFORE or
AFTER.
I have an Oracle database version 9.0.1.1.1
Attn: Stephane Faroult
[[EMAIL PROTECTED]] - for insert your approach works
(although I have to change a bit the select
in exists condirion) but what about the update statements.
- moreover i think this will not keep my integrity
rule consistent, if someone try to simply use typical
insertupdate statements.
Attn: Khedr, Waleed
[[EMAIL PROTECTED]] - Can you give me an example
for your unique function based index, I mean
how can you assign an unique number for various intervals.
- anyway if this can be done I assume that would be a
very nice, clean solution
Attn: Richard Huntley
[[EMAIL PROTECTED]], Gogala, Mladen [[EMAIL PROTECTED]] -
RE: Complex Integrity Checking
First of all I want to thank you all for your answers. Let's take'em one by one: Attn: Mercadante, Thomas F [[EMAIL PROTECTED]] - I cannont use instead of trigger because of this error: ORA-25002: cannot create INSTEAD OF triggers on tables Cause: Only BEFORE or AFTER triggers can be created on a table. Action: Change the trigger type to BEFORE or AFTER. I have an Oracle database version 9.0.1.1.1 Attn: Stephane Faroult [[EMAIL PROTECTED]] - for insert your approach works (although I have to change a bit the select in exists condirion) but what about the update statements. - moreover i think this will not keep my integrity rule consistent, if someone try to simply use typical insertupdate statements. Attn: Khedr, Waleed [[EMAIL PROTECTED]] - Can you give me an example for your unique function based index, I mean how can you assign an unique number for various intervals. - anyway if this can be done I assume that would be a very nice, clean solution Attn: Richard Huntley [[EMAIL PROTECTED]], Gogala, Mladen [[EMAIL PROTECTED]] - this really doesn't suit my needs, create 2 tables instead of one Attn: DENNIS WILLIAMS [[EMAIL PROTECTED]], Aponte, Tony [[EMAIL PROTECTED]] - I did make a function: FUNCTION check_for_overlapped_intervals ( p_start_time IN NUMBER, p_end_time IN NUMBER) RETURN NUMBER IS n NUMBER; BEGIN -- when this select have records to count -- means that the new interval overlap an existing one -- and still is not corectly implement for update stament -- where it should not consider the current record SELECT COUNT(*) INTO n FROM intervals WHERE start_time p_end_time AND end_time p_start_time; RETURN(n); END; and use it in the trigger: CREATE OR REPLACE TRIGGER bi_interval BEFORE INSERT OR UPDATE ON intervals REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF check_for_overlapped_intervals(:new.start_time, :new.end_time) 0 THEN raise_application_error(-20100, 'Overlapped intervals'); END IF; END; but still got the same mutating table error. Am I wrong someplace. Thanks again. I try to test all of your solution and above are my answers. Can you still help me. I simplify my problem using a table INTERVALS with 2 columns START_TIME, END_TIME of NUMBER type. CREATE TABLE intervals ( start_time NUMBER NOT NULL, end_time NUMBER NOT NULL ) Please try to insert some data and implement an integrity system like I wanted Regards iulian ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Orange Romania SA is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Complex Integrity Checking
-Original Message- Sent: Wednesday, June 05, 2002 4:53 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** two questions: How many records do you insert into that table before a commit ? Is the whole issue simply mutating table error when running some business logic in an insert/update trigger for the intervals table? Regards, Waleed I'm sorry bu I can't answer to your questions because I don't see the point. Here's a test table: CREATE TABLE intervals ( start_time NUMBER NOT NULL, end_time NUMBER NOT NULL ) Here are some statemens: INSERT INTO intervals (START_TIME,END_TIME) VALUES (3,5) / INSERT INTO intervals (START_TIME,END_TIME) VALUES (2,3) / INSERT INTO intervals (START_TIME,END_TIME) VALUES (7,8) What I want is that the integrity rule (no overlapped intervals) be operational even if i insert a new record or more or update one or more. Think of it the same way an unique key works. This is a simplified table for example purpose. In fact my application is a resource scheduler, so I want a resource not to be assigned for more than 1 client at the same time. Here the start_time and end_time are of number type just for testing, but of course it'll be of date type. I'm starting to think that what I want, can be done in a simple, clean manner but using complex workarounds, isn't it? Thanks! iulian -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/5/02 4:33 AM First of all I want to thank you all for your answers. Let's take'em one by one: Attn: Mercadante, Thomas F [[EMAIL PROTECTED]] - I cannont use instead of trigger because of this error: ORA-25002: cannot create INSTEAD OF triggers on tables Cause: Only BEFORE or AFTER triggers can be created on a table. Action: Change the trigger type to BEFORE or AFTER. I have an Oracle database version 9.0.1.1.1 Attn: Stephane Faroult [[EMAIL PROTECTED]] - for insert your approach works (although I have to change a bit the select in exists condirion) but what about the update statements. - moreover i think this will not keep my integrity rule consistent, if someone try to simply use typical insertupdate statements. Attn: Khedr, Waleed [[EMAIL PROTECTED]] - Can you give me an example for your unique function based index, I mean how can you assign an unique number for various intervals. - anyway if this can be done I assume that would be a very nice, clean solution Attn: Richard Huntley [[EMAIL PROTECTED]], Gogala, Mladen [[EMAIL PROTECTED]] - this really doesn't suit my needs, create 2 tables instead of one Attn: DENNIS WILLIAMS [[EMAIL PROTECTED]], Aponte, Tony [[EMAIL PROTECTED]] - I did make a function: FUNCTION check_for_overlapped_intervals ( p_start_time IN NUMBER, p_end_time IN NUMBER) RETURN NUMBER IS n NUMBER; BEGIN -- when this select have records to count -- means that the new interval overlap an existing one -- and still is not corectly implement for update stament -- where it should not consider the current record SELECT COUNT(*) INTO n FROM intervals WHERE start_time p_end_time AND end_time p_start_time; RETURN(n); END; and use it in the trigger: CREATE OR REPLACE TRIGGER bi_interval BEFORE INSERT OR UPDATE ON intervals REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF check_for_overlapped_intervals(:new.start_time, :new.end_time) 0 THEN raise_application_error(-20100, 'Overlapped intervals'); END IF; END; but still got the same mutating table error. Am I wrong someplace. Thanks again. I try to test all of your solution and above are my answers. Can you still help me. I simplify my problem using a table INTERVALS with 2 columns START_TIME, END_TIME of NUMBER type. CREATE TABLE intervals ( start_time NUMBER NOT NULL, end_time NUMBER NOT NULL ) Please try to insert some data and implement an integrity system like I wanted Regards iulian ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient
RE: Complex Integrity Checking
I said something like the way the unique constraints work. Ok. Here's my context. I have a table say intervals and 2 columns start_time and end_time. I want to check for overlapped intervals. I know what conditions to check but I can't implement them. Thanks! iulian -Original Message- Sent: Tuesday, June 04, 2002 5:13 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** Hi if unique does not suit your need what exactly do you need to check? duplicates: use primary key Jack Iulian.ILIES@oran ge.roTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) [EMAIL PROTECTED] Subject: Complex Integrity Checking 04-06-2002 15:58 Please respond to ORACLE-L Hi guys. Here's my problem. I want to check the new values (when insertingupdating a table) against the ones in the existing rows. Something like checking for duplicate values, but using a unique constraint doesn't suit my needs. I think of a before insertupdate trigger, wherein checking my condition and raise a error if not valid. The problem is, in case of an update statement, I get the mutating ORA-04091 table my table is mutating. I read a lot of doc but I didn't find any helping ideas. Can you give me some, or maybe a new approach to this kind of problem? Thanks in advance! iulian ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Orange Romania SA is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been
RE: Complex Integrity Checking
I'm reticent about putting the checking code in the application before insertupdate statement, although I'm not sure why. I'm just thinking about concurency and all the implications. On the other hand I wanted to put the bussiness rules on the database side. Anyway, I'll use this approach, you told me, after all. But what if after I check for overlapped intervals, but before inserting, another user insert another record with an interval extending over mine. I know it sounds crazy but I really like to know how this kind of stuff are implemented. That's the way the unique values constraint work, I guess, and I'd like to know more about it. iulian -Original Message- Sent: Tuesday, June 04, 2002 6:24 PM To: Multiple recipients of list ORACLE-L maybe I'm being simplistic and I know this will impact performance but why not simply do a select to see if the condition exists before the insert or update? --- [EMAIL PROTECTED] wrote: I said something like the way the unique constraints work. Ok. Here's my context. I have a table say intervals and 2 columns start_time and end_time. I want to check for overlapped intervals. I know what conditions to check but I can't implement them. Thanks! iulian -Original Message- Sent: Tuesday, June 04, 2002 5:13 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** Hi if unique does not suit your need what exactly do you need to check? duplicates: use primary key Jack Iulian.ILIES@oran ge.roTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) [EMAIL PROTECTED] Subject: Complex Integrity Checking 04-06-2002 15:58 Please respond to ORACLE-L Hi guys. Here's my problem. I want to check the new values (when insertingupdating a table) against the ones in the existing rows. Something like checking for duplicate values, but using a unique constraint doesn't suit my needs. I think of a before insertupdate trigger, wherein checking my condition and raise a error if not valid. The problem is, in case of an update statement, I get the mutating ORA-04091 table my table is mutating. I read a lot of doc but I didn't find any helping ideas. Can you give me some, or maybe a new approach to this kind of problem? Thanks in advance! iulian ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Orange Romania SA is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat
RE: Why is this code not working?
I guess this is what you want:
select table_name, column_name
from dba_tab_columns
where (column_name = 'REGISTRATION_NUMBER' or column_name =
'DOCUMENT_NUMBER')
or
select table_name, column_name
from dba_tab_columns
where column_name in ('REGISTRATION_NUMBER', 'DOCUMENT_NUMBER')
-Original Message-
Sent: Tuesday, December 11, 2001 5:20 PM
To: Multiple recipients of list ORACLE-L
**
This email has been tested for viruses by F-Secure Antivirus
administered by IT Network Department.
**
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).
**
The information contained in this communication is confidential and
may be legally privileged. It is intended solely for the use of the
individual or entity to whom it is addressed and others authorised to
receive it. If you are not the intended recipient you are hereby
notified that any disclosure, copying, distribution or taking action in
reliance of the contents of this information is strictly prohibited and
may be unlawful. Mobil Rom is neither liable for the proper, complete
transmission of the information contained in this communication nor
any delay in its receipt.
**
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: excel data into oracle through sql loader
Have you considered to use findreplace tool in excel and replace all the th string with nothing, and then reformat the cell as you need. Iulian -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 6:00 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** Hi lists, I need to load the excel sheet data to oracle tables. A date column is improperly entered by users. In the excel sheet the date column was filled up without using hyphens or slashes the data is like this todays date: 10th Dec 2001 (it is supposed to be 10/12/2001 or 10-Dec-2001 etc...) But it was entered in the cells as 101201 When I formatted the data using the excel option cells - format The data it is displaying in a strage format: 1/27/2177 All the date values are displayed improperly. Does anybody come across such a situation. How to format the date cells in excelsheet. (should I change any options for the sheet) Thnx in advance, Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) 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). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sql query
Does this suit you? select to_number(null) as id, to_char(null) as car_make, 0 ordcol from dual union select id, car_make, 1 ordcol from carmake order by ordcol, car_make; Iulian -Original Message- Sent: Tuesday, December 11, 2001 5:45 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** Hi, I have this sql statement: select to_number(null) as id, to_char(null) as car_make from dual union select id, car_make from carmake order by car_make; So this selects a blank record and then the records from carmake. But I want the blank record to appear at the top of the list, and it must be done in the select statement. Can this be done? TIA, Thanks, Steven Hovington -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington 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). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Import/Export table
Hi list. I have 3 tables in a schema and I want to export them to another schema in another database. Any ideeas? Thanks! Iulian ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Import/Export table
Thanks. Still have another questions. - When importing can I change the original tables name? - I try exp80imp80 and besides the tables I saw that the import tool get the constraints related to those tables. Can I import only the structeuredata of the table without indexes and any other objects connected with those tables. Anyway even without these I managed to export/import the tables so I appreciate you all for the helping hand. Iulian -Original Message- Sent: Monday, December 10, 2001 1:15 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** You can export the tables in Table level export and then import it to the target database. As suggested by GopalKrishnan, if the names are different you may have to use FROMUSER TOUSER during the import. -Original Message- Gopalakrishnan Sent: Monday, December 10, 2001 2:45 PM To: Multiple recipients of list ORACLE-L Hi, You can do a USER Level export and import them to another schema. If the names are different you may have to use FROMUSER TOUSER during the import. Oracle Utilities Manual will have more information. Best Regards, K Gopalakrishnan (408) 934 9310 -Original Message- [EMAIL PROTECTED] Sent: Monday, December 10, 2001 12:35 AM To: Multiple recipients of list ORACLE-L Hi list. I have 3 tables in a schema and I want to export them to another schema in another database. Any ideeas? Thanks! Iulian ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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: Rahul Mehendale 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: 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
RE: Inserstatement
You cannot achieve this by one sql statement. Instead you should consider use PL/SQL. Make your own procedure or an anonymous PL/SQL block. I would like to give you an example but you have to tell more about your problem, like the update should be done based on a relation between those 2 tables... and furthermore it's an insert or an update what you were talking about? If you want just an insert you can use something like: INSERT INTO X (field_in_X) SELECT field_in_P FROM P Regards Iulian -Original Message- Sent: Tuesday, November 27, 2001 1:45 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** How can I update one field in table X from another table, table P. Table P have 5 different fields but only one of them should be used to update table X. Give me an example on a sql statement for this. Sincerely Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Inserstatement
Yeah, Iain, you are right, and I don't hate to recognize. Iulian -Original Message- Sent: Tuesday, November 27, 2001 3:35 PM To: Multiple recipients of list ORACLE-L I hate to disagree but why couldn't you update x set field = (select field1 from p where p.join_field = x.join_field) where conditions Iain Nicoll -Original Message- Sent: Tuesday, November 27, 2001 12:45 PM To: Multiple recipients of list ORACLE-L You cannot achieve this by one sql statement. Instead you should consider use PL/SQL. Make your own procedure or an anonymous PL/SQL block. I would like to give you an example but you have to tell more about your problem, like the update should be done based on a relation between those 2 tables... and furthermore it's an insert or an update what you were talking about? If you want just an insert you can use something like: INSERT INTO X (field_in_X) SELECT field_in_P FROM P Regards Iulian -Original Message- Sent: Tuesday, November 27, 2001 1:45 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** How can I update one field in table X from another table, table P. Table P have 5 different fields but only one of them should be used to update table X. Give me an example on a sql statement for this. Sincerely Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: 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 Can I get A sequence Current Value in PROC
You can use another pseducolumn CURRVAL but you must use at least once per session the NEXTVAL before using the CURRVAL or else you would get the ORA-08002 error. ... SELECT your_sequence.CURRVAL INTO your_variable FROM dual; ... HTH Iulian -Original Message- Sent: Monday, November 26, 2001 2:50 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** Hi when writing a program PROC sequenceName.NEXTVAL points to the next value of a sequence, is there anything like sequenceName.SOMETHING that returns current value of sequence. Thanks E. __ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ehsan sinavalda 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). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How Can I get A sequence Current Value in PROC
Or I think you can query the dictionary SELECT last_number INTO your_variable FROM user_sequences WHERE sequence_name = UPPER(your_sequence) I don't know why it's necessary to use nextval, but they state this in the documentation. Iulian -Original Message- Sent: Monday, November 26, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Does anyone know why Oracle forces you to use NEXTVAL before doing CURRVAL. I would think sometimes you would want to use CURRVAL without incrementing sequence first. Thanks Rick -Original Message- Sent: Monday, November 26, 2001 8:50 AM To: Multiple recipients of list ORACLE-L You can use another pseducolumn CURRVAL but you must use at least once per session the NEXTVAL before using the CURRVAL or else you would get the ORA-08002 error. ... SELECT your_sequence.CURRVAL INTO your_variable FROM dual; ... HTH Iulian -Original Message- Sent: Monday, November 26, 2001 2:50 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** Hi when writing a program PROC sequenceName.NEXTVAL points to the next value of a sequence, is there anything like sequenceName.SOMETHING that returns current value of sequence. Thanks E. __ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ehsan sinavalda 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). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) 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: 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 - please reply fast
Can you tell us what the last word in the field value mean (except 'Info
missing' and 'Bad info')
I mean is there a rule that those T, S1 does respect?
Something like this:
- first character is a letter and in this set of values ('S', 'T' etc)
- the rest are digits
etc.
Try to find out a pattern and let us know, may be we can help you out.
Iulian
-Original Message-
Sent: Thursday, November 15, 2001 8:56 AM
To: Multiple recipients of list ORACLE-L
**
This email has been tested for viruses by F-Secure Antivirus
administered by IT Network Department.
**
Hej
Would like to have som help with this. Please give me an example of sql
statement.
I have field1.
Pelle T
Kalle S1000
Info missing
Pelle Svensson T3
Bad info
and want this result after the selectstatement.
Pelle
Kalle
Info missing
Pelle Svensson
Bad info
which means that I dont want to have the last word in this field except
'Info missing' and 'Bad info'. These two should remain unchanged, but all
other should not have the last word .
Thanksin advance
Roland S
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
**
The information contained in this communication is confidential and
may be legally privileged. It is intended solely for the use of the
individual or entity to whom it is addressed and others authorised to
receive it. If you are not the intended recipient you are hereby
notified that any disclosure, copying, distribution or taking action in
reliance of the contents of this information is strictly prohibited and
may be unlawful. Mobil Rom is neither liable for the proper, complete
transmission of the information contained in this communication nor
any delay in its receipt.
**
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Sql question - please reply fast
select field1, decode(field1, 'Info missing', 'Info missing', 'Bad info', 'Bad info', substr(field1, 1, instr(translate(field1, '0123456789', '@@'), '@')-3)) from your_table HTH Iulian -Original Message- Sent: Thursday, November 15, 2001 8:56 AM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** Hej Would like to have som help with this. Please give me an example of sql statement. I have field1. Pelle T Kalle S1000 Info missing Pelle Svensson T3 Bad info and want this result after the selectstatement. Pelle Kalle Info missing Pelle Svensson Bad info which means that I dont want to have the last word in this field except 'Info missing' and 'Bad info'. These two should remain unchanged, but all other should not have the last word . Thanksin advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: komplex sql
I hope I got you right. Anyway here's a not so complex sql SELECT DECODE(mxurval_namn, NULL, 'No Info', ltrim(substr(mxurval_namn,instr(mxurval_namn,' ' FROM mxurval; I'm not sure what do you mean by 'No Info' and 'Missing Info'??? HTH Iulian -Original Message- Sent: Wednesday, November 14, 2001 12:50 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** Hallo, How can I do a select statement that creates this: I have the field1 Jimmy Y1000 Timmy L3 No Info Missing Info and I want the select to give me this: Jimmy Timmy No Info Missing Info You see It should still be 'No info' and 'Missing Info'after the select statement. How can I change this statement? SELECT ltrim(substr(mxurval_namn,instr(mxurval_namn,' '))) FROM mxurval; Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Where 1 = 1
I sometime used this kind of aproach when I dinamically build the WHERE expression. Something like this: where_condition := ' WHERE 1 = 1 ' LOOP where_condition := ' AND ' || new_condition END LOOP The point is if there are any condition to add the first condition is always true so it won't affect the final where_condition. The same is when there aren't any condition to add. And in some cases there are really no conditions to add, thus your final query will have only a condition like ' WHERE 1 = 1 '. If you need to connect all the intermediate condition by OR operator probably you will use the WHERE 1 = 0. I hope I'll make my point clear enough and of course is any related to your case. Maybe if you show us the entire query, someone can tell you more about it. Regards Iulian Ilies -Original Message- Sent: Friday, October 26, 2001 2:20 AM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** I was monitoring a load test of my company's new application when I came across something interesting. One of the more monstrous queries had the following as part of its WHERE clause: 'AND 1 = 1' Has anyone seen this used before? What is it supposed to achieve? __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ed Bittel 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). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A range function ??
Try this
ASCII(your_char) NOT BETWEEN ASCII('A') AND ASCII('Z')
ASCII('A')-ASCII('Z') is an interval and ASCII('a')-ASCII('z') is another so
be careful
_
Iulian Ilies
mailto: [EMAIL PROTECTED]
mailto: [EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, September 26, 2001 9:45 AM
To: Multiple recipients of list ORACLE-L
**
This email has been tested for viruses by F-Secure Antivirus
administered by IT Network Department.
**
Hi,
I would like to exclude an arrange of characters.
there is for instance:
not in ('A','B','C','D','E'...);
Is there not perhaps something easier? ie:
not in ( A-Z, a-z) ;
Does anyone know of a function or some other more economical way of doing
this?
Many Regards
Denham
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Denham Eva
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).
**
The information contained in this communication is confidential and
may be legally privileged. It is intended solely for the use of the
individual or entity to whom it is addressed and others authorised to
receive it. If you are not the intended recipient you are hereby
notified that any disclosure, copying, distribution or taking action in
reliance of the contents of this information is strictly prohibited and
may be unlawful. Mobil Rom is neither liable for the proper, complete
transmission of the information contained in this communication nor
any delay in its receipt.
**
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Forms Developer - Conversion Trouble
... w number; ... x := get_window_property(my_win, width); ... What's wrong with this code. I get the message '...ORA-06502...' I check the return value of get_window_property and it's a varchar2 value like '400,000' Oracle itself has in help an example like this a number variable is assigned with the varchar2 value returned by get_window_property(..., width) Is it about formating that sort of values '999,999' _ Iulian Ilies mailto: [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
