1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

few statements in one script

Discussion in 'SQL PL/SQL' started by ecivgamer, May 12, 2011.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all, I've got two "CREATE TABLE" statements, two "COMMENT ON TABLE" statements and few "COMMENT ON COLUMN" statements. The question is - how do I combine all statements in one script, is it possible at all? Should I create procedure instead?

    1:
    Code (SQL):

    CREATE TABLE table1
    (ID VARCHAR2(20) PRIMARY KEY,
    arcdate DATE NOT NULL,
    CONTRAGENTID NUMBER (10) NOT NULL,
    RELMANAGER VARCHAR2(30) NOT NULL,
    RELMANAGERNAME VARCHAR2(100) NOT NULL )
    2:
    Code (SQL):

    CREATE TABLE table2
    (ID VARCHAR2(20) PRIMARY KEY,
    CONTRAGENTID NUMBER (10) NOT NULL,
    FOUNDERNAME1 VARCHAR2(220),
    FOUNDERCODE1 NUMBER (10),
    FOUNDERCOUNTRY1 VARCHAR2(60),
    FOUNDERPART1 VARCHAR2(10),
    FOUNDERNAME2 VARCHAR2(220),
    FOUNDERCODE2 NUMBER (10),
    FOUNDERCOUNTRY2 VARCHAR2(60),
    FOUNDERPART2 VARCHAR2(10) )
    3:
    Code (SQL):
    COMMENT ON TABLE table1 IS 'This is a table for report A.'
    4:
    Code (SQL):
    COMMENT ON TABLE table2 IS 'This is a table for report B.'
    5:
    Code (SQL):
    COMMENT ON COLUMN table1.id IS 'date+contragentid.'
    6:
    Code (SQL):
    COMMENT ON COLUMN table2.id IS 'date+contragentid.'
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Simply concatenate the scripts in the order you posted them. Presuming you have proper statement terminators after each (a ; or a / as the last line) the script will execute properly. As an example:

    Code (SQL):
     
    CREATE TABLE norf (bleepo NUMBER);
     
    CREATE INDEX norf_idx ON norf(bleepo);
     
    comment ON COLUMN norf.bleepo IS 'wow, I did this';
     
    Executing that one script would create the table, index and comment.
     
    ecivgamer likes this.
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Btw, procedures are not normally intended for creating objects in database.
     
    ecivgamer likes this.