Sql Loader Data loading issue by sys user

Home Forums Database Administration Sql Loader Data loading issue by sys user

Tagged: 

This topic contains 5 replies, has 2 voices, and was last updated by  Amit Bansal 4 years, 5 months ago.

  • Author
    Posts
  • #2267

    Ashish
    Participant

    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.

     

     

  • #2268

    Amit Bansal
    Keymaster

    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
  • #2270

    Ashish
    Participant

    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

  • #2271

    Amit Bansal
    Keymaster

    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

  • #2272

    Ashish
    Participant

    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

  • #2273

    Amit Bansal
    Keymaster

    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

You must be logged in to reply to this topic.