Home » SQL & PL/SQL » SQL & PL/SQL » table confusion (9i, Win XP)
table confusion [message #337807] Fri, 01 August 2008 04:40 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

I have a problem I am trying to load a dump file from 8i to 9i while doing this some tables are not being created and I am trying to manually create them.

One table (SE_ADMIN) does not appear when I try to user this query

select table_name from user_tables


but when I search it by name from user_tables it appears (nor it is a temporary table). I searched dba_tables but could not find its owner.

Any guesses how I could find its owner and why it is not appearing in the tables list from user_tables?

Re: table confusion [message #337817 is a reply to message #337807] Fri, 01 August 2008 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste what you did don't just describe it.
Copy and paste the loading log and your verifications.

Regards
Michel
Re: table confusion [message #337820 is a reply to message #337807] Fri, 01 August 2008 05:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
My first guess: mixed case objectname
Re: table confusion [message #337825 is a reply to message #337817] Fri, 01 August 2008 05:18 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
SQL> select table_name from user_Tables;


TABLE_NAME
------------------------------
SE_ADVRTDTL_TEMP
SE_ADVRTRATE
SE_ADVRTSMNT_TEMP
SE_CAMACTVSBUDGETVIEW
SE_CAMPAIGN_TEMP
SE_CAMPGNCAT_TEMP
SE_CAMPGNDTL_TEMP
SE_CAMPLIST
SE_CAPSDTL_TEMP
SE_CAPSULE_TEMP
SE_CONFIG

TABLE_NAME
------------------------------
SE_CONFIGUREHIST
SE_CONTPTS
SE_DRAWS
SE_LOTPTS
SE_MATCHPROFILE
SE_PANDINGJACKPOTWINNER
SE_PANDINGWINNER
SE_REFACCDTL

19 rows selected.


The table SE_ADMIN is not being displayed in the above list but when i try this

SQL> select object_type from user_objects where object_name='SE_ADMIN';


OBJECT_TYPE
------------------
TABLE


This table was made from an object (U_ADMIN_TY), Is this because of that?

[Updated on: Fri, 01 August 2008 05:20] by Moderator

Report message to a moderator

Re: table confusion [message #337830 is a reply to message #337825] Fri, 01 August 2008 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This table was made from an object (U_ADMIN_TY),

Post description or better create statement (for all objects connected to this one).

Should we have to ask you for each bit of information?

Regards
Michel

[Updated on: Fri, 01 August 2008 05:23]

Report message to a moderator

Re: table confusion [message #337839 is a reply to message #337830] Fri, 01 August 2008 05:40 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
no...sorry

here is the type creation and table creation script.

create type u_admin_ty as object
(admmbrid_c		varchar2(15)
,password_m		varchar2(10)
,update_d		date
,
static procedure p_insertadmin 
(padmmbrid_c		varchar2
,ppassword_m		varchar2
,pupdate_d		date
,sname_m		varchar2
,tname_m		varchar2
)
)
/

create type body u_admin_ty as 
static procedure p_insertadmin
(padmmbrid_c		varchar2
,ppassword_m		varchar2
,pupdate_d		date
,sname_m		varchar2
,tname_m		varchar2
)
is
sqlstmt varchar2(200);
begin
 sqlstmt := 'insert into ' ||sname_m||'.'||tname_m|| ' values (:1,:2,:3)';
execute immediate sqlstmt using
 padmmbrid_c		
,ppassword_m		
,pupdate_d		
;
end;	
end;
/

create table se_admin of u_admin_ty
(constraint admmbridc_pk primary key(admmbrid_c)
,constraint passwordm_nn password_m not null
,constraint passwordm_ck check (length(password_m) > 6)
);


Re: table confusion [message #337846 is a reply to message #337839] Fri, 01 August 2008 06:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How odd.

Sure enough, the table doesn't show in USER_TABLES.

<Pause for document reading>

Right:

the *_TABLES views are for looking at the details of Relational tables (look at the definition)

To see object tables, there are a set of views *_OBJECT_TABLES, and another set (*_NESTED_TABLES) for looking at Nested tables.

There we go. All very neat and precise, but a little irritating that there isn't a single overarching tables view.
Re: table confusion [message #337873 is a reply to message #337846] Fri, 01 August 2008 07:45 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Thanks a lot..I will try this and let you know if I face any problem..
Re: table confusion [message #337893 is a reply to message #337839] Fri, 01 August 2008 08:32 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create type u_admin_ty as object
  2  (admmbrid_c  varchar2(15)
  3  ,password_m  varchar2(10)
  4  ,update_d  date
  5  ,
  6  static procedure p_insertadmin 
  7  (padmmbrid_c  varchar2
  8  ,ppassword_m  varchar2
  9  ,pupdate_d  date
 10  ,sname_m  varchar2
 11  ,tname_m  varchar2
 12  )
 13  )
 14  /

Type created.

SQL> 
SQL> create type body u_admin_ty as 
  2  static procedure p_insertadmin
  3  (padmmbrid_c  varchar2
  4  ,ppassword_m  varchar2
  5  ,pupdate_d  date
  6  ,sname_m  varchar2
  7  ,tname_m  varchar2
  8  )
  9  is
 10  sqlstmt varchar2(200);
 11  begin
 12   sqlstmt := 'insert into ' ||sname_m||'.'||tname_m|| ' values (:1,:2,:3)';
 13  execute immediate sqlstmt using
 14   padmmbrid_c  
 15  ,ppassword_m  
 16  ,pupdate_d  
 17  ;
 18  end; 
 19  end;
 20  /

Type body created.

SQL> create table se_admin of u_admin_ty
  2  (constraint admmbridc_pk primary key(admmbrid_c)
  3  ,constraint passwordm_nn password_m not null
  4  ,constraint passwordm_ck check (length(password_m) > 6)
  5  );

Table created.

SQL> select table_name from user_tables where table_name='SE_ADMIN';

no rows selected

SQL> select table_name from user_object_tables where table_name='SE_ADMIN';
TABLE_NAME
------------------------------
SE_ADMIN

1 row selected.

Thanks to JRowbottom and for providing the (correct) code that we can execute.

Regards
Michel

[Updated on: Fri, 01 August 2008 08:33]

Report message to a moderator

Previous Topic: Query logic
Next Topic: creating and running a trigger
Goto Forum:
  


Current Time: Thu Dec 08 18:34:59 CST 2016

Total time taken to generate the page: 0.16333 seconds