Forum Stats

  • 3,759,065 Users
  • 2,251,495 Discussions
  • 7,870,478 Comments

Discussions

Create a dynamic Table using procedure

User_KKLFR
User_KKLFR Member Posts: 1 Green Ribbon

create or replace procedure Pro_Tbl_Cre( P_TBL_NAME IN VARCHAR2)

is 

BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE '||P_TBL_NAME||' (Empname VARCHAR2(100), EmpId Number)';

End;

I'm facing error. How to write it properly?

Answers

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 117 Silver Badge

    What error do you get? I am able to execute.

    With dynamic ddl use intermediate to be able to view generated statement.

    create or replace procedure Pro_Tbl_Cre( P_TBL_NAME IN VARCHAR2)

    is

    v_statement varchar2(32767);

    BEGIN

     v_statement := 'CREATE TABLE '||P_TBL_NAME||' (Empname VARCHAR2(100), EmpId Number)';

     dbms_output.put_line(v_statement);

    EXECUTE IMMEDIATE v_statement;

    exception when others then dbms_output.put_line(sqlerrm);

    End;


    begin pro_tbl_cre('newtab');

    end;

    User_KKLFR
  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond


    Why on Earth are you trying to create tables dynamically at runtime?

    That's not how proper system design takes place. You should know the tables you require at the time the design of the system/application/process happens, and those should be created outside of any runtime code that is developed. By the time you create runtime code to implement the required system, all the required tables and other database objects should be in place. There shouldn't be any need to create new objects on-the-fly.

    Essentially, and without meaning to sound rude, typically trying to create database objects on-the-fly is a sign of poor systems design.

    There are certainly times when runtime dynamic statements can be appropriate (such as truncating data from tables if they're not already defined as temporary tables), but those should be rare exceptions and well justified.

    JackKEdStevensBilly VerreynneUser_KKLFR
  • EdStevens
    EdStevens Member Posts: 28,467 Gold Crown

    "I'm facing error." is not found in any error reference that I have access to.

    When posting code to the forum, please, please, PLEASE format it. See https://community.oracle.com/tech/apps-infra/kb/articles/12-format-text

    Also please read and re-read the comment from BlueShadow. If hadn't already written that, I would have.

    User_KKLFR
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,060 Red Diamond

    Hi, @User_KKLFR

    Why do you want a procedure like that? What is the business problem you're trying to solve? If you can explain your requirements in English, then someone can help you find a good solution in SQL or PL/SQL. (As others have said, creating tables in PL/SQL is almost certainly not a good way, let alone the best way, to do whatever you need to do.)

    User_KKLFR