ora-01031 error
Hi Folks, Forgive me if this is a stupid question, but I've consulted the esteem manuals and my brain appears to be dysfunctional today. I'm trying to create some views from within a package of stored procedures using dynamic sql. If I run the scripts in sqlplus all is well. If I try it inside a package of stored procedures using execute immediate I get the ORA 01031 insufficient privs error. All objects addressed are owned by the schema authoring and executing the package of stored procedures. What am I missing? Thanks, D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Darlene Marley 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: Data Purging Strategy
Hair of the dog! I wonder if that would help my sorry butt. Might have to leave for the airport early and sit at the bar until the stupor clears. Hope I don't miss my plane. D [EMAIL PROTECTED] wrote: > Burnt mud? Isn't that all scotch? > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Darlene Marley 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: set current_schema & PLS-00201
Thanks for the info. I knew you had to have a good reason. Suzy Vordos wrote: When session is altered to the schema that owns the object, schema qualification isn't needed. Our reason for eliminating the use of public/private synonyms (where possible) is from a performance point of view on the library cache. Steve Adams explains it best http://www.ixora.com.au. Alter session is working for all of our clients except those using PL/SQL. I'm stumped. Darlene Marley wrote: > > The view will always have to have the schema qualification without a > synonym: > select from y.view_name. > I use roles, synonyms and stored procedures extensively in my application > and the combination has worked well so far. Is there a reason in your case > for not using synonyms? > Darlene > > Suzy Vordos wrote: > > > I understand that privileges granted via role are disabled within stored > > procedures, so privileges need to be granted explicitly to the user, > > else PLS-00201 error is returned. > > > > We have a situation where user X connects to the database, executes > > select against user Y's view using PL/SQL, and receives the PLS-00201 > > error. User X has explicit select grants to Y's view, not grants via > > role. We are using a logon trigger which sets X's current_schema user > > Y. Here's what it looks like: > > > > grant create session to user X; > > grant select on Y.view_name to X; > > > > X connects to database > > login trigger execs 'alter session set current_schema=Y' > > PL/SQL code selects from view_name, PLS-00201 error returned > > > > To eliminate this error, the workaround was to create a public synonym > > (ick) with select grants to user X. > > > > The client is executing PL/SQL code within their application, not > > executing a PL/SQL code stored in the database (if that makes a diff). > > > > The whole point of using the logon trigger was to eliminate the use of > > public/private synonyms. I'm missing where user X inherited privs via > > role when we didn't grant privs via role. Is it related to altering > > current_schema, or is it something more obvious?? > > > > Thanks, > > Suzy > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Suzy Vordos > > 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: Darlene Marley > 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: Suzy Vordos 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: set current_schema & PLS-00201
The view will always have to have the schema qualification without a synonym: select from y.view_name. I use roles, synonyms and stored procedures extensively in my application and the combination has worked well so far. Is there a reason in your case for not using synonyms? Darlene Suzy Vordos wrote: > I understand that privileges granted via role are disabled within stored > procedures, so privileges need to be granted explicitly to the user, > else PLS-00201 error is returned. > > We have a situation where user X connects to the database, executes > select against user Y's view using PL/SQL, and receives the PLS-00201 > error. User X has explicit select grants to Y's view, not grants via > role. We are using a logon trigger which sets X's current_schema user > Y. Here's what it looks like: > >grant create session to user X; >grant select on Y.view_name to X; > >X connects to database >login trigger execs 'alter session set current_schema=Y' >PL/SQL code selects from view_name, PLS-00201 error returned > > To eliminate this error, the workaround was to create a public synonym > (ick) with select grants to user X. > > The client is executing PL/SQL code within their application, not > executing a PL/SQL code stored in the database (if that makes a diff). > > The whole point of using the logon trigger was to eliminate the use of > public/private synonyms. I'm missing where user X inherited privs via > role when we didn't grant privs via role. Is it related to altering > current_schema, or is it something more obvious?? > > Thanks, > Suzy > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Suzy Vordos > 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: Darlene Marley 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: Becoming a DBA questions
Or maybe this is one of the reasons the price of a Rx is so high. I know Pharmacutical reps make quite a bit of money too. Just a thought Randy Kirkpatrick wrote: > Either he's full of crap, or they're paying him way too much unless he has > some other managerial responsibilities. The only way to make 200K is to be a > sought-after independent consultant (at $100 an hour or so). The going rate > in the Denver area is $40 - $60 an hour (sometimes up to $75, but never > $100!) > > Randy > > -Original Message- > [EMAIL PROTECTED] > Sent: Friday, January 04, 2002 11:16 AM > To: Multiple recipients of list ORACLE-L > > Lemme get this right. > > This guy is a *new* DBA. He's making 150k and he's not > even a senior DBA, where he can make 200k? > > That's all from me. I'm gonna go sulk now. > > Jared > > DENNIS WILLIAMS > list ORACLE-L <[EMAIL PROTECTED]> > TOUCH.COM>cc: > Sent by: Subject: RE: Becoming a DBA > questions > [EMAIL PROTECTED] > m > > 01/04/02 09:05 > AM > Please respond > to ORACLE-L > > The following eweek article might be of interest. If the link gets mangled, > the article is at http://www.eweek.com Following the Data to a DBA Job by > Jeff Moad. > http://www.eweek.com/article/0,3658,s%253D703%2526a%253D20563,00.asp > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: DENNIS WILLIAMS > 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: > 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: Randy Kirkpatrick > 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: Darlene Marley 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: Urgenr : Sql Loader Question
FIELDS TERMINATED BY X'9' > "Chowdary, Suren" wrote: > > How wud i load a TAB delimited data file through SQL*Loader into a > Table. > > Like i use FIELDS TERMINATED BY "," (for a comma seperated data file) > > what shud i use for TAB delimited file... > > Thanx for ur help, > Chowday begin:vcard n:Marley;Darlene tel;fax:561-682-0100 tel;work:561-682-2172 x-mozilla-html:FALSE url:[EMAIL PROTECTED] org:South Florida Water Management District;Everglades Department adr:;;3301 GunClub Rd;West Palm Beach;Florida;33416;USA version:2.1 email;internet:[EMAIL PROTECTED] title:Specialist Systems Analyst/Programmer fn:Darlene Marley end:vcard
Re: unix command question
"The two major products to come out of Berkeley are LSD & UNIX. We don't believe this to be a coincidence" -- Author unknown "David A. Barbour" wrote: > It's commands like awk, grep, xargs and troff that give UNIX a bad > name. I read somewhere (more years ago than I'd care to recall) that > you should always beware of an operating system with commands that > sounded like bad bodily functions. > > Jared Still wrote: > > > > Haven't seen this one mentioned yet: > > > > find . -type d -print | xargs ls -ld > > > > Jared > > > > On Tuesday 01 May 2001 19:55, David A. Barbour wrote: > > > ls -Ra | grep / > > > > > > That's ls -Ra 'pipe' grep / > > > > > > You know, the spell checker has fits with the stuff on this list. > > > > > > Regards, > > > > > > David a. Barbour > > > Oracle DBA, OCP > > > > > > Roy Ferguson wrote: > > > > what I would like to see is all directories...not files starting with a > > > > particular letter but all directories... > > > > > > > > ls -d - doesn't work > > > > ls -ld p* - doesn't work either > > > > > > > > >Roy, > > > > > > > > > >You could do say ls -ld p* to list the directories starting with p. > > > > > > > > > >Rgds, > > > > > > > > > >raja > > > > >-- > > > > > > > > > >On Tue, 01 May 2001 13:35:46 > > > > > > > > > > Roy Ferguson wrote: > > > > >>what is the ls command to view only a list of directories? > > > > >> > > > > >>ls -la lists both files and directories...I want to view only a list of > > > > >>directories... > > > > >> > > > > >>environment is sun sparc solaris 2.6 > > > > >> > > > > >>thanks in advance > > > > >> > > > > >>roy > > > > >> > > > > >>-- > > > > >>Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > > >>-- > > > > >>Author: Roy Ferguson > > > > >> 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). > > > > > > > > > >Get 250 color business cards for FREE! > > > > >http://businesscards.lycos.com/vp/fastpath/ > > > > >-- > > > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > > >-- > > > > >Author: Viraj Luthra > > > > > 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). > > > > > > > > Roy E. Ferguson II > > > > Intel Sacramento > > > > 916-854-1123 > > > > > > > > -- > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > > -- > > > > Author: Roy Ferguson > > > > 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: Jared Still > > 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: David A. Barbour > INET: [EMAIL PROTECTED] > > Fat City Networ
