Home » SQL & PL/SQL » SQL & PL/SQL » How to update table by passing table name through procedure
How to update table by passing table name through procedure [message #344122] Thu, 28 August 2008 16:29 Go to next message
janthony
Messages: 3
Registered: August 2008
Junior Member
Hi,
I am now updating a table by giving the table name inside the procedure. But, I want to pass the table name in the procedure and then update the table using the parameter I mentioned in store procedure. The purpose of this is that I can use the same procedure to update whatever table I want. Here is my code:

CREATE OR REPLACE PROCEDURE duplicate_ck (in_id varchar2 , in_disease varchar2)

IS

BEGIN

UPDATE duplicate_check
SET duplicate = 'Y'
WHERE in_id = id AND
in_disease = disease;

commit;

END duplicate_ck;
/

Can anyone please help me with this problem?
Thanks
Jass
How to update table by passing table name through procedure [message #344124 is a reply to message #344122] Thu, 28 August 2008 16:32 Go to previous messageGo to next message
janthony
Messages: 3
Registered: August 2008
Junior Member
Hi,
I am now updating a table by giving the table name inside the procedure. But, I want to pass the table name in the procedure and then update the table using the parameter I mentioned in store procedure. The purpose of this is that I can use the same procedure to update whatever table I want. Here is my code:

CREATE OR REPLACE PROCEDURE duplicate_ck (in_id varchar2 , in_disease varchar2)

IS

BEGIN

UPDATE duplicate_check
SET duplicate = 'Y'
WHERE in_id = id AND
in_disease = disease;

commit;

END duplicate_ck;
/

Can anyone please help me with this problem?
Thanks
Jass
Re: How to update table by passing table name through procedure [message #344126 is a reply to message #344124] Thu, 28 August 2008 16:41 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Contrary to what you think now, this is a very bad idea.


>Can anyone please help me with this problem?
I refuse to hand a loaded gun to a baby.

While it can be done, it should only be done who know when to (ab)use such processing capabilities.

[Updated on: Thu, 28 August 2008 16:41] by Moderator

Report message to a moderator

Re: How to update table by passing table name through procedure [message #344127 is a reply to message #344124] Thu, 28 August 2008 16:45 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
It is certainly possible to do this using DBMS_SQL, and EXECUTE IMMEDIATE.

HOWEVER...

You should know this is considered a terrifically bad idea by most people who have been working in the Oracle space for any length of time. Here are just three reasons why:

Quote:
1) security. A generic piece of code like this opens the door to huge misuse of the system.

2) performance. It will be slow and expensive. None of your statements will be reusable and hence you will fill your sql cache on the instance with one-offs. Additionally each statement will incur all the overhead of parsing (and a few other query plan steps) so it will run slow.

3) scalability. Because of the same reasons as #2, your code will never scale.


In short, your solution may work, but once it goes to production and starts getting used a lot, it will cause you headaches galore, most of which will be a real big problem because to back out the code will require writing all the code you were initially trying to avoid.

Good luck, Kevin
Re: How to update table by passing table name through procedure [message #344136 is a reply to message #344122] Thu, 28 August 2008 17:53 Go to previous message
janthony
Messages: 3
Registered: August 2008
Junior Member
Thank you very much for your suggestions.
Previous Topic: sysrefcursor printing time
Next Topic: Sending attachment using UTL_SMTP package
Goto Forum:
  


Current Time: Sun Dec 11 07:56:01 CST 2016

Total time taken to generate the page: 0.05251 seconds