Re:RE: pl/sql engine doubt

[dgoulet]

" c) Procedural part can be parsed either by an application tool or oracle
   kernel. The thing to watch for is the version of the PL/SQL engine
   embedded in the application tool. The ideal situation is when these
   two versions are the same. If they are not, life can get interesting."

To put it MILDLY!!!

Dick Goulet



Reply Separator
Author: "Gogala; Mladen" <[EMAIL PROTECTED]>
Date:   3/24/2003 7:08 AM

Let me throw some shade onto the issue:
a) PL/SQL engine does not execute SQL statements - ever. It passes them 
   over to the SQL Executor engine.
b) PL/SQL is just a procedural enclosure of the SQL language. There is 
   another one: it's called Java.
c) Procedural part can be parsed either by an application tool or oracle
   kernel. The thing to watch for is the version of the PL/SQL engine
   embedded in the application tool. The ideal situation is when these
   two versions are the same. If they are not, life can get interesting.

-Original Message-
Sent: Monday, March 24, 2003 4:29 AM
To: Multiple recipients of list ORACLE-L


Hi List,
I was going thru Oracle "PL/SQL User's Guide and
Reference".
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920
/a96624/01_oview.htm#962

Paragraphs below (near the "Figure 1-4 PL/SQL Engine"
in the doc) confused me little.

para1 ---
"These two environments are independent. PL/SQL is
bundled with the Oracle server but might be
unavailable in some tools. In either environment, the
PL/SQL engine accepts as input any valid PL/SQL block
or subprogram. Figure 1-4 shows the PL/SQL engine
processing an anonymous block. The engine executes
procedural statements but sends SQL statements to the
SQL Statement Executor in the Oracle server."


para2--
"In the Oracle Database Server:
Application development tools that lack a local PL/SQL
engine must rely on Oracle to process PL/SQL blocks
and subprograms. When it contains the PL/SQL engine,
an Oracle server can process PL/SQL blocks and
subprograms as well as single SQL statements. The
Oracle server passes the blocks and subprograms to its
local PL/SQL engine."


Now my doubt is:
1.In para1 - Does pl/sql engine only processes 
the procedural statements and always passes SQL 
statements to SQL Statement Executor ?

2.In para2 sentence "When it contains ..." says 
Oracle pl/sql engine can process SQL statements.

Statements in both paragraphs seems ambiguous. 
Pls shade some light.

Thanks
Sam


__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: sam d
  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: Gogala, Mladen
  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: pl/sql engine doubt

[Bjørn Engsig]

PL/SQL can be seen as a stand-alone interpreted language and can as such
exist in various environments.  "Various envrionments" does in real life
mean the PL/SQL engine is found in both the Oracle server and in the Oracle
Forms tool.  If an application needs to execute some PL/SQL it should (at
least in principle) be able to execute this in any available environment
where the PL/SQL engine is present, in practice, however, this is only the
case in the database server and in Oracle Forms (plus probably Oracle Reports,
I really don't know, but it's beside the point).  In practical terms, if
you are coding forms (plus probably reports), you have PL/SQL available in
the tool and in the database server; in all other cases, it's only in the
database server.  Whenever PL/SQL needs to execute some SQL statements, it
will have to go to the Oracle server; if your PL/SQL happens to already execute
there, it is a simple internal context switch inside the server, if your
PL/SQL happens to execute in Oracle Forms (or reports), it will have to go
over your SQL*Net connection to execute the SQL code.

To confuse things somewhat, PL/SQL actually has a SQL parser; hence, PL/SQL
can verify SQL statements during parse without actually talking to the database.
 This has some interesting side effects in version 8, where the SQL parser
inside  PL/SQL tend to not have adopted all the latest SQL features, i.e.
there is valid SQL (in e.g. 8.1.7), that you cannot use in PL/SQL without
using dynamic SQL (either DBMS_SQL or native dynamic SQL).

/Bjørn.

sam d wrote:

  Hi List,
I was going thru Oracle "PL/SQL User's Guide and
Reference".
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96624/01_oview.htm#962

Paragraphs below (near the "Figure 1-4 PL/SQL Engine"
in the doc) confused me little.

para1 ---
"These two environments are independent. PL/SQL is
bundled with the Oracle server but might be
unavailable in some tools. In either environment, the
PL/SQL engine accepts as input any valid PL/SQL block
or subprogram. Figure 1-4 shows the PL/SQL engine
processing an anonymous block. The engine executes
procedural statements but sends SQL statements to the
SQL Statement Executor in the Oracle server."


para2--
"In the Oracle Database Server:
Application development tools that lack a local PL/SQL
engine must rely on Oracle to process PL/SQL blocks
and subprograms. When it contains the PL/SQL engine,
an Oracle server can process PL/SQL blocks and
subprograms as well as single SQL statements. The
Oracle server passes the blocks and subprograms to its
local PL/SQL engine."


Now my doubt is:
1.In para1 - Does pl/sql engine only processes 
the procedural statements and always passes SQL 
statements to SQL Statement Executor ?

2.In para2 sentence "When it contains ..." says 
Oracle pl/sql engine can process SQL statements.

Statements in both paragraphs seems ambiguous. 
Pls shade some light.

Thanks
Sam


__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
  


-- 
 Bjørn Engsig, Miracle A/S 
 Member of Oak Table Network 
 [EMAIL PROTECTED] - http://MiracleAS.dk 
 

RE: pl/sql engine doubt

[Gogala, Mladen]

Let me throw some shade onto the issue:
a) PL/SQL engine does not execute SQL statements - ever. It passes them 
   over to the SQL Executor engine.
b) PL/SQL is just a procedural enclosure of the SQL language. There is 
   another one: it's called Java.
c) Procedural part can be parsed either by an application tool or oracle
   kernel. The thing to watch for is the version of the PL/SQL engine
   embedded in the application tool. The ideal situation is when these
   two versions are the same. If they are not, life can get interesting.

-Original Message-
Sent: Monday, March 24, 2003 4:29 AM
To: Multiple recipients of list ORACLE-L


Hi List,
I was going thru Oracle "PL/SQL User's Guide and
Reference".
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920
/a96624/01_oview.htm#962

Paragraphs below (near the "Figure 1-4 PL/SQL Engine"
in the doc) confused me little.

para1 ---
"These two environments are independent. PL/SQL is
bundled with the Oracle server but might be
unavailable in some tools. In either environment, the
PL/SQL engine accepts as input any valid PL/SQL block
or subprogram. Figure 1-4 shows the PL/SQL engine
processing an anonymous block. The engine executes
procedural statements but sends SQL statements to the
SQL Statement Executor in the Oracle server."


para2--
"In the Oracle Database Server:
Application development tools that lack a local PL/SQL
engine must rely on Oracle to process PL/SQL blocks
and subprograms. When it contains the PL/SQL engine,
an Oracle server can process PL/SQL blocks and
subprograms as well as single SQL statements. The
Oracle server passes the blocks and subprograms to its
local PL/SQL engine."


Now my doubt is:
1.In para1 - Does pl/sql engine only processes 
the procedural statements and always passes SQL 
statements to SQL Statement Executor ?

2.In para2 sentence "When it contains ..." says 
Oracle pl/sql engine can process SQL statements.

Statements in both paragraphs seems ambiguous. 
Pls shade some light.

Thanks
Sam


__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: sam d
  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: Gogala, Mladen
  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).

pl/sql engine doubt

[sam d]

Hi List,
I was going thru Oracle "PL/SQL User's Guide and
Reference".
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96624/01_oview.htm#962

Paragraphs below (near the "Figure 1-4 PL/SQL Engine"
in the doc) confused me little.

para1 ---
"These two environments are independent. PL/SQL is
bundled with the Oracle server but might be
unavailable in some tools. In either environment, the
PL/SQL engine accepts as input any valid PL/SQL block
or subprogram. Figure 1-4 shows the PL/SQL engine
processing an anonymous block. The engine executes
procedural statements but sends SQL statements to the
SQL Statement Executor in the Oracle server."


para2--
"In the Oracle Database Server:
Application development tools that lack a local PL/SQL
engine must rely on Oracle to process PL/SQL blocks
and subprograms. When it contains the PL/SQL engine,
an Oracle server can process PL/SQL blocks and
subprograms as well as single SQL statements. The
Oracle server passes the blocks and subprograms to its
local PL/SQL engine."


Now my doubt is:
1.In para1 - Does pl/sql engine only processes 
the procedural statements and always passes SQL 
statements to SQL Statement Executor ?

2.In para2 sentence "When it contains ..." says 
Oracle pl/sql engine can process SQL statements.

Statements in both paragraphs seems ambiguous. 
Pls shade some light.

Thanks
Sam


__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: sam d
  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: doubt

[Kevin Lange]

 Excuse the heresy for 
answering an SS question on this list.
 
When I first read that statement I was wondering 
why you would be asking a question about the Nazi SS on an Oracle List 
.  then it dawned on me that you were talking about Microsoft SQL 
Server ...  at which point it did not mater ... Nazi SS ...  Microsoft 
...  prety much the same thing. 

  -Original Message-From: Grant Allen 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 9:20 
  AMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  doubt
  Excuse the heresy for answering an SS question on 
  this list.
   
  Jai, 
  you can fight with the INFORMATION_SCHEMA views, or use sp_help to see all the 
  objects for all users (not just tables).  If you looking for 
  just tables, and only for a given user, use
   
  select o.namefrom sysobjects o, sysusers u where 
  o.uid = u.uidand o.xtype = 'U'and u.name = 
  'yourusernamehere'
   
  (obviously change the yourusernamehere bit to the 
  username desired).  You might want to try the usenet 
  comp.databases.ms-sqlserver group for more info.
   
  Ciao
  Fuzzy
   
  (yech ... I'm sorry, the MS brainwashing must be 
  stronger than I thought ... I have this irresistible urge to clap my hands and 
  sway back and forth with a moronic grin on my face after thinking of SQL 
  Server.  Can anyone suggest a therapist? :-) :-) :-) 
  ).
   
  
-Original Message-From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED]Sent: 
Thursday, January 02, 2003 14:04To: Multiple recipients of list 
ORACLE-LSubject: doubtDear all, 
      
                 we use 
 "select * from tab" to list all objects in a particular user in 
oracle. could you tell me the equivalent 
query in sql server ?   Regards Jai 

RE: doubt

[Grant Allen]

Excuse 
the heresy for answering an SS question on this list.
 
Jai, 
you can fight with the INFORMATION_SCHEMA views, or use sp_help to see all the 
objects for all users (not just tables).  If you looking for just 
tables, and only for a given user, use
 
select 
o.namefrom sysobjects o, sysusers u where o.uid = u.uidand o.xtype = 
'U'and u.name = 'yourusernamehere'
 
(obviously change the yourusernamehere bit to the 
username desired).  You might want to try the usenet 
comp.databases.ms-sqlserver group for more info.
 
Ciao
Fuzzy
 
(yech 
... I'm sorry, the MS brainwashing must be stronger than I thought ... I have 
this irresistible urge to clap my hands and sway back and forth with a moronic 
grin on my face after thinking of SQL Server.  Can anyone suggest a 
therapist? :-) :-) :-) ).
 

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED]Sent: 
  Thursday, January 02, 2003 14:04To: Multiple recipients of list 
  ORACLE-LSubject: doubtDear all, 
        
                   we use 
   "select * from tab" to list all objects in a particular user in 
  oracle. could you tell me the equivalent 
  query in sql server ?   Regards Jai 

RE: doubt

[Farnsworth, Dave]

In 
Query Analyzer select the database if interest and then run
 
EXEC 
SP_HELP (really, this is not a joke)  ;o)
 
It 
will list system and user objects within that DB.
 
Dave

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 8:04 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  doubtDear all,                   
       we use  "select * from tab" to list all objects in a 
  particular user in oracle. could you tell 
  me the equivalent query in sql server ?   
  Regards Jai 

doubt

[JayK]

Dear all,
 
                     we use  "select * from tab" to list all objects in a particular user in oracle.
could you tell me the equivalent query in sql server ?
 
Regards
Jai

Re: Question on in-doubt transaction

[David Davis]

Check out the documentation for the dbms_transaction package. I have used it 
previously (dbms_transaction.purge_lost_db_entry). In my case, the cause was 
due to a problem in SQL Server (DTS process).

See Docids on Metalink:

67590.1 - PROCEDURE:DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY
100664.1 - How to Troubleshoot Distributed Transactions

Good Luck.



From: "Mandal, Ashoke" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: Question on in-doubt transaction
Date: Mon, 04 Nov 2002 09:54:14 -0800

Greetings All,

We have a stand alone database on a Windows-NT box. As far as I understand 
from the users of this database that this is not in distributed 
environment.

But there is one in-doubt transaction in this database and if users try to 
work in this database the response is very slow due to this in-doubt 
transaction.

We bounced the database. Restarted the service but in-doubt transaction is 
still sitting there from Feb 2002.

We could see this transaction in DBA Studio also.

How to clean this transaction?

Thanks,
Ashoke
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mandal, Ashoke
  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).


_
Choose an Internet access plan right for you -- try MSN! 
http://resourcecenter.msn.com/access/plans/default.asp

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: David Davis
 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).

Question on in-doubt transaction

[Mandal, Ashoke]

Greetings All,

We have a stand alone database on a Windows-NT box. As far as I understand from the 
users of this database that this is not in distributed environment.

But there is one in-doubt transaction in this database and if users try to work in 
this database the response is very slow due to this in-doubt transaction.

We bounced the database. Restarted the service but in-doubt transaction is still 
sitting there from Feb 2002.

We could see this transaction in DBA Studio also.

How to clean this transaction?

Thanks,
Ashoke
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mandal, Ashoke
  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: Snapshots - Doubt about

[DENNIS WILLIAMS]

Eriovaldo
   As I said, Oracle now refers to snapshots as materialized views. You will
need to paste the links I have provided back together.

>From the Oracle8i Replication Manual, Chapter 3, Snapshot Concepts
"Oracle uses snapshots, also referred to as materialized views"
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76959/mvie
w.htm#26336
 

>From the Oracle9i SQL Reference, Create Materialized View Statement
When you create a materialized view, Oracle creates one internal table and
at least one index, and may create one view, all in the schema of the
materialized view.
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920
/a96540/statements_63a.htm#2063795
 

>From the Oracle9i Database Concepts Manual, Chapter 21, Data Integrity
You can place deferred unique and foreign key constraints on materialized
views, allowing fast and complete refresh to complete successfully.
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920
/a96524/c22integ.htm#11235
 

I hope this provides the clarification you seek. Even better, create a
materialized view and then create an index on it to prove to yourself how
this works.

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]

-Original Message-
 ]
Sent: Thursday, October 31, 2002 2:39 AM
To: Multiple recipients of list ORACLE-L


Dennis :

Is there any documento that explain all the resources about snapshot ?

I had read the Oracle Documentation, but when i am reading about snapshot I
don't see about index , understand ?

I would like a document specific about snapshot.

Regards

Eriovaldo



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, October 31, 2002 2:48 AM


> Eriovaldo - Oracle is moving to referring to snapshots as materialized
> views, and yes you can put an index on them.
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Wednesday, October 30, 2002 7:04 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Friends
>
> I would like to know about snapshots.
>
> Can I create a index on snapshot ? How to do it ?
> Why refresh with rowid is not recomended ?
> How to do when master table doesn't have a PK ?
>
> Regards
>
> Eriovaldo
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
 
> --
> Author: Eriovaldo Andrietta
>   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: 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).
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
 
--
Author: Eriovaldo Andrietta
  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: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538

Re: Snapshots - Doubt about

[Eriovaldo Andrietta]

Dennis :

Is there any documento that explain all the resources about snapshot ?

I had read the Oracle Documentation, but when i am reading about snapshot I
don't see about index , understand ?

I would like a document specific about snapshot.

Regards

Eriovaldo



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, October 31, 2002 2:48 AM


> Eriovaldo - Oracle is moving to referring to snapshots as materialized
> views, and yes you can put an index on them.
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Wednesday, October 30, 2002 7:04 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Friends
>
> I would like to know about snapshots.
>
> Can I create a index on snapshot ? How to do it ?
> Why refresh with rowid is not recomended ?
> How to do when master table doesn't have a PK ?
>
> Regards
>
> Eriovaldo
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Eriovaldo Andrietta
>   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: 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eriovaldo Andrietta
  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: Snapshots - Doubt about

[DENNIS WILLIAMS]

Eriovaldo - Oracle is moving to referring to snapshots as materialized
views, and yes you can put an index on them. 

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Wednesday, October 30, 2002 7:04 PM
To: Multiple recipients of list ORACLE-L


Friends

I would like to know about snapshots.

Can I create a index on snapshot ? How to do it ?
Why refresh with rowid is not recomended ?
How to do when master table doesn't have a PK ?

Regards

Eriovaldo



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eriovaldo Andrietta
  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: 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).

Snapshots - Doubt about

[Eriovaldo Andrietta]

Friends

I would like to know about snapshots.

Can I create a index on snapshot ? How to do it ?
Why refresh with rowid is not recomended ?
How to do when master table doesn't have a PK ?

Regards

Eriovaldo



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eriovaldo Andrietta
  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: doubt in INDEX

[Rajesh Dayal]

Dear "Guess Who",

   I don't think so.
   BTW, who are the "people" and why do they think so, we would love to see their 
explanation/theory.
   There could be several possibilities for having the rowids changed, like 
Exporting and Importing the tables, Moving the tables to diff T.S etc. 
   Other ideas are welcome ...

HTH,
Rajesh


- Original Message -
Date: Sun, 01 Sep 2002 20:13:21 -0800
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>


> 1.)
> People are saying that if a table is indexed then the all the 
> rowids will be changed , whether it is trueIf so , then how it 
> is done , give example please.
> 
> 2.)What are all the ways for the rowids to be changed
> 
> Explain.
> 
> Regards,
> Prakash.
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: guess who
>   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).
> 

-- 
__
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rajesh Dayal
  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: doubt in INDEX

[Mladen Gogala]

Creat index will not change rowids. Rowids are not easily changeable. 
Here are the evil ways of changing rowids:

a) alter table move ..
b) Update moves the row from one partition to another.
c) Update of the cluster key moves the row from one cluster block to another.
d) Update of the primary key in an IOT (there are complications with IOT,
   so people should generally use urowid. If you are enableing constraints, you 
   should generally use $ORACLE_HOME/rdbms/admin/utlexpt1.sql to create the exceptions 
   table, so tthat EXCEPTIONS table is created with urowid)
e) export/truncate/import

Creating index does not change rowid. 

On 2002.09.02 00:13 guess who wrote:
> 1.)
> People are saying that if a table is indexed then the all the 
> rowids will be changed , whether it is trueIf so , then how it 
> is done , give example please.
> 
> 2.)What are all the ways for the rowids to be changed
> 
> Explain.
> 
> Regards,
> Prakash.
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: guess who
>   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).
> 

-- 
Mladen Gogala
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mladen Gogala
  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: doubt in INDEX

[Naveen Nahata]

Prakash,

Though i don't remember fully, but the ROWID consists of the Block Address,
DataFile and other info.

Rowid's change when the row is migrated to a different location.

Since creating index doesn't migrate the row, if the table is indexed the
rowid's wont change.

Naveen

-Original Message-
Sent: Monday, September 02, 2002 9:43 AM
To: Multiple recipients of list ORACLE-L


1.)
People are saying that if a table is indexed then the all the 
rowids will be changed , whether it is trueIf so , then how it 
is done , give example please.

2.)What are all the ways for the rowids to be changed

Explain.

Regards,
Prakash.



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: guess who
  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: Naveen Nahata
  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).

doubt in INDEX

[guess who]

1.)
People are saying that if a table is indexed then the all the 
rowids will be changed , whether it is trueIf so , then how it 
is done , give example please.

2.)What are all the ways for the rowids to be changed

Explain.

Regards,
Prakash.



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: guess who
  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).

VB doubt

[guess who]

can any one send me the code for getting the server name to which 
the current working machine is connected to...

this code i want in VB guys...

help me very urgent...

Regards,
Prakash.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: guess who
  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).

Multimaster Replication Doubt...

[Rahul Dandekar]

1.
It is my understanding that Oracle 7.x has continuous purge
of the propogated deftrans. There is no option of scheduling
periodic purge at specific time. Am I correct?

2.
How to schedule continuous purge of propogated deftrans in
Oracle 8i from command line?

prompt  SCHEDULE PURGE AT MASTER SITE 
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
NEXT_DATE => SYSDATE,
INTERVAL => 'SYSDATE + 1/24',
DELAY_SECONDS => 0,
ROLLBACK_SEGMENT => '');
END;
/

Manual says set DELAY_SECONDS to 500,000 and set INTERVAL to a value less
than
DELAY_SECONDS. I could not understand exactly how this works...

+Rahul

  ___   ______   ___   ___
 /  /\ /  /\  /  /\ /  /\ /  /\
/  /::\   /  /::\/  /://  /://  /:/
   /  /:/\:\ /  /:/:|   /  /://  /://  /:/
  /  /::\ \:\   /  /:/|:|  /  /::\  __   /  /:/  ___   /  /:/
 /__/:/\:\_\:\ /__/::\|:| /__/:/\:\/ /\ /__/:/  /  /\ /__/:/
 \__\/~|::\/:/ \__\/\:\:| \__\/  \:\/:/ \  \:\ /  /:/ \  \:\
|  |:|::/ \__\::|  \__\::/   \  \:\  /:/   \  \:\
|  |:|\/   |  |:|  /  /:/ \  \:\/:/ \  \:\
|__|:| |__|:| /__/:/   \  \::/   \  \:\
 \__\|  \__\| \__\/ \__\/ \__\/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rahul Dandekar
  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: Another doubt in 9.0.1.0.0 database..

[Enrique Orbegozo]

  Hi,
      There is a bug when you asign java_pool_size=0, it happened to 
  me.
   
  ORA-04031: no 
  se han podido asignar 4032 bytes de memoria compartida ("shared 
  pool","unknown object","joxs heap 
  init","ioc_allocate_pal")
  Metalink:
   
   fact: Oracle Server - 
  Enterprise Edition 9.0.1 symptom: ORA-00604: error 
  occurred at recursive SQL level 1 symptom: 
  ORA-04031: unable to allocate %n bytes of shared memory 
  (" shared 
  pool",%s,%s,%s) symptom: ORA-06512: at 
  "SYS.DBMS_CDC_PUBLISH", line 0 symptom: ORA-06512: 
  at line 4 change: Java_pool_size <= 
  4M cause: 
  : ERROR OCCURRED AT RECURSIVE 
  SQL LEVEL 1
   
  fix:
   
  Startup the database caused 
  following errors logged in the alert file:
   
  ORA-00604: error occurred 
  at recursive SQL level 1ORA-04031: unable to allocate 4032 bytes of shared 
  memory ("shared 
  pool&quot;,   
  "unknown object","joxs heap init","ioc_allocate_pal")ORA-06512: at 
  "SYS.DBMS_CDC_PUBLISH", line 0ORA-06512: at line 4
   
  Afterwards, since the 
  database is open, the errors also occur when creating ordropping database 
  objects (tables, external tables, ...), neverthenless withslightly 
  different parameters in the ORA-4031 error message, like:
   
  ORA-04031: unable to 
  allocate 4032 bytes of shared memory ("shared 
  pool&quot;,   
  "unknown object","joxs heap init","ioc_allocate_pal")
   
  or
   
  ORA-04031: unable to 
  allocate 4032 bytes of shared memory ("shared 
  pool&quot;,   
  "sun/misc/URLClassPathSYS","joxlod: in 
  ehe",&quot;ioc_allocate_pal")
   
  or
   
  ORA-04031: unable to 
  allocate 4032 bytes of shared memory ("shared 
  pool&quot;   
  "oracle/CDC/ChangeTableSYS","joxlod: in 
  ehe",&quot;ioc_allocate_pal")
   
  The size of the shared 
  pool doesn't influence the result. This means thatincreasing the 
  shared_pool_size will not solve the problem.
   
  The problem is fixed, 
  setting the java_pool_size greater then 4M. In fact avalue of 4194305 (4M 
  + 1 byte) would be sufficient, since the java_pool_size isadjusted to a 
  multiple of 4M. In this case the java_pool_size would be 8M.
   
  It seems that there is a 
  threshold of java_pool_size of 5M based on v$sgastat.
   
  Remark:--
   
   is 
  initially fixed in version 9.2.
   
  . 
  
   
   
   -Mensaje 
  original-De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En 
  nombre de Arul kumarEnviado el: Jueves, 14 de Febrero de 2002 
  03:08 a.m.Para: Multiple recipients of list 
  ORACLE-LAsunto: Another doubt in 9.0.1.0.0 
  database..Hi DBAs, 
  
  While reducing the JAVA_POOL_SIZE in initfile , i observed that minimum 
  it takes 8M and not less than that. 
  If we dont specify this parameter, default value it takes as 20 M 
  !!. 
  If there is no JAVA related stuff in my application / database..., why 
  should i have this space allocated.? 
  Moreover, this space allocation seems to be affecting the PGA size!! 
  Any suggestions to make it NULL or zero. ?? 
  Thank you. 
  regards, Arul. 
  *DisclaimerThis 
  message (including any attachments) contains confidential information 
  intended for a specific individual and purpose, and is protected by law. 
  If you are not the intended recipient, you should delete this message 
  and are hereby notified that any disclosure, copying, or distribution of 
  thismessage, or the taking of any action based on it, is strictly 
  prohibited.*Visit 
  us at http://www.mahindrabt.com

  -Mensaje original-De: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]En nombre de Arul kumarEnviado el: 
  Jueves, 14 de Febrero de 2002 03:08 a.m.Para: Multiple recipients 
  of list ORACLE-LAsunto: Another doubt in 9.0.1.0.0 
  database..Hi DBAs, 
  While reducing the JAVA_POOL_SIZE in initfile , i observed that minimum 
  it takes 8M and not less than that. 
  If we dont specify this parameter, default value it takes as 20 M 
  !!. 
  If there is no JAVA related stuff in my application / database..., why 
  should i have this space allocated.? 
  Moreover, this space allocation seems to be affecting the PGA size!! 
  Any suggestions to make it NULL or zero. ?? 
  Thank you. 
  regards, Arul. 
  *DisclaimerThis 
  message (including any attachments) contains confidential information 
  intended for a specific individual and purpose, and is protected by law. 
  If you are not the intended recipient, you should delete this message 
  and are hereby notified that any disclosure, copying, or distribution of 
  thismessage, or the taking of any action based on it, is strictly 
  prohibited.*Visit 
  us at http://www.mahindrabt.com

Re: A doubt in 9.0.1.0.0 database..

[Arul kumar]

Hi Joe,
Thanks for the prompt reply in the forum.
btw, When we use AUTO value for UNDO Management, it is supposed to be
handled by oracle AUTOmatically.
I have read the docs. there is not much info reg. this..
My question is :
If Oracle is handling it, then why contention for UNDO HEADER in V$waitstat...inspite
of being allocated enough space for the underlying tablespace ( UNDOTBS
)??
I will appreciate , if u can really help me.
TIA
regards,
Arul.
 
 
Joe Testa wrote:
its not a doubt and you're not using rollback, your
using automatic undo
management.
read up on it in the admin guide,.
joe
Arul kumar wrote:
> Hi DBAs,
>
> I got a problem in Rollback Segments..
>
> This is the setting for UNDO ( Rollback ) in my init file...
>
> undo_management=AUTO
> undo_retention=900
> undo_suppress_errors=FALSE
> undo_tablespace=UNDOTBS
>
> While querying V$waitstat, got the foll. :
>
> CLASS   
COUNT
TIME
> system undo header   
0   
0
> system undo block 
0   
0
> *undo header 
312 
64*
> undo block
1 
32
>
> The entry for "undo header" indicates Undo Header contention for
> rollback.. Right ?
>
> Since it is "AUTO" managed, how do i tune ??
>
> I have chk  the space usage of the tablespace, it is fairly
enough free
> space...
>
> Any suggestions / tips ?
>
> Thank You.
>
> regards,
> Arul.
> *
> Disclaimer
>
> This message (including any attachments) contains
> confidential information intended for a specific
> individual and purpose, and is protected by law.
> If you are not the intended recipient, you should
> delete this message and are hereby notified that
> any disclosure, copying, or distribution of this
> message, or the taking of any action based on it,
> is strictly prohibited.
>
> *
> Visit us at http://www.mahindrabt.com
>
--
Joe Testa, Oracle DBA
Nothing new to put here, hmm
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Joe Testa
  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 message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*
Visit us at http://www.mahindrabt.com

Re: A doubt in 9.0.1.0.0 database..

[Joe Testa]

its not a doubt and you're not using rollback, your using automatic undo 
management.


read up on it in the admin guide,.

joe


Arul kumar wrote:

> Hi DBAs,
> 
> I got a problem in Rollback Segments..
> 
> This is the setting for UNDO ( Rollback ) in my init file...
> 
> undo_management=AUTO
> undo_retention=900
> undo_suppress_errors=FALSE
> undo_tablespace=UNDOTBS
> 
> While querying V$waitstat, got the foll. :
> 
> CLASSCOUNT TIME
> system undo header00
> system undo block  00
> *undo header  312  64*
> undo block 1  32
> 
> The entry for "undo header" indicates Undo Header contention for 
> rollback.. Right ?
> 
> Since it is "AUTO" managed, how do i tune ??
> 
> I have chk  the space usage of the tablespace, it is fairly enough free
> space...
> 
> Any suggestions / tips ?
> 
> Thank You.
> 
> regards,
> Arul.
> *
> Disclaimer
> 
> This message (including any attachments) contains
> confidential information intended for a specific
> individual and purpose, and is protected by law.
> If you are not the intended recipient, you should
> delete this message and are hereby notified that
> any disclosure, copying, or distribution of this
> message, or the taking of any action based on it,
> is strictly prohibited.
> 
> *
> Visit us at http://www.mahindrabt.com
> 


-- 
Joe Testa, Oracle DBA
Nothing new to put here, hmm






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joe Testa
  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: Another doubt in 9.0.1.0.0 database..

[Joe Testa]

read admin guide.

joe


Arul kumar wrote:

> Hi DBAs,
> 
> While reducing the JAVA_POOL_SIZE in initfile , i observed that 
> minimum it takes *8M* and not less than that.
> 
> If we dont specify this parameter, default value it takes as *20 M *!!.
> 
> If there is no JAVA related stuff in my application / database..., why 
> should i have this space allocated.?
> 
> Moreover, this space allocation seems to be affecting the PGA size!!
> 
> Any suggestions to make it NULL or zero. ??
> 
> Thank you.
> 
> regards,
> Arul.
> *
> Disclaimer
> 
> This message (including any attachments) contains
> confidential information intended for a specific
> individual and purpose, and is protected by law.
> If you are not the intended recipient, you should
> delete this message and are hereby notified that
> any disclosure, copying, or distribution of this
> message, or the taking of any action based on it,
> is strictly prohibited.
> 
> *
> Visit us at http://www.mahindrabt.com
> 


-- 
Joe Testa, Oracle DBA
Nothing new to put here, hmm






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joe Testa
  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: Another doubt in 9.0.1.0.0 database..

[Connor McDonald]

>From the performance guide:

"Memory for the shared pool and buffer cache is
allocated in units of granules. A granule can be 4 MB
or 16 MB, depending on the total size of your SGA at
the time of instance startup. If the size of your SGA
is less than 128 MB, then the granules are 4 MB in
size; otherwise, they are 16 MB."

hth
connor

 --- Arul kumar <[EMAIL PROTECTED]> wrote: > Hi
DBAs,
> 
> While reducing the JAVA_POOL_SIZE in initfile ,
> i observed that
> minimum it takes 8M and not less than that.
> 
> If we dont specify this parameter, default value it
> takes as 20 M !!.
> 
> If there is no JAVA related stuff in my application
> / database..., why
> should i have this space allocated.?
> 
> Moreover, this space allocation seems to be
> affecting the PGA size!!
> 
> Any suggestions to make it NULL or zero. ??
> 
> Thank you.
> 
> regards,
> Arul.
> 
>
*
> Disclaimer
> 
> This message (including any attachments) contains 
> confidential information intended for a specific 
> individual and purpose, and is protected by law. 
> If you are not the intended recipient, you should 
> delete this message and are hereby notified that 
> any disclosure, copying, or distribution of this
> message, or the taking of any action based on it, 
> is strictly prohibited.
> 
>
*
> Visit us at http://www.mahindrabt.com 

=
Connor McDonald
http://www.oracledba.co.uk (mirrored at 
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  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).

Another doubt in 9.0.1.0.0 database..

[Arul kumar]

Hi DBAs,
While reducing the JAVA_POOL_SIZE in initfile , i observed that
minimum it takes 8M and not less than that.
If we dont specify this parameter, default value it takes as 20 M
!!.
If there is no JAVA related stuff in my application / database..., why
should i have this space allocated.?
Moreover, this space allocation seems to be affecting the PGA size!!
Any suggestions to make it NULL or zero. ??
Thank you.
regards,
Arul.



*
Disclaimer

This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*
Visit us at http://www.mahindrabt.com

A doubt in 9.0.1.0.0 database..

[Arul kumar]

Hi DBAs,
I got a problem in Rollback Segments..
This is the setting for UNDO ( Rollback ) in my init file...
undo_management=AUTO
undo_retention=900
undo_suppress_errors=FALSE
undo_tablespace=UNDOTBS
While querying V$waitstat, got the foll. :
CLASS   
COUNT
TIME
system undo header   
0   
0
system undo block 
0   
0
undo header 
312 
64
undo block
1 
32
The entry for "undo header" indicates Undo Header contention for rollback..
Right ?
Since it is "AUTO" managed, how do i tune ??
I have chk  the space usage of the tablespace, it is fairly enough
free
space...
Any suggestions / tips ?
Thank You.
regards,
Arul.



*
Disclaimer

This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*
Visit us at http://www.mahindrabt.com

selftest q's doubt

[Nirmal Kumar Muthu Kumaran]

Title: selftest q's doubt





Helo guru's


I had small doubts, may be it's trival too. 


Please remind me, How the following statements are true:


a) "Imiplicit cursors process only one row at a time"
Eg. Suppose a delete statement wipe off 10 rows from a table. 
This case How many rows are processes?, Is't 10 rows or 1 row?


b) "When using an ORDER BY clause to sort the rows returned by a query,
 the sort limit is the number of columns that exist in the tables(s) listed in the FROM clause."
Eg. Assume that a table t having 2 columns c1, c2. In this case i can do the following query:
sql> select c1, c2, c1||'a', 'abc' from t order by 1,2,3,4;
Here the table having only 2 columns, however i can able to sort in a query upto the number of columns exists in the select list.

If so, how come quoted statement becomes true?.


Is i'm missing something?.


PS:. These hints got from the selftest.


Rgds,
Nirmal.

offline - Dynamic SQL doubt...

[Arul kumar]

Hi,

I want to call a procedure dynamically which returns a pl/sql table (OUT
Parameter).

This is the package where the procedure is defined.

==
create or replace package body pk1 as

procedure proc1(no number, pltab out dbms_sql.varchar2_table)
as
tempdate date;
begin
select sysdate into tempdate from dual;
pltab(1):='First';
pltab(2):='Second';
pltab(3):='Third';
pltab(4):='Fourth';
pltab(5):='Fifth';
end proc1;
end pk1;
/
===

Anonymous pl/sql block calling the above procedure

fire.sql


declare

pltab2 dbms_sql.varchar2_table;
ln_num number:=100;

c number;
dummy number;
stmt varchar2(100);

begin

stmt := 'begin pk1.proc1(:ln_num, :pltab); end;';

c := dbms_sql.open_cursor;

dbms_sql.parse(c, stmt, dbms_sql.native);
dbms_sql.bind_variable(c, ':ln_num', ln_num);
dbms_sql.bind_array(c, ':pltab', pltab2,1,2);
dummy := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
end;
/

ERROR at line 1:
ORA-29252: collection does not contain elements at index locations in
call to dbms_sql.bind_array
ORA-06512: at "SYS.DBMS_SYS_SQL", line 895
ORA-06512: at "SYS.DBMS_SQL", line 140
ORA-06512: at line 21

I am getting the above error.
Can anyone tell me what is wrong in this code?

Thank You.

Arul.



*
Disclaimer

This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*
Visit us at http://www.mahindrabt.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Arul kumar
  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: QUERRY DOUBT..

[Saurabh Sharma]

THANKS A LOT, Seley 
 
the querry works absolutely fine. 
instead i've created a view. which can be easier to querry. and it also let me 
able to devise way for finding each individuals' report.
 
thanks again.
 
saurabh sharma

  - Original Message - 
  From: 
  Seley, 
  Linda 
  To: Multiple recipients of list ORACLE-L 
  Sent: Friday, May 25, 2001 1:51 PM
  Subject: RE: QUERRY DOUBT..
  
  Try 
  this:
   
  select d.owner, 
  d.table_name,b.constraint_name,b.constraint_type,d.column_namefrom 
  all_tables a,all_constraints b,all_cons_columns c, all_tab_columns dwhere 
  d.owner = c.owner (+)and d.table_name = c.table_name (+)and 
  d.column_name = c.column_name (+)and c.table_name = a.table_name 
  (+)and c.table_name = b.table_name (+)and c.constraint_name = 
  b.constraint_name(+)and c.owner = a.owner (+)and c.owner = b.owner 
  (+)order by d.owner, d.table_name, d.column_id;
   
  HTH
   
  Linda
  
-Original Message-From: Saurabh Sharma 
[mailto:[EMAIL PROTECTED]]Sent: Friday, May 25, 2001 12:46 
AMTo: Multiple recipients of list ORACLE-LSubject: 
    QUERRY DOUBT..
hi all,
 
i've a querry that is to find 
out what all columns have constraints applied on them. giving the 
table_name,column_name,constraint name and constraint type.
 
the querry goes 
like..
 
select 
a.table_name,b.constraint_name,b.constraint_type,c.column_namefrom 
user_tables a,user_constraints b,user_cons_columns cwhere 
a.table_name=b.table_name 
andb.constraint_name=c.constraint_name/
 
now i need to make a report 
which gives out in the same result all the tables' columns which have 
constraints and which have not, both.
leaving the constraint_type and 
constraint name columns null in the same querry.
is it possible, or do we have 
other alternative to do that.
 
pls suggest.
thanks
 
saurabh
 
 

RE: QUERRY DOUBT..

[Seley, Linda]

Try 
this:
 
select 
d.owner, d.table_name,b.constraint_name,b.constraint_type,d.column_namefrom 
all_tables a,all_constraints b,all_cons_columns c, all_tab_columns dwhere 
d.owner = c.owner (+)and d.table_name = c.table_name (+)and 
d.column_name = c.column_name (+)and c.table_name = a.table_name (+)and 
c.table_name = b.table_name (+)and c.constraint_name = 
b.constraint_name(+)and c.owner = a.owner (+)and c.owner = b.owner 
(+)order by d.owner, d.table_name, d.column_id;
 
HTH
 
Linda

  -Original Message-From: Saurabh Sharma 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, May 25, 2001 12:46 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  QUERRY DOUBT..
  hi all,
   
  i've a querry that is to find out 
  what all columns have constraints applied on them. giving the 
  table_name,column_name,constraint name and constraint type.
   
  the querry goes 
  like..
   
  select 
  a.table_name,b.constraint_name,b.constraint_type,c.column_namefrom 
  user_tables a,user_constraints b,user_cons_columns cwhere 
  a.table_name=b.table_name 
  andb.constraint_name=c.constraint_name/
   
  now i need to make a report which 
  gives out in the same result all the tables' columns which have constraints 
  and which have not, both.
  leaving the constraint_type and 
  constraint name columns null in the same querry.
  is it possible, or do we have 
  other alternative to do that.
   
  pls suggest.
  thanks
   
  saurabh
   
   

QUERRY DOUBT..

[Saurabh Sharma]

hi all,
 
i've a querry that is to find out 
what all columns have constraints applied on them. giving the 
table_name,column_name,constraint name and constraint type.
 
the querry goes like..
 
select 
a.table_name,b.constraint_name,b.constraint_type,c.column_namefrom 
user_tables a,user_constraints b,user_cons_columns cwhere 
a.table_name=b.table_name 
andb.constraint_name=c.constraint_name/
 
now i need to make a report which 
gives out in the same result all the tables' columns which have constraints and 
which have not, both.
leaving the constraint_type and 
constraint name columns null in the same querry.
is it possible, or do we have other 
alternative to do that.
 
pls suggest.
thanks
 
saurabh
 
 

RE: Easy Doubt in sql/plus

[Ron Rogers]

You could also set the column format to be equal to the page width and then each 
column will be on a different row.
ROR mª¿ªm

>>> [EMAIL PROTECTED] 03/21/01 02:37PM >>>
isn't that a bit of overkill?

select column1||chr(10), column2||chr(10),. from table;

should do it


>From: "Trassens, Christian" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED] 
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: Easy Doubt in sql/plus
>Date: Wed, 21 Mar 2001 10:20:43 -0800
>
>With subqueries.
>
>Regards.
>
> > -Mensaje original-
> > De: Eriovaldo do Carmo Andrietta [SMTP:[EMAIL PROTECTED]] 
> > Enviado el: miércoles 21 de marzo de 2001 19:03
> > Para:   Multiple recipients of list ORACLE-L
> > Asunto: Easy Doubt in sql/plus
> >
> > Hi Friends :
> >
> > How can I do for put each column in new line using sql/plus
> >
> > Example :
> >
> > Select * from tab;
> >
> > and the result sould be :
> >
> > 'value of column1'
> > 'value of column2'
> > 'value of column3'
> > 'value of column4'
> > 'value of column5'
> >
> > and not
> >
> > 'value of column1' 'value of column2' 'value of column3' 'value of
> > column4'
> > 'value of column5'
> >
> > Thanks
> >
> > Eriovaldo
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com 
> > --
> > Author: Eriovaldo do Carmo Andrietta
> >   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: Trassens, Christian
>   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 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Rachel Carmichael
  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: Ron Rogers
  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: Easy Doubt in sql/plus

[elkinsl]

Eriovaldo,

If you don't want to have to mess around with your SQL adding chr(10), and
since you said in SQL*Plus, you could use column commands:

SQL> column dname newline
SQL> column loc newline

  1* select * from dept
SQL> /

10
ACCOUNTING
NEW YORK

20
RESEARCH
DALLAS

You can also look into using fold_after or fold_before with the col(umn)
command.

Not that I think the column commands approach is any better  or worse, and,
it *is* less typing to do the char(10) approach -- just wanted to bring up
another approach that hadn't been mentioned.

Regards,

Larry G. Elkins
[EMAIL PROTECTED]
-Original Message-
Carmo Andrietta
Sent: Wednesday, March 21, 2001 12:03 PM
To: Multiple recipients of list ORACLE-L


Hi Friends :

How can I do for put each column in new line using sql/plus

Example :

Select * from tab;

and the result sould be :

'value of column1'
'value of column2'
'value of column3'
'value of column4'
'value of column5'

and not

'value of column1' 'value of column2' 'value of column3' 'value of column4'
'value of column5'

Thanks

Eriovaldo

-- 
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: Easy Doubt in sql/plus

[Rachel Carmichael]

isn't that a bit of overkill?

select column1||chr(10), column2||chr(10),. from table;

should do it


>From: "Trassens, Christian" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: Easy Doubt in sql/plus
>Date: Wed, 21 Mar 2001 10:20:43 -0800
>
>With subqueries.
>
>Regards.
>
> > -Mensaje original-
> > De: Eriovaldo do Carmo Andrietta [SMTP:[EMAIL PROTECTED]]
> > Enviado el: miércoles 21 de marzo de 2001 19:03
> > Para:   Multiple recipients of list ORACLE-L
> > Asunto: Easy Doubt in sql/plus
> >
> > Hi Friends :
> >
> > How can I do for put each column in new line using sql/plus
> >
> > Example :
> >
> > Select * from tab;
> >
> > and the result sould be :
> >
> > 'value of column1'
> > 'value of column2'
> > 'value of column3'
> > 'value of column4'
> > 'value of column5'
> >
> > and not
> >
> > 'value of column1' 'value of column2' 'value of column3' 'value of
> > column4'
> > 'value of column5'
> >
> > Thanks
> >
> > Eriovaldo
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Eriovaldo do Carmo Andrietta
> >   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: Trassens, Christian
>   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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  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: Easy Doubt in sql/plus

[Eriovaldo do Carmo Andrietta]

No, using only one statment select like

select * from tab,

what can i set before  ?

Eriovaldo do Carmo Andrietta
[EMAIL PROTECTED]
ICQ #102604225



- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Wednesday, March 21, 2001 3:20 PM


With subqueries.

Regards.

> -Mensaje original-
> De: Eriovaldo do Carmo Andrietta [SMTP:[EMAIL PROTECTED]]
> Enviado el: miércoles 21 de marzo de 2001 19:03
> Para: Multiple recipients of list ORACLE-L
> Asunto: Easy Doubt in sql/plus
>
> Hi Friends :
>
> How can I do for put each column in new line using sql/plus
>
> Example :
>
> Select * from tab;
>
> and the result sould be :
>
> 'value of column1'
> 'value of column2'
> 'value of column3'
> 'value of column4'
> 'value of column5'
>
> and not
>
> 'value of column1' 'value of column2' 'value of column3' 'value of
> column4'
> 'value of column5'
>
> Thanks
>
> Eriovaldo
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Eriovaldo do Carmo Andrietta
>   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: Trassens, Christian
  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: Eriovaldo do Carmo Andrietta
  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: Easy Doubt in sql/plus

[Jack C. Applewhite]

Eriovaldo,

Try this...

Select Col1 || Chr(10) || Col2 || Chr(10) || Col3 || Chr(10)
|| Col4 As TheList From Table;

Results...

THELIST
---
Value of Col1
Value of Col2
Value of Col3
Value of Col4

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]


-Original Message-
Eriovaldo do Carmo Andrietta
Sent: Wednesday, March 21, 2001 12:03 PM
To: Multiple recipients of list ORACLE-L


Hi Friends :

How can I do for put each column in new line using sql/plus

Example :

Select * from tab;

and the result sould be :

'value of column1'
'value of column2'
'value of column3'
'value of column4'
'value of column5'

and not

'value of column1' 'value of column2' 'value of column3'
'value of column4'
'value of column5'

Thanks

Eriovaldo

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  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: Easy Doubt in sql/plus

[Cale, Rick T (Richard)]

SELECT column1||chr(10)||column2||chr(10)||columnN
from table;

Rick

> -Original Message-
> From: Eriovaldo do Carmo Andrietta [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, March 21, 2001 1:03 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  Easy Doubt in sql/plus
> 
> Hi Friends :
> 
> How can I do for put each column in new line using sql/plus
> 
> Example :
> 
> Select * from tab;
> 
> and the result sould be :
> 
> 'value of column1'
> 'value of column2'
> 'value of column3'
> 'value of column4'
> 'value of column5'
> 
> and not
> 
> 'value of column1' 'value of column2' 'value of column3' 'value of
> column4'
> 'value of column5'
> 
> Thanks
> 
> Eriovaldo
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Eriovaldo do Carmo Andrietta
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cale, Rick T (Richard)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Easy Doubt in sql/plus

[Tim Sawmiller]

or this messy way:

select 
column1 || chr(10)
|| column2 || chr(10)
|| column3 || chr(10
.
|| column99
/

chr(10) is a return command

>>> [EMAIL PROTECTED] 03/21/01 01:20PM >>>
With subqueries.

Regards.

> -Mensaje original-
> De:   Eriovaldo do Carmo Andrietta [SMTP:[EMAIL PROTECTED]] 
> Enviado el:   miércoles 21 de marzo de 2001 19:03
> Para: Multiple recipients of list ORACLE-L
> Asunto:   Easy Doubt in sql/plus 
> 
> Hi Friends :
> 
> How can I do for put each column in new line using sql/plus
> 
> Example :
> 
> Select * from tab;
> 
> and the result sould be :
> 
> 'value of column1'
> 'value of column2'
> 'value of column3'
> 'value of column4'
> 'value of column5'
> 
> and not
> 
> 'value of column1' 'value of column2' 'value of column3' 'value of
> column4'
> 'value of column5'
> 
> Thanks
> 
> Eriovaldo
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com 
> -- 
> Author: Eriovaldo do Carmo Andrietta
>   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: Trassens, Christian
  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: Tim Sawmiller
  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: Easy Doubt in sql/plus

[Montero, German Dario]

Title: RE: Easy Doubt in sql/plus 





Probe this.


SQL> set pages 0
SQL> set feed off
SQL> set head off
SQL> set linesize 1000
SQL> set trims on
SQL> r
  1  select cod_estado || chr(10) ||
  2 des_estado
  3* from co_estados
CO
CORRECTO
AV
AVISADO
SS
SUSPENDIDO
EJ
EN PROCESO EXTRAJUDICIAL
PJ
EN PROCESO JUDICIAL
DI
DEUDOR INCOBRABLE
CF
CUENTA FINAL


bye.


gmon.



-Mensaje original-
De: Eriovaldo do Carmo Andrietta [mailto:[EMAIL PROTECTED]]
Enviado el: Miercoles 21 de Marzo de 2001 15:03
Para: Multiple recipients of list ORACLE-L
Asunto: Easy Doubt in sql/plus 



Hi Friends :


How can I do for put each column in new line using sql/plus


Example :


Select * from tab;


and the result sould be :


'value of column1'
'value of column2'
'value of column3'
'value of column4'
'value of column5'


and not


'value of column1' 'value of column2' 'value of column3' 'value of column4'
'value of column5'


Thanks


Eriovaldo


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eriovaldo do Carmo Andrietta
  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: Easy Doubt in sql/plus

[Trassens, Christian]

With subqueries.

Regards.

> -Mensaje original-
> De:   Eriovaldo do Carmo Andrietta [SMTP:[EMAIL PROTECTED]]
> Enviado el:   miércoles 21 de marzo de 2001 19:03
> Para: Multiple recipients of list ORACLE-L
> Asunto:   Easy Doubt in sql/plus 
> 
> Hi Friends :
> 
> How can I do for put each column in new line using sql/plus
> 
> Example :
> 
> Select * from tab;
> 
> and the result sould be :
> 
> 'value of column1'
> 'value of column2'
> 'value of column3'
> 'value of column4'
> 'value of column5'
> 
> and not
> 
> 'value of column1' 'value of column2' 'value of column3' 'value of
> column4'
> 'value of column5'
> 
> Thanks
> 
> Eriovaldo
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Eriovaldo do Carmo Andrietta
>   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: Trassens, Christian
  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).

Easy Doubt in sql/plus

[Eriovaldo do Carmo Andrietta]

Hi Friends :

How can I do for put each column in new line using sql/plus

Example :

Select * from tab;

and the result sould be :

'value of column1'
'value of column2'
'value of column3'
'value of column4'
'value of column5'

and not

'value of column1' 'value of column2' 'value of column3' 'value of column4'
'value of column5'

Thanks

Eriovaldo

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eriovaldo do Carmo Andrietta
  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).

Doubt about Designer 2000

[Eriovaldo do Carmo Andrietta]

Hi Friends :

I have the follow situation working with designer ...

I have a table A. The owner´s table is application A, user SCHEMA_A.
This table is shared with application B.

Then

I have application B and inside it tables that do references with table A.

When i genereate database from server model, it doesn´t genereate for me the
correct reference in the script.

ALTER TABLE XXX ADD CONSTRAINT
 FK_XXX FOREIGN KEY
  (FIELD1) REFERENCES A
  (FIELD1)

the correct is :

ALTER TABLE XXX ADD CONSTRAINT
 FK_XXX FOREIGN KEY
  (FIELD1) REFERENCES SCHEMA_A.A
  (FIELD1)

Does anyone know how to do it ?

Eriovaldo do Carmo Andrietta
[EMAIL PROTECTED]
ICQ #102604225


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eriovaldo do Carmo Andrietta
  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: [Doubt] - Returning Type Table

[jkstill]

> scott> VARIABLE rtc REFCURSOR
> scott> EXECUTE :rtc := rtc_dept
> begin :rtc := rtc_dept; end;
>

The first thing I notice is that the code
above is assinging an 'index by table' to a ref cursor.

You might start there.

Jared


On Mon, 19 Feb 2001, Eriovaldo do Carmo Andrietta wrote:

>
> Hi Friends :
>
> I have the code bellow and would like to return the variable type table.
>
>
> CREATE OR REPLACE PACKAGE cv_types AS
>TYPE DeptCurType is REF CURSOR RETURN dept%ROWTYPE;
>TYPE retorno IS TABLE OF SCOTT.dept%ROWTYPEINDEX BY BINARY_INTEGER;
>TYPE Tipo_Tabela_Emp  IS TABLE OF EMP%ROWTYPE  INDEX BY BINARY_INTEGER;
>TYPE Tipo_Tabela_Dept IS TABLE OF DEPT%ROWTYPE INDEX BY BINARY_INTEGER;
> END cv_types;
>
>
> create or replace function rtc_dept
> RETURN cv_types.Tipo_Tabela_Dept
> IS
> cursor resultset is
>  select * from dept;
> tabdept   cv_types.Tipo_tabela_Dept;
> I BINARY_INTEGER;
> tab  dept%rowtype;
>BEGIN
> i := 1;
> open resultset;
> loop
> fetch resultset into tab;
> exit when resultset%notfound;
> tabdept(i).dname :=   '- ' || tab.dname;
> dbms_output.put_line (tabdept(i).dname);
> i := i + 1;
> end loop;
> close resultset;
> RETURN(tabdept);
> END;
>
> scott>
> scott>
> scott>
> scott> VARIABLE rtc REFCURSOR
> scott> EXECUTE :rtc := rtc_dept
> begin :rtc := rtc_dept; end;
>
>   *
> ERROR at line 1:
> ORA-06550: line 1, column 15:
> PLS-00382: expression is of wrong type
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
>
> scott> PRINT rtc
> ERROR:
> ORA-24338: statement handle not executed
>
>
> Error printing variable "rtc"
> scott>
>
> what is wrong ?
>
> Eriovaldo do Carmo Andrietta
> Widesoft Sistemas Ltda.
> Limeira/SP - Brasil
>
>
>

-- 
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).

[Doubt] Returning cursor / Type Table / Array

[Eriovaldo do Carmo Andrietta]

Does anyone have examples that works with :

Variable in out (refcursor, type table, type record )
Using its in procedures / functions ?

Regards

Eriovaldo do Carmo Andrietta
Limeira/SP - Brasil


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eriovaldo do Carmo Andrietta
  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).

[Doubt] - Returning Type Table

[Eriovaldo do Carmo Andrietta]

 
Hi Friends :
 
I have the code bellow and would like to return the variable type 
table.
 
 
CREATE OR REPLACE PACKAGE cv_types AS   TYPE DeptCurType is 
REF CURSOR RETURN dept%ROWTYPE;   TYPE retorno IS TABLE OF 
SCOTT.dept%ROWTYPE    INDEX BY BINARY_INTEGER;   
TYPE Tipo_Tabela_Emp  IS TABLE OF EMP%ROWTYPE  INDEX BY 
BINARY_INTEGER;   TYPE Tipo_Tabela_Dept IS TABLE OF DEPT%ROWTYPE 
INDEX BY BINARY_INTEGER;END cv_types;
 
create or replace function 
rtc_dept    RETURN cv_types.Tipo_Tabela_DeptIScursor 
resultset is select * from dept;tabdept   
cv_types.Tipo_tabela_Dept;I BINARY_INTEGER;tab  
dept%rowtype;   BEGIN    i := 
1;    open resultset;    
loop    fetch resultset into 
tab;    exit when 
resultset%notfound;    
tabdept(i).dname :=   '- ' || 
tab.dname;    dbms_output.put_line 
(tabdept(i).dname);    i := i + 
1;    end loop;    close 
resultset;    RETURN(tabdept);END;
scott> scott> scott> scott> 
VARIABLE rtc REFCURSORscott> EXECUTE :rtc := rtc_deptbegin :rtc := 
rtc_dept; end;
 
  
*ERROR at line 1:ORA-06550: line 1, column 15:PLS-00382: expression 
is of wrong typeORA-06550: line 1, column 7:PL/SQL: Statement 
ignored
 
scott> PRINT rtcERROR:ORA-24338: 
statement handle not executed
 
Error printing variable "rtc"scott> 

 
what is wrong ?
 
Eriovaldo do Carmo Andrietta
Widesoft Sistemas Ltda.Limeira/SP - Brasil

Doubt About Designer - Summary Information

[Eriovaldo do Carmo Andrietta]

Title: RE: Set Autotrace on



Hi friends :
 
How can I do for put the summary information in the 
diagram model.
I wrote in the File / Summary 
Information
but 
i don´t know how put it in the 
diagram.
 
thanks
Eriovaldo do Carmo AndriettaLimeira/SP - Brasil

  - Original Message - 
  From: 
  Jacques Kilchoer 
  To: Multiple recipients of list ORACLE-L 
  Sent: Wednesday, February 14, 2001 7:10 
  PM
  Subject: RE: Set Autotrace on
  
  > -Original Message- > 
  From: Shakeel Qureshi [mailto:[EMAIL PROTECTED]] > > In oracle 8.1.6 I only run as a 
  schema owner > the utlxplan.sql and everything 
  works fine. I just set > sql trace explain and it 
  gives me all the details of > execution 
  plan. 
  My understanding is that running 
  "$ORACLE_HOME/sqlplus/admin/plustrce.sql" will allow a user without DBA or 
  "select any table" privilege to do a 'set autotrace  statistics" in 
  SQL*Plus.
  Jacques R. Kilchoër x8816 

Doubt About Designer

[Eriovaldo do Carmo Andrietta]

Hi Friends :
 
Every time , my computer down , and i am using 
Designer, 
The next time I start it
It says to me that the application is located by 
other user.
For to solve it i shutdown the database and start 
it again.
After this, it run ok.
 
Why it?
 
What can I do for unlock the apllication without 
shutdown the instance ?
 
Thanks
 
 
Eriovaldo do Carmo AndriettaWidesoft Sistemas Ltda.Limeira/SP - 
Brasil

RE: Doubt about charecter

[Trassens, Christian]

Why don't you use like stmt in where clause ?.

> -Mensaje original-
> De:   Eriovaldo do Carmo Andrietta [SMTP:[EMAIL PROTECTED]]
> Enviado el:   martes 13 de febrero de 2001 18:26
> Para: Multiple recipients of list ORACLE-L
> Asunto:   Doubt about charecter 
> 
> I have a problem where in a statment of sql the DB Oracle doesn´t get the
> line in the follow situation :
>  
> select name  
>  from table1
>   where field = 'AAA  #989';
>  
> I think that between A and # exist some strange character that i can´t
> identify it ..
>  
> How can I do it ?
>  
> Regards
>  
> Eriovaldo
> 
> 
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Trassens, Christian
  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: Offline doubt - Exclusive OR operator in PL/SQL??.

[MacGregor, Ian A.]

I said it was a bit xor. Your first message was not very specific.  There is no 
general xor operator in Oracle.  However under Oracle 8i you can create one.  Look at 
the create operator command.

You could also do something like

(select x from A where y = 2
union
select x from A where z = 2)
minus
(select x from A where y = 2
intersect
select x from dual where z= 2)


Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]  

-Original Message-
Sent: Tuesday, February 13, 2001 11:17 AM
To: Multiple recipients of list ORACLE-L


The only reference I found in the documentation was in the UTL_RAW package.

BIT_XOR Function
This function performs bitwise logical "exclusive or" of the values in RAW
r1 with RAW r2 and returns the "xor'd" result RAW. 

If r1 and r2 differ in length, then the "xor" operation is terminated after
the last byte of the shorter of the two RAWs, and the unprocessed portion of
the longer RAW is appended to the partial result.The result length equals
the longer of the two input RAWs. 

Syntax
UTL_RAW.BIT_XOR (
   r1 IN RAW,
   r2 IN RAW) 
  RETURN RAW;

Pragmas
pragma restrict_references(bit_xor, WNDS, RNDS, WNPS, RNPS); 

Parameters
Table 63-40 BIT_XOR Function Parameters 

> -Original Message-
> From: Arul kumar [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, February 12, 2001 7:31 AM
> To:   Multiple recipients of list ORACLE-L
> Subject:  Offline doubt - Exclusive OR operator in PL/SQL??.
> 
> Hi Gurus,
> 
> We are currently working on migration of ORACLE / VB env. to ORACLE WEB
> SERVER env.
> 
> In this, we need to change one part of VB code which is using EXCLUSIVE
> OR operator functionality  to ORACLE (PL/SQL).
> 
> Do we have such  Exclusive OR operator in PL/SQL ? or is anyother way to
> get the work done.?
> 
> Note - Oracle Database Version - 7.x
> 
> Thank You.
> Arul.
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Arul kumar
>   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: Elliott, Patrick
  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: MacGregor, Ian A.
  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: Offline doubt - Exclusive OR operator in PL/SQL??.

[Elliott, Patrick]

The only reference I found in the documentation was in the UTL_RAW package.

BIT_XOR Function
This function performs bitwise logical "exclusive or" of the values in RAW
r1 with RAW r2 and returns the "xor'd" result RAW. 

If r1 and r2 differ in length, then the "xor" operation is terminated after
the last byte of the shorter of the two RAWs, and the unprocessed portion of
the longer RAW is appended to the partial result.The result length equals
the longer of the two input RAWs. 

Syntax
UTL_RAW.BIT_XOR (
   r1 IN RAW,
   r2 IN RAW) 
  RETURN RAW;

Pragmas
pragma restrict_references(bit_xor, WNDS, RNDS, WNPS, RNPS); 

Parameters
Table 63-40 BIT_XOR Function Parameters 

> -Original Message-
> From: Arul kumar [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, February 12, 2001 7:31 AM
> To:   Multiple recipients of list ORACLE-L
> Subject:  Offline doubt - Exclusive OR operator in PL/SQL??.
> 
> Hi Gurus,
> 
> We are currently working on migration of ORACLE / VB env. to ORACLE WEB
> SERVER env.
> 
> In this, we need to change one part of VB code which is using EXCLUSIVE
> OR operator functionality  to ORACLE (PL/SQL).
> 
> Do we have such  Exclusive OR operator in PL/SQL ? or is anyother way to
> get the work done.?
> 
> Note - Oracle Database Version - 7.x
> 
> Thank You.
> Arul.
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Arul kumar
>   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: Elliott, Patrick
  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: Doubt about charecter

[Ruth Gramolini]

How about using this statement:
select name
 from table1
  where field  like  'AAA% #989';

The % represents any character.

HTH,
Ruth

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, February 13, 2001 12:25 PM


Oracle 816 on linuxI have a problem where in a statment of sql the DB Oracle
doesn?t get the line in the follow situation :

select name
 from table1
  where field = 'AAA  #989';

I think that between A and # exist some strange character that i can?t
identify it ..

How can I do it ?

Regards

Eriovaldo




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ruth Gramolini
  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: Doubt about charecter

[gregory . t . norris]

select name
   from table1
   where field like 'AAA_#989';
 
When used with LIKE, the underscore is a wildcard which will match any 
single character.
 
-Original Message-
Sent: Tuesday, February 13, 2001 11:26 AM
To: ORACLE-L
Cc: eca




I have a problem where in a statment of sql the DB Oracle doesn´t get 
the line in the follow situation :
 
    select name  
 from table1
  where field = 'AAA  #989';
 
I think that between A and # exist some strange character that i can´t 
identify it ..
 
How can I do it ?
 
Regards
 
Eriovaldo
    

Re: Doubt about charecter

[Scott Canaan]

Use 'AAA_#989',  the _ means any 1 character substitution.
Eriovaldo do Carmo Andrietta wrote:

I have a problem where in a statment
of sql the DB Oracle doesn´t get the line in the follow situation
: select name from table1 
where field = 'AAA  #989'; I think that between A and # exist
some strange character that i can´t identify it .. How can I
do it ? Regards Eriovaldo 

--
Scott Canaan ([EMAIL PROTECTED])
"Life is like a sewer, what you get out of it depends on what you put
into it" - Tom Lehrer
 



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Scott Canaan
  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).

Doubt about charecter

[Eriovaldo do Carmo Andrietta]

Title: Oracle 816 on linux



I have a problem where in a statment of sql the DB Oracle doesn´t get 
the line in the follow situation :
 
    select name  
 from table1
  where field = 'AAA  #989';
 
I think that between A and # exist some strange character that i can´t 
identify it ..
 
How can I do it ?
 
Regards
 
Eriovaldo
    

Doubt about java in 8i

[J. Laurindo Chiappa]

Hi ! I'm beginnig to test the Java/JavaStoredProcs options in a 8ir3 Personal database.
 I can create and exec a JSP, as it :

D:\>type Dba.java
// Dba.java
// Save the file with the same name as the public class

import java.util.*;
public class Dba
{
public static void printHello ()
{
String prtString="Hello World";
System.out.println(prtString);
}
}

D:\>loadjava -u scott/tiger@po8ir3 -resolve D:\Dba.java

D:\>
D:\>sqlplus scott/tiger@po8ir3

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Feb 12 16:33:06 2001

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Personal Oracle8i Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production


scott@o8ir3::SQL>CREATE OR REPLACE PROCEDURE printHello
  2  AS LANGUAGE JAVA
  3  NAME 'Dba.printHello()';
  4  /

Procedure created.

scott@o8ir3::SQL>set SERVEROUPUT ON;
SP2-0735: unknown SET option beginning "SERVEROUPU..."
scott@o8ir3::SQL>set serveroutput on
scott@o8ir3::SQL>CALL dbms_java.set_output(1000);

Call completed.

scott@o8ir3::SQL>call printHello();
Hello World

Call completed.
scott@o8ir3::SQL>


*** =>>> BUT when I select from v$option Oracle says 'Java=False' (as follows). 
Why ? What
it means ? The java entry refers to Java Server, or another java-related tool ?

[]s
Chiappa


scott@o8ir3::SQL>select * from v$option;

PARAMETER   VALUE
--- 
PartitioningTRUE
Objects TRUE
Parallel Server FALSE
Advanced replicationTRUE
Bit-mapped indexes  TRUE
Connection multiplexing TRUE
Connection pooling  TRUE
Database queuingTRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recoveryTRUE
Parallel execution  TRUE
Parallel load   TRUE
Point-in-time tablespace recovery   TRUE
Fine-grained access control TRUE
N-Tier authentication/authorization TRUE
Function-based indexes  TRUE
Plan Stability  TRUE
Online Index Build  TRUE
Coalesce Index  TRUE
Managed Standby TRUE

PARAMETER   VALUE
--- 
Materialized view rewrite   TRUE
Materialized view warehouse refresh TRUE
Database resource manager   TRUE
Spatial TRUE
Visual Information RetrievalTRUE
Export transportable tablespacesTRUE
Transparent Application FailoverTRUE
Fast-Start Fault Recovery   TRUE
Sample Scan TRUE
Duplexed backupsTRUE
JavaFALSE
OLAP Window Functions   TRUE

33 rows selected.

scott@o8ir3::SQL>


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: J. Laurindo Chiappa
  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: Offline doubt - Exclusive OR operator in PL/SQL??.

[MacGregor, Ian A.]

Have a look at the utl_raw package which contains the bitwise functions.

Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]

-Original Message-
Sent: Monday, February 12, 2001 5:31 AM
To: Multiple recipients of list ORACLE-L


Hi Gurus,

We are currently working on migration of ORACLE / VB env. to ORACLE WEB
SERVER env.

In this, we need to change one part of VB code which is using EXCLUSIVE
OR operator functionality  to ORACLE (PL/SQL).

Do we have such  Exclusive OR operator in PL/SQL ? or is anyother way to
get the work done.?

Note - Oracle Database Version - 7.x

Thank You.
Arul.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Arul kumar
  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: MacGregor, Ian A.
  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).

Offline doubt - Exclusive OR operator in PL/SQL??.

[Arul kumar]

Hi Gurus,

We are currently working on migration of ORACLE / VB env. to ORACLE WEB
SERVER env.

In this, we need to change one part of VB code which is using EXCLUSIVE
OR operator functionality  to ORACLE (PL/SQL).

Do we have such  Exclusive OR operator in PL/SQL ? or is anyother way to
get the work done.?

Note - Oracle Database Version - 7.x

Thank You.
Arul.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Arul kumar
  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: Offline doubt - Password checking in Oracle..

[Jorma . Vuorio]

If user is connected to Oracle and is able to use
Your function, You shouldn't have any reason to check the
old password.

or

If user is not connected when passing those parameters,
let Oracle check, if the password is correct when (application)
try to connect

or

If old password is a Sybase password, You should check it
against Sybase db, not Oracle.

HTH
Jorma

> -Original Message-
> From: ext Arul kumar [mailto:[EMAIL PROTECTED]]
> Sent: 02 February, 2001 8:45
> To: Multiple recipients of list ORACLE-L
> Subject: Offline doubt - Password checking in Oracle..
> 
> 
> Hi Gurus,
> 
> I am working on a Sybase to Oracle migration project  where i need to
> write a function which accepts username , old password and 
> new password
> as parameters and modifies the account(Oracle Schema ) with the new
> password.
> If the password is correct, then only password modification 
> should take
> place.
> 
> Now, my question is :
> 
> How to validate the user given password as correct or not? 
> (becos Oracle
> stores password by encrypting it!).
> 
> Any suggestions will be of great help to me.
> 
> TIA,
> Arul.
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Arul kumar
>   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).

Offline doubt - Password checking in Oracle..

[Arul kumar]

Hi Gurus,

I am working on a Sybase to Oracle migration project  where i need to
write a function which accepts username , old password and new password
as parameters and modifies the account(Oracle Schema ) with the new
password.
If the password is correct, then only password modification should take
place.

Now, my question is :

How to validate the user given password as correct or not? (becos Oracle
stores password by encrypting it!).

Any suggestions will be of great help to me.

TIA,
Arul.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Arul kumar
  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).