Home » SQL & PL/SQL » SQL & PL/SQL » Help Please (Oracle, 9.2.2.x, Unix)
Help Please [message #319243] Fri, 09 May 2008 08:18 Go to next message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
I have an oracle package called automation and the package is compiled by the user 'customer'. This package has a procedure create_bit which has a piece of code


dbms_pclxutil.build_part_index(1,2,'TRN_SUMM','TRN_SUMM_INDEX',false);



TRN_SUMM and TRN_SUMM_INDEX are the table and the bitmap indexes created with schema owner 'customer'. TRN_SUMM is a partitioned table with 9 partitions in it. The procedure create_bit is now run from a different schema user 'customernumber' which is granted a execute privilege on the automation package. The particular build code mentioned above throws an error saying that the table TRN_SUMM doesn't exist or not partitioned.

But when the same procedure is run from user 'customer' the build works fine. I went around Google and found that to run the dbms_pclxutil.build_part_index the procedure has to be run from the same schema which owns the table 'TRN_SUMM' and the index 'TRN_SUMM_INDEX'. is there anything that could be done to make it work from different schema user?

Thanks
Balaji
Re: Help Please [message #319249 is a reply to message #319243] Fri, 09 May 2008 08:37 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
What's your definition of your package? It is having authorisation identifier set to "Definer" or set to "Current User" ?

Regards

Raj
Re: Help Please [message #319259 is a reply to message #319243] Fri, 09 May 2008 08:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Next time use a more descriptive thread-title please.
Re: Help Please [message #319262 is a reply to message #319243] Fri, 09 May 2008 09:00 Go to previous messageGo to next message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Helo Raj,

The authorisation identifier of the package is set to current user rather than the definer.

thanks
Balaji

Re: Help Please [message #319274 is a reply to message #319262] Fri, 09 May 2008 09:46 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You have the answer for your original question in your previous answer. Think about it you will understand what I mean.

Regards

Raj
Re: Help Please [message #319280 is a reply to message #319274] Fri, 09 May 2008 10:06 Go to previous messageGo to next message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Hi Raj,

I got your answer. The one possibility is to set the authorisation identifier to the schema owner.
But im just looking if i could get done this at the code level by specifying schema names any where and make this build part run. Let me know if this idea could click.

Cheers
Balaji
Re: Help Please [message #319281 is a reply to message #319280] Fri, 09 May 2008 10:13 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link. It might be of help to you.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2012.htm#i2231814

Search for current_schema. To be honest I have not tested it but it's still worth giving a try.

Hope that helps.

Regards

Raj

[Updated on: Fri, 09 May 2008 10:14]

Report message to a moderator

Re: Help Please [message #319289 is a reply to message #319281] Fri, 09 May 2008 10:53 Go to previous messageGo to next message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Hi Raj,

Thanks for your help. That dint work out Sad

Balaji

[Updated on: Fri, 09 May 2008 10:53]

Report message to a moderator

Re: Help Please [message #319294 is a reply to message #319289] Fri, 09 May 2008 11:32 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Yes I realised it after some doing search. Check this link.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6265422366927
Inorder to achieve what you want you need to have the appropriate privilege granted on the object to the schema user. To be honest I won't be going in that route to solve this issue instead I will try to change the definition of the package (i.e) changing the authid from current_user to definer rights. Having said that, since I don't know the complete specification on the package I cannot comment more on that.

Regards

Raj
Re: Help Please [message #319597 is a reply to message #319294] Mon, 12 May 2008 06:10 Go to previous message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Hi Raj,

i got little confused with the Auth id. The package specification is not mentioned with any authid. so i guess it should take the definer as authid. But still this doesnt seem to work . Please find the PKS code here

create or replace
PACKAGE automation AS 

   PROCEDURE XXY(errorcode OUT NUMBER, date_time IN OUT VARCHAR2);

   PROCEDURE XXZ(errorcode OUT NUMBER, date_time IN OUT VARCHAR2);

   PROCEDURE create_bit(errorcode OUT NUMBER, date_time IN OUT VARCHAR2);

   PROCEDURE ZZZ(errorcode OUT NUMBER, date_time IN OUT VARCHAR2);

END automation;


Thank you so much for your help!

[Updated on: Mon, 12 May 2008 06:11]

Report message to a moderator

Previous Topic: How to get output for procedure using optional parameter and ref cursor
Next Topic: Problem with Utl_file.fopen
Goto Forum:
  


Current Time: Mon Dec 05 10:53:56 CST 2016

Total time taken to generate the page: 0.19328 seconds