RE: What is wrong with this?
Don't forget that your 'operator sarcasm' or OS, may in fact round to the nearest 0.1 cs (cobbled-together statistic) unless you set 'RTFM=TRUE' :( Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 26 March 2003 21:44 To: Multiple recipients of list ORACLE-L Subject: Re: What is wrong with this? It was 11.7 seconds, but I rounded up. Jared Ron Rogers [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/26/2003 12:33 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: What is wrong with this? Jared, You have entirely to much free time if you are timing each of your work items. OR is it the compulsive disorder that DBA acquire? RON 1.2.3.4.5.6.7.8. [EMAIL PROTECTED] 03/26/03 02:28PM Roland, It took me exactly 12 seconds to find out exactly how to do this from the documentation. This was simply by clicking on 'CREATE SEQUENCE' in the SQL Reference manual. I strongly suggest you learn to use and read the documentation. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/26/2003 01:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:What is wrong with this? Hallo, I have this sequence, CREATE SEQUENCE SYS_STORE_ID START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10; I am trying to run this script, sys_store_id.nextval: but get this errormessage: unknown command beginning SYS_STORE_... - rest of line ignored. How can I solve this problem? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Ron Rogers 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: 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: Niall Litchfield 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
What is wrong with this?
Hallo, I have this sequence, CREATE SEQUENCE SYS_STORE_ID START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10; I am trying to run this script, sys_store_id.nextval: but get this errormessage: unknown command beginning SYS_STORE_... - rest of line ignored. How can I solve this problem? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: What is wrong with this?
select sys_store_id.nextval from dual; [EMAIL PROTECTED] wrote: Hallo, I have this sequence, CREATE SEQUENCE SYS_STORE_ID START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10; I am trying to run this script, sys_store_id.nextval: but get this errormessage: unknown command beginning SYS_STORE_... - rest of line ignored. How can I solve this problem? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Wisser 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: What is wrong with this?
select sys_store_id.nextval from dual; should solve the problem. sys_store_id.nextval is not a sqlplus command. It can be used as select List-item in a sql statement like the one in my example. A 'select list-item' is normally a column name from the table in the from clause in the sql statement. -Original Message- Sent: Wednesday, March 26, 2003 3:19 AM To: Multiple recipients of list ORACLE-L Hallo, I have this sequence, CREATE SEQUENCE SYS_STORE_ID START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10; I am trying to run this script, sys_store_id.nextval: but get this errormessage: unknown command beginning SYS_STORE_... - rest of line ignored. How can I solve this problem? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Biswas, Pradip 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: What is wrong with this?
You should: select sys_store_id.nextval from dual; or similar to get a value from sequence. Br.Jorma Hallo, I have this sequence, CREATE SEQUENCE SYS_STORE_ID START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10; I am trying to run this script, sys_store_id.nextval: but get this errormessage: unknown command beginning SYS_STORE_... - rest of line ignored. How can I solve this problem? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: What is wrong with this?
Hallo, select sys_store_id.nextval from dual; Cheers -Original Message- Sent: Wednesday, March 26, 2003 4:19 AM To: Multiple recipients of list ORACLE-L Hallo, I have this sequence, CREATE SEQUENCE SYS_STORE_ID START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10; I am trying to run this script, sys_store_id.nextval: but get this errormessage: unknown command beginning SYS_STORE_... - rest of line ignored. How can I solve this problem? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sherman, Edward 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: What is wrong with this?
Roland, It took me exactly 12 seconds to find out exactly how to do this from the documentation. This was simply by clicking on 'CREATE SEQUENCE' in the SQL Reference manual. I strongly suggest you learn to use and read the documentation. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/26/2003 01:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:What is wrong with this? Hallo, I have this sequence, CREATE SEQUENCE SYS_STORE_ID START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10; I am trying to run this script, sys_store_id.nextval: but get this errormessage: unknown command beginning SYS_STORE_... - rest of line ignored. How can I solve this problem? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: What is wrong with this?
Jared, You have entirely to much free time if you are timing each of your work items. OR is it the compulsive disorder that DBA acquire? RON 1.2.3.4.5.6.7.8. [EMAIL PROTECTED] 03/26/03 02:28PM Roland, It took me exactly 12 seconds to find out exactly how to do this from the documentation. This was simply by clicking on 'CREATE SEQUENCE' in the SQL Reference manual. I strongly suggest you learn to use and read the documentation. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/26/2003 01:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:What is wrong with this? Hallo, I have this sequence, CREATE SEQUENCE SYS_STORE_ID START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10; I am trying to run this script, sys_store_id.nextval: but get this errormessage: unknown command beginning SYS_STORE_... - rest of line ignored. How can I solve this problem? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Ron Rogers 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: What is wrong with this?
It was 11.7 seconds, but I rounded up. Jared Ron Rogers [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/26/2003 12:33 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: What is wrong with this? Jared, You have entirely to much free time if you are timing each of your work items. OR is it the compulsive disorder that DBA acquire? RON 1.2.3.4.5.6.7.8. [EMAIL PROTECTED] 03/26/03 02:28PM Roland, It took me exactly 12 seconds to find out exactly how to do this from the documentation. This was simply by clicking on 'CREATE SEQUENCE' in the SQL Reference manual. I strongly suggest you learn to use and read the documentation. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/26/2003 01:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:What is wrong with this? Hallo, I have this sequence, CREATE SEQUENCE SYS_STORE_ID START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10; I am trying to run this script, sys_store_id.nextval: but get this errormessage: unknown command beginning SYS_STORE_... - rest of line ignored. How can I solve this problem? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Ron Rogers 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: 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: What is wrong with this package? Solved. Thanks
Thank you all for the help. Your suggestion worked. Thanks for Rich Jesse, Ron Thomas, Stephane Faroult, Rick Cale. I replaced nn with with n1 in the body during the definition of function. create or replace package body p1 as function check_ok (n1 in number) return number ^^^ Once again, thank you all. Regards, Rao -Original Message- From: [EMAIL PROTECTED] [mailto:maheswara.rao;sungard.com] Sent: Monday, October 21, 2002 3:43 PM To: Multiple recipients of list ORACLE-L Subject: What is wrong with this package? Pls. help When we compile this package body, we are getting the error - PLS - 00323. Please help. Platform: Solaris 7. Oracle 8.1.6. Package is getting created without any errors. When we try to compile package body we are getting the error 3/12 PLS-00323: subprogram or cursor 'CHECK_OK' is declared in a package specification and must be defined in the package body Package: CREATE OR REPLACE PACKAGE P1 is msg_g number(3); function check_OK (n1 in number) return number; procedure test; end p1; create or replace package body p1 as function check_ok (nn in number) return number IS KK number; BEGIN KK := msg_g * nn; return KK; end check_ok; procedure test as v1 number; begin null; end test; end p1; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
What is wrong with this package? Pls. help
When we compile this package body, we are getting the error - PLS - 00323. Please help. Platform: Solaris 7. Oracle 8.1.6. Package is getting created without any errors. When we try to compile package body we are getting the error 3/12 PLS-00323: subprogram or cursor 'CHECK_OK' is declared in a package specification and must be defined in the package body Package: CREATE OR REPLACE PACKAGE P1 is msg_g number(3); function check_OK (n1 in number) return number; procedure test; end p1; create or replace package body p1 as function check_ok (nn in number) return number IS KK number; BEGIN KK := msg_g * nn; return KK; end check_ok; procedure test as v1 number; begin null; end test; end p1; / Thanks, Rao -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: What is wrong with this package? Pls. help
Easy one. The function declaration does not match between the spec and the body. function check_OK (n1 in number) function check_ok (nn in number) Argument names must be the same. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To: [EMAIL PROTECTED] 10/21/2002 01:43 cc: PM Subject: What is wrong with this package? Pls. help Please respond to ORACLE-L When we compile this package body, we are getting the error - PLS - 00323. Please help. Platform: Solaris 7. Oracle 8.1.6. Package is getting created without any errors. When we try to compile package body we are getting the error 3/12 PLS-00323: subprogram or cursor 'CHECK_OK' is declared in a package specification and must be defined in the package body Package: CREATE OR REPLACE PACKAGE P1 is msg_g number(3); function check_OK (n1 in number) return number; procedure test; end p1; create or replace package body p1 as function check_ok (nn in number) return number IS KK number; BEGIN KK := msg_g * nn; return KK; end check_ok; procedure test as v1 number; begin null; end test; end p1; / Thanks, Rao -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas 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: What is wrong with this package? Pls. help
Rao, You've defined the package to use n1 as a parameter to CHECK_OK, but nn in the body. Make them match and all should be well. HTH! :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:maheswara.rao;sungard.com] Sent: Monday, October 21, 2002 3:43 PM To: Multiple recipients of list ORACLE-L Subject: What is wrong with this package? Pls. help When we compile this package body, we are getting the error - PLS - 00323. Please help. Platform: Solaris 7. Oracle 8.1.6. Package is getting created without any errors. When we try to compile package body we are getting the error 3/12 PLS-00323: subprogram or cursor 'CHECK_OK' is declared in a package specification and must be defined in the package body Package: CREATE OR REPLACE PACKAGE P1 is msg_g number(3); function check_OK (n1 in number) return number; procedure test; end p1; create or replace package body p1 as function check_ok (nn in number) return number IS KK number; BEGIN KK := msg_g * nn; return KK; end check_ok; procedure test as v1 number; begin null; end test; end p1; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: What is wrong with this package? Pls. help
[EMAIL PROTECTED] wrote: When we compile this package body, we are getting the error - PLS - 00323. Please help. Platform: Solaris 7. Oracle 8.1.6. Package is getting created without any errors. When we try to compile package body we are getting the error 3/12 PLS-00323: subprogram or cursor 'CHECK_OK' is declared in a package specification and must be defined in the package body Package: CREATE OR REPLACE PACKAGE P1 is msg_g number(3); function check_OK (n1 in number) return number; procedure test; end p1; create or replace package body p1 as function check_ok (nn in number) return number IS KK number; BEGIN KK := msg_g * nn; return KK; end check_ok; procedure test as v1 number; begin null; end test; end p1; / Thanks, Rao -- Rao, Replace n1 with nn in the package part and it will work like a charm. PL/SQL is _very_ stupid and prameters must bear the same names in specifications and actual declaration, even if doesn't make much sense. I always use cut-and-paste :-). -- 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).
Re: what is wrong with this idea ...
but you dont know how many records can be there in child table . Also if the status changes for child record you will have to take care of updating teh parent one also . -bp - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 14, 2002 6:18 AM Hi. We have a table in our data warehouse which keeps info about calls made. This table has a child table with some detailed information about parts of the call. There may be any number of parts within a call (1 to many) and every part has a status. MY developer wants to add a string field to the parent table which will concatinate all the statuses for all the parts within this call. For example if a call has 4 parts and their statuses are A,B,A and F, the value of that field will be ABFA. Then the developer will be able to query smalle parent table instead of a large child table in order to see how many calls had at least one part with status A or statuses A and F etc by using a INSTR (or SUBSTR) command. Would it be better (from performance/CPU standpoint) to add several separate fields: STATUS_A_CNT, STATUS_B_CNT (the list of status codes is fairly static) instead? There is something about this string that rubs me the wrong way, but I can't put my finger on it. Any thoughts? thank you Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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: BigP 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: what is wrong with this idea ... - thanks
This is to thank all those who replied to my post - Thomas, Dennis, Jack, Melissa, Manavendra, Jerome, Steven , Stephen and Michael (sorry if I missed someone). I have been able to create the aggregate which IMO will speed up the reports so hopefully the developers will go along. thank you all for your responses. Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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).
what is wrong with this idea ...
Hi. We have a table in our data warehouse which keeps info about calls made. This table has a child table with some detailed information about parts of the call. There may be any number of parts within a call (1 to many) and every part has a status. MY developer wants to add a string field to the parent table which will concatinate all the statuses for all the parts within this call. For example if a call has 4 parts and their statuses are A,B,A and F, the value of that field will be ABFA. Then the developer will be able to query smalle parent table instead of a large child table in order to see how many calls had at least one part with status A or statuses A and F etc by using a INSTR (or SUBSTR) command. Would it be better (from performance/CPU standpoint) to add several separate fields: STATUS_A_CNT, STATUS_B_CNT (the list of status codes is fairly static) instead? There is something about this string that rubs me the wrong way, but I can't put my finger on it. Any thoughts? thank you Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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: what is wrong with this idea ...
Gene, You ask what's wrong with this? The main challenge I see is: how are you going to maintain this field? If a status gets updated in the child table, are you supposed to update the parent table? And to do that, you will need to requery the child table to get all the status's of all children records whenever one status changes. Pretty silly really, Why not do this - create a function that, given the PK of the parent table, queries all the child records and concatenates all the status values into one string. Then either create a view for the duhveloper to use to return what he wants to return at query time. Something like this: Create or replace function get_child_status(in_pk_column) return varchar2 is cursor c1 is select status_code from child_table where pk_column=in_pk_column; ret_string varchar2(10); begin for c1_rec in c1 loop ret_string := ret_string || c1_rec.status_code; end loop; return ret_string end; The duhveloper can then: select parent_pk_column, get_child_status(parent_pk_column) from parent_table; Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 14, 2002 9:19 AM To: Multiple recipients of list ORACLE-L Hi. We have a table in our data warehouse which keeps info about calls made. This table has a child table with some detailed information about parts of the call. There may be any number of parts within a call (1 to many) and every part has a status. MY developer wants to add a string field to the parent table which will concatinate all the statuses for all the parts within this call. For example if a call has 4 parts and their statuses are A,B,A and F, the value of that field will be ABFA. Then the developer will be able to query smalle parent table instead of a large child table in order to see how many calls had at least one part with status A or statuses A and F etc by using a INSTR (or SUBSTR) command. Would it be better (from performance/CPU standpoint) to add several separate fields: STATUS_A_CNT, STATUS_B_CNT (the list of status codes is fairly static) instead? There is something about this string that rubs me the wrong way, but I can't put my finger on it. Any thoughts? thank you Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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: Mercadante, Thomas F 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: what is wrong with this idea ...
Sounds like a bit of denormalization. Which during development you may have to do for performance reasons. Checkout www.dmreview.com for some articles on this. Or Ralph Kimball's site. I believe one of the design rules state that you would want to avoid creating those STATUS_A_CNT, etc. fields. What happens when you need new status counts?? Then you have to add more fields?? Sounds bad. Isn't that a violation of First Normal Form?? Also, I would imagine that parsing a single column to get all the status counts is a lot easier and faster then having to check multiple columns. Dunno. You would have to gen up the code to see. BTW, I believe there are more parsing functions then the standard fare in the book that may help in this situation. Like OWA_PATTERN, here is a snippet from my notes... OWA_PATTERN An example from Jared Still... declare tstr varchar2(100) := 'this c34ontains s0239everal 2340 numeric 882dig2its'; begin dbms_output.put_line( tstr); -- remove the digits owa_pattern.change( tstr, '\d', '', 'g'); dbms_output.put_line( tstr); -- remove the extra spaces owa_pattern.change( tstr, '\s+', ' ', 'g'); dbms_output.put_line( tstr); end; / A link to documentation on the package... http://otn.oracle.com/doc/windows/was.30/admdoc/docs/cart/pspatt.htm And here is a link to asktom.oracle.com that may help... http://asktom.oracle.com/pls/ask/f?p=4950:8:995447::NO::F4950_P8_DISPLAYID,F 4950_P8_CRITERIA:3570878994858,%7Bowa_pattern%7D Scripts to execute to create the package in the database. $ORACLE_HOME/rdbms/admin/pubpat.sql $ORACLE_HOME/rdbms/admin/privpat.sql -Original Message- Sent: Wednesday, August 14, 2002 9:19 AM To: Multiple recipients of list ORACLE-L Hi. We have a table in our data warehouse which keeps info about calls made. This table has a child table with some detailed information about parts of the call. There may be any number of parts within a call (1 to many) and every part has a status. MY developer wants to add a string field to the parent table which will concatinate all the statuses for all the parts within this call. For example if a call has 4 parts and their statuses are A,B,A and F, the value of that field will be ABFA. Then the developer will be able to query smalle parent table instead of a large child table in order to see how many calls had at least one part with status A or statuses A and F etc by using a INSTR (or SUBSTR) command. Would it be better (from performance/CPU standpoint) to add several separate fields: STATUS_A_CNT, STATUS_B_CNT (the list of status codes is fairly static) instead? There is something about this string that rubs me the wrong way, but I can't put my finger on it. Any thoughts? thank you Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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: Grabowy, Chris 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: what is wrong with this idea ...
With individual columns you save on the CPU cycles/time involved to parse the string and extract each character. Additionally, if you ever need to update this string, it would be tedious. IMO, its always better to have atomic data since one can then leverage the features of the DBMS. With Warm Regards, Manav. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 14, 2002 6:18 AM Hi. We have a table in our data warehouse which keeps info about calls made. This table has a child table with some detailed information about parts of the call. There may be any number of parts within a call (1 to many) and every part has a status. MY developer wants to add a string field to the parent table which will concatinate all the statuses for all the parts within this call. For example if a call has 4 parts and their statuses are A,B,A and F, the value of that field will be ABFA. Then the developer will be able to query smalle parent table instead of a large child table in order to see how many calls had at least one part with status A or statuses A and F etc by using a INSTR (or SUBSTR) command. Would it be better (from performance/CPU standpoint) to add several separate fields: STATUS_A_CNT, STATUS_B_CNT (the list of status codes is fairly static) instead? There is something about this string that rubs me the wrong way, but I can't put my finger on it. Any thoughts? thank you Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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: Manavendra Gupta 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: what is wrong with this idea ...
Title: RE: what is wrong with this idea ... Gene, You would be denormalizing to hopefully improve performance. The only way to tell is to test and test some more. Personally I don't see how putting the info in the parent table with either method will improve things. I bet updates and inserts will take longer. using a INSTR (or SUBSTR) command. That will be slow. A function based index MIGHT help but I doubt it. (the list of status codes is fairly static) If the status codes were chiseled in stone, I MIGHT consider doing something like that. But things always change and even 'stones' erode. Adding or even subtracting one status code could require updating a lot of forms, reports, and SQL statements each time. From a performance standpoint, it might be much better to tune what you already have. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Gurelei [SMTP:[EMAIL PROTECTED]] Hi. We have a table in our data warehouse which keeps info about calls made. This table has a child table with some detailed information about parts of the call. There may be any number of parts within a call (1 to many) and every part has a status. MY developer wants to add a string field to the parent table which will concatinate all the statuses for all the parts within this call. For example if a call has 4 parts and their statuses are A,B,A and F, the value of that field will be ABFA. Then the developer will be able to query smalle parent table instead of a large child table in order to see how many calls had at least one part with status A or statuses A and F etc by using a INSTR (or SUBSTR) command. Would it be better (from performance/CPU standpoint) to add several separate fields: STATUS_A_CNT, STATUS_B_CNT (the list of status codes is fairly static) instead? There is something about this string that rubs me the wrong way, but I can't put my finger on it. Any thoughts? thank you Gene
RE: what is wrong with this idea ...
Tom, The child table is not going to be updated ad-hoc. As I said this is a data warehouse and there is no ad-hoc updates. Only one load during the day and during this load that field is going to be populated. The thing I don't like about the procedure is that it will be doing a query against a large table every time the developer runs a query using that procedure. By loading that field(d) during the load we only do it once. --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Gene, You ask what's wrong with this? The main challenge I see is: how are you going to maintain this field? If a status gets updated in the child table, are you supposed to update the parent table? And to do that, you will need to requery the child table to get all the status's of all children records whenever one status changes. Pretty silly really, Why not do this - create a function that, given the PK of the parent table, queries all the child records and concatenates all the status values into one string. Then either create a view for the duhveloper to use to return what he wants to return at query time. Something like this: Create or replace function get_child_status(in_pk_column) return varchar2 is cursor c1 is select status_code from child_table where pk_column=in_pk_column; ret_string varchar2(10); begin for c1_rec in c1 loop ret_string := ret_string || c1_rec.status_code; end loop; return ret_string end; The duhveloper can then: select parent_pk_column, get_child_status(parent_pk_column) from parent_table; Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- From: Gurelei [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 14, 2002 9:19 AM To: Multiple recipients of list ORACLE-L Subject: what is wrong with this idea ... Hi. We have a table in our data warehouse which keeps info about calls made. This table has a child table with some detailed information about parts of the call. There may be any number of parts within a call (1 to many) and every part has a status. MY developer wants to add a string field to the parent table which will concatinate all the statuses for all the parts within this call. For example if a call has 4 parts and their statuses are A,B,A and F, the value of that field will be ABFA. Then the developer will be able to query smalle parent table instead of a large child table in order to see how many calls had at least one part with status A or statuses A and F etc by using a INSTR (or SUBSTR) command. Would it be better (from performance/CPU standpoint) to add several separate fields: STATUS_A_CNT, STATUS_B_CNT (the list of status codes is fairly static) instead? There is something about this string that rubs me the wrong way, but I can't put my finger on it. Any thoughts? thank you Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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: what is wrong with this idea ...
-- Gurelei [EMAIL PROTECTED] Hi. We have a table in our data warehouse which keeps info about calls made. This table has a child table with some detailed information about parts of the call. There may be any number of parts within a call (1 to many) and every part has a status. MY developer wants to add a string field to the parent table which will concatinate all the statuses for all the parts within this call. For example if a call has 4 parts and their statuses are A,B,A and F, the value of that field will be ABFA. Then the developer will be able to query smalle parent table instead of a large child table in order to see how many calls had at least one part with status A or statuses A and F etc by using a INSTR (or SUBSTR) command. Would it be better (from performance/CPU standpoint) to add several separate fields: STATUS_A_CNT, STATUS_B_CNT (the list of status codes is fairly static) instead? There is something about this string that rubs me the wrong way, but I can't put my finger on it. I would avoid composite fields at all costs. Performance is one reason: you have to perform a substr to get at the foreign key value. Function indexes can help there but properly normalizing the data will do a better job. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark 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: what is wrong with this idea ...
Gene - What is the motivation here? Are you trying to save space in a fact table with many, many rows? I agree that in data warehousing we sometimes violate the normal rules we adhere to in OLTP databases, and I think some of your responses have been assuming OLTP rules. For example, someone pointed out that updating might cause inconsistencies, etc. Well, in a data warehouse you usually write once, read many. You may never update the data, depending on the warehouse. Is your tradeoff between four separate columns or a single column with concatenated values? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 14, 2002 8:19 AM To: Multiple recipients of list ORACLE-L Hi. We have a table in our data warehouse which keeps info about calls made. This table has a child table with some detailed information about parts of the call. There may be any number of parts within a call (1 to many) and every part has a status. MY developer wants to add a string field to the parent table which will concatinate all the statuses for all the parts within this call. For example if a call has 4 parts and their statuses are A,B,A and F, the value of that field will be ABFA. Then the developer will be able to query smalle parent table instead of a large child table in order to see how many calls had at least one part with status A or statuses A and F etc by using a INSTR (or SUBSTR) command. Would it be better (from performance/CPU standpoint) to add several separate fields: STATUS_A_CNT, STATUS_B_CNT (the list of status codes is fairly static) instead? There is something about this string that rubs me the wrong way, but I can't put my finger on it. Any thoughts? thank you Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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: DENNIS WILLIAMS 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: what is wrong with this idea ...
Gene, you asked what was wrong and I think everyone gave you their opinion. you know by now that what you are told today will change next month. once you begin down this path, you will never stop. you know that as soon as you set this up, and they have written a ton of reports, that a new process will come along and will update the status field. then, you are stuck trying to update the parent record because it would be too much work for them to go back and fix their reports. don't give away the farm today if it will possibly cause you to purchase it back at twice the price later! rules that I always follow: 1). keep the design as clean as possible 2). *never* design database tables to make programmers lives easier, while violating rule #1. it's totally up to you. it sounds like your organization should entertain re-designing the warehouse for reporting needs. good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 14, 2002 10:24 AM To: Multiple recipients of list ORACLE-L Tom, The child table is not going to be updated ad-hoc. As I said this is a data warehouse and there is no ad-hoc updates. Only one load during the day and during this load that field is going to be populated. The thing I don't like about the procedure is that it will be doing a query against a large table every time the developer runs a query using that procedure. By loading that field(d) during the load we only do it once. --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Gene, You ask what's wrong with this? The main challenge I see is: how are you going to maintain this field? If a status gets updated in the child table, are you supposed to update the parent table? And to do that, you will need to requery the child table to get all the status's of all children records whenever one status changes. Pretty silly really, Why not do this - create a function that, given the PK of the parent table, queries all the child records and concatenates all the status values into one string. Then either create a view for the duhveloper to use to return what he wants to return at query time. Something like this: Create or replace function get_child_status(in_pk_column) return varchar2 is cursor c1 is select status_code from child_table where pk_column=in_pk_column; ret_string varchar2(10); begin for c1_rec in c1 loop ret_string := ret_string || c1_rec.status_code; end loop; return ret_string end; The duhveloper can then: select parent_pk_column, get_child_status(parent_pk_column) from parent_table; Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- From: Gurelei [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 14, 2002 9:19 AM To: Multiple recipients of list ORACLE-L Subject: what is wrong with this idea ... Hi. We have a table in our data warehouse which keeps info about calls made. This table has a child table with some detailed information about parts of the call. There may be any number of parts within a call (1 to many) and every part has a status. MY developer wants to add a string field to the parent table which will concatinate all the statuses for all the parts within this call. For example if a call has 4 parts and their statuses are A,B,A and F, the value of that field will be ABFA. Then the developer will be able to query smalle parent table instead of a large child table in order to see how many calls had at least one part with status A or statuses A and F etc by using a INSTR (or SUBSTR) command. Would it be better (from performance/CPU standpoint) to add several separate fields: STATUS_A_CNT, STATUS_B_CNT (the list of status codes is fairly static) instead? There is something about this string that rubs me the wrong way, but I can't put my finger on it. Any thoughts? thank you Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author
Re: what is wrong with this idea ...
Gene, One of the basic rules of third normal form is to not store multiple (non-atomic) values or logic inside a column like this. The reason is that you end up storing the same values more than once, wasting space, and you have to substr the value to get your logic out of it, which could prevent index lookups, resulting in potentially nasty table scans and bad performance. If you have a concatenated index on the FK and status columns of the child table, that status lookup should be rapid. With an index that contains all the columns to be looked up, your query might be totally resolved in the index itself and not need a table visit at all. An extension of this idea would be storing the child records on an IOT, with all low-usage columns in the overflow. This would help ensure that the developer could resolve any set of highly-used columns rapidly, with the downside of relatively slow lookups for low-usage columns. Research this one first, though, since IOT tables come with their own set of challenges. hth, Jack --- Gurelei [EMAIL PROTECTED] wrote: Hi. We have a table in our data warehouse which keeps info about calls made. This table has a child table with some detailed information about parts of the call. There may be any number of parts within a call (1 to many) and every part has a status. MY developer wants to add a string field to the parent table which will concatinate all the statuses for all the parts within this call. For example if a call has 4 parts and their statuses are A,B,A and F, the value of that field will be ABFA. Then the developer will be able to query smalle parent table instead of a large child table in order to see how many calls had at least one part with status A or statuses A and F etc by using a INSTR (or SUBSTR) command. Would it be better (from performance/CPU standpoint) to add several separate fields: STATUS_A_CNT, STATUS_B_CNT (the list of status codes is fairly static) instead? There is something about this string that rubs me the wrong way, but I can't put my finger on it. Any thoughts? thank you Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey 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:RE: what is wrong with this idea ...
I agree with Tom, from a practical point of view. Some 2 years ago this young whippersnapper came on board to design a data warehouse for us. One of his ideas was to concatenate a bunch of columns from the operational data into one column in the warehouse, store it as a separate table with an ID and foreign key it into the fact table. Well guess what we're breaking back up! Reason, you can't put all of the variables inside the mess he created. BTW, he did not just concatenate single characters, but strings, like 'High Temp'||'Nominal input voltage'||'Nominal Load'||.. Ad nauseum Dick Goulet Reply Separator Author: Mercadante; Thomas F [EMAIL PROTECTED] Date: 8/14/2002 7:28 AM Gene, you asked what was wrong and I think everyone gave you their opinion. you know by now that what you are told today will change next month. once you begin down this path, you will never stop. you know that as soon as you set this up, and they have written a ton of reports, that a new process will come along and will update the status field. then, you are stuck trying to update the parent record because it would be too much work for them to go back and fix their reports. don't give away the farm today if it will possibly cause you to purchase it back at twice the price later! rules that I always follow: 1). keep the design as clean as possible 2). *never* design database tables to make programmers lives easier, while violating rule #1. it's totally up to you. it sounds like your organization should entertain re-designing the warehouse for reporting needs. good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 14, 2002 10:24 AM To: Multiple recipients of list ORACLE-L Tom, The child table is not going to be updated ad-hoc. As I said this is a data warehouse and there is no ad-hoc updates. Only one load during the day and during this load that field is going to be populated. The thing I don't like about the procedure is that it will be doing a query against a large table every time the developer runs a query using that procedure. By loading that field(d) during the load we only do it once. --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Gene, You ask what's wrong with this? The main challenge I see is: how are you going to maintain this field? If a status gets updated in the child table, are you supposed to update the parent table? And to do that, you will need to requery the child table to get all the status's of all children records whenever one status changes. Pretty silly really, Why not do this - create a function that, given the PK of the parent table, queries all the child records and concatenates all the status values into one string. Then either create a view for the duhveloper to use to return what he wants to return at query time. Something like this: Create or replace function get_child_status(in_pk_column) return varchar2 is cursor c1 is select status_code from child_table where pk_column=in_pk_column; ret_string varchar2(10); begin for c1_rec in c1 loop ret_string := ret_string || c1_rec.status_code; end loop; return ret_string end; The duhveloper can then: select parent_pk_column, get_child_status(parent_pk_column) from parent_table; Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- From: Gurelei [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 14, 2002 9:19 AM To: Multiple recipients of list ORACLE-L Subject: what is wrong with this idea ... Hi. We have a table in our data warehouse which keeps info about calls made. This table has a child table with some detailed information about parts of the call. There may be any number of parts within a call (1 to many) and every part has a status. MY developer wants to add a string field to the parent table which will concatinate all the statuses for all the parts within this call. For example if a call has 4 parts and their statuses are A,B,A and F, the value of that field will be ABFA. Then the developer will be able to query smalle parent table instead of a large child table in order to see how many calls had at least one part with status A or statuses A and F etc by using a INSTR (or SUBSTR) command. Would it be better (from performance/CPU standpoint) to add several separate fields: STATUS_A_CNT, STATUS_B_CNT (the list of status codes is fairly static) instead? There is something about this string that rubs me the wrong way, but I can't put my finger on it. Any thoughts? thank you Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City
RE: what is wrong with this idea ...
Dennis, Yes, at this moment I'm debating whether to go with one combined string or several separate fields. I'm not thrilled by any of the options, but the former really doesn't look good to me. What I'm trying to avoid is every transaction having to run a query against a large (tens of millions rows) child table in order to count the statuses. Another - totally different - option would be to create an aggregate (I think someone did mention that), but I haven't had much sucess designing an appropriate aggregate yet Gene --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Is your tradeoff between four separate columns or a single column with concatenated values? __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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: what is wrong with this idea ...
I'd prefer to build a normalized ODS structure and then build any denormalized structures on top of it. Thus, I'd keep the Parent to Child relationship and put each status in it's own row, with a temporal time stamp. If you need some denormalized view of that (because of performance) then I'd build on top of it whatever structure (e.g. snowflake) you need. Keep the underlying data store as normalized as you can. It makes detail analysis and drill down much easier IMHO. HTH RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author Oracle9i RMAN Backup and Recovery (Oracle Press - Oct 2002) Oracle9i New Features (Oracle Press) Mastering Oracle8i (Sybex) The avalanche has begun, It is too late for the pebbles to vote. -Original Message- Sent: Wednesday, August 14, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Gene - What is the motivation here? Are you trying to save space in a fact table with many, many rows? I agree that in data warehousing we sometimes violate the normal rules we adhere to in OLTP databases, and I think some of your responses have been assuming OLTP rules. For example, someone pointed out that updating might cause inconsistencies, etc. Well, in a data warehouse you usually write once, read many. You may never update the data, depending on the warehouse. Is your tradeoff between four separate columns or a single column with concatenated values? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 14, 2002 8:19 AM To: Multiple recipients of list ORACLE-L Hi. We have a table in our data warehouse which keeps info about calls made. This table has a child table with some detailed information about parts of the call. There may be any number of parts within a call (1 to many) and every part has a status. MY developer wants to add a string field to the parent table which will concatinate all the statuses for all the parts within this call. For example if a call has 4 parts and their statuses are A,B,A and F, the value of that field will be ABFA. Then the developer will be able to query smalle parent table instead of a large child table in order to see how many calls had at least one part with status A or statuses A and F etc by using a INSTR (or SUBSTR) command. Would it be better (from performance/CPU standpoint) to add several separate fields: STATUS_A_CNT, STATUS_B_CNT (the list of status codes is fairly static) instead? There is something about this string that rubs me the wrong way, but I can't put my finger on it. Any thoughts? thank you Gene __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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: DENNIS WILLIAMS 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: Freeman, Robert 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: what is wrong with this idea ...
Title: RE: what is wrong with this idea ... If you are using 8i+ consider using materl. views. They are wonderful and make maintenance of denormalized objects much less of a chore. I am oh so happy with them. There are some limitations but still. -Original Message- From: Gurelei [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 14, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Subject: RE: what is wrong with this idea ... Dennis, Yes, at this moment I'm debating whether to go with one combined string or several separate fields. I'm not thrilled by any of the options, but the former really doesn't look good to me. What I'm trying to avoid is every transaction having to run a query against a large (tens of millions rows) child table in order to count the statuses. Another - totally different - option would be to create an aggregate (I think someone did mention that), but I haven't had much sucess designing an appropriate aggregate yet Gene --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Is your tradeoff between four separate columns or a single column with concatenated values? __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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).
what is wrong with this export?
Hi friends, Iam exporting some of tables, but keep getting error!! Could you help.. Mine is AIX415 with oracle4.3, If I want import an individual table, what is correct form of parameters? exp user/passwd tables = \ ( table1, tab2, tab3, tab40,tab5,ttdsls051061,ttdsls051110,ttdsls051120,ttdsls051130, ttdinv700040,ttdinv700061,ttdinv700110,ttdinv700120,ttdinv700130, ttfgld410210,ttfgld421010,ttfgld410060,ttfgld100010,ttfgld106060, ttfgld106100\ ) COMPRESS=N direct=Y log=fullsls.log file=fullsls.dmp Thanks in advance Raghu. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raghu Kota 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: what is wrong with this export?
I'm not working on AIX, but I use syntax exp user/passwd tables=(TABLE1,TABLE2) ... etc. Why do you use \( and \) ? JP On Tue 30. April 2002 21:38, you wrote: Hi friends, Iam exporting some of tables, but keep getting error!! Could you help.. Mine is AIX415 with oracle4.3, If I want import an individual table, what is correct form of parameters? exp user/passwd tables = \ ( table1, tab2, tab3, tab40,tab5,ttdsls051061,ttdsls051110,ttdsls051120,ttdsls051130, ttdinv700040,ttdinv700061,ttdinv700110,ttdinv700120,ttdinv700130, ttfgld410210,ttfgld421010,ttfgld410060,ttfgld100010,ttfgld106060, ttfgld106100\ ) COMPRESS=N direct=Y log=fullsls.log file=fullsls.dmp Thanks in advance Raghu. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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: what is wrong with this export?
Raghu - I notice that your command string extends over several lines. Perhaps you should consider putting your export parameters into a parameter file. I can send you an example if that would help. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, April 30, 2002 2:39 PM To: Multiple recipients of list ORACLE-L Hi friends, Iam exporting some of tables, but keep getting error!! Could you help.. Mine is AIX415 with oracle4.3, If I want import an individual table, what is correct form of parameters? exp user/passwd tables = \ ( table1, tab2, tab3, tab40,tab5,ttdsls051061,ttdsls051110,ttdsls051120,ttdsls051130, ttdinv700040,ttdinv700061,ttdinv700110,ttdinv700120,ttdinv700130, ttfgld410210,ttfgld421010,ttfgld410060,ttfgld100010,ttfgld106060, ttfgld106100\ ) COMPRESS=N direct=Y log=fullsls.log file=fullsls.dmp Thanks in advance Raghu. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raghu Kota 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: DENNIS WILLIAMS 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: what is wrong with this export?
Raghu Still do you want to send your export dmp to tape or on disk. Much better to study Oracle Utilities Manual for complete understanding. For short type exp (or imp) help=y enter on unix prompt and you will find all options available with your oracle release. To export on tape (for 7.3.4) exp user/passwd file=/dev/tape tables=(table1,table2,table3) compress=n direct=y enter on disk: exp user/passwd file=/tmp/raghu.dmp tables=(table1,table2,table3) compress=n direct=y Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 30 Apr 2002 11:53:44 -0800 I'm not working on AIX, but I use syntax exp user/passwd tables=(TABLE1,TABLE2) ... etc. Why do you use \( and \) ? JP On Tue 30. April 2002 21:38, you wrote: Hi friends, Iam exporting some of tables, but keep getting error!! Could you help.. Mine is AIX415 with oracle4.3, If I want import an individual table, what is correct form of parameters? exp user/passwd tables = \ ( table1, tab2, tab3, tab40,tab5,ttdsls051061,ttdsls051110,ttdsls051120,ttdsls051130, ttdinv700040,ttdinv700061,ttdinv700110,ttdinv700120,ttdinv700130, ttfgld410210,ttfgld421010,ttfgld410060,ttfgld100010,ttfgld106060, ttfgld106100\ ) COMPRESS=N direct=Y log=fullsls.log file=fullsls.dmp Thanks in advance Raghu. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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). MOHAMMAD RAFIQ _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq 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: what is wrong with this export?
Basing it on my Solaris experience the \ is a control character so the system will treat the ( and ) as part of the export command. But doesn't it need to be immediate before the ( and )? I.e., \( instead of \ (? What is the error you're getting? -Original Message- Sent: Tuesday, April 30, 2002 3:54 PM To: Multiple recipients of list ORACLE-L I'm not working on AIX, but I use syntax exp user/passwd tables=(TABLE1,TABLE2) ... etc. Why do you use \( and \) ? JP On Tue 30. April 2002 21:38, you wrote: Hi friends, Iam exporting some of tables, but keep getting error!! Could you help.. Mine is AIX415 with oracle4.3, If I want import an individual table, what is correct form of parameters? exp user/passwd tables = \ ( table1, tab2, tab3, tab40,tab5,ttdsls051061,ttdsls051110,ttdsls051120,ttdsls051130, ttdinv700040,ttdinv700061,ttdinv700110,ttdinv700120,ttdinv700130, ttfgld410210,ttfgld421010,ttfgld410060,ttfgld100010,ttfgld106060, ttfgld106100\ ) COMPRESS=N direct=Y log=fullsls.log file=fullsls.dmp Thanks in advance Raghu. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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: Miller, Jay 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: what is wrong with this export?
Just run like this. It will ask for password. Assuming you are running it manually. Make sure you have ORACLE_SID set correctly. exp user FULL=N tables = ( table1, tab2, tab3, tab40,tab5,ttdsls051061,ttdsls051110,ttdsls051120,ttdsls051130, ttdinv700040,ttdinv700061,ttdinv700110,ttdinv700120,ttdinv700130, ttfgld410210,ttfgld421010,ttfgld410060,ttfgld100010,ttfgld106060, ttfgld106100\ ) COMPRESS=N direct=Y log=fullsls.log file=fullsls.dmp -Original Message- Sent: Tuesday, April 30, 2002 4:28 PM To: Multiple recipients of list ORACLE-L Basing it on my Solaris experience the \ is a control character so the system will treat the ( and ) as part of the export command. But doesn't it need to be immediate before the ( and )? I.e., \( instead of \ (? What is the error you're getting? -Original Message- Sent: Tuesday, April 30, 2002 3:54 PM To: Multiple recipients of list ORACLE-L I'm not working on AIX, but I use syntax exp user/passwd tables=(TABLE1,TABLE2) ... etc. Why do you use \( and \) ? JP On Tue 30. April 2002 21:38, you wrote: Hi friends, Iam exporting some of tables, but keep getting error!! Could you help.. Mine is AIX415 with oracle4.3, If I want import an individual table, what is correct form of parameters? exp user/passwd tables = \ ( table1, tab2, tab3, tab40,tab5,ttdsls051061,ttdsls051110,ttdsls051120,ttdsls051130, ttdinv700040,ttdinv700061,ttdinv700110,ttdinv700120,ttdinv700130, ttfgld410210,ttfgld421010,ttfgld410060,ttfgld100010,ttfgld106060, ttfgld106100\ ) COMPRESS=N direct=Y log=fullsls.log file=fullsls.dmp Thanks in advance Raghu. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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: Miller, Jay 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: Wong, Bing 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: what is wrong with this export?
Does Oracle 4.3 have export utility? -Original Message- Sent: Tuesday, April 30, 2002 3:39 PM To: Multiple recipients of list ORACLE-L Hi friends, Iam exporting some of tables, but keep getting error!! Could you help.. Mine is AIX415 with oracle4.3, If I want import an individual table, what is correct form of parameters? exp user/passwd tables = \ ( table1, tab2, tab3, tab40,tab5,ttdsls051061,ttdsls051110,ttdsls051120,ttdsls051130, ttdinv700040,ttdinv700061,ttdinv700110,ttdinv700120,ttdinv700130, ttfgld410210,ttfgld421010,ttfgld410060,ttfgld100010,ttfgld106060, ttfgld106100\ ) COMPRESS=N direct=Y log=fullsls.log file=fullsls.dmp Thanks in advance Raghu. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raghu Kota 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: Khedr, Waleed 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).
what is wrong with this timestamp??
set timing on; SELECT SUM(orig_transac_amt), COUNT(1) FROM dsg.ar_open_items WHERE timestamp_date between '06-sep-01 21:22:34' and '06-sep-01 22:01:57' AND user_id_no = 0; Ora-1830?? Thanks Raghu. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raghu Kota 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: what is wrong with this timestamp??
Off hand, I'd say you either need to convert the strings to dates or the dates to strings ( to_date/to_char ) -Original Message- Sent: Wednesday, September 12, 2001 2:25 PM To: Multiple recipients of list ORACLE-L set timing on; SELECT SUM(orig_transac_amt), COUNT(1) FROM dsg.ar_open_items WHERE timestamp_date between '06-sep-01 21:22:34' and '06-sep-01 22:01:57' AND user_id_no = 0; Ora-1830?? Thanks Raghu. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raghu Kota 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: John Lewis 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: what is wrong with this timestamp??
First guess you need to use the to_date conversion on your string
(assuming your field timestamp_date is defined as a date.
set timing on;
SELECT SUM(orig_transac_amt), COUNT(1)
FROM dsg.ar_open_items
WHERE timestamp_date between to_date('06-sep-01 21:22:34','dd-mon-yy
hh24:hi:ss') and to_date('06-sep-01 22:01:57','dd-mon-yy hh24:mi:ss')
AND user_id_no = 0;
-Original Message-
Sent: Wednesday, September 12, 2001 4:25 PM
To: Multiple recipients of list ORACLE-L
set timing on;
SELECT SUM(orig_transac_amt),
COUNT(1)
FROM dsg.ar_open_items
WHERE timestamp_date between '06-sep-01 21:22:34'
and '06-sep-01 22:01:57'
AND user_id_no = 0;
Ora-1830??
Thanks
Raghu.
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raghu Kota
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: Kevin Lange
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: what is wrong with this timestamp??
Thank you nick!!!Its great help.
From: Nick Wagner [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: what is wrong with this timestamp??
Date: Wed, 12 Sep 2001 13:45:18 -0800
try this where clause
WHERE timestamp_date between to_date('06-sep-01 21:22:34','DD-mon-YY
HH24:MI:SS')
and to_date('06-sep-01 22:01:57','DD-mon-YY HH24:MI:SS')
AND user_id_no = 0;
Nick
-Original Message-
Sent: Wednesday, September 12, 2001 2:25 PM
To: Multiple recipients of list ORACLE-L
set timing on;
SELECT SUM(orig_transac_amt),
COUNT(1)
FROM dsg.ar_open_items
WHERE timestamp_date between '06-sep-01 21:22:34'
and '06-sep-01 22:01:57'
AND user_id_no = 0;
Ora-1830??
Thanks
Raghu.
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raghu Kota
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).
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raghu Kota
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).
what is wrong in query
Title: RE: STOP THE PRESS!!
Hi,
I have a table t_acc_usage having column tx_batch defined as
raw(16)..
when i am running the following code i am getting error:
SELECT ( (SELECT count(usage.id_sess) from t_acc_usage usage
WHERE usage.tx_batch = hex_to_raw (ltrim('%%VARBIN_BATCH_ID%%', '0x')) +
( SELECT count(error.id_sess)
FROM t_pv_error error WHERE error.c_tx_batch = N'%%STRING_BATCH_ID%%')) ) AS
committed_count FROM dual
WHERE usage.tx_batch = hex_to_raw (ltrim('%%VARBIN_BATCH_ID%%', '0x')) +
*
ERROR at line 2:
ORA-00932: inconsistent datatypes
what is wrong in query..
Thanks
-Harvinder
what is wrong in query
Hi,
I am trying to run following query thru ODBC and getting error:
09/06/01 11:10:22 [DBAccess][ERROR] Database Execute() failed. Error
Description = [Microsoft][ODBC driver for Oracle][Oracle]ORA-00920: invalid
relational operator
Which relational operator it is referring?.
select DISTINCT(t_po.id_po), t_po.id_eff_date,
t_po.id_avail, t_po.b_user_subscribe, t_po.b_user_unsubscribe,
t_base_props.n_name,
t_base_props.n_desc,t_base_props.n_display_name,t_base_props.nm_name,
t_base_props.nm_desc,
t_base_props.nm_display_name,te.n_begintype as
te_n_begintype, te.dt_start as te_dt_start, te.n_beginoffset
as te_n_beginoffset,te.n_endtype as
te_n_endtype, te.dt_end as te_dt_end, te.n_endoffset as te_n_endoffset,
ta.n_begintype as ta_n_begintype,
ta.dt_start as ta_dt_start, ta.n_beginoffset as ta_n_beginoffset,
ta.n_endtype as ta_n_endtype, ta.dt_end as
ta_dt_end, ta.n_endoffset as ta_n_endoffset,
decode(sign((select count(id_pi_type) from
t_pl_map,t_base_props tb where tb.id_prop = t_pl_map.id_pi_type AND
tb.n_kind = 20 and t_po.id_po =
t_pl_map.id_po)),1,'Y','N') as b_RecurringCharge
,t_ep_po.c_ExternalInformationURL
t_ep__c_ExternalInformationURL,t_ep_po.c_glcode
t_ep_po_c_glcode,t_ep_po.c_InternalInformationURL
t_ep__c_InternalInformationURL
from t_av_internal tav,t_effectivedate
te,t_effectivedate ta,t_base_props,t_pricelist,
t_base_props
template_base,t_acc_usage_cycle,t_usage_cycle,(select GetUTCDate() now from
dual) cdate,
t_pl_map,t_recur,t_discount,t_aggregate
where
{oj t_po LEFT OUTER JOIN t_ep_po on
t_ep_po.id_prop = t_po.id_po},
t_recur.id_prop = t_pl_map.id_pi_template(+)
and
t_discount.id_prop = t_pl_map.id_pi_template
and
t_aggregate.id_prop =
t_pl_map.id_pi_template and
t_pl_map.id_po = t_po.id_po AND
t_pl_map.id_paramtable is not NULL AND t_pl_map.id_sub is NULL AND
tav.id_acc = 136 AND
t_pricelist.id_pricelist =
t_pl_map.id_pricelist AND tav.c_currency = t_pricelist.nm_currency_code
AND
te.id_eff_date = t_po.id_eff_date AND
ta.id_eff_date = t_po.id_avail AND
t_base_props.id_prop = t_po.id_po AND
template_base.id_prop =
t_pl_map.id_pi_template AND
t_po.id_po not in
(select id_po from t_sub,t_effectivedate
tesub where id_acc = 136 AND t_sub.id_eff_date =tesub.id_eff_date AND
(tesub.dt_end is NULL AND tesub.dt_start =
cdate.now))
AND
((ta.dt_start = cdate.now or ta.dt_start is
null) AND (cdate.now = ta.dt_end or ta.dt_end is null)) AND
t_acc_usage_cycle.id_acc = 136 AND
t_usage_cycle.id_usage_cycle =
t_acc_usage_cycle.id_usage_cycle AND
(t_recur.id_cycle_type is null or
t_recur.id_cycle_type = t_usage_cycle.id_cycle_type) AND
(t_discount.id_cycle_type is null or
t_discount.id_cycle_type = t_usage_cycle.id_cycle_type) AND
(t_aggregate.id_cycle_type is null or
t_aggregate.id_cycle_type = t_usage_cycle.id_cycle_type) AND
te.n_begintype 0 AND ta.n_begintype 0
Thanks
-Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: what is wrong in query
I haven't seen LEFT OUTER JOIN as a valid oracle operator maybe the tool you are using isn't aware of Oracle ?? Also the outer join condition is in curly braces ... I don't think that is legal in oracle as well. Maybe the stuff in curly braces if left as an exercise for the developer by the toll you are using. The easiest way to find exact location of error is run it in SQLPLUS ... it will show you where the error is. Good luck Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra 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: what is wrong in query
Oh my god, man!?!
Did you write this chaos yourself??
Try this:
Oracle's JDBC drivers do not support outer join syntax: {oj outer-join}. The
workaround is to use Oracle outer join syntax:
Instead of:
Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery
(SELECT ename, dname
FROM {OJ dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno}
ORDER BY ename);
Use Oracle SQL syntax:
Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery
(SELECT ename, dname
FROM emp a, dept b WHERE a.deptno = b.deptno(+)
ORDER BY ename);
Jan Pruner
Dne ?t 6. zá?í 2001 19:46 jste napsal(a):
Hi,
I am trying to run following query thru ODBC and getting error:
09/06/01 11:10:22 [DBAccess][ERROR] Database Execute() failed. Error
Description = [Microsoft][ODBC driver for Oracle][Oracle]ORA-00920: invalid
relational operator
Which relational operator it is referring?.
select DISTINCT(t_po.id_po), t_po.id_eff_date,
t_po.id_avail,t_po.b_user_subscribe, t_po.b_user_unsubscribe,
t_base_props.n_name,
t_base_props.n_desc,t_base_props.n_display_name,t_base_props.nm_name,
t_base_props.nm_desc,
t_base_props.nm_display_name,te.n_begintype as
te_n_begintype, te.dt_start as te_dt_start, te.n_beginoffset
as te_n_beginoffset,te.n_endtype as
te_n_endtype, te.dt_end as te_dt_end, te.n_endoffset as te_n_endoffset,
ta.n_begintype as ta_n_begintype,
ta.dt_start as ta_dt_start, ta.n_beginoffset as ta_n_beginoffset,
ta.n_endtype as ta_n_endtype, ta.dt_end as
ta_dt_end, ta.n_endoffset as ta_n_endoffset,
decode(sign((select count(id_pi_type) from
t_pl_map,t_base_props tb where tb.id_prop = t_pl_map.id_pi_type AND
tb.n_kind = 20 and t_po.id_po =
t_pl_map.id_po)),1,'Y','N') as b_RecurringCharge
,t_ep_po.c_ExternalInformationURL
t_ep__c_ExternalInformationURL,t_ep_po.c_glcode
t_ep_po_c_glcode,t_ep_po.c_InternalInformationURL
t_ep__c_InternalInformationURL
from t_av_internal tav,t_effectivedate
te,t_effectivedate ta,t_base_props,t_pricelist,
t_base_props
template_base,t_acc_usage_cycle,t_usage_cycle,(select GetUTCDate() now from
dual) cdate,
t_pl_map,t_recur,t_discount,t_aggregate
where
{oj t_po LEFT OUTER JOIN t_ep_po on
t_ep_po.id_prop = t_po.id_po},
t_recur.id_prop = t_pl_map.id_pi_template(+)
and
t_discount.id_prop = t_pl_map.id_pi_template
and
t_aggregate.id_prop =
t_pl_map.id_pi_template and
t_pl_map.id_po = t_po.id_po AND
t_pl_map.id_paramtable is not NULL AND t_pl_map.id_sub is NULL AND
tav.id_acc = 136 AND
t_pricelist.id_pricelist =
t_pl_map.id_pricelist AND tav.c_currency = t_pricelist.nm_currency_code
AND
te.id_eff_date = t_po.id_eff_date AND
ta.id_eff_date = t_po.id_avail AND
t_base_props.id_prop = t_po.id_po AND
template_base.id_prop =
t_pl_map.id_pi_template AND
t_po.id_po not in
(select id_po from t_sub,t_effectivedate
tesub where id_acc = 136 AND t_sub.id_eff_date =tesub.id_eff_date AND
(tesub.dt_end is NULL AND tesub.dt_start =
cdate.now))
AND
((ta.dt_start = cdate.now or ta.dt_start is
null) AND (cdate.now = ta.dt_end or ta.dt_end is null)) AND
t_acc_usage_cycle.id_acc = 136 AND
t_usage_cycle.id_usage_cycle =
t_acc_usage_cycle.id_usage_cycle AND
(t_recur.id_cycle_type is null or
t_recur.id_cycle_type = t_usage_cycle.id_cycle_type) AND
(t_discount.id_cycle_type is null or
t_discount.id_cycle_type = t_usage_cycle.id_cycle_type) AND
(t_aggregate.id_cycle_type is null or
t_aggregate.id_cycle_type = t_usage_cycle.id_cycle_type) AND
te.n_begintype 0 AND ta.n_begintype 0
Thanks
-Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To
Re: what is wrong in query
Well, I don't believe this:
{oj t_po LEFT OUTER JOIN t_ep_po on t_ep_po.id_prop = t_po.id_po}
is valid in Oracle, unless there has been some drastic change I wasn't
informed of.
At 09:46 AM 9/6/01 -0800, you wrote:
Hi,
I am trying to run following query thru ODBC and getting error:
09/06/01 11:10:22 [DBAccess][ERROR] Database Execute() failed. Error
Description = [Microsoft][ODBC driver for Oracle][Oracle]ORA-00920: invalid
relational operator
Which relational operator it is referring?.
select DISTINCT(t_po.id_po), t_po.id_eff_date,
t_po.id_avail, t_po.b_user_subscribe, t_po.b_user_unsubscribe,
t_base_props.n_name,
t_base_props.n_desc,t_base_props.n_display_name,t_base_props.nm_name,
t_base_props.nm_desc,
t_base_props.nm_display_name,te.n_begintype as
te_n_begintype, te.dt_start as te_dt_start, te.n_beginoffset
as te_n_beginoffset,te.n_endtype as
te_n_endtype, te.dt_end as te_dt_end, te.n_endoffset as te_n_endoffset,
ta.n_begintype as ta_n_begintype,
ta.dt_start as ta_dt_start, ta.n_beginoffset as ta_n_beginoffset,
ta.n_endtype as ta_n_endtype, ta.dt_end as
ta_dt_end, ta.n_endoffset as ta_n_endoffset,
decode(sign((select count(id_pi_type) from
t_pl_map,t_base_props tb where tb.id_prop = t_pl_map.id_pi_type AND
tb.n_kind = 20 and t_po.id_po =
t_pl_map.id_po)),1,'Y','N') as b_RecurringCharge
,t_ep_po.c_ExternalInformationURL
t_ep__c_ExternalInformationURL,t_ep_po.c_glcode
t_ep_po_c_glcode,t_ep_po.c_InternalInformationURL
t_ep__c_InternalInformationURL
from t_av_internal tav,t_effectivedate
te,t_effectivedate ta,t_base_props,t_pricelist,
t_base_props
template_base,t_acc_usage_cycle,t_usage_cycle,(select GetUTCDate() now from
dual) cdate,
t_pl_map,t_recur,t_discount,t_aggregate
where
{oj t_po LEFT OUTER JOIN t_ep_po on
t_ep_po.id_prop = t_po.id_po},
t_recur.id_prop = t_pl_map.id_pi_template(+)
and
t_discount.id_prop = t_pl_map.id_pi_template
and
t_aggregate.id_prop =
t_pl_map.id_pi_template and
t_pl_map.id_po = t_po.id_po AND
t_pl_map.id_paramtable is not NULL AND t_pl_map.id_sub is NULL AND
tav.id_acc = 136 AND
t_pricelist.id_pricelist =
t_pl_map.id_pricelist AND tav.c_currency = t_pricelist.nm_currency_code
AND
te.id_eff_date = t_po.id_eff_date AND
ta.id_eff_date = t_po.id_avail AND
t_base_props.id_prop = t_po.id_po AND
template_base.id_prop =
t_pl_map.id_pi_template AND
t_po.id_po not in
(select id_po from t_sub,t_effectivedate
tesub where id_acc = 136 AND t_sub.id_eff_date =tesub.id_eff_date AND
(tesub.dt_end is NULL AND tesub.dt_start =
cdate.now))
AND
((ta.dt_start = cdate.now or ta.dt_start is
null) AND (cdate.now = ta.dt_end or ta.dt_end is null)) AND
t_acc_usage_cycle.id_acc = 136 AND
t_usage_cycle.id_usage_cycle =
t_acc_usage_cycle.id_usage_cycle AND
(t_recur.id_cycle_type is null or
t_recur.id_cycle_type = t_usage_cycle.id_cycle_type) AND
(t_discount.id_cycle_type is null or
t_discount.id_cycle_type = t_usage_cycle.id_cycle_type) AND
(t_aggregate.id_cycle_type is null or
t_aggregate.id_cycle_type = t_usage_cycle.id_cycle_type) AND
te.n_begintype 0 AND ta.n_begintype 0
Thanks
-Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Regina Harter
INET: [EMAIL PROTECTED]
Fat City Network
RE: what is wrong in query
we can use this kind of left outer join syntax thru odbc...our other queries works well with this syntax.. -Original Message- Sent: Thursday, September 06, 2001 2:07 PM To: Multiple recipients of list ORACLE-L I haven't seen LEFT OUTER JOIN as a valid oracle operator maybe the tool you are using isn't aware of Oracle ?? Also the outer join condition is in curly braces ... I don't think that is legal in oracle as well. Maybe the stuff in curly braces if left as an exercise for the developer by the toll you are using. The easiest way to find exact location of error is run it in SQLPLUS ... it will show you where the error is. Good luck Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra 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: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what is wrong in query
if need to support both sqlserver and oracle this syntax is daily
routine...
enjoy
-H
-Original Message-
Sent: Thursday, September 06, 2001 2:16 PM
To: Multiple recipients of list ORACLE-L
Well, I don't believe this:
{oj t_po LEFT OUTER JOIN t_ep_po on t_ep_po.id_prop = t_po.id_po}
is valid in Oracle, unless there has been some drastic change I wasn't
informed of.
At 09:46 AM 9/6/01 -0800, you wrote:
Hi,
I am trying to run following query thru ODBC and getting error:
09/06/01 11:10:22 [DBAccess][ERROR] Database Execute() failed. Error
Description = [Microsoft][ODBC driver for Oracle][Oracle]ORA-00920: invalid
relational operator
Which relational operator it is referring?.
select DISTINCT(t_po.id_po), t_po.id_eff_date,
t_po.id_avail, t_po.b_user_subscribe, t_po.b_user_unsubscribe,
t_base_props.n_name,
t_base_props.n_desc,t_base_props.n_display_name,t_base_props.nm_name,
t_base_props.nm_desc,
t_base_props.nm_display_name,te.n_begintype as
te_n_begintype, te.dt_start as te_dt_start, te.n_beginoffset
as te_n_beginoffset,te.n_endtype as
te_n_endtype, te.dt_end as te_dt_end, te.n_endoffset as te_n_endoffset,
ta.n_begintype as ta_n_begintype,
ta.dt_start as ta_dt_start, ta.n_beginoffset as ta_n_beginoffset,
ta.n_endtype as ta_n_endtype, ta.dt_end as
ta_dt_end, ta.n_endoffset as ta_n_endoffset,
decode(sign((select count(id_pi_type) from
t_pl_map,t_base_props tb where tb.id_prop = t_pl_map.id_pi_type AND
tb.n_kind = 20 and t_po.id_po =
t_pl_map.id_po)),1,'Y','N') as b_RecurringCharge
,t_ep_po.c_ExternalInformationURL
t_ep__c_ExternalInformationURL,t_ep_po.c_glcode
t_ep_po_c_glcode,t_ep_po.c_InternalInformationURL
t_ep__c_InternalInformationURL
from t_av_internal tav,t_effectivedate
te,t_effectivedate ta,t_base_props,t_pricelist,
t_base_props
template_base,t_acc_usage_cycle,t_usage_cycle,(select GetUTCDate() now from
dual) cdate,
t_pl_map,t_recur,t_discount,t_aggregate
where
{oj t_po LEFT OUTER JOIN t_ep_po on
t_ep_po.id_prop = t_po.id_po},
t_recur.id_prop =
t_pl_map.id_pi_template(+)
and
t_discount.id_prop =
t_pl_map.id_pi_template
and
t_aggregate.id_prop =
t_pl_map.id_pi_template and
t_pl_map.id_po = t_po.id_po AND
t_pl_map.id_paramtable is not NULL AND t_pl_map.id_sub is NULL AND
tav.id_acc = 136 AND
t_pricelist.id_pricelist =
t_pl_map.id_pricelist AND tav.c_currency = t_pricelist.nm_currency_code
AND
te.id_eff_date = t_po.id_eff_date AND
ta.id_eff_date = t_po.id_avail AND
t_base_props.id_prop = t_po.id_po AND
template_base.id_prop =
t_pl_map.id_pi_template AND
t_po.id_po not in
(select id_po from t_sub,t_effectivedate
tesub where id_acc = 136 AND t_sub.id_eff_date =tesub.id_eff_date AND
(tesub.dt_end is NULL AND tesub.dt_start
=
cdate.now))
AND
((ta.dt_start = cdate.now or ta.dt_start
is
null) AND (cdate.now = ta.dt_end or ta.dt_end is null)) AND
t_acc_usage_cycle.id_acc = 136 AND
t_usage_cycle.id_usage_cycle =
t_acc_usage_cycle.id_usage_cycle AND
(t_recur.id_cycle_type is null or
t_recur.id_cycle_type = t_usage_cycle.id_cycle_type) AND
(t_discount.id_cycle_type is null or
t_discount.id_cycle_type = t_usage_cycle.id_cycle_type) AND
(t_aggregate.id_cycle_type is null or
t_aggregate.id_cycle_type = t_usage_cycle.id_cycle_type) AND
te.n_begintype 0 AND ta.n_begintype
0
Thanks
-Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You
RE: what is wrong with this procedure
temp_name should be %rowtype variable;
-Original Message-
Sent: Saturday, June 02, 2001 2:18 AM
To: Multiple recipients of list ORACLE-L
create or replace procedure remove_bucket
as
cursor c1 is
select table_name from (
select table_name from user_constraints
where r_constraint_name ='PK_T_ACC_USAGE_1');
temp_name varchar2(255);
temp_count number(10);
str varchar2(2000);
begin
open c1;
loop
fetch c1 into temp_name;
exit when c1%notfound;
select count(*) into temp_count from temp_name;
dbms_output.put_line(temp_count);
end loop;
close c1;
EXCEPTION
WHEN OTHERS
THEN DBMS_OUTPUT.PUT_LINE('INVALID SYNTAX');
end;
/
I am geeting following error:
15/1 PL/SQL: SQL Statement ignored
15/38PLS-00201: identifier 'TEMP_NAME' must be declared
what is the possible solution...
Thanks
Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Shahid Malik(IT)
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).
