Home » SQL & PL/SQL » SQL & PL/SQL » INSERT STATEMENT with Username included, Can I ? (10g)
INSERT STATEMENT with Username included, Can I ? [message #308898] Tue, 25 March 2008 21:20 Go to next message
icm63
Messages: 21
Registered: December 2007
Junior Member
Can I do this in PLSQL ...

CREATE OR REPLACE PROCEDURE PROC_POST_DATA
IS
BEGIN

INSERT INTO <Schema>.<InputTableName>
SELECT <Field>,<Field>,<Field>
FROM <Schema>.<SourceTableName> 'UserName:= BOB';

END PROC_POST_DATA;

This allows me to pull data from a table or view with my username, so I can allow another username to run the procedure (PROC_POST_DATA). The username that runs the procedure does not have access to the table or view that the INSERT STATEMENT is pulling the data from.

How can I do this ???

[Updated on: Tue, 25 March 2008 21:22]

Report message to a moderator

Re: INSERT STATEMENT with Username included, Can I ? [message #308902 is a reply to message #308898] Tue, 25 March 2008 22:02 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Can I do this in PLSQL
I give up. Can you?

I'd like to understand what is the business purpose being accomplished by such a procedure.
Re: INSERT STATEMENT with Username included, Can I ? [message #308939 is a reply to message #308898] Wed, 26 March 2008 00:45 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
today i have not got a single post in recommended format till.

Shocked
Re: INSERT STATEMENT with Username included, Can I ? [message #309266 is a reply to message #308898] Wed, 26 March 2008 23:34 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Whether U want to put data into your schema table or in user that is running the procedure.

If u wan't user to put data into your table then u have to grant him execute privilege on your procedure.

By default procedure runs in DEFINER right model.

Or if user has it's own table then create your procedure with AUTHID CURRENT_USER. It means when user executes that procedure it will refer it's own schema table.

Syntax:-

PROCEDURE [schema.]name [( parameter [, parameter ...] ) ]
   [AUTHID DEFINER | CURRENT_USER]
IS
   [declarations]
BEGIN
   executable statements

[ EXCEPTION
     exception handlers]
END [name];

Re: INSERT STATEMENT with Username included, Can I ? [message #309268 is a reply to message #308898] Wed, 26 March 2008 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
rajatratewal,

Please read & follow posting guidelines.
http://www.orafaq.com/forum/t/88153/0/

"U" is not a member of this forum!

[Updated on: Wed, 26 March 2008 23:39] by Moderator

Report message to a moderator

icon10.gif  Re: INSERT STATEMENT with Username included, Can I ? [message #309272 is a reply to message #309268] Wed, 26 March 2008 23:44 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
hi anacedent. I am new to this forum.Thanks for pointing out the mistake. I have once gone through the forum guidelines but i think it will take some time to capture everything written there.

I will kept this thing in mind next time when i post.Very Happy
Previous Topic: bind variable error...WHY?
Next Topic: use of functions and procedure
Goto Forum:
  


Current Time: Mon Dec 05 18:47:46 CST 2016

Total time taken to generate the page: 0.09274 seconds