Home » RDBMS Server » Server Utilities » ORA-00942: table or view does not exist
ORA-00942: table or view does not exist [message #247196] Mon, 25 June 2007 00:55 Go to next message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member
Hi there all,

I'm trying to use the SQL*Loader function but it ends in an error message stating that the table does not exist. I've created the table in our organization schema and the given privillages to public and even created a synonym but I still get the same error. Does anyone have any ideas??

Here's the script I used to create everything I've described above:
-- Create table
create table PLSO.pls_pmt_items_t
(
    file_id                 VARCHAR2(40),
    item_number             VARCHAR2(700),
	  description             VARCHAR2(240),
	  quantity                number,
    diameter                number,
    length                  number,
    width                   number,
    height                  number,
    unit_weight             NUMBER,
	  sizes 		              Varchar2(240),
	  material                varchar2(240),
    remark                  varchar2(240),
    vendor_id               number,
    attribute1              VARCHAR2(40),
	  makebuy                 varchar2(240),
    project                 number,
	  template_name           VARCHAR2(30),
	  primary_uom_code        VARCHAR2(3),
	  costing_enabled_flag    VARCHAR2(1),
    cost_of_sales_account   NUMBER,
	  un_number_id            NUMBER,
	  hazard_class_id         NUMBER,
	  expense_account         NUMBER,
	  volume_uom_code         VARCHAR2(3),
    unit_volume             NUMBER,
    inventory_planning_code NUMBER,
	  max_minmax_quantity     NUMBER,
	  sales_account           NUMBER,
	  attribute2              VARCHAR2(40),
	  attribute3              VARCHAR2(40),
	  attribute4              VARCHAR2(40),
	  attribute5              VARCHAR2(40),
	  attribute6              VARCHAR2(40),
	  attribute7              VARCHAR2(40),
	  attribute8              VARCHAR2(40),
    attribute9              VARCHAR2(40),
	  attribute10             VARCHAR2(40),
	  attribute11             VARCHAR2(40),
	  attribute12             VARCHAR2(40),
	  creation_date           DATE,
	  created_by              NUMBER,
	  created_by_name         VARCHAR2(40)
)
tablespace PLSO_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select, insert, update, delete on PLSO.pls_pmt_items_t to PUBLIC;

create synonym apps.pls_pmt_items_t for plso.pls_pmt_items_t;


I've been able to insert data from pl/sql developer but the application ends in an error on the SQL*Loader concurrent program.

Report message to a moderator

Re: ORA-00942: table or view does not exist [message #247200 is a reply to message #247196] Mon, 25 June 2007 00:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Privs acquired via ROLE do not apply within PL/SQL procedures.

Report message to a moderator

Re: ORA-00942: table or view does not exist [message #247205 is a reply to message #247200] Mon, 25 June 2007 01:11 Go to previous messageGo to next message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member
The thing is that I've created a table based on the same privileges for a different program and it worked fine. I've compared the two and they look the same. What would you suggest I do for the privileges then??

Report message to a moderator

Re: ORA-00942: table or view does not exist [message #247211 is a reply to message #247205] Mon, 25 June 2007 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For myself, I'm unable to give an answer untill you post what you did: the command to execute SQL*Loader and the control file.

And of course, the point that should always be in ALL posts: full Oracle version.

Regards
Michel

[Edit: typo]

[Updated on: Mon, 25 June 2007 01:45]

Report message to a moderator

Re: ORA-00942: table or view does not exist [message #247212 is a reply to message #247205] Mon, 25 June 2007 01:37 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
what does your ctl file look like?
[edit: ah, Michel beat me Smile ]

[Updated on: Mon, 25 June 2007 01:37]

Report message to a moderator

Re: ORA-00942: table or view does not exist [message #247224 is a reply to message #247212] Mon, 25 June 2007 02:29 Go to previous messageGo to next message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member
 
OPTIONS (SKIP=1)
LOAD DATA 
BADFILE 'pls_pmt_items_t.bad'
DISCARDFILE 'pls_pmt_items_t.dsc'
DISCARDMAX 999
APPEND
INTO TABLE "pls_pmt_items_t"  
FIELDS  TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS 
(
	file_id			CHAR(40), 
	item_number     CHAR(700),  
	description 	CHAR(240),
	quantity 		INTEGER EXTERNAL,
	diameter 		INTEGER EXTERNAL,
	length 			INTEGER EXTERNAL,
	width 			INTEGER EXTERNAL,
	height 			INTEGER EXTERNAL,
	unit_weight		FLOAT EXTERNAL,
	sizes 			CHAR(240),
	material 		CHAR(240),
	remark 			CHAR(240),
	vendor_id 		INTEGER EXTERNAL,
	attribute1      CHAR(40),
	makebuy 		CHAR(240),
	project  		INTEGER EXTERNAL
)


Oracle Info:
RDBMS : 10.2.0.2.0
Oracle Applications : 11.5.10.2

Report message to a moderator

Re: ORA-00942: table or view does not exist [message #247228 is a reply to message #247224] Mon, 25 June 2007 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the command to launch SQL*Loader was?

Regards
Michel

Report message to a moderator

Re: ORA-00942: table or view does not exist [message #247234 is a reply to message #247228] Mon, 25 June 2007 03:33 Go to previous messageGo to next message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member
As I said above I use a concurrent program to do the SQL*Loader as this will eventually be run by the user. I create the Executable with SQL*Loader Execution method and this is used by the Concurrent Program when it runs. I pass the csv file name through the Concurrent and thats where it is getting stuck with the Table doesn't exist error

Report message to a moderator

Re: ORA-00942: table or view does not exist [message #247237 is a reply to message #247234] Mon, 25 June 2007 03:39 Go to previous message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All what we can say is that your "concurrent program" uses a user that don't have access to your table or try to load into another schema which does not contain the "pls_pmt_items_t" table.

Regards
Michel

Report message to a moderator

Previous Topic: SQL*Load - prevent carriage return char in last column?
Next Topic: Cross platform export
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jun 27 20:01:06 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.