Help in a simple procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Help in a simple procedure

  1. #1
    Join Date
    Jun 2008
    Posts
    38

    Help in a simple procedure

    i want to develope a pl/sql procedure that will create an empty table. below the code that i wrote but it is not working

    Code:
    create procedure myProc AS
    declare 
    	first_name    VARCHAR(15),
             	last_name     VARCHAR(15),
             	salary        FLOAT(8,2),
             	city          VARCHAR(10),
            	description   VARCHAR(15)
    BEGIN
    EXECUTE IMMEDIATE ('create table employee_table');
    END;

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    why did you think it would work - that isnt a correct create table statement

  3. #3
    Join Date
    Jun 2008
    Posts
    38
    Thank you sir for pointing that out. I m very new to Sql procedure can you tell/show me the correct syntax for the above code

  4. #4
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    create table
    ( first_name VARCHAR(15),
    last_name VARCHAR(15),
    salary FLOAT(8,2),
    city VARCHAR(10),
    description VARCHAR(15)
    );
    lucky

  5. #5
    Join Date
    Jun 2008
    Posts
    38
    u didnt get me i want to know how to create empty table using sql procedure and not using sql query....

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Well... your procedure has to:

    1) Be able to get all data it needs -owner, table_name, column_names, column_attributes, etc - as input parameters
    2) Build a proper create table statement on the fly -dynamic sql
    3) Execute such an statement
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by mahajanakhil198 View Post
    create table
    ( first_name VARCHAR(15),
    last_name VARCHAR(15),
    salary FLOAT(8,2),
    city VARCHAR(10),
    description VARCHAR(15)
    );

    what the hell is that?

  8. #8
    Join Date
    Oct 2006
    Posts
    175
    create table employee_table
    ( first_name VARCHAR(15),
    last_name VARCHAR(15),
    salary number(8,2),
    city VARCHAR(10),
    description VARCHAR(15)
    );

    or
    BEGIN
    EXECUTE IMMEDIATE '
    create table employee_table
    (
    first_name VARCHAR(15),
    last_name VARCHAR(15),
    salary number(8,2),
    city VARCHAR(10),
    description VARCHAR(15)
    )';
    END;
    or
    create procedure myProc AS
    BEGIN
    EXECUTE IMMEDIATE '
    create table employee_table
    (
    first_name VARCHAR(15),
    last_name VARCHAR(15),
    salary number(8,2),
    city VARCHAR(10),
    description VARCHAR(15)
    )';
    END;

    Lots of ways there. You can't just give table structure that way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width