OT
Jared (Still the list owner) - I hope this one is correct :-) - Bhat -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 29, 2001 1:26 AM To: Multiple recipients of list ORACLE-L Subject:Re: Steve Adams's Removal PLEASE stop bringing this up on this list. Steve Adams is a valuable resource on this list, and in no way shape or fashion has he been removed, nor have I even contemplated removing him. Jared Still ( the list owner ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with DBMS_SQL
Hi Steve/Dick, Thanks for your clarifications. You are right. I was relying on the grant via role and once I granted the privilege directly it worked fine. Regards, - Bhat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with DBMS_SQL
Hi Steve, The account has DBA privilege and by using a direct DDL I am able to create the table. Anything else I can check-up. Thanks, - Bhat -Original Message- From: Steve Adams [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 18, 2001 7:01 PM To: Multiple recipients of list ORACLE-L Subject:RE: Problem with DBMS_SQL Hi Bhat, The CREATE TABLE privilege probably needs to be granted directly. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ PS. Please don't copy "[EMAIL PROTECTED]" on questions to the list. -Original Message- Sent: Wednesday, 18 April 2001 19:14 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Hi Gurus, I am experincing a problem with a procedure containing DBMS_SQL to create a table. On execution of the script I get the message PL/SQL procedure successfully completed, but the table doesn't get created. In the error log file I can see ORA-01031: insufficient privileges message. Any ideas. HP-UX : Oracle 7.3.4.4.1 Thanks. - Bhat Here is the procedure create or replace PROCEDURE create_table_mbn015 IS dyn_sql LONG; cid INTEGER; a integer; b varchar2(100); abcdinteger; BEGIN cid := DBMS_SQL.OPEN_CURSOR; dyn_sql := 'CREATE TABLE mbn015 STORAGE (INITIAL 5M NEXT 5M) TABLESPACE MUGDBDATA1 AS( SELECT DISTINCT p.item, p.loc, p.cppprodmethod, c.loadoffsetdur, p.scheddate, (p.scheddate - c.loadoffsetdur/1440) calcdate FROM stsc.planorder p,stsc.cppprodmethodstep c WHERE p.item = c.item AND p.loc = c.loc AND p.cppprodmethod = c.cppprodmethod AND c.stepnum = 20)'; DBMS_SQL.PARSE(cid, dyn_sql, dbms_sql.v7); abcd := DBMS_SQL.EXECUTE(cid); dbms_output.put_line(abcd); DBMS_SQL.CLOSE_CURSOR(cid); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(cid); a := sqlcode; b := substr(sqlerrm,1,100); INSERT INTO errors VALUES (sysdate, 'A:CT', a, b); END create_table_mbn015; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Problem with DBMS_SQL
Hi Gurus, I am experincing a problem with a procedure containing DBMS_SQL to create a table. On execution of the script I get the message PL/SQL procedure successfully completed, but the table doesn't get created. In the error log file I can see ORA-01031: insufficient privileges message. Any ideas. HP-UX : Oracle 7.3.4.4.1 Thanks. - Bhat Here is the procedure create or replace PROCEDURE create_table_mbn015 IS dyn_sql LONG; cid INTEGER; a integer; b varchar2(100); abcdinteger; BEGIN cid := DBMS_SQL.OPEN_CURSOR; dyn_sql := 'CREATE TABLE mbn015 STORAGE (INITIAL 5M NEXT 5M) TABLESPACE MUGDBDATA1 AS( SELECT DISTINCT p.item, p.loc, p.cppprodmethod, c.loadoffsetdur, p.scheddate, (p.scheddate - c.loadoffsetdur/1440) calcdate FROM stsc.planorder p,stsc.cppprodmethodstep c WHERE p.item = c.item AND p.loc = c.loc AND p.cppprodmethod = c.cppprodmethod AND c.stepnum = 20)'; DBMS_SQL.PARSE(cid, dyn_sql, dbms_sql.v7); abcd := DBMS_SQL.EXECUTE(cid); dbms_output.put_line(abcd); DBMS_SQL.CLOSE_CURSOR(cid); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(cid); a := sqlcode; b := substr(sqlerrm,1,100); INSERT INTO errors VALUES (sysdate, 'A:CT', a, b); END create_table_mbn015; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Stored procedures, functions and packages
You can use describe in SQL*Plus to look at the arguments (parameters) only. HTH - Bhat -Original Message- From: Carlos Porras [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 22, 2001 7:01 AM To: Multiple recipients of list ORACLE-L Subject:Stored procedures, functions and packages Hi all, Gurus. Is there a view or synonym that stores the declaration part of procedures, functions and/or packages?. That is, something like SYS.argument$, user_arguments or all_arguments (useful for example to access the arguments of stored procedures and functions in an easy, structured way), but in this case of course only related to variables, types, constants, etc. (that is, the declaration part within the stored procedures, functions and packages)? Thanks a lot. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carlos Porras INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: MINUS VS NOT IN
Mark, The start time (when you press ENTER also matters here)... take care. How about 'set timing on'? Regards, - Bhat -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 10, 2001 4:05 AM To: Multiple recipients of list ORACLE-L Subject:Re:RE: MINUS VS NOT IN Mark, It's a pretty simple one, cause I do it all the time. check out the 'set prompt' command in SQL*Plus and 'set time on'. Dick Goulet Reply Separator Author: "Mark Leith" <[EMAIL PROTECTED]> Date: 2/9/2001 11:25 AM Jared, Cool little trick with your SQL prompt there, care to share how you did it? Cheers Mark -Original Message- [EMAIL PROTECTED] Sent: Friday, February 09, 2001 05:35 To: Multiple recipients of list ORACLE-L On Thu, 8 Feb 2001, Peter Hazelton wrote: > Considering the following: > > Query One > > select distinct icons from inpatient > minus select distinct icons from ptca; > > Query 2 > > select distinct icons from inpatient > where icons NOT IN(select distinct icons from ptca) > > Query number one began to run in about 5 seconds whereas query 2 took > forever to run. My question is why is the MINUS so much faster? Query 1 is a simple set operation inside the database, and therefore is rather fast. Query 2 requires doing a lookup in ptca for each row in inpatient; very ineffecient. Somebody mentioned a join taking place, but this is actually an anti join. If you had an appropriate index on ptca, a NOT EXISTS query would be much faster than the NOT IN. Below you will find the execution paths for each query ( no indexes ) Query 1 ran in a few seconds. Query 2 took several minutes. Jared 09:09:58 jkstill@jks02 SQL> 09:09:58 jkstill@jks02 SQL> create table c1 as 09:09:58 2 select * from dba_objects; Table created. 09:10:00 jkstill@jks02 SQL> 09:10:00 jkstill@jks02 SQL> create table c2 as 09:10:00 2 select * from c1; Table created. 09:10:00 jkstill@jks02 SQL> 09:10:00 jkstill@jks02 SQL> 09:10:00 jkstill@jks02 SQL> delete from 09:10:00 2 c2 where rownum < 201; 200 rows deleted. 09:10:00 jkstill@jks02 SQL> 09:10:00 jkstill@jks02 SQL> commit; Commit complete. 09:10:00 jkstill@jks02 SQL> 09:10:00 jkstill@jks02 SQL> 09:10:00 jkstill@jks02 SQL> set autotrace on 09:10:01 jkstill@jks02 SQL> 09:10:01 jkstill@jks02 SQL> select object_name, owner from c1 09:10:01 2 minus 09:10:01 3 select object_name, owner from c2 09:10:01 4 / OBJECT NAMEOWNER -- -- /1001a851_ConstantDefImpl SYS /1005bd30_LnkdConstant SYS /10076b23_OraCustomDatumClosur SYS ... 200 rows selected. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MINUS 2 1 SORT (UNIQUE) 3 2 TABLE ACCESS (FULL) OF 'C1' 4 1 SORT (UNIQUE) 5 4 TABLE ACCESS (FULL) OF 'C2' Statistics -- 0 recursive calls 200 rows processed 09:10:03 jkstill@jks02 SQL> 09:10:03 jkstill@jks02 SQL> select object_name, owner 09:10:03 2 from c1
Outer Join puzzle - How this works?
Hi All, I have the two following queries alongwith the results. Can anybody interpret the results (fourth sql) and let me know why it works like this (fourth sql). SQL> select * from a; NUM -- - - - - - 1 2 3 SQL> select * from b; NUM1NUM2 -- - - - - - - - - - - 1 << NUM2 is null in this record 2 2 3 1 SQL> select NUM, NUM1, NUM2 from a, b Where nvl(num2,1) = 1 and num=num1(+); NUM1NUM2 NUM3 -- - - - - - - - - - -- - - - - - 1 1 3 31 SQL> select NUM, NUM1, NUM2 from a, b Where nvl(num2(+),1) = 1 and num=num1(+); NUM1NUM2 NUM3 -- - - - - - - - - - -- - - - - - 1 1 2 3 3 1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
