Home » SQL & PL/SQL » SQL & PL/SQL » PROCEDURE FOR ALL_OBEJCTS (windows 2000)
PROCEDURE FOR ALL_OBEJCTS [message #359735] Tue, 18 November 2008 02:00 Go to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Hi,
I am making a procedure as:

PROCEDURE OBJECT_STATUS
IS
CURSOR OBJECTS IS
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE';

BEGIN
FOR OBJ IN OBJECTS LOOP
DBMS_OUTPUT.PUT_LINE(OBJ.OBJECT_NAME);
END LOOP;
END;

It works absolutely fine and gives me all the object names. But if i change the above query as under:

PROCEDURE OBJECT_STATUS
IS
CURSOR OBJECTS IS
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM ALL_OBJECTS
WHERE OBJECT_NAME = 'E_I';
BEGIN
FOR OBJ IN OBJECTS LOOP
DBMS_OUTPUT.PUT_LINE(OBJ.OBJECT_NAME);
END LOOP;
END;

It does not enter the loop and does not give any output.
Any suggestions???
Re: PROCEDURE FOR ALL_OBEJCTS [message #359745 is a reply to message #359735] Tue, 18 November 2008 02:14 Go to previous messageGo to next message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
Could it be possible that there is no object named 'E_I'?
You may be thinking about the "like" statement
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM ALL_OBJECTS
WHERE OBJECT_NAME like 'E_I%';
Re: PROCEDURE FOR ALL_OBEJCTS [message #359754 is a reply to message #359735] Tue, 18 November 2008 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: PROCEDURE FOR ALL_OBEJCTS [message #359756 is a reply to message #359745] Tue, 18 November 2008 02:30 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Thanks for your reply

If i run this query in SQL....
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM ALL_OBJECTS
WHERE OBJECT_NAME = 'E_I';

...it gives me two records.
Re: PROCEDURE FOR ALL_OBEJCTS [message #359762 is a reply to message #359756] Tue, 18 November 2008 02:37 Go to previous messageGo to next message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
could it be that you are running the procedure from another user without rights to view the table data? Not sure because it all looks OK to me...
Re: PROCEDURE FOR ALL_OBEJCTS [message #359764 is a reply to message #359756] Tue, 18 November 2008 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The content of ALL_TABLE depends on the roles.
Roles are not enable in the procedure.
Execute "set role none;" and your statement and I bet you'll get the same result.

Regards
Michel
Re: PROCEDURE FOR ALL_OBEJCTS [message #359765 is a reply to message #359735] Tue, 18 November 2008 02:41 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Okay Michel Cadot
Next time i'll take care of that
Re: PROCEDURE FOR ALL_OBEJCTS [message #359785 is a reply to message #359735] Tue, 18 November 2008 03:54 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Thank you all of you for the reply.
set role none will deactivate all the roles, doesn't it?
If i deactivate all the roles then i need to revoke all of them again?
Re: PROCEDURE FOR ALL_OBEJCTS [message #359788 is a reply to message #359785] Tue, 18 November 2008 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If i deactivate all the roles then i need to revoke all of them again?

What do you mean?
"set role" allowas you to activate/deactivate roles in your session until next "set role" or next connexion. It does not change anything to the granted roles.

Regards
Michel
Re: PROCEDURE FOR ALL_OBEJCTS [message #359795 is a reply to message #359735] Tue, 18 November 2008 04:08 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Oh soory, yes i got it what you said.

Now i set role to none and then re-execute the procedure but it still not working......???
Re: PROCEDURE FOR ALL_OBEJCTS [message #359799 is a reply to message #359795] Tue, 18 November 2008 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the procedure but it still not working..

No it is working as expected: you get the same thing with the procedure and the query now.

Regards
Michel
Re: PROCEDURE FOR ALL_OBEJCTS [message #359805 is a reply to message #359735] Tue, 18 November 2008 04:25 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
I mean that if i run.......
SELECT Owner,
Object_Name,
Object_Type,
Status
FROM All_Objects
WHERE Object_Name = 'E_I';
....in SQL, it gives me two records but when i use the same query in the procedure in gives no result.
Re: PROCEDURE FOR ALL_OBEJCTS [message #359806 is a reply to message #359805] Tue, 18 November 2008 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it gives me two records

Even after "set role none;"?

Regards
Michel
Re: PROCEDURE FOR ALL_OBEJCTS [message #359809 is a reply to message #359735] Tue, 18 November 2008 04:30 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
yes sir even after set role none
Re: PROCEDURE FOR ALL_OBEJCTS [message #359811 is a reply to message #359809] Tue, 18 November 2008 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you executed it with the same user (owner of the procedure)?

Regards
Michel
Re: PROCEDURE FOR ALL_OBEJCTS [message #359817 is a reply to message #359735] Tue, 18 November 2008 04:37 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
yes sir....
Re: PROCEDURE FOR ALL_OBEJCTS [message #359840 is a reply to message #359817] Tue, 18 November 2008 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't trust you.
Use SQL*PLus, copy and paste the session starting from the procedure creation until the execution of procedure and query calls.

Before see how to format your post as I mentionned in a previous post above.

Regards
Michel
Re: PROCEDURE FOR ALL_OBEJCTS [message #359847 is a reply to message #359735] Tue, 18 November 2008 06:03 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
I did the way you said but still no result.
Re: PROCEDURE FOR ALL_OBEJCTS [message #359851 is a reply to message #359847] Tue, 18 November 2008 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Use SQL*PLus, copy and paste the session starting from the procedure creation until the execution of procedure and query calls.

Regards
Michel
Re: PROCEDURE FOR ALL_OBEJCTS [message #360310 is a reply to message #359735] Thu, 20 November 2008 06:33 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Now i got the problem. I think ALL_OBJECTS only allowing the object access of those schema from which i log in. eg if i log in from the abc scehma it allow me to access all the objects of abc.
I am trying to access the objects from the user which also has full rights.
So any suggestions...???
Re: PROCEDURE FOR ALL_OBEJCTS [message #360338 is a reply to message #360310] Thu, 20 November 2008 08:58 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Michel Cadot wrote on Tue, 18 November 2008 12:06
Quote:
Use SQL*PLus, copy and paste the session starting from the procedure creation until the execution of procedure and query calls.

Regards
Michel


Previous Topic: Identifying Control Characters as well as viewing them.. (merged)
Next Topic: Decrease the length of a column with select
Goto Forum:
  


Current Time: Tue Dec 06 02:25:22 CST 2016

Total time taken to generate the page: 0.13348 seconds