How To Create Procedure for DML in Toad for Oracle?

by

In this tutorial, you will learn how to create the procedure for DML statements in Toad for Oracle. Toad has an option to generate Oracle stored procedure for Insert, Update and Delete DML statements. You can create these procedures in a package or can build as standalone. This method is very similar to Oracle SQL Developer's Create Table API option. The following are the steps to create DML procedure in Toad:

Steps to Create Procedure for DML in Toad for Oracle

  1. In Toad for Oracle, click on the menu Database > Schema Browser.
  2. Then in the Schema Browser's Table tab, select your table for which you want to create the procedure and do the right click on it.
  3. From the shortcut menu choose Create DML Procedures.
  4. Then the Create DML Procedures window will open. Select INSERT, UPDATE and DELETE checkboxes to create the procedure for these three statements.
  5. Then click on the Other Options tab and click on the Use Packages checkbox to generate these procedures in a package. It is better to create a database package rather than creating three procedures separately because all these DML belongs to one table.
  6. Now click on the Output tab and choose the destination for your package. You can select to create this package in a file or create directly in the database or to the clipboard.
  7. Then click on the OK button, and you will get the Package Specification and the Package Body for DML procedures based on the Countries table as shown in the below example.

You can create the same DML package online check this link: Create DML Package Online

CREATE OR REPLACE PACKAGE DML_COUNTRIES
IS
   PROCEDURE INS_COUNTRIES (
      in_COUNTRY_ID             IN COUNTRIES.COUNTRY_ID%TYPE,
      in_COUNTRY_ISO_CODE       IN COUNTRIES.COUNTRY_ISO_CODE%TYPE,
      in_COUNTRY_NAME           IN COUNTRIES.COUNTRY_NAME%TYPE,
      in_COUNTRY_SUBREGION      IN COUNTRIES.COUNTRY_SUBREGION%TYPE,
      in_COUNTRY_SUBREGION_ID   IN COUNTRIES.COUNTRY_SUBREGION_ID%TYPE,
      in_COUNTRY_REGION         IN COUNTRIES.COUNTRY_REGION%TYPE,
      in_COUNTRY_REGION_ID      IN COUNTRIES.COUNTRY_REGION_ID%TYPE,
      in_COUNTRY_TOTAL          IN COUNTRIES.COUNTRY_TOTAL%TYPE,
      in_COUNTRY_TOTAL_ID       IN COUNTRIES.COUNTRY_TOTAL_ID%TYPE,
      in_COUNTRY_NAME_HIST      IN COUNTRIES.COUNTRY_NAME_HIST%TYPE);

   PROCEDURE UPD_COUNTRIES (
      in_COUNTRY_ID             IN COUNTRIES.COUNTRY_ID%TYPE,
      in_COUNTRY_ISO_CODE       IN COUNTRIES.COUNTRY_ISO_CODE%TYPE,
      in_COUNTRY_NAME           IN COUNTRIES.COUNTRY_NAME%TYPE,
      in_COUNTRY_SUBREGION      IN COUNTRIES.COUNTRY_SUBREGION%TYPE,
      in_COUNTRY_SUBREGION_ID   IN COUNTRIES.COUNTRY_SUBREGION_ID%TYPE,
      in_COUNTRY_REGION         IN COUNTRIES.COUNTRY_REGION%TYPE,
      in_COUNTRY_REGION_ID      IN COUNTRIES.COUNTRY_REGION_ID%TYPE,
      in_COUNTRY_TOTAL          IN COUNTRIES.COUNTRY_TOTAL%TYPE,
      in_COUNTRY_TOTAL_ID       IN COUNTRIES.COUNTRY_TOTAL_ID%TYPE,
      in_COUNTRY_NAME_HIST      IN COUNTRIES.COUNTRY_NAME_HIST%TYPE);

   PROCEDURE DEL_COUNTRIES (in_COUNTRY_ID IN COUNTRIES.COUNTRY_ID%TYPE);
END DML_COUNTRIES;
/

CREATE OR REPLACE PACKAGE BODY DML_COUNTRIES
IS
   PROCEDURE INS_COUNTRIES (
      in_COUNTRY_ID             IN COUNTRIES.COUNTRY_ID%TYPE,
      in_COUNTRY_ISO_CODE       IN COUNTRIES.COUNTRY_ISO_CODE%TYPE,
      in_COUNTRY_NAME           IN COUNTRIES.COUNTRY_NAME%TYPE,
      in_COUNTRY_SUBREGION      IN COUNTRIES.COUNTRY_SUBREGION%TYPE,
      in_COUNTRY_SUBREGION_ID   IN COUNTRIES.COUNTRY_SUBREGION_ID%TYPE,
      in_COUNTRY_REGION         IN COUNTRIES.COUNTRY_REGION%TYPE,
      in_COUNTRY_REGION_ID      IN COUNTRIES.COUNTRY_REGION_ID%TYPE,
      in_COUNTRY_TOTAL          IN COUNTRIES.COUNTRY_TOTAL%TYPE,
      in_COUNTRY_TOTAL_ID       IN COUNTRIES.COUNTRY_TOTAL_ID%TYPE,
      in_COUNTRY_NAME_HIST      IN COUNTRIES.COUNTRY_NAME_HIST%TYPE)
   IS
   BEGIN
      INSERT INTO COUNTRIES (COUNTRY_ID,
                             COUNTRY_ISO_CODE,
                             COUNTRY_NAME,
                             COUNTRY_SUBREGION,
                             COUNTRY_SUBREGION_ID,
                             COUNTRY_REGION,
                             COUNTRY_REGION_ID,
                             COUNTRY_TOTAL,
                             COUNTRY_TOTAL_ID,
                             COUNTRY_NAME_HIST)
          VALUES (in_COUNTRY_ID,
                  in_COUNTRY_ISO_CODE,
                  in_COUNTRY_NAME,
                  in_COUNTRY_SUBREGION,
                  in_COUNTRY_SUBREGION_ID,
                  in_COUNTRY_REGION,
                  in_COUNTRY_REGION_ID,
                  in_COUNTRY_TOTAL,
                  in_COUNTRY_TOTAL_ID,
                  in_COUNTRY_NAME_HIST);
   END INS_COUNTRIES;

   PROCEDURE UPD_COUNTRIES (
      in_COUNTRY_ID             IN COUNTRIES.COUNTRY_ID%TYPE,
      in_COUNTRY_ISO_CODE       IN COUNTRIES.COUNTRY_ISO_CODE%TYPE,
      in_COUNTRY_NAME           IN COUNTRIES.COUNTRY_NAME%TYPE,
      in_COUNTRY_SUBREGION      IN COUNTRIES.COUNTRY_SUBREGION%TYPE,
      in_COUNTRY_SUBREGION_ID   IN COUNTRIES.COUNTRY_SUBREGION_ID%TYPE,
      in_COUNTRY_REGION         IN COUNTRIES.COUNTRY_REGION%TYPE,
      in_COUNTRY_REGION_ID      IN COUNTRIES.COUNTRY_REGION_ID%TYPE,
      in_COUNTRY_TOTAL          IN COUNTRIES.COUNTRY_TOTAL%TYPE,
      in_COUNTRY_TOTAL_ID       IN COUNTRIES.COUNTRY_TOTAL_ID%TYPE,
      in_COUNTRY_NAME_HIST      IN COUNTRIES.COUNTRY_NAME_HIST%TYPE)
   IS
   BEGIN
      UPDATE COUNTRIES
         SET COUNTRY_ISO_CODE = in_COUNTRY_ISO_CODE,
             COUNTRY_NAME = in_COUNTRY_NAME,
             COUNTRY_SUBREGION = in_COUNTRY_SUBREGION,
             COUNTRY_SUBREGION_ID = in_COUNTRY_SUBREGION_ID,
             COUNTRY_REGION = in_COUNTRY_REGION,
             COUNTRY_REGION_ID = in_COUNTRY_REGION_ID,
             COUNTRY_TOTAL = in_COUNTRY_TOTAL,
             COUNTRY_TOTAL_ID = in_COUNTRY_TOTAL_ID,
             COUNTRY_NAME_HIST = in_COUNTRY_NAME_HIST
       WHERE COUNTRY_ID = in_COUNTRY_ID;
   END UPD_COUNTRIES;

   PROCEDURE DEL_COUNTRIES (in_COUNTRY_ID IN COUNTRIES.COUNTRY_ID%TYPE)
   IS
   BEGIN
      DELETE FROM COUNTRIES
            WHERE COUNTRY_ID = in_COUNTRY_ID;
   END DEL_COUNTRIES;
END DML_COUNTRIES;
/

How to Use This DML Package?

Suppose you want to insert a row in the Countries table, then call the DML_COUNTRIES packaged procedure INS_COUNTRIES as following:

DECLARE
   IN_COUNTRY_ID             NUMBER;
   IN_COUNTRY_ISO_CODE       CHAR (2);
   IN_COUNTRY_NAME           VARCHAR2 (40);
   IN_COUNTRY_SUBREGION      VARCHAR2 (30);
   IN_COUNTRY_SUBREGION_ID   NUMBER;
   IN_COUNTRY_REGION         VARCHAR2 (20);
   IN_COUNTRY_REGION_ID      NUMBER;
   IN_COUNTRY_TOTAL          VARCHAR2 (11);
   IN_COUNTRY_TOTAL_ID       NUMBER;
   IN_COUNTRY_NAME_HIST      VARCHAR2 (40);
BEGIN
   IN_COUNTRY_ID := 1001;
   IN_COUNTRY_ISO_CODE := 'US';
   IN_COUNTRY_NAME := 'United States of America';
   IN_COUNTRY_SUBREGION := 'Northern America';
   IN_COUNTRY_SUBREGION_ID := 52797;
   IN_COUNTRY_REGION := 'Americas';
   IN_COUNTRY_REGION_ID := 52801;
   IN_COUNTRY_TOTAL := NULL;
   IN_COUNTRY_TOTAL_ID := NULL;
   IN_COUNTRY_NAME_HIST := NULL;

   DML_COUNTRIES.INS_COUNTRIES (IN_COUNTRY_ID,
                                   IN_COUNTRY_ISO_CODE,
                                   IN_COUNTRY_NAME,
                                   IN_COUNTRY_SUBREGION,
                                   IN_COUNTRY_SUBREGION_ID,
                                   IN_COUNTRY_REGION,
                                   IN_COUNTRY_REGION_ID,
                                   IN_COUNTRY_TOTAL,
                                   IN_COUNTRY_TOTAL_ID,
                                   IN_COUNTRY_NAME_HIST);
   COMMIT;
END;
/

See also:

1 thought on “How To Create Procedure for DML in Toad for Oracle?”

Leave a Comment