RE: strange problem ??!!
How does the app calls this procedure? -Original Message- Sent: Thursday, June 12, 2003 5:26 PM To: Multiple recipients of list ORACLE-L Hi, I got a very strange problem (9.2 on Linux Red Had, 9iAS). I have a package which works fine, I added a new procedure in that package, if the procedure's name starts with order_, (the pkg has some procedure starts with order) the pkg compiles, and app runs fine. if I rename it something else, pkg compiles, but when runs the app, I got: [Thu Jun 12 14:52:52 2003] [error] mod_plsql: /prv/html_rpt.LIS_count HTTP-404 html_rpt.LIS_count: SIGNATURE (parameter names) MISMATCH VARIABLES IN FORM NOT IN PROCEDURE: NON-DEFAULT VARIABLES IN PROCEDURE NOT IN FORM: NUM_ENTRIES,NAME_ARRAY,VALUE_ARRAY,RESERVED I renamed the LIS_count into order_LIS_count, (change in header, body and calling procedure) no parameters changes at all, and app runs! ??? __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy 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: Richard Ji 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: Strange problem with charactersets
Title: Strange problem with charactersets Unfortunately the only solution is to recreate the database in German character set. Oracle is very strict in this respect. Database character set once chosen can not be changed. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Wednesday, May 15, 2002 4:13 PM Subject: Strange problem with charactersets Hi list, I have a strange problem with charctersets, character display. I have a oracle 8.0.5 on HPUX, database characterset is WE8ISO8859P1. On clients we have NT 4.0 with Oracle 8.1.5 client. Everything is fine. Now I've installed a Windows 2000 PC with Oracle 8.1.7 client and here a the strange behaviours. Oracle 8.1.7 client shows instead of öäüß (german special characters) oau?. I've never seen this. Normally the special characters would be shown as unreadable signs. On client we tried these different NLS settings, but nothing works: NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1 NLS_LANG=GERMAN_GERMANY.WE8ISO8859P15 NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252 NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15 Windows 2000 territory settings are all to german. All infos are welcome. Volker Schoen E-Mail: [EMAIL PROTECTED] http://www.inplan.de
RE: Strange problem with charactersets
Title: Strange problem with charactersets Execute the following query: select substr(parameter,1,30),substr(value,1,30) from sys.v_$nls_parameters order by 1; This way you will know the database`s nls settings. If the client has the same settings as the database, there will be no character conversion between the client and the database. If the characters are still displayed in a bad fashon on the client side, then data in the database is not correct andyou have to reloadit. HTH, Tamas Szecsy -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Wednesday, May 15, 2002 3:13 PMTo: Multiple recipients of list ORACLE-LSubject: Strange problem with charactersets Hi list, I have a strange problem with charctersets, character display. I have a oracle 8.0.5 on HPUX, database characterset is WE8ISO8859P1. On clients we have NT 4.0 with Oracle 8.1.5 client. Everything is fine. Now I've installed a Windows 2000 PC with Oracle 8.1.7 client and here a the strange behaviours. Oracle 8.1.7 client shows instead of öäüß (german special characters) oau?. I've never seen this. Normally the special characters would be shown as unreadable signs. On client we tried these different NLS settings, but nothing works: NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1 NLS_LANG=GERMAN_GERMANY.WE8ISO8859P15 NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252 NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15 Windows 2000 territory settings are all to german. All infos are welcome. Volker Schoen E-Mail: [EMAIL PROTECTED] http://www.inplan.de
RE: Strange problem with charactersets
Title: Strange problem with charactersets From Oracle 8i and up you do not have to recreate the database, you just have to issue an alter command and reload the data. Unfortunately this would not help Volker any further. Tamas Szecsy -Original Message-From: Nicolai Tufar [mailto:[EMAIL PROTECTED]]Sent: Wednesday, May 15, 2002 4:03 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Strange problem with charactersets Unfortunately the only solution is to recreate the database in German character set. Oracle is very strict in this respect. Database character set once chosen can not be changed. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Wednesday, May 15, 2002 4:13 PM Subject: Strange problem with charactersets Hi list, I have a strange problem with charctersets, character display. I have a oracle 8.0.5 on HPUX, database characterset is WE8ISO8859P1. On clients we have NT 4.0 with Oracle 8.1.5 client. Everything is fine. Now I've installed a Windows 2000 PC with Oracle 8.1.7 client and here a the strange behaviours. Oracle 8.1.7 client shows instead of öäüß (german special characters) oau?. I've never seen this. Normally the special characters would be shown as unreadable signs. On client we tried these different NLS settings, but nothing works: NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1 NLS_LANG=GERMAN_GERMANY.WE8ISO8859P15 NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252 NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15 Windows 2000 territory settings are all to german. All infos are welcome. Volker Schoen E-Mail: [EMAIL PROTECTED] http://www.inplan.de
RE: Strange problem deleting rows
Check for any trace files reporting some errors in bdump directory. Dropping indexes on the table might help if there is any reporting errors/locks in any trace file. Waleed -Original Message- Sent: Monday, February 25, 2002 3:13 PM To: Multiple recipients of list ORACLE-L I created a procedure to remove up to 2.4 million records out of a 13+ million record table with bulk binds and forall. Figured that would be the fastest way. While doing this delete, other processes were accessing the table, but not the rows that were being deleted. Things went fine. Eventually, records that were being deleted were being updated, then the probelms started. The job that did the big delete was killed in favor of deleteing the records in smaller batches. However, whenever I try to delete or update some of the records that were affected, the process hangs, like it's waiting on a commit or rollback from a previous transaction. I have bounced the db and this is still occuring. Is there a way to check if the blocks are indeed waiting for a commit or rollback and provide that as needed? Thank you -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Strange problem deleting rows
Rick, Your process is waiting (check v$session_wait) or it is consuming CPU. If it is consuming CPU, it is probably doing some work of the rollback. If you think that the process is hanging check v$session and report to us the event it is waiting on. Anjo. [EMAIL PROTECTED] wrote: I created a procedure to remove up to 2.4 million records out of a 13+ million record table with bulk binds and forall. Figured that would be the fastest way. While doing this delete, other processes were accessing the table, but not the rows that were being deleted. Things went fine. Eventually, records that were being deleted were being updated, then the probelms started. The job that did the big delete was killed in favor of deleteing the records in smaller batches. However, whenever I try to delete or update some of the records that were affected, the process hangs, like it's waiting on a commit or rollback from a previous transaction. I have bounced the db and this is still occuring. Is there a way to check if the blocks are indeed waiting for a commit or rollback and provide that as needed? Thank you -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk 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: Strange problem deleting rows
Rick
If you are waiting on rollback, this script (written by Steve Adams) will
help you find out how long the rollback will take.
set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back ...
prompt
declare
cursor tx is
select
s.username,
t.xidusn,
t.xidslot,
t.xidsqn,
t.used_ublk
from
sys.v_$transaction t,
sys.v_$session s
where
t.used_ublk 1 and
s.saddr = t.ses_addr;
user_name varchar2(30);
xid_usnnumber;
xid_slot number;
xid_sqnnumber;
used_ublk1 number;
used_ublk2 number;
begin
open tx;
loop
fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
exit when tx%notfound;
if tx%rowcount = 1
then
sys.dbms_lock.sleep(10);
end if;
select
sum(used_ublk)
into
used_ublk2
from
sys.v_$transaction
where
xidusn = xid_usn and
xidslot = xid_slot and
xidsqn = xid_sqn;
if used_ublk2 used_ublk1
then
sys.dbms_output.put_line(
user_name ||
'''s transaction ' ||
xid_usn || '.' ||
xid_slot || '.' ||
xid_sqn ||
' will finish rolling back at approximately ' ||
to_char(
sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
'HH24:MI:SS DD-MON-'
)
);
end if;
end loop;
if user_name is null
then
sys.dbms_output.put_line('No transactions appear to be rolling back.');
end if;
end;
/
Babu
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, February 25, 2002 3:13 PM
I created a procedure to remove up to 2.4 million records out of a 13+
million record table with bulk binds and forall. Figured that would be the
fastest way. While doing this delete, other processes were accessing the
table, but not the rows that were being deleted. Things went fine.
Eventually, records that were being deleted were being updated, then the
probelms started. The job that did the big delete was killed in favor of
deleteing the records in smaller batches. However, whenever I try to delete
or update some of the records that were affected, the process hangs, like
it's waiting on a commit or rollback from a previous transaction. I have
bounced the db and this is still occuring.
Is there a way to check if the blocks are indeed waiting for a commit or
rollback and provide that as needed?
Thank you
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Babu Nagarajan
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: strange problem with v$recover_file ???
Hi , ONLINE is a reserved word. It should not be used as a column name. I think it is bug. rukmini - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 28, 2001 3:51 AM All, I got a problem with v$recover_file. Somehow the query failed if selecting is on ONLINE column. SQL desc v$recover_file; Name Null?Type - --- FILE# NUMBER ONLINE VARCHAR2(7) ERROR VARCHAR2(18) CHANGE#NUMBER TIME DATE These are OK: --- select CHANGE# from v$recover_file; select TIME from v$recover_file; select * from v$recover_file; However these gave me error: select ONLINE from v$recover_file; select online, ERROR from v$recover_file; select FILE# , ONLINE,ERROR , CHANGE#,TIME from v$recover_file; * ERROR at line 1: ORA-00936: missing expression SQL select v.name, b.online 2 from v$datafile v, v$recover_file b 3 where v.file# = b.file#; select v.name, b.online * ERROR at line 1: ORA-01747: invalid user.table.column, table.column, or column specification What's happening?! Thank you. Andrea __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle 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: Rukmini Devi 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: strange problem with v$recover_file ???
Andrea, From Metalink, the column is defined in uppercase: SQL select online from v$recover_file; select online from v$recover_file * ERROR at line 1: ORA-00936: missing expression SQL select ONLINE from v$recover_file; no rows selected Also, online is a reserved word and that is why you originally get the ora-00936 error: SQL select * from v$reserved_words where keyword ='ONLINE'; KEYWORD LENGTH -- ONLINE6 SQL Regards, Bruce Reardon. -Original Message- Sent: Tuesday, 28 August 2001 8:22 All, I got a problem with v$recover_file. Somehow the query failed if selecting is on ONLINE column. SQL desc v$recover_file; Name Null?Type - --- FILE# NUMBER ONLINE VARCHAR2(7) ERROR VARCHAR2(18) CHANGE#NUMBER TIME DATE These are OK: --- select CHANGE# from v$recover_file; select TIME from v$recover_file; select * from v$recover_file; However these gave me error: select ONLINE from v$recover_file; select online, ERROR from v$recover_file; select FILE# , ONLINE,ERROR , CHANGE#,TIME from v$recover_file; * ERROR at line 1: ORA-00936: missing expression SQL select v.name, b.online 2 from v$datafile v, v$recover_file b 3 where v.file# = b.file#; select v.name, b.online * ERROR at line 1: ORA-01747: invalid user.table.column, table.column, or column specification What's happening?! Thank you. Andrea -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: strange problem after installing Oracle816
You might need to change LD_LIBRARY_PATH=$ORACLE_HOME/lib Regards, Waleed -Original Message- Sent: Tuesday, June 19, 2001 5:36 PM To: Multiple recipients of list ORACLE-L Hi all, I installed Oracle8.1.6 on a SunOS 5.6 box. That box already had 815 and 817 installed. When I'm in 816 Oracle_HOME/bin, sqlplus works fine but when I do svrmgrl which does exist, I got this: ld.so.1: svrmgrl: fatal: relocation error: file svrmgrl: symbol kghla42_: referenced symbol not found Killed I got the same error when run in 817home/bin, but in 815/bin, it works just fine. The PATH only have 815home/bin in it. Is it the path problem??? but I was in 816home/bin when I executed svrmgrl. Thank you! Andrea __ Do You Yahoo!? Spot the hottest trends in music, movies, and more. http://buzz.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: strange problem after installing Oracle816
Check your path statements and other environment setting on the Sun box. If you already had the old software installed, you probably are still using those for the old path. When you are in the 8.1.6 ORACLE_HOME/bin directory its finding the binary file for that version of oracle but its fining the Library and modules from the other version thru the path. Make sure everything is updated for the new Oracle Home. -Original Message- Sent: Tuesday, June 19, 2001 4:36 PM To: Multiple recipients of list ORACLE-L Hi all, I installed Oracle8.1.6 on a SunOS 5.6 box. That box already had 815 and 817 installed. When I'm in 816 Oracle_HOME/bin, sqlplus works fine but when I do svrmgrl which does exist, I got this: ld.so.1: svrmgrl: fatal: relocation error: file svrmgrl: symbol kghla42_: referenced symbol not found Killed I got the same error when run in 817home/bin, but in 815/bin, it works just fine. The PATH only have 815home/bin in it. Is it the path problem??? but I was in 816home/bin when I executed svrmgrl. Thank you! Andrea __ Do You Yahoo!? Spot the hottest trends in music, movies, and more. http://buzz.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: strange problem after installing Oracle816
Hi Just check all of your paths. You might be trying to use files from the other version libs or some similar issue. Peter At 01:35 PM 19/06/2001 -0800, you wrote: Hi all, I installed Oracle8.1.6 on a SunOS 5.6 box. That box already had 815 and 817 installed. When I'm in 816 Oracle_HOME/bin, sqlplus works fine but when I do svrmgrl which does exist, I got this: ld.so.1: svrmgrl: fatal: relocation error: file svrmgrl: symbol kghla42_: referenced symbol not found Killed I got the same error when run in 817home/bin, but in 815/bin, it works just fine. The PATH only have 815home/bin in it. Is it the path problem??? but I was in 816home/bin when I executed svrmgrl. Thank you! Andrea __ Do You Yahoo!? Spot the hottest trends in music, movies, and more. http://buzz.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle 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: Peter McLarty 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: strange problem
Hey Kirti! Congrats on your accomplishment of Tuning book. Must have been a gratifying experience. Keep the good work up! -Rahul Dandekar -- 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: strange problem
When using sqlplus to do the select you are most probably using the privileges
that have been assigned to you in a role. Privileges assigned to
a user through a role are not used (or applied) inside PL/SQL blocks (procedures/functions).
You need to have the appropriate privilege explicitly granted to the user
who is executing the procedure/function. In your case you will need
to explicitly grant select on the v$session view (or base table, sys.v_$session)
to the user account that is executing the procedure.
Glen
Narender Akula wrote:
hi gurus,
I am able to select from every hting from v$session at command line.
but when i write in procedure it fails why.
SQL> select osuser from v$session where username = 'IMAGE_INDEX' and
rownum
= 1;
OSUSER
--
MLOBO
this is ok...
CREATE OR REPLACE PROCEDURE n2 IS
tmpVar varchar2(100);
BEGIN
select osuser into tmpvar from v$session where username = 'MLOBO'
and rownum = 1;
dbms_output.put_line ('name '||tmpvar);
EXCEPTION
WHEN NO_DATA_FOUND THEN
Null;
WHEN OTHERS THEN
Null;
END n2;
this is failing says sys.V_$session must be declared.
what could be reason ?
> narender.akula
>
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Narender Akula
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).
--
Glen Mitchell NZ Phone: +64 9 3730400
Energy Research Lab URL: http://www.peace.com
Peace Software Email: [EMAIL PROTECTED]
Re: strange problem
hi gurus,
I am able to select from every hting from v$session at command line.
but when i write in procedure it fails why.
SQL select osuser from v$session where username = 'IMAGE_INDEX' and rownum
= 1;
OSUSER
--
MLOBO
this is ok...
CREATE OR REPLACE PROCEDURE n2 IS
tmpVar varchar2(100);
BEGIN
select osuser into tmpvar from v$session where username = 'MLOBO'
and rownum = 1;
dbms_output.put_line ('name '||tmpvar);
EXCEPTION
WHEN NO_DATA_FOUND THEN
Null;
WHEN OTHERS THEN
Null;
END n2;
this is failing says sys.V_$session must be declared.
what could be reason ?
How was select on v$session granted to user who owns the procedure?
If via a role this is the expected result.
Pat
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Pat Hildebrand
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: strange problem
Hi Narender,
You will need to grant explicit select privilege to the user id for
accessing the underlying view sys.v_$session to be able to do this in a
PL/SQL procedure. The id may have access to the v$session via a role. And
that would not work in a PL/SQL procedure.
HTH..
Regards,
- Kirti Deshpande
Verizon Information Services
http://www.superpages.com
-Original Message-
From: Narender Akula [SMTP:[EMAIL PROTECTED]]
Sent: Monday, May 14, 2001 7:16 PM
To: Multiple recipients of list ORACLE-L
Subject: strange problem
hi gurus,
I am able to select from every hting from v$session at command line.
but when i write in procedure it fails why.
SQL select osuser from v$session where username = 'IMAGE_INDEX' and
rownum
= 1;
OSUSER
--
MLOBO
this is ok...
CREATE OR REPLACE PROCEDURE n2 IS
tmpVar varchar2(100);
BEGIN
select osuser into tmpvar from v$session where username = 'MLOBO'
and rownum = 1;
dbms_output.put_line ('name '||tmpvar);
EXCEPTION
WHEN NO_DATA_FOUND THEN
Null;
WHEN OTHERS THEN
Null;
END n2;
this is failing says sys.V_$session must be declared.
what could be reason ?
narender.akula
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Narender Akula
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: Deshpande, Kirti
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: strange problem
hi pat...
thanks for info.
I granted explicit previleges to user,
narender
-Original Message-
Sent: Tuesday, May 15, 2001 2:50 PM
To: Multiple recipients of list ORACLE-L
hi gurus,
I am able to select from every hting from v$session at command line.
but when i write in procedure it fails why.
SQL select osuser from v$session where username = 'IMAGE_INDEX' and
rownum
= 1;
OSUSER
--
MLOBO
this is ok...
CREATE OR REPLACE PROCEDURE n2 IS
tmpVar varchar2(100);
BEGIN
select osuser into tmpvar from v$session where username = 'MLOBO'
and rownum = 1;
dbms_output.put_line ('name '||tmpvar);
EXCEPTION
WHEN NO_DATA_FOUND THEN
Null;
WHEN OTHERS THEN
Null;
END n2;
this is failing says sys.V_$session must be declared.
what could be reason ?
How was select on v$session granted to user who owns the procedure?
If via a role this is the expected result.
Pat
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Pat Hildebrand
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: Narender Akula
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).
