Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: dynamic sql
durga wrote:
> hi folks,
>
> i am an oracle novice....i am working on a small project, i've
> created a few tables holding employee, projects and clients info. My
> parent table is OGEMP whose primary key is SSN, there are 5 other
> tables whose foreign key is SSN(referring OGEMP).
> Here's my problem, if an employee says that his SSN has been entered
> wrong, then I should be able to modify my OGEMP table and insert the
> new SSN and delete the old one. I have to use a bottom-up approach
> here, since there are other tables who have SSN as foreign keys.
> I am supposed to use dynamic SQL for this problem. I got a clue
> here........first I've to create a new table which is going to hold my
> OGEMP and related tables in a particular order (parent to child), then
> insert the new ssn as a new record in the parent table, update in all
> my child tables and finally delete the old SSN from the OGEMP table.
> I've created the new table.........but my problem is inserting rows
> (tables in parent to child order)...........I've to use dynamic SQL
> and user_constriants......help please!!!!
Dynamic SQL is not required by Oracle though it may be required by your instructor. In a real-world situation one might choose to create deferrable constraints making this a trivial task.
For demos of dynamic SQL that you might be able to modify for your use look at Morgan's Library at www.psoug.org.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Tue Sep 11 2007 - 10:13:51 CDT