Home » RDBMS Server » Server Utilities » dividing by 100 in sql loader
dividing by 100 in sql loader [message #406140] Tue, 02 June 2009 08:31 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
i am loading a .csv into oracle table, and for a particular column, i want whatever the value is there in the file / 100
being loaded..

i wrote a function which divides a number by 100

create or replace function div(in_num in number)
return number is
v_res number:=0;
begin
	
	v_res:= in_num / 100;

	return(v_res); 

end f_div;
/


and in my control file, for that column, i did this:

load_column "div(load_column)"

but it gives error:

ORA-00984: column not allowed here

its a syntax error. can anyone help ?
Re: dividing by 100 in sql loader [message #406145 is a reply to message #406140] Tue, 02 June 2009 08:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
 load_column "div(:load_column)"
Re: dividing by 100 in sql loader [message #406146 is a reply to message #406140] Tue, 02 June 2009 08:55 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Don't you think, you are trying to call function from alias?

Quote:
ORA-00984: column not allowed here
Cause: A column name was used in an expression where it is not permitted, such as in the VALUES clause of an INSERT statement.
Action: Check the syntax of the statement and use column names only where appropriate.


regards,
Delna
Re: dividing by 100 in sql loader [message #406197 is a reply to message #406146] Tue, 02 June 2009 14:57 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The problem, as correctly pointed out by Mahesh Rajendran, is that the colon is missing. This causes it to see load_column as a column name, instead of of :load_column as a bind variable, which is why the error message says that a column is not allowed in the place where it was expecting a bind variable.

Previous Topic: How to run TKPROF on Oracle9i(9.0.1)
Next Topic: expdp (merged 4)
Goto Forum:
  


Current Time: Thu Dec 08 00:41:44 CST 2016

Total time taken to generate the page: 0.14363 seconds