Home » SQL & PL/SQL » SQL & PL/SQL » failed to retrieve data from all_views from pl/sql
failed to retrieve data from all_views from pl/sql [message #318394] Tue, 06 May 2008 14:38 Go to next message
yashora
Messages: 39
Registered: August 2006
Member
Hello,

The following sql statement is fetchin records from SQL environment where as it is showing "No Data" in pl/sql procedure. Can you please let me know, why ths is happening and what should i do if i want to fetch the data from pl/sql too.

SELECT OWNER,VIEW_NAME,'DM_'||SUBSTR(OWNER,1,1)||SUBSTR(OWNER,INSTR(OWNER,'_',1)+1,1)||SUBSTR(OWNER,INSTR(OWNER,'_',1,2)+1)
||'_'||VIEW_NAME||'_'||'MV' MVNAME
FROM
ALL_VIEWS
WHERE
OWNER
LIKE
'SOP%';
Thanks and regards
Yashora
Re: failed to retrieve data from all_views from pl/sql [message #318399 is a reply to message #318394] Tue, 06 May 2008 14:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Roles are not enable in procedure.
So ALL views content changes if you are in or out of a procedure.

Regards
Michel
Re: failed to retrieve data from all_views from pl/sql [message #318401 is a reply to message #318394] Tue, 06 May 2008 14:57 Go to previous messageGo to next message
yashora
Messages: 39
Registered: August 2006
Member
Hi Michel,

good to know that. Is there any round about, because, my requirement is like that to access from pl/sql.

Regards,
Yashora
Re: failed to retrieve data from all_views from pl/sql [message #318402 is a reply to message #318394] Tue, 06 May 2008 14:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Read http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

Regards
Michel
Re: failed to retrieve data from all_views from pl/sql [message #318403 is a reply to message #318401] Tue, 06 May 2008 14:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Is there any round about,

Use "AUTHID CURRENT_USER" clause in CREATE PROCEDURE but of course, this changes the behaviour of the procedure, you no more have the rights of the owner, you have your own.

Regards
Michel
Re: failed to retrieve data from all_views from pl/sql [message #318410 is a reply to message #318394] Tue, 06 May 2008 15:52 Go to previous message
yashora
Messages: 39
Registered: August 2006
Member
Thanks Michel, This is how we can learn. Too good to hear from you.

Regards,
Yashora
Previous Topic: How does google do pagination while showing # of results?
Next Topic: How to pass decimal value to Oracle Stored Procedure
Goto Forum:
  


Current Time: Sat Feb 08 20:56:39 CST 2025