2 Replies Latest reply: Jun 29, 2012 3:39 PM by Paulie RSS

    Samples to Create, Insert, Modify and Delete Oracle Database Objects

    946719
      Found some samples on how to Create, Insert, Modify and Delete tables, views, indexes, constraints, stored procedures, functions, triggers:


      From : http://www.dtsql.com/docs/db/db_oracle.htm

      Table

      Create Table

      Oracle allows to create a new table. After entering the table name and column information, DtSQL can generate and execute the SQL to create the table.

      ■Column Name
      ■Column Type
      ■Length/Precesion
      ■Scale (decimal)
      ■Default Value
      ■Nullable
      ■Primary Key
      ■Unique
      Sample :
      CREATE TABLE “TEST_SCHEMA”.”TEST_TABLE” (
      a bigint DEFAULT 1 NOT NULL,
      b char ( 12 ) DEFAULT ‘abc’ NOT NULL,
      c decimal ( 12, 2 ) ,

      PRIMARY KEY ( a, b ),
      UNIQUE ( b,c )
      )

      Rename Table

      Oracle allows to rename an existing table. After entering the table name, DtSQL can generate and execute the SQL to rename the table.

      Sample : ALTER TABLE “TEST_SCHEMA”.”TEST_TABLE” RENAME TO TESTTABLE

      Truncate Table

      Oracle does not allows to truncate an table and deleting table is used instead. After entering the table name, DtSQL can delete all data in the table.

      Sample : TRUNCATE TABLE TEST_SCHEMA”.”TEST_TABLE”

      Drop Table

      Oracle allows to drop an existing table. After entering the table name, DtSQL can generate and execute the SQL to drop the table.

      Sample : DROP TABLE “TEST_SCHEMA”.”TEST_TABLE”



      Column

      Add Column

      Oracle allows to add a new column to an existing table. After selecting the table and entering column information, DtSQL can generate and execute the SQL to add the new column to the table.

      ■Column Name
      ■Column Type
      ■Length/Precesion
      ■Scale (decimal)
      ■Default Value
      ■Nullable
      Sample :
      ALTER TABLE
      “TEST_SCHEMA”.”TEST_TABLE”
      ADD
      d varchar(20) DEFAULT ‘def’ NOT NULL

      Change Data Type

      Oracle allows to change the column data. After selecting the new data type, DtSQL can generate and execute the SQL to change the column data type.

      Sample :
      ALTER TABLE “TEST_SCHEMA”.”TEST_TABLE”
      MODIFY ( F CHAR (22) )

      Drop Column

      Oracle allows to delete an existing column. After selecting the column, DtSQL can generate and execute the SQL to delete the column.

      Sample :
      ALTER TABLE “TEST_SCHEMA”.”TEST_TABLE”
      DROP COLUMN F
      TOP


      View

      Create View

      Oracle allows to create a new view. After entering the view name and select SQL, DtSQL can generate and execute the SQL to create the view.

      Sample :
      CREATE VIEW “TEST_SCHEMA”.”TEST_VIEW”
      AS
      SELECT * FROM “TEST_SCHEMA”.”TEST_TABLE”

      Drop View

      Oracle allows to drop an existing view. After selecting the view name, DtSQL can generate and execute the SQL to delete the view.

      Sample : DROP VIEW “TEST_SCHEMA”.”TEST_VIEW”
      TOP


      Index

      Create Index

      Oracle allows to create a new index. After selecting the table columns and entering the view name, DtSQL can generate and execute the SQL to create the index.

      Sample :
      CREATE UNIQUE INDEX “TEST_SCHEMA”.”TEST_INDEX”
      ON “TEST_SCHEMA”.”TEST_TABLE” ( A, B DESC, C DESC )

      Rename Index

      Oracle allows to rename an existing index. After selecting the index and entering the new index name, DtSQL can generate and execute the SQL to rename the index.

      Sample : ALTER INDEX “TEST_SCHEMA”.”TEST_INDEX” RENAME TO “TESTINDEX”

      Drop Index

      Oracle allows to drop an existing index. After selecting the index name, DtSQL can generate and execute the SQL to delete the index.

      Sample : DROP INDEX “TEST_SCHEMA”.”TEST_INDEX”
      TOP


      Trigger

      Create Trigger

      Oracle allows to create a new trigger. After entering the trigger name and trigger actions, DtSQL can generate and execute the SQL to create the trigger.

      Sample :
      CREATE TRIGGER “TEST_SCHEMA”.”TEST_TRIGGER”
      AFTER INSERT
      ON “TEST_SCHEMA”.”TEST_TABLE”
      FOR EACH ROW
      BEGIN
      RAISE_APPLICATION_ERROR (num => 0, msg => ‘Trigger action message’);
      END

      Drop Trigger

      Oracle allows to drop an existing trigger. After selecting the trigger name, DtSQL can generate and execute the SQL to delete the trigger.

      Sample : DROP TRIGGER “TEST_SCHEMA”.”TEST_TRIGGER”
      TOP


      Constraint

      Create Primary Key

      Oracle allows to create a primary key for a table. After selecting the table columns, DtSQL can generate and execute the SQL to create the primary key.

      Sample :
      ALTER TABLE
      “TEST_SCHEMA”.”TEST_TABLE”
      ADD
      PRIMARY KEY ( B, C )

      Create Foreign Key

      Oracle allows to create a foreign key for a table to reference to another table. After selecting the table columns and referenced table columns, DtSQL can generate and execute the SQL to create the foreign key.

      Sample :
      ALTER TABLE
      “TEST_SCHEMA”.”TEST_TABLE”
      ADD
      FOREIGN KEY ( A, C )
      REFERENCES “TEST_SCHEMA”.”TEST_TB” (B, A )

      Create Check Constraint

      Oracle allows to create a check constraint for a table column. After entering the check constraint name and check action, DtSQL can generate and execute the SQL to create the check constraint.

      Sample :
      ALTER TABLE
      “TEST_SCHEMA”.”TEST_TABLE”
      ADD CONSTRAINT “TEST_CHECK”
      CHECK ( C > 10 )

      Create Unique Constraint

      Oracle allows to create an unique constraint for a table columns. After entering the unique constraint name and selecting table columns, DtSQL can generate and execute the SQL to create the unique constraint.

      Sample :
      ALTER TABLE
      “TEST_SCHEMA”.”TEST_TABLE”
      ADD CONSTRAINT “TEST_UNIQUE”
      UNIQUE ( B, C )

      Drop Primary Key

      Oracle allows to drop an existing primary key. After selecting the primary key, DtSQL can generate and execute the SQL to delete the primary key.

      Sample : ALTER TABLE “TEST_SCHEMA”.”TEST_TABLE”
      DROP CONSTRAINT “TEST_TABLE_ID_PK”

      Drop Foreign Key

      Oracle allows to drop an existing foreign key. After selecting the foreign key, DtSQL can generate and execute the SQL to delete the foreign key.

      Sample :
      ALTER TABLE “TEST_SCHEMA”.”TEST_TABLE”
      DROP CONSTRAINT “TEST_TABLE_FK”

      Drop Check Constraint

      Oracle allows to drop an existing check constraint. After selecting the check constraint, DtSQL can generate and execute the SQL to delete the check constraint.

      Sample :
      ALTER TABLE
      “TEST_SCHEMA”.”TEST_TABLE”
      DROP CONSTRAINT “TEST_CHECK”

      Drop Unique Constraint

      Oracle allows to drop an existing unique constraint. After selecting the unique constraint, DtSQL can generate and execute the SQL to delete the unique constraint.

      Sample :
      ALTER TABLE
      “TEST_SCHEMA”.”TEST_TABLE”
      DROP CONSTRAINT “TEST_UNIQUE”
      TOP


      Procedure

      Create Procedure

      Oracle allows to create procedures. After entering the procedure name, parameter names and procedure body, DtSQL can generate and execute the SQL to create the procedure.

      Sample :
      CREATE PROCEDURE TEST_SCHEMA.TEST_PROC
      ( IN PARAM1 VARCHAR(20), INOUT PARAM2 BIGINT, OUT parm3 TIMESTAMP)
      LANGUAGE SQL
      BEGIN
      DECLARE a1 BIGINT;
      SELECT COUNT(*) INTO a1 FROM SYSIBM.TABLES;
      END

      Drop Procedure

      Oracle allows to drop an existing procedure. After selecting the procedure, DtSQL can generate and execute the SQL to delete the procedure.

      Sample : DROP PROCEDURE “TEST_SCHEMA”.”TEST_PROC”
      TOP


      Function

      Create Function

      Oracle allows to create functions. After entering the procedure name, parameter names and function body, DtSQL can generate and execute the SQL to create the function.

      Sample :
      CREATE OR REPLACE FUNCTION TEST_FUNC
      ( PARAM1 char(20), PARAM2 long, PARAM3 date )
      RETURNS char(30)
      IS
      charValue char(30);
      BEGIN
      SELECT USERNAME INTO charValue FROM “SYS”.”ALL_USERS”;
      RETURN(charValue);
      END

      Drop Function

      Oracle allows to drop an existing function. After selecting the function, DtSQL can generate and execute the SQL to delete the function.

      Sample : DROP FUNCTION “TEST_SCHEMA”.”TEST_FUNC”
      TOP


      Sequence

      Create Sequence

      Oracle allows to create sequences. After entering the sequence parameters, DtSQL can generate and execute the SQL to create the sequence.

      Sample :
      CREATE SEQUENCE “TEST_SCHEMA”.”TEST_SEQ”
      INCREMENT BY 2
      MINVALUE 1
      MAXVALUE 10000
      START WITH 10
      CACHE 20
      CYCLE

      Drop Sequence

      Oracle allows to drop an existing sequence. After selecting the sequence, DtSQL can generate and execute the SQL to delete the sequence.

      Sample : DROP SEQUENCE “TEST_SCHEMA”.”TEST_SEQ”

      Edited by: 943716 on Jun 29, 2012 12:44 PM