Creating a Table in PL/SQL

[CassidyHunt]

I keep getting an error on the create statement of this table. Can anyone tell me what I am doing wrong?

Procedure VerifyReportTable as
    i number;
  begin
  
    select count(*) into i from systables s where s.NAME = 'PARTCOMP';
  
    if i > 0 then
      delete from PARTCOMP;
    else
      create table PARTCOMP(part_id varchar2(80),
                            description varchar2(255),
                            commodity_code varchar2(80),
                            stocked char(2),
                            qty_on_hand number,
                            qty_sold number,
                            turn_over number,
                            actual_price number,
                            actual_cost number,
                            actual_gross_profit number,
                            last_reciept_cost number,
                            part_price number,
                            part_cost number,
                            part_gross_profit number,
                            engineering_master_cost number,
                            engineering_master_qty number,
                            engineergineering_master_unit_cost number,
                            part_vs_actual number,
                            part_vs_fifo number,
                            part_vs_engineering number);
    
    end if;
  end;

Thanks

Cassidy

 

[SantaMufasa]

Cassidy,

Any SQL DDL (Data-Definition Language) commands in a PL/SQL block must occur within an "EXECUTE IMMEDIATE ('<DDL command>')" invocation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Beilstwh]

Also, I am there is no such object as systables in oracle. What you are looking for is

dba_tables - All tables in system, only visible to administrators

all_tables - All tables in all schema's that the connected user has access to.

user_tables - all tables in the connected users schema.

Bill
Oracle DBA/Developer
New York State, USA

 

[CassidyHunt]

I just ran a query on systables and got 2063 rows containing all the tables in my database. I ran the queries for dba_tables and user_tables and got the systables that I ran split in two basically. all_tables came back with 1362 rows which makes me wonder what is missing.

Systables comes back successful for me so makes me wonder where it would come from and why it would contain more than any of the other variations you gave me.

Thanks

Cassidy

 

[Beilstwh]

It looks like your oracle database was converted from an Infomix or DB2 database which uses systables. for example, the oracle conversion tool will create a systables view so that existing code will work. However in an unconverted database, the oracle views are the way to go.

Bill
Oracle DBA/Developer
New York State, USA