[Ashish]

Dear Amit,

 

I am trying to load some data in my database via sys login but it’s fai getting below error, can you help me for the same:
Z:\>sqlldr userid=sys/orcl

control = Spl_Master.ctl

SQL*Loader: Release 11.2.0.1.0 – Production on Mon Sep 24 17:17:04 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-128: unable to begin a session
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

If even i am using with below:
Z:\>sqlldr userid=sys/orcl control=Spl_Master.ctl log=spl.log

SQL*Loader: Release 11.2.0.1.0 – Production on Mon Sep 24 17:24:31 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-128: unable to begin a session
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

 

so please Help me for the Same.

 

 

[Amit Bansal]

Hi Ashish,

As error states, you need to specify ‘as sysdba’. So it should be

sqlldr userid=sys/orcl as sysdba control=Spl_Master.ctl log=spl.log

[Ashish]

Thanx Amit,

Via Sql loader i loaded one sql server table data in Oracle by changing the datatype to varchar2 ,  Now i want to change as it is, so how to write Procedure for the same by which i can changed the data type of oracle Table? (  I was facing problem in date format so i changed it to varchar, then all data loaded proprly appx 140524 rows)

OR

Please tell me any other way to chage data type.

Regards,

AShish

[Amit Bansal]

Ashish,

I think you can do it using CTAS.

Suppose table emp has two columns empid,hiredate(varchar2)

create table emp_new as select empid,to_date(hiredate,'DD-MON-YYYY') hiredate from emp;

I believe above thing would work

[Ashish]

When i am trying to run below commond on oracle table where we need to change Varchar2  to date DATA type for resp column: 

insert into evaluation_new (CREATEDDATE)
(Select  To_date(CREATEDDATE,’yyyy-mm-dd hh24:mi:ss’)
from evaluation_detail);

 

Error:

ORA-01830: date format picture ends before converting entire input string
01830. 00000 –  “date format picture ends before converting entire input string”
*Cause:   
*Action:

(Note: Old str of column   “2009-09-16 18:38:00.000”)

 Please help me for the same

[Amit Bansal]

Above data indicates that you should be using timestamp datatype

 

SQL> select to_date( ‘2012-09-28 02:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS’) from dual;
select to_date( ‘2012-09-28 02:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS’) from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

If I change this to timestamp it would work fine

select to_timestamp( ‘2012-09-28 02:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FF’) from dual;

TO_TIMESTAMP(‘2012-09-2802:00:00.000′,’YYYY-MM-DDHH24:MI:SS.FF’)
—————————————————————————
28-SEP-12 02.00.00.000000000 AM

regards

Amit

[Author]

Posts