Re: how to specify tablespace in Designer
Ben, If you had posted this question to designer-L you'd have gotten insight from some real experts. In designer table definition(table columns, indexes, keys, constraints) is different from table implementation(schema, tablespace, storage parameters). The same table definition can be associated with many different table implementation (e.g. different storage for the same table depending on test, prodction environment etc). In design editor's db admin tab you need to define database (under your application folder) then tablespace and/or storage definitions and users under that database. You also need to give users quota on tablespaces. Now generate the table DDL while in D Admin tab. Your DDL will now contain the all the artifacts. This is tedious process if you have large set of tables. If you want to do it repeatedly, create the database, tablespace, user and quotas ahead of time. While converting your ER model (you use ER modeler right?) using database design transformer, you can specify the table space for table and indexes. The transformer will create both table definitions and table implementations for you. Then from Design Editor's DB Admin tab you generate DDL for for your tables. --- Ben <[EMAIL PROTECTED]> wrote: > Hi > > Can someone who has used Designer to generate DDL let > me know how you specify which tablespace a table or index > is to be created in. I am using the Design Editor and > the DBAdmin tab and can't seem to find it. > Thanks in advance. > > Ben > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Ben > 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). = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Query
Mike, Just wanted to supplement your example with another one from my environment (uses nested tables with CAST and MULTISET to achieve the same): SELECT c.customer_id ,customer_name ,utils.code_table_to_string(CAST( MULTISET( SELECT distinct dlr_dealer_code FROM dealer_customers dc ,dlr_dealer_master_t WHERE dc.customer_id = dc_in.customer_id AND dlr_dealer_id = dc.dealer_id ORDER BY dlr_dealer_code ) AS code_table_t )) spans_these_dealers FROM customers c ,( SELECT customer_id FROM dealer_customers GROUP BY customer_id HAVING COUNT(distinct dealer_id) > 1 ) dc_in WHERE dc_in.customer_id = c.customer_id; Does eseentially the same thing. In this case it concatenates the dealer_codes per customer (identifies customers spanning multiple dealers) on the same row. Here is a sample of the output: 2969473096 Mountain Enterprises IncD100,D470,D480 2969473121 K C ConstructionB010,B150,B190 2969473195 GOODFELLOW BROS H140,H330 The TYPE code_table_t is defined as: TYPE CODE_TABLE_T IS table of varchar2(3000); And the conversion function is very similar in its functionality to dbms_utility.comma_to_table procedure: FUNCTION code_table_to_string (in_table code_table_t, in_rec_sep varchar2 DEFAULT config.c_rec_sep) RETURN delimited_list_t IS v_list delimited_list_t; BEGIN FOR i IN 1..in_table.COUNT LOOP IF i = 1 THEN v_list := in_table(1); ELSE v_list := v_list||in_rec_sep||in_table(i); END IF; END LOOP; RETURN v_list; END code_table_to_string; As opposed your suggestion of using the following: SELECT id, CONCAT_LIST(CURSOR( > SELECT text > FROM mike m_in ORDER BY ordr > WHERE m_in.id = m.id)) list > FROM ( select distinct id from mike) m; --- Mike Spalinger <[EMAIL PROTECTED]> wrote: > Imran, > > Tom Kyte has a thread that might help: > http://asktom.oracle.com/pls/ask/f?p=4950:8:1554835115460038644::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:229614022562, > > Essentially, you can do this: > > CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR ) >RETURN VARCHAR2 IS > ret VARCHAR2(32000); > tmp VARCHAR2(4000); > BEGIN > loop > fetch cur into tmp; > exit when cur%NOTFOUND; > ret := ret || ' ' || tmp; > end loop; > RETURN ret; > END; > / > > SQL> select * from mike; > > ID TEXT ORDR > -- -- -- > 1 B 2 > 1 A 1 > 1 C 3 > > SELECT id, SUBSTR(CONCAT_LIST(CURSOR( > SELECT text >FROM (select id, text, ordr from mike order by ordr) ee >WHERE e.id = ee.id)),1,40) list > FROM ( select distinct id from mike) e; > > ID LIST > -- > 1 A B C > > Mike > > > Imran Ashraf wrote: > > Hi, > > > > I have the following data , table Temp > > > > ID Text Order > > 1 B2 > > 1 A1 > > 1 C3 > > > > > > I want to write a query which says: wherever there is more than 1 > occurrence > > of ID then concatenate the text in the order specified in the > order column. > > So i would get: > > > > ID Text > > 1 A B C > > > > Any suggestions? > > > > Thanks > > > > Imran > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mike Spalinger > 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). = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
Re: SQL Query
Mike, Just wanted to supplement your example with another one from my environment: SELECT c.customer_id ,customer_name ,utils.code_table_to_string(CAST( MULTISET( SELECT distinct dlr_dealer_code FROM dealer_customers dc ,dlr_dealer_master_t WHERE dc.customer_id = dc_in.customer_id AND dlr_dealer_id = dc.dealer_id ORDER BY dlr_dealer_code ) AS code_table_t )) spans_these_dealers FROM customers c ,( SELECT customer_id FROM dealer_customers GROUP BY customer_id HAVING COUNT(distinct dealer_id) > 1 ) dc_in WHERE dc_in.customer_id = c.customer_id; Does eseentially the same thing. In this case it concatenates the dealer_codes per customer (identifies customers spanning multiple dealers) on the same row. Here is a sample of the output: 2969473096 Mountain Enterprises IncD100,D470,D480 2969473121 K C ConstructionB010,B150,B190 2969473195 GOODFELLOW BROS H140,H330 --- Mike Spalinger <[EMAIL PROTECTED]> wrote: > Imran, > > Tom Kyte has a thread that might help: > http://asktom.oracle.com/pls/ask/f?p=4950:8:1554835115460038644::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:229614022562, > > Essentially, you can do this: > > CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR ) >RETURN VARCHAR2 IS > ret VARCHAR2(32000); > tmp VARCHAR2(4000); > BEGIN > loop > fetch cur into tmp; > exit when cur%NOTFOUND; > ret := ret || ' ' || tmp; > end loop; > RETURN ret; > END; > / > > SQL> select * from mike; > > ID TEXT ORDR > -- -- -- > 1 B 2 > 1 A 1 > 1 C 3 > > SELECT id, SUBSTR(CONCAT_LIST(CURSOR( > SELECT text >FROM (select id, text, ordr from mike order by ordr) ee >WHERE e.id = ee.id)),1,40) list > FROM ( select distinct id from mike) e; > > ID LIST > -- > 1 A B C > > Mike > > > Imran Ashraf wrote: > > Hi, > > > > I have the following data , table Temp > > > > ID Text Order > > 1 B2 > > 1 A1 > > 1 C3 > > > > > > I want to write a query which says: wherever there is more than 1 > occurrence > > of ID then concatenate the text in the order specified in the > order column. > > So i would get: > > > > ID Text > > 1 A B C > > > > Any suggestions? > > > > Thanks > > > > Imran > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mike Spalinger > 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). = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini 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: Executing stored procedure from diff user
To fix this problem, compile your package with AUTHID CURRENT USER
directive in the Package Signature.
Basically when user B executes package of user A, for the course of
the package's run user B becomes user A and the ownership context is
that of user A's. When user B runs show_user(tablename), effectively
ORacle is trying to resolve tablename in user A's namespacewhich
is doomed for failure. Worse if A, B and C both have tables with the
same name, you will be reporting incorrect results (always A's
tables).
HTH
--- Saminathan Seerangan <[EMAIL PROTECTED]> wrote:
>
> Hi List,
>
> Could you please help me out to resolve this issue?
> Basically i have created one stored procedure(show_space) in
> SYSTEM schema, then granted execute rights to PUBLIC and created
> public synonym.
> When i conenct as diff user(MUT) I am not able to execute the
> procedure. Any help would be really appreciated.
>
> TIA
>
> SQL> connect system/[EMAIL PROTECTED]
> Connected.
> SQL> @D:\share\oracle\Asktom\show_space8i.sql
> 51 /
>
> Procedure created.
>
> SQL> grant execute on show_space to public;
> Grant succeeded.
>
> SQL> create public synonym show_space for show_space;
> Synonym created.
>
> SQL> connect mut/[EMAIL PROTECTED]
> Connected.
>
> SQL> execute show_space('RW_RFX_HEADER');
> BEGIN show_space('RW_RFX_HEADER'); END;
>
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist
> ORA-06512: at "SYS.DBMS_SPACE", line 55
> ORA-06512: at "SYSTEM.SHOW_SPACE", line 22
> ORA-06512: at line 1
>
>
> SQL> desc show_space
> PROCEDURE show_space
> Argument Name TypeIn/Out
> Default?
> -- --- --
>
> P_SEGNAME VARCHAR2IN
> P_OWNERVARCHAR2IN
> DEFAULT
> P_TYPE VARCHAR2IN
> DEFAULT
>
>
> SQL> desc dbms_space
> PROCEDURE FREE_BLOCKS
> Argument Name TypeIn/Out
> Default?
> -- --- --
>
> SEGMENT_OWNER VARCHAR2IN
> SEGMENT_NAME VARCHAR2IN
> SEGMENT_TYPE VARCHAR2IN
> FREELIST_GROUP_ID NUMBER IN
> FREE_BLKS NUMBER OUT
> SCAN_LIMIT NUMBER IN
> DEFAULT
> PARTITION_NAME VARCHAR2IN
> DEFAULT
> PROCEDURE UNUSED_SPACE
> Argument Name TypeIn/Out
> Default?
> -- --- --
>
> SEGMENT_OWNER VARCHAR2IN
> SEGMENT_NAME VARCHAR2IN
> SEGMENT_TYPE VARCHAR2IN
> TOTAL_BLOCKS NUMBER OUT
> TOTAL_BYTESNUMBER OUT
> UNUSED_BLOCKS NUMBER OUT
> UNUSED_BYTES NUMBER OUT
> LAST_USED_EXTENT_FILE_ID NUMBER OUT
> LAST_USED_EXTENT_BLOCK_ID NUMBER OUT
> LAST_USED_BLOCKNUMBER OUT
> PARTITION_NAME VARCHAR2IN
> DEFAULT
>
> Free multi-lingual web-based and POP3 email service with a
> generous 15MB of storage, a choice of themes for your mailbox,
> message filtering, plus spam and virus protection
> Sign up now: http://www.gawab.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Saminathan Seerangan
> 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).
>
=
Sundeep Maini
Consultant
Currently on Assignement at Caterpillar Peoria
[EMAIL PROTECTED]
__
Do you Yahoo!?
Re: Which is beter a cursor or a for loop?
Proof of pudding is in eating Whip up a couple of examples and check out the timings. I suspect the CURSOR FOR LOOPS would run faster. I recall a similar mention by Tom Kyte in Oracle magazine while answering a question about explicit and implicit cusrsors. Unlike the conventional wisdom (a.k.a Feuerstein's recommendation in his best selling books) to use explicit cursors, he showed via an example how and why the implicit cursors are better (Key difference was the processing PL/SQL had to do for the explicit cursor). It is pretty much the same for FOR LOOP (it opens, closes and fetches from the cursor for you) and is likly to run faster. - Sundeep --- Denham Eva <[EMAIL PROTECTED]> wrote: > Hello, > > I was just asked by one of our developers which is beter to use:- > a cursor or a for loop? > I must admit I am not sure > > Anyway the specific piece of code in discussion is similar to the > following > > FOR X IN (SELECT X FROM TABLE_NAME > WHERE COL1 = 'Something')) > LOOP > Do a whole lot of stuff in database here.. > LOOP END; > > I would guess that the cursor would follow similar execution > criteria but > using > the cursor syntax. > > Any ideas? > > TIA > regards > Denham Eva > Oracle DBA > The real problem is not whether machines think but whether men do. > - B. F. Skinner > > > _ > DISCLAIMER > This message is for the named person's use only. It may contain > confidential, > proprietary or legally privileged information. No confidentiality > or privilege is waived or lost by any mistransmission. If you > receive > this message in error, please immediately delete it and all copies > of it from your system, destroy any hard copies of it and notify > the > sender. You must not, directly or indirectly, use, disclose, > distribute, print, or copy any part of this message if you are not > the intended recipient. TFMC, its holding company, and any of its > subsidiaries each reserve the right to monitor and manage all > e-mail > communications through its networks. > > Any views expressed in this message are those of the individual > sender, > except where the message states otherwise and the sender is > authorized > to state them to be the views of any such entity. > > > _ > This e-mail message has been scanned for Viruses and Content and > cleared > by MailMarshal > > For more information please visit www.marshalsoftware.com > _ > = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] ______ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Tuning Help
bout performance being echoed by other users but no > solutions. > > Any tips or insights as to how to avoid the full table scans (all > of > which are 10-100M in size) of the large table without the RULE > hint. > A more thorough explanation of what is happening and why would be a > bonus. > > TIA > > > = > > Sundeep Maini > Consultant > Currently on Assignement at Caterpillar Peoria > [EMAIL PROTECTED] > > __ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: sundeep maini > 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: DENNIS WILLIAMS > 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). > = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini 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).
SQL Tuning Help
Oracle 8.1.7.4 on HP-UX
I am using collections to pass multiple values from client to the
database to gather values for more than input values. The queries
produce the results but the without the RULE hint the response is
dramatically slower. Following is just one of the examples but I have
many many queries which exhibit the same behavior.
Since RULE based optimization is headed for the chopping block we are
wondering what is the alternative.
SELECT eqp.equipment_id,
eqp.manufacturer_code,
eqp.model_num,
eqp.equipment_serial_num,
DECODE(SIGN(eqp.last_pm_performed_at_hrs -
eqp.current_meter_reading_hrs),1,
'SMU_ADJUSTED',eqp.last_pm_performed_at_hrs) smu_adjusted,
eqp.productlink_equipment_code,
ecps.pm_schedule_name,
epp.performed_datetime,
DECODE(epp.comment_text,NULL,1,0) comments_available,
emr.reading_date,
emr.meter_reading_value,
equipment_event_log.event_status(eqp.equipment_id,2),
equipments_next_pm_due.pms_due_list(eqp.equipment_id) pms_due_list,
equipments_next_pm_due.perform_at_hrs_cnt(eqp.equipment_id)
next_pm_hrs
FROM equipments eqp,
equipment_meter_readings emr,
equipment_pm_performed epp,
equipment_class_pm_schedules ecps,
TABLE(CAST(id_table_t(100071,100072,100073,100074)
AS id_table_t)) eqp_list
WHERE eqp.epp_id_last_pm_performed = epp.epp_id (+)
AND eqp.emr_id_current_meter_reading = emr.emr_id (+)
AND epp.ecps_id = ecps.ecps_id (+)
AND eqp.equipment_id = eqp_list.column_value
/
Plan:
SELECT STATEMENT Hint=CHOOSE8 K 510
HASH JOIN OUTER 8 K 582 K 510
HASH JOIN OUTER 8 K 510 K 497
HASH JOIN OUTER 8 K 390 K 489
HASH JOIN 8 K 279 K 287
COLLECTION ITERATOR CONSTRUCTOR FETCH
TABLE ACCESS FULL EQUIPMENTS 192 K 6 M 256
TABLE ACCESS FULL EQUIPMENT_METER_READINGS221 K 2 M 151
TABLE ACCESS FULL EQUIPMENT_PM_PERFORMED 96 1 K
TABLE ACCESS FULL EQUIPMENT_CLASS_PM_SCHEDULES2 K 22 K4
Following is the Plan with /*+ RULE */ hint has the expected fast
response and the desired plan:
SELECT STATEMENT Hint=HINT: RULE
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS
COLLECTION ITERATOR CONSTRUCTOR FETCH
TABLE ACCESS BY INDEX ROWID EQUIPMENTS
INDEX UNIQUE SCAN EQP_PK
TABLE ACCESS BY INDEX ROWID EQUIPMENT_PM_PERFORMED
INDEX UNIQUE SCAN EPP_PK
TABLE ACCESS BY INDEX ROWID EQUIPMENT_CLASS_PM_SCHEDULES
INDEX UNIQUE SCAN ECPMS_PK
TABLE ACCESS BY INDEX ROWID EQUIPMENT_METER_READINGS
INDEX UNIQUE SCAN EMR_PK
I have tried both versions IN (TABLE(CAST( as a predicate and as a
pseudo-table in FROM (as in the query above) and it made no
difference to the plan. I searched askTOM and heard similar
sentiments about performance being echoed by other users but no
solutions.
Any tips or insights as to how to avoid the full table scans (all of
which are 10-100M in size) of the large table without the RULE hint.
A more thorough explanation of what is happening and why would be a
bonus.
TIA
=
Sundeep Maini
Consultant
Currently on Assignement at Caterpillar Peoria
[EMAIL PROTECTED]
__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: sundeep maini
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: SQL question
p://www.orafaq.net > -- > Author: Charu Joshi > 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). > = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini 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: Table Naming Conventions
1 > 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). = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Tuning - How to avoid TOCHAR function against a date
How, exactly could it be done? > > > SELECT DATE_KEY > FROM DATE_DIM > WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = > TO_CHAR(:b1,'DD-MON-') > > > SQL> desc date_dim; > NameNull?Type > --- > DATE_KEYNOT NULL NUMBER(5) > ORACLE_DATE NOT NULL DATE > DATACOM_DATE NUMBER(6) > DATACOM_REVERSE_DATE NUMBER(6) > DAY_OF_WEEK NOT NULL > VARCHAR2(30) > DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) > DAY_NUMBER_OVERALL NOT NULL NUMBER(9) > WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) > WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) > MONTH NOT NULL > VARCHAR2(30) > MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) > YEARNOT NULL NUMBER(5) > WEEKDAY_IND NOT NULL CHAR(1) > LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) > DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE > DATA_MART_MOD_DATETIME NOT NULL DATE > > > > SQL> select oracle_date from date_dim where > rownum=1; > > ORACLE_DA > ----- > 01-JAN-70 > > > Thanks in advance for any help. > > Cherie Machler > Oracle DBA > Gelco Information Network > > > > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > === message truncated === = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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: 8.1.7 LMTs Autoallocate vs Uniform Extents
Dennis, I mentioned Autoallocate and not autoextend. I have been using LMTs for over a year now but only with UNIFORM extent sizes. I am not sure if I am ready to handover space management to Oracle (via Autoallocate which is being foisted upon me as a panecea of all ills) least I have evaluated the pros and cons. The stuff in Manual and technet I have reveiwed so far doesn't give you enough information like how much space is wated in Autoallocate vs Uniform extent sizes. How does Oracle decide to allocate an extent for an Insert or an Update in Autoallocate option and how does it decide on the extent size? So far I have looked at SQLRef, Concepts manual and technet. Any other pointers? TIA Sundeep --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > Sundeep - Start by reading the classic paper "How to > Stop Defragmenting and > Start Living" at this link: > http://www.dbatoolbox.com/WP2001/spacemgmt/defrag.htm > > Actually, autoallocate and uniform extents work very > well together. But you > need to understand the concepts behind them first. > And make sure your > application doesn't use up all your disk space if > you autoallocate. > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -Original Message- > Sent: Tuesday, March 26, 2002 12:54 PM > To: Multiple recipients of list ORACLE-L > > > Can someone point me to good reading material on > this > subject. Is one better than the other for > performance > and manageability? > > Syntactically the autoallocate is shorter and seems > to > be more hands off (does that mean worry free also?). > > > > TIA > > > = > > Sundeep Maini > Consultant > Currently on Assignement at Marshfield Clinic WI > [EMAIL PROTECTED] > > __ > Do You Yahoo!? > Yahoo! Movies - coverage of the 74th Academy Awards® > http://movies.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: sundeep maini > 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). ===== Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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).
8.1.7 LMTs Autoallocate vs Uniform Extents
Can someone point me to good reading material on this subject. Is one better than the other for performance and manageability? Syntactically the autoallocate is shorter and seems to be more hands off (does that mean worry free also?). TIA = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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).
Urgent: Dictionary - Missing Column
We have an issue with compiling PL/SQL code. The compilation gives "PLS-00201 identifier must be declared" on a certain column which exists in the table (a describe or select from the table works). An export and import of our test db made the problem go away. We had the same bug happen again in QA and again export and import set it right but we can't do this in production. Our environment is 8.1.7 on HP-UX11. Has anyone see this bug or knows a workaround? TIA = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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).
Searching Inside CLOBs
Our developers store XML docuements inside CLOBS and want to search the CLOB contents based on a search string. I have lot of concern about such queries but could benefit from someone on the list who has already solved the performnce issues with such searches. Typical existing queries I have come across are: Select columns FROM list of tables WHERE join conditions AND UPPER(tab1.varcharcol) LIKE '%:in_string%' OR UPPER(tab2.varcharcol) LIKE '%:in_string%' OR . OR dbms_lob.instr(lobcol,:in_string,1,1) <> 0; Note: in_string is always upper case but the columns are Concerns: 1. Poor index selection due to UPPER 2. CLOB searches are obviously wrong. To do a mixed case string match one needs to read chunks of the lob in a user defined function of some sort, convert it to upper and then do string matches. Any tips on this one? = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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).
Searching Inside CLOBs
Our developers store XML docuements inside CLOBS and want to search the CLOB contents based on a search string. I have lot of concern about such queries but could benefit from someone on the list who has already solved the performnce issues with such searches. Typical existing queries I have come across are: SELECT columns FROM list of tables WHERE join conditions AND UPPER(tab1.topic) LIKE '%:in_string%' OR UPPER(tab2.subtopic) LIKE '%:in_string%' OR . OR dbms_lob.instr(bodytext,:in_string,1,1) <> 0; Note: in_string is always upper case but the column contents are not. Concerns: 1. Poor index selection due to UPPER 2. CLOB searches are wrong as the CLOB contents are case sensitive. One way to remedy this would be to build a user defined function which will read the CLOB col. in chunks and look for string matches inside individual chunks till a success or end of CLOB. Either way that is a pretty intense way to search for a string among table data when a user is waiting for a quick response. Keyword searches are supported differently. This mechanism is only for random string searches. Any cues or suggestions? Especially for case sensitive searches inside CLOBS? TIA Sundeep = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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).
Backend Architecture in 3 tier environment
In our current environment most database interaction is via DMLs issued from client or middle tier via embeded SQL calls. Is it always a good idea to go stored procedure (packages) route? In my opinion going with stored procedure route isolates DB design from the upper tiers (creates an API via stored procedure signatures making most DB changes transparent to client least the signature is changed). It also offers the advantage of pre-compiled, pre-tuned logic vs the JAVA/Client coders embedding SQLs of their choice whereever they want. It is also possible to use more Database facilities or features than palin SQL alone would accord. Finally, I can reverse engineer them into Designer/ERWin which allows me to know impact of cahnges on the DB. However, in many cases of small SQLs issued directly from Java the PL/SQL route may have overheads. Also, the OO programmers who are usually responsible for the app design have little experince designing DB structures much less concerened about DB tuning which makes it difficult to get a good seperation between data service layer and other layers. Can I ask the group to share their experience and/or opinion? Are there potential downsides to stored procedures route? TIA Sundeep = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to stop access to prod instance ...
Raj, If you are using 8i or above, I would highly recommend you go through fine grained access features. They are a bit exhaustive to cover here but I have used them previously for some very complex stratifications and they will work for your requirements too. Here is a brief note for each of your criterion but you will have to build the procedures. Use log on triggers with fine grain access (dbms_rls and sys_context/user_context) to enable roles from forms application and impose user/role restrictions at logon. You can design appropriate roles to segregate users to control their behavior. This solution is not profile dependent and not tool specific. Albit it will have the context of an application(s) and alter the queries and set the initial environment/restrictions trough a trusted PL/SQL package whichever client the queries may be issued from. You also have the potential to add dynamic predicates to further control query behavoir. Your reporting tool doesn't have to do anything special like invoking a runtime role. The logon triggers the environment change or enabling of specialized roles. You can store policies with tables and you can segregate behavior for selects vs updates (upd,ins,del - by encapsulating them in packages if required or simply by adding appropriate dynamic predicate to your table and Oracle will rewrite the queries involving those tables and views by including the predicate). In a rare departure, the Oracle manuals (application developers guide and dbms_rls documentation in pl/sql packages reference and sys_context documentation) will give you all the fodder to feed your fancy. Not only what you propose is doable, I am sure you'll find more flexibility than you have asked for. HTH Sundeep --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > Thanks all ... > > The problem doesn't end here ... the requirements > (loosely specified) are as > follows > > 1. Developers and end users can access production > ONLY through FORMS > APPLICATION. > 2. End users can't get to sqlplus so I am not even > worried about that. It is > developers that I am worried about. > 3. Enabling roles at runtime is an option as long as > we are not talking > about reports. We use SQR and Oracle reports, a > reports agent looks up > requests and creates shell files on Unix server > which are then scheduled to > run. This scheme should work there as well. > 4. How about people accessing tables through db > links? > > I don't think there is a 100% proof method, but > basically I want to be able > to restrict developers accessing the instance except > through forms > application. Dropping developer's accounts is not an > option. > > I thought over it, discussed it with my senior DBAs > and finally I am turning > to you guys. All the suggestions are helpful but I'd > like to avoid as many > loose ends as possible. > > Thanks in advance > Raj > __ > Rajendra JamadagniMIS, 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 > > = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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: Fwd: Re: How to select and display 10 records at a time from a table using sql query
You can use the Rank () function to rank your rows based on a simple or complex criteria in a subquery or an inline view and then extract the requisite set by using a simple condition on the Rank col. Below is a simple example just to give you some idea of what is possible. You may want to take a look at some ref manual to explore this for meeting your particular requirement (Best place is Datawarehousing Guide): SELECT keycol, some_other_col(s) FROM (SELECT keycol, some_other_col(s), RANK() OVER (ORDER BY keycol) AS Ranking FROM mytable WHERE condition(s) ) WHERE ranking between 11 and 20; <== alter this cond. to get a different set. You can use more complex criteria in ranking/ordering your rows. --- Jan Pruner <[EMAIL PROTECTED]> wrote: > Does anybody know how to do it better way? > > > But this will return only the first ten rows not > subsequent sets. How do I > > display rows from 11 to 20, 21 to 30 and so on? > > ROWNUM is counting output tuples. You cannot use > ROWNUM > 10. > > I think you have to order select by your primary > key, make filter based on > your primary key and take only first 10 tuples > using ROWNUM. > > JP > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of Jan Pruner > > Sent: Wednesday, August 29, 2001 4:01 PM > > To: Multiple recipients of list ORACLE-L > > Subject: Re: How to select and display 10 records > at a time from a table > > using sql query > > > > > > select * from ( select column1, column2 from > my_table order by 1) where > > ROWNUM < 11; > > > > Look at manual how to use ROWNUM. > > It's ease to make mistakes with it. > > > > JP > > > > Dne st 29. srpen 2001 10:55 jste napsal(a): > > > Dear DBA Gurus, > > > > > > How do I select and display 10 records at a > time from a table using sql > > > query only? > > > > > > Thanks and Regards, > > > > > > Ranganath > > > > > > > > > DISCLAIMER: This correspondence is confidential > and intended for the > > > named recipient(s) only. If you are not the > named recipient and receive > > > this correspondence in error, you must not copy, > distribute or take any > > > action in reliance on it and you should delete > it from your system and > > > notify the sender immediately. Unless otherwise > stated, any views or > > > opinions expressed are solely those of the > author and do not represent > > > those of Subex Systems Limited. > > > > > > www.subexgroup.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). > > > > > > DISCLAIMER: This correspondence is confidential > and intended for the named > > recipient(s) only. If you are not the named > recipient and receive this > > correspondence in error, you must not copy, > distribute or take any action > > in reliance on it and you should delete it from > your system and notify the > > sender immediately. Unless otherwise stated, any > views or opinions > > expressed are solely those of the author and do > not represent those of > > Subex Systems Limited. > > > > www.subexgroup.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,
RE: Stored procedures that return multiple rows
I was about to write a similar testimonial when I saw Steve Orr's message. Don't use OCI based class111.zip. It has Oracle properity classes. The pure java class111.zip works great and is portable. Passing ref cursors from stored procedures makes the solution simple and elegent to implement. The seperation between the client rendering (HTML/Javascript/XML crowd) and middle tier Java servlets and Middle tier and RDBMS developer (using ref cursors over thin JDBC) is perhaps the best way to go. Avoid embeded SQL in Java if possible. Access to RDBMS via ref_cursor hides the DB schema from the Java developers making DB schema changes less painful and also allowing future switch of the databases if required, lot less painful. No meddling from Java folks too. I don't know how anybody decides on application partitioning issues as this division between business/domain objects and their persistence in the database raises all sorts of interesting design issues Regds --- "Orr, Steve" <[EMAIL PROTECTED]> wrote: > On my previous gig all DB access was via stored > procedures returning ref > cursors through JDBC. The Java dweebs could do just > about anything with the > ref cursor. It worked really well. I could actually > tune SQL queries on the > running production application without any > recompilation. Nice seperation of > code. The DBA can tune real SQL code and the > developers won't even know. No > embedded SQL. Yeah!! > > OCI can also accommodate ref cursors. > > Steve Orr > > > -Original Message- > Sent: Thursday, August 23, 2001 2:11 PM > To: Multiple recipients of list ORACLE-L > > > We're considering a mandate that all database access > be via stored > procedures (probably in packages). These would be > called either via OCCI > (the C++ call interface) or JDBC. My question is > whether anyone's had > experience in returning a result set from a PL/SQL > procedure under these > circumstances, and how it was implemented: did you > return a ref cursor, an > index-by table, a set of arrays? Any advice > will be appreciated. > Thanks! > > > Paul Baumgartel > MortgageSight Holdings, LLC > [EMAIL PROTECTED] > > > > = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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: OLAP question
Bala, Couple of things, first you can find lots of documentation and examples in Oracle 8i Data Warehousing Guide (ch-9 Dimesions, ch-15-summary advisor and ch-19 Query Rewrite). Also, dimensaions are not used in query syntax but used by Optimizer for query rewites and for better understanding of the innate nature of the data. In an OLAP environment you can't go wrong declaring dimensions and hierarchies but they are just declarative constructs for the Optimizer much like Optimizer Hints. Im not sure if there is more to your question but would the following select get you the mobil number transactions? SELECT co.company_code, co.company_desc, co.parent_company_code, NVL(txn.mobil_num,'No mobile usage on '||txn_date) FROM company_dim co WHERE co.company_code = desired_company_co AND co.parent_company_code = txn.company_code (+) AND txn.activity_date = txn_date; --- "Bala, Prakash" <[EMAIL PROTECTED]> wrote: > Hi, > > I am a newbie to this OLAP area. > > I have this table (company_dimension) where every > company has a parent, > which in turn can be a child to another parent and > so on. It is about 10 > levels deep. > > TEST@D0MARS> desc company_dimension > Name Null?Type > - > > COMPANY_CODE NOT NULL VARCHAR2(50) > ---> primary key > COMPANY_DESC > VARCHAR2(100) > PARENT_COMPANY_CODEVARCHAR2(50) > ---> foreign key > pointing to company_code > > > The following table maintains the mobile phone > numbers for every company. > > TEST@D0MARS> desc DLY_TRANSACTION_DETAIL > Name Null? Type > -- -- > > ACTIVITY_DATE DATE > COMPANY_CODE VARCHAR2(10) > ---> foreign key to the > company_dimension table > MOBILE_NUMBER NUMBER(10) > > The requirement is to get all the phone numbers for > a given company and its > children for a given day. Since I cannot join these > 2 tables using the > 'connect by' clause, how can I accomplish it, > without using a sub-query? > (actually 2 other dimensions play a role in the > above table and I have > simplified the structure) > > Created a dimension on the company_dimension table > and used the > dbms_olap.validate_dimension procedure to validate > it. Everything looks > good, but none of the manuals explains how to use > the dimension in a query. > > This is Oracle 8.1.7 on Sun Solaris. > > > Thanks in advance. > > Prakash > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Bala, Prakash > 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). = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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: dimension time question
Joe, If you had only 1..4 values for quarters you can compare quarters across years or to each other in your SQL queries. If users wanted to compare particular quarters e.g year 2000 Q1 to year 1999 Q4 they could always use both Year and Quarter fields in the queries. However if they wanted to compare Q1 in last 5 years with Q2 or Q3 over the same period you will make their life in the quering tool difficult. You'd be better off using 1..4 or Q1..Q4 values over 20011 etc. --- JOE TESTA <[EMAIL PROTECTED]> wrote: > my latest assignment takes me into the world of DW. > > the dm_time table has: > > dm_time_id number > .. > .. > .. > sales_year number(4) > sales_qtr number(1) > sales_month number(2) > sales_week number(2) > sales_day number(3). > > so an entry looks like: time_id 10456 > sales_year=2001 > sales_qtr=1 > sales_month=1 > sales_week=1 > sales_day=1 > to rollup the data we define dimensions. I was > asked this: > > In a "normal" time_dimension, doesn't qtr need to > have the year as part of it, so that it looks like > 20011 for the 1st qtr? > > thanks, joe > = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Send instant messages & get email alerts with Yahoo! Messenger. http://im.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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: rename foreign keys from system-assigned constraint names to more
gt; col_rec.column_name; > ELSE > v_fklist_R := v_fklist_R || ', ' || > col_rec.column_name; > END IF; > END LOOP; > > > IF ((v_table_name = cons_rec.table_name) >AND (v_Rtable_name = cons_rec.Rtable)) >THEN v_ctr := v_ctr + 1; > Else > v_ctr := 0; > v_table_name := > cons_rec.table_name; > v_Rtable_name := cons_rec.Rtable; > End If; > > v_fklist := v_fklist || ')'; > v_fklist_R := v_fklist_R || ')'; > > DBMS_OUTPUT.put_line > > 'alter table ' || cons_rec.owner || '.' || > cons_rec.table_name > ); > DBMS_OUTPUT.put_line > > 'drop constraint ' || > cons_rec.constraint_name || ';' > ); > DBMS_OUTPUT.put_line > > 'alter table ' || cons_rec.owner || '.' || > cons_rec.table_name > ); > IF v_ctr > 0 THEN > v_global_name := 'FK_' || > cons_rec.table_name || '_' > || cons_rec.RTable || > to_char(v_ctr); > ELSE > v_global_name := 'FK_' || > cons_rec.table_name || '_' > || cons_rec.RTable; > END IF; > IF length( v_global_name ) > 29 Then > IF v_ctr > 0 then > v_global_name := 'FK_' || > cons_rec.RtrimTable || '_' > || cons_rec.RtrimRTable || > to_char(v_ctr); > ELSE > v_global_name := 'FK_' || > cons_rec.RtrimTable || '_' > || cons_rec.RtrimRTable; > END IF; > END IF; > > DBMS_OUTPUT.put_line > > 'add constraint ' || v_global_name || ' > foreign key ' || > v_fklist > ); > DBMS_OUTPUT.put_line > > 'references ' || cons_rec.ROwner || '.' || > cons_rec.Rtable || ' ' > || > v_fklist_R || > ';' > ); > > > > DBMS_OUTPUT.new_line (); >END LOOP; > END; > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Glenn Travis > 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 > === message truncated === = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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: Unique Index or Constraint??
Vishak, Unique constraints by default leads to creation of a unique index by Oracle. This index is used for imposing uniqueness. If you have a unique index in mind then constraint is the way to go. 1. An explicit constraint allows you to use Oracle mechanisms to trap exceptions in another table while ENABLEing a previously disabled constraint or trap errors in your code. 2. Optimizer can make use of constraint declarations for query rewrite. 3. If you are 8i you can define a unique constraint in DISABLEd VALIDATE mode and have Oracle skip the automatic creation of Index. If you are a datawarehouse environment you can still have the optimizer make use of the constraint for query rewite even if the index is not there. 4. Constraint declaration gives you lots of flexibility on when to kick in the check. For OLTP environment you can defer constraint checks depending on your transaction's needs (in 8i). There is gobs of information in the SQL ref manual on syntax and the options I have mentioned above. HTH - Sundeep === PS: I used to work for Wipro in 1987. I am wondering if the likes of Ashok Soota and Manoj Chug are still part of the company. You can send me email at my personal email address. Thanks --- Vishak <[EMAIL PROTECTED]> wrote: > Hello Gurus, > > I would like to know whether creating an > unique-index on a column or a > unique constraint on a column(during creation of the > table) would be fine. > > Which option would be better? > > TIA > Regards > Vishak > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Vishak > 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). = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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: Script to Compare table structures
Download free version of TOAD from www.toadsoft.com. Once installed and logged into look under View--->Schema Differences you can compare many aspects of two schemas (eg compare your test and production schemas). Table comparisons are one of the many things you can compare. HTH --- Johnson Poovathummoottil <[EMAIL PROTECTED]> wrote: > Hi, > > I would appreciate if someone could pass me a script > which compares table structures? > > Johnson > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/?.refer=text > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Johnson Poovathummoottil > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/?.refer=text -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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).
