Calling built-in functions in code

Good morning,

Let me first apologize for asking what I am sure is a question that has been covered before in these forums. I have been searching for days for a solution but I’m guessing my ignorance of the terminology is an obstacle I just can’t overcome. Preface: I am working on a project that requires me to alter the primary key in several tables. As we all know, that also requires me to drop and then recreate any foreign key constraint that references the primary key before proceeding, and there in lies my dilemma. I know Toad gives me the ability to right click on a restraint and generate the code (“create script”) that will recreate the restraint, what I want to do is call that in my script based on the name of the primary key which I will pass in through the script. (For example, if my customer table has a primary key of “people” and it is referenced in the address and office table as a foreign key constraint, my script would use the SYS tables to determine what foreign keys reference “people” and then call the “create script” command on those keys and spool them [maybe] so that I am create them again after the primary key is changed) Is what I’m trying to do possible? If so, what are those built-in commands called? Is there a more efficient way of doing what I am trying to do? Any help will be appreciated…

Thanks,

Ed

You’re talking about calling Toad functionality from a script, and I don’t know about that. But as an alternative, here’s a PL/SQL function you can call from
a SQL script (or a PL/SQL module). Look at the package function DBMS_METADATA.GET_DDL. Pass it an object type (such as ‘CONSTRAINT’), and the object name and owner, and it should give you the exact command that you can later EXECUTE IMMEDIATE to rebuild the
object after you drop it.

Nate Schroeder

US Row Crops IT Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WB - Saint Louis, MO - 63167

314-694-2592

From: edluver216 [mailto:bounce-edluver216@toadworld.com]

Sent: Monday, March 31, 2014 11:14 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Calling built-in functions in code

Calling built-in functions in code

Thread created by edluver216

Good morning,

Let me first apologize for asking what I am sure is a question that has been covered before in these forums. I have been searching for days for a solution but I’m guessing my ignorance of the
terminology is an obstacle I just can’t overcome. Preface: I am working on a project that requires me to alter the primary key in several tables. As we all know, that also requires me to drop and then recreate any foreign key constraint that references
the primary key before proceeding, and there in lies my dilemma. I know Toad gives me the ability to right click on a restraint and generate the code (“create script”) that will recreate the restraint, what I want to do is call that in my script based on
the name of the primary key which I will pass in through the script. (For example, if my customer table has a primary key of “people” and it is referenced in the address and office table as a foreign key constraint, my script would use the SYS tables to determine
what foreign keys reference “people” and then call the “create script” command on those keys and spool them [maybe] so that I am create them again after the primary key is changed) Is what I’m trying to do possible? If so, what are those built-in commands
called? Is there a more efficient way of doing what I am trying to do? Any help will be appreciated…

Thanks,

Ed

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled

to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and

all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its

subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of “Viruses” or other “Malware”.

Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying

this e-mail or any attachment.

The information contained in this email may be subject to the export control laws and regulations of the United States, potentially

including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of

Treasury, Office of Foreign Asset Controls (OFAC). As a recipient of this information you are obligated to comply with all

applicable U.S. export laws and regulations.

Better question is why would you do that?

What are you trying to achieve?

P.S.

Any custom table should never be in SYS schema!