CODEWITHSUNDEEP
oracle-databaseplsql
NITTIN
NITTIN

Reputation: 1

how to create plsql table

begin
create or replace table ins_company_master
(
ins_company_id          NUMBER(2),    
ins_Company_code        VARCHAR(15),   
ins_company_name        VARCHAR(15),
ins_company_contact         VARCHAR(15),
ins_company_address         VARCHAR(15),
ins_company_location        VARCHAR(15),
ins_company_status          CHAR(1),
constraint ins_company_id   primary key(id),
constraint ins_company_code     unique(code)
); 
end;
/

Upvotes: 0

Views: 106

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

As commented, it is dynamic SQL you need. Though, think twice! In Oracle, we - usually - don't create tables dynamically. We create them once, at SQL level, and use anywhere we want. That stands for temporary tables as well.

So, in your case, it would be as follows (note comments I wrote for lines 12, 13 and 14). Also, Oracle recommends us to use VARCHAR2 datatype (not VARCHAR).

SQL> begin
  2  execute immediate '
  3  create table ins_company_master
  4  (
  5  ins_company_id          NUMBER(2),
  6  ins_Company_code        VARCHAR2(15),
  7  ins_company_name        VARCHAR2(15),
  8  ins_company_contact     VARCHAR2(15),
  9  ins_company_address     VARCHAR2(15),
 10  ins_company_location    VARCHAR2(15),
 11  ins_company_status      CHAR(1),
 12  constraint ins_company_id       primary key(ins_company_id),  --> fix column ...
 13  constraint ins_company_code     unique(ins_company_code)      --> ... names
 14  ) ';  --> remove semi-colon at the end of the CREATE TABLE statement
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL> select * from ins_company_master;

no rows selected

SQL>

Upvotes: 1

Related Questions