Home » SQL & PL/SQL » SQL & PL/SQL » Executing procedure without prefixing schema name (10.2)
Executing procedure without prefixing schema name [message #339256] Thu, 07 August 2008 02:52 Go to next message
hparulkar
Messages: 15
Registered: August 2008
Location: India
Junior Member
I have created a procedure 123 for ABC user.
created public synonym for procedure 123 and given grant execute abc.123to xyz.
I want xyz can execute procedure 123 with prefixing abc (schema name)
If the above implmentation is wrong , please let me know how can you implement to execute the procedure without prefixing schema name.

Re: Executing procedure without prefixing schema name [message #339258 is a reply to message #339256] Thu, 07 August 2008 02:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What you've written sounds good.
If you're having problems, show us the SQL that you've used.
Re: Executing procedure without prefixing schema name [message #339259 is a reply to message #339256] Thu, 07 August 2008 02:58 Go to previous messageGo to next message
hparulkar
Messages: 15
Registered: August 2008
Location: India
Junior Member
schema owner= abc
procedure=truncate_table
created synonym truncate_table
granted execute permission on truncate_table to 123.

DECLARE
TABLE_NAME VARCHAR2(200);
STORAGE_TYPE VARCHAR2(200);

BEGIN
TABLE_NAME := 'PLAN_TABLE';
STORAGE_TYPE := NULL;

TRUNCATE_TABLE ( TABLE_NAME, STORAGE_TYPE );
COMMIT;
END;

Error:
TRUNCATE_TABLE ( TABLE_NAME, STORAGE_TYPE );
*
ERROR at line 9:
ORA-06550: line 9, column 3:
PLS-00201: identifier 'TRUNCATE_TABLE' must be declared
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored

Re: Executing procedure without prefixing schema name [message #339261 is a reply to message #339259] Thu, 07 August 2008 03:06 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Commit not required.


SQL> VARIABLE TABLE_NAME VARCHAR2(200);
SQL> VARIABLE STORAGE_TYPE VARCHAR2(200);
SQL> EXEC TRUNCATE_TABLE('PLAN_TABLE', NULL);
Re: Executing procedure without prefixing schema name [message #339263 is a reply to message #339256] Thu, 07 August 2008 03:10 Go to previous messageGo to next message
hparulkar
Messages: 15
Registered: August 2008
Location: India
Junior Member
output .. still gives error..

SQL> VARIABLE TABLE_NAME VARCHAR2(200);

SQL> VARIABLE STORAGE_TYPE VARCHAR2(200);
SQL> EXEC TRUNCATE_TABLE('PLAN_TABLE', NULL);
BEGIN TRUNCATE_TABLE('PLAN_TABLE', NULL); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TRUNCATE_TABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Re: Executing procedure without prefixing schema name [message #339264 is a reply to message #339259] Thu, 07 August 2008 03:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I meant show us the SQL that you used to create the synonym and grant the privileges - preferable the actual SQL, with the results of running it.

It's clear you're not showing us exactly what's going on, because 123 is not a valid username.
Re: Executing procedure without prefixing schema name [message #339265 is a reply to message #339256] Thu, 07 August 2008 03:21 Go to previous messageGo to next message
hparulkar
Messages: 15
Registered: August 2008
Location: India
Junior Member
ok..

Schema owner =bobip
application user=bobippuser

SQL> CREATE OR REPLACE PUBLIC SYNONYM TRUNCATE_TABLE FOR BOBIP. TRUNCATE_TABLE;

Synonym created.


SQL> grant execute on BOBIP.TRUNCATE_TABLE to BOBIPUSER;

Grant succeeded.

SQL> sho user
USER is "BOBIPUSER"
SQL> EXEC TRUNCATE_TABLE('PLAN_TABLE', NULL);
BEGIN TRUNCATE_TABLE('PLAN_TABLE', NULL); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TRUNCATE_TABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



Re: Executing procedure without prefixing schema name [message #339266 is a reply to message #339265] Thu, 07 August 2008 03:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do a desc of the object (procedure or function) as the owner of the object please.
Re: Executing procedure without prefixing schema name [message #339268 is a reply to message #339256] Thu, 07 August 2008 03:29 Go to previous messageGo to next message
hparulkar
Messages: 15
Registered: August 2008
Location: India
Junior Member
SQL> conn bobip
Enter password:
Connected.
SQL> desc truncate_table
PROCEDURE truncate_table
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN
STORAGE_TYPE VARCHAR2 IN

SQL>
Re: Executing procedure without prefixing schema name [message #339271 is a reply to message #339268] Thu, 07 August 2008 03:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Stupid question, but are you 100% sure you are connecting to the same database instance?
Re: Executing procedure without prefixing schema name [message #339275 is a reply to message #339268] Thu, 07 August 2008 03:43 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Should have given grant priviledge explicitely not as a Role

[Updated on: Thu, 07 August 2008 03:44]

Report message to a moderator

Re: Executing procedure without prefixing schema name [message #339285 is a reply to message #339275] Thu, 07 August 2008 03:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Olivia wrote on Thu, 07 August 2008 10:43
Should have given grant priviledge explicitely not as a Role

bobipuser is a user, not a role
Re: Executing procedure without prefixing schema name [message #339303 is a reply to message #339256] Thu, 07 August 2008 04:40 Go to previous messageGo to next message
hparulkar
Messages: 15
Registered: August 2008
Location: India
Junior Member
ya i am 100% sure connecting to correct DB and bobipuser is a user not role. I have give grant directly to user.
Re: Executing procedure without prefixing schema name [message #339309 is a reply to message #339256] Thu, 07 August 2008 04:45 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Login to SQL
SQL> SHOW USER


Till the steps you tried to execute the procedure (including ALL ie,priviledge steps).Can you please post the source?


Regards,
Oli
Re: Executing procedure without prefixing schema name [message #339314 is a reply to message #339256] Thu, 07 August 2008 04:49 Go to previous messageGo to next message
hparulkar
Messages: 15
Registered: August 2008
Location: India
Junior Member
already posted. Still again posting

SQL> CREATE OR REPLACE PUBLIC SYNONYM TRUNCATE_TABLE FOR BOBIP. TRUNCATE_TABLE;

Synonym created.


SQL> grant execute on BOBIP.TRUNCATE_TABLE to BOBIPUSER;

Grant succeeded.

SQL> sho user
USER is "BOBIPUSER"
SQL> EXEC TRUNCATE_TABLE('PLAN_TABLE', NULL);
BEGIN TRUNCATE_TABLE('PLAN_TABLE', NULL); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TRUNCATE_TABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Re: Executing procedure without prefixing schema name [message #339317 is a reply to message #339314] Thu, 07 August 2008 04:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What if you exec bobip.truncate_table(...) ?
Re: Executing procedure without prefixing schema name [message #339318 is a reply to message #339314] Thu, 07 August 2008 04:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
hparulkar wrote on Thu, 07 August 2008 11:49

SQL> CREATE OR REPLACE PUBLIC SYNONYM TRUNCATE_TABLE FOR BOBIP. TRUNCATE_TABLE;



What's that space doing there between . and TRUNCATE_TABLE?
Re: Executing procedure without prefixing schema name [message #339319 is a reply to message #339256] Thu, 07 August 2008 04:58 Go to previous messageGo to next message
hparulkar
Messages: 15
Registered: August 2008
Location: India
Junior Member
if you execute prefixing schema name is runs fine. But using this way will be a huge re work for application team as they have no where prefixed schema name. All procedures and pkgs are failing.

Business doesn't want that rework.
Re: Executing procedure without prefixing schema name [message #339320 is a reply to message #339256] Thu, 07 August 2008 05:00 Go to previous messageGo to next message
hparulkar
Messages: 15
Registered: August 2008
Location: India
Junior Member
Even if the space is there the synoynm gets created. I dropped and re created without and executed the procedure.. still give error Sad
Re: Executing procedure without prefixing schema name [message #339321 is a reply to message #339320] Thu, 07 August 2008 05:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You are doing something that you're not telling us about.

The idea works perfectly, and I bet that if you run this script as a DBA, you'll see that it works for you too:Script
drop user test_user_1 cascade;

drop user test_user_2 cascade;

create user test_user_1 identified by test_user_1;

grant connect,resource to test_user_1;

grant create public synonym to test_user_1;

create user test_user_2 identified by test_user_2;

grant connect,resource to test_user_2;

connect test_user_1/test_user_1

create procedure test_proc (p_var in  varchar2) as begin null; end;
/

create public synonym test_proc for test_user_1.test_proc;

grant execute on test_proc to test_user_2;

connect test_user_2/test_user_2;

begin
  test_proc('A');
end;
/


Results
SQL> drop user test_user_1 cascade;

User dropped.

SQL> drop user test_user_2 cascade;

User dropped.

SQL> create user test_user_1 identified by test_user_1;

User created.

SQL> grant connect,resource to test_user_1;

Grant succeeded.

SQL> grant create public synonym to test_user_1;

Grant succeeded.

SQL> create user test_user_2 identified by test_user_2;

User created.

SQL> grant connect,resource to test_user_2;

Grant succeeded.

SQL> connect test_user_1/test_user_1
Connected.
SQL> create procedure test_proc (p_var in  varchar2) as begin null; end;
  2  /

Procedure created.

SQL> create public synonym test_proc for test_user_1.test_proc;

Synonym created.

SQL> grant execute on test_proc to test_user_2;

Grant succeeded.

SQL> connect test_user_2/test_user_2;
Connected.
SQL> begin
  2    test_proc('A');
  3  end;
  4  /

PL/SQL procedure successfully completed.


What user were you connected as when you granted Execute on Truncate_Table?
Re: Executing procedure without prefixing schema name [message #339327 is a reply to message #339256] Thu, 07 August 2008 05:14 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

SQL> CREATE OR REPLACE PUBLIC SYNONYM TRUNCATE_TABLE FOR BOBIP. TRUNCATE_TABLE;

Synonym created.


SQL> grant execute on BOBIP.TRUNCATE_TABLE to BOBIPUSER;

Grant succeeded.

SQL> sho user
USER is "BOBIPUSER"
SQL> EXEC TRUNCATE_TABLE('PLAN_TABLE', NULL);
BEGIN TRUNCATE_TABLE('PLAN_TABLE', NULL); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TRUNCATE_TABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



Here OP is trying all as "BOBIPUSER"
Re: Executing procedure without prefixing schema name [message #339331 is a reply to message #339327] Thu, 07 August 2008 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course, BOBIPUSER does not have a PRIVATE synonym of something that does not exist?

Regards
Michel
Re: Executing procedure without prefixing schema name [message #339343 is a reply to message #339327] Thu, 07 August 2008 05:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Connected as a a DBA user, can you run:
SELECT object_name,object_type,owner 
FROM dba_objects
WHERE object_name = 'TRUNCATE_TABLE';


Olivia: The only way that the BOBIPUSER could grant itself Execute privileges on BOBIP.TRUNCATE table would be if it already had either Execute Any Procedure, or had Execute granted to it with the 'With Grant Option' specified.

It's things like that that convince me that convince me that we're not being shown everything that is happening.
Re: Executing procedure without prefixing schema name [message #339347 is a reply to message #339256] Thu, 07 August 2008 05:44 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
@hparulkar:
Can you please do what JRowbottom has suggested? Please run the query and show us the output.

Regards,
Oli

[Updated on: Thu, 07 August 2008 05:44]

Report message to a moderator

Re: Executing procedure without prefixing schema name [message #339367 is a reply to message #339256] Thu, 07 August 2008 06:40 Go to previous messageGo to next message
hparulkar
Messages: 15
Registered: August 2008
Location: India
Junior Member
Thanks a ton JRowbottom. I got the issue.
GRANT given to BOBIPUSER on BOBIP.TRUNCATE_TABLE was given by sys user.
surprisegnly, even though sys is owner the grant didn't work.
As suggested by you I gave the grant from BOBIP(actual owner) and I am glad it worked.

Learnt new thing today. Thanks a lot.
Re: Executing procedure without prefixing schema name [message #339371 is a reply to message #339367] Thu, 07 August 2008 06:48 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As a general rule, do nothing as SYS that you don't have to be SYS to do.

This is why I wanted you to show us the exact SQL that you'd used - if you had done, we'd have seen this a lot earlier. As it was, the composite SQL that you posted made it look like you were logged on as BOBIPUSER when the synonym and grant were being made, which just couldn't have been true.

I can't think of a reason why the GRANT from SYS wouldn't have worked, but without seeing exactly what you did, theres no way of telling.
Previous Topic: Can we say the connect string as a database?
Next Topic: Query
Goto Forum:
  


Current Time: Sat Dec 03 16:17:07 CST 2016

Total time taken to generate the page: 0.12095 seconds