Home » RDBMS Server » Server Utilities » Sql Loader issue
icon5.gif  Sql Loader issue [message #279593] Fri, 09 November 2007 04:50 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
hi all,

See I want to insert the record into the table using sqlloader that i did. But i want to show the how many records to be inserted into the table while i run the sql loader script that particular table.But it can be shown in backend(how many records to be inserted). Please help me out how to do that task..Is there any scripts u have?

Thank u in advance
Re: Sql Loader issue [message #279595 is a reply to message #279593] Fri, 09 November 2007 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You mean you want to see how many records has been inserting till now during a loader execution?
Oracle version?

Regards
Michel

[Updated on: Fri, 09 November 2007 04:59]

Report message to a moderator

icon5.gif  Re: Sql Loader issue [message #279596 is a reply to message #279593] Fri, 09 November 2007 05:06 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
see This is my sql loader script
 LOAD DATA
INFILE 'BONUSACC.TXT'
append
INTO TABLE OMF_API_BONUS_ACCOUNT
(BNS_EMPLOYEE_NO                POSITION(1:30),
BNS_ACCOUNT_NO                  POSITION(31:60),
BNS_START_DATE                  POSITION(61:68) "TO_DATE(:BNS_START_DATE,'yyyymmdd')",
BNS_AMOUNT_TILL_DATE            POSITION(69:76) "TO_DATE(:BNS_AMOUNT_TILL_DATE,'yyyymmdd')",
BNS_INT_ACCRUED                 POSITION(77:95) ,
BNS_INT_ACCRUED_DT              POSITION(96:103) "TO_DATE(:BNS_INT_ACCRUED_DT,'yyyymmdd')",
BNS_LAST_BONUS_RECEIVED_AMT     POSITION(104:122),
BNS_LAST_BONUS_RECEIVED_DT      POSITION(123:130) "TO_DATE(:BNS_LAST_BONUS_RECEIVED_DT,'yyyymmdd')",
BNS_INT_RATE                    POSITION(131:149),
BNS_ID                          "BONACC_SEQNUM.NEXTVAL"
)



In 'BONUSACC.TXT' totally 50000 records after run this script. I want to see how many records to be inserted in backend oracle...
please can u send the scripts,if u have

Re: Sql Loader issue [message #279605 is a reply to message #279596] Fri, 09 November 2007 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand the question.
If there is 50000 records in the file then does it 50000 the answer?
Does this in the log file?
In short, what do you want?

Regards
Michel
Re: Sql Loader issue [message #279620 is a reply to message #279593] Fri, 09 November 2007 07:14 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
See Michel

In my script its uploading the records is fine after i run the script.After i seeing the log file and found how many records to be inserted everythng is fine... But my requirement I want to create one table and show how many records to be inserted..

Example:

See OMF_API_BONUS_ACCOUNT--This table uploading the record is fine when i got the result from log file... But I want to see how many records to be inserted In backend.. so i want to write one program and see the result as per the log file result

Like
Table_name count
OMF_API_BONUS_ACCOUNT 50000
In backend Oracle


icon5.gif  Re: Sql Loader issue [message #279635 is a reply to message #279593] Fri, 09 November 2007 07:57 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Sorry Michel,


Now they requirement changed

In my sql loader Script like this
 LOAD DATA
INFILE 'BONUSACC.TXT'
append
INTO TABLE OMF_API_BONUS_ACCOUNT
(BNS_EMPLOYEE_NO                           POSITION(1:30),
BNS_ACCOUNT_NO                              POSITION(31:60),
BNS_START_DATE                             POSITION(61:68) "TO_DATE(:BNS_START_DATE,'yyyymmdd')",
BNS_AMOUNT_TILL_DATE                   POSITION(69:76) "TO_DATE(:BNS_AMOUNT_TILL_DATE,'yyyymmdd')",
BNS_INT_ACCRUED                              POSITION(77:95) ,
BNS_INT_ACCRUED_DT                        POSITION(96:103) "TO_DATE(:BNS_INT_ACCRUED_DT,'yyyymmdd')",
BNS_LAST_BONUS_RECEIVED_AMT     POSITION(104:122),
BNS_LAST_BONUS_RECEIVED_DT       POSITION(123:130) "TO_DATE(:BNS_LAST_BONUS_RECEIVED_DT,'yyyymmdd')",
BNS_INT_RATE                                     POSITION(131:149),
BNS_ID                          "BONACC_SEQNUM.NEXTVAL"
)


This table ''BONUSACC.TXT''contains total 50000 records After i run this script complied suceesfully shown in log file... Its fine. But In Backend oracle i want to see the how many records to be inserted in "OMF_API_BONUS_ACCOUNT" and also to show the total no of records in "'BONUSACC.TXT'

For eg:
I want to get this format in Oracle Backend

Count Desc Count of records
BONUSACC.TXT 50000
OMF_API_BONUS_ACCOUNT 34000(Suppose this records to be uploaded)


Please send the scripts if u have, ASAP
Re: Sql Loader issue [message #279649 is a reply to message #279635] Fri, 09 November 2007 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use SQL*Loader, use external table and load the target table using a package then you can do what you want before, during and after the load, for instance counting and displaying the rows.

Regards
Michel
icon5.gif  Re: Sql Loader issue [message #279658 is a reply to message #279593] Fri, 09 November 2007 09:14 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Thank u,

we have to created for 30 Tables using sqlloader upto now..At last moment I think its not possible to be changed the codings like to prepare the scripts using packages...

If u have to ready to prepare the script(using Package) as per my requirement else can u possible to solve tht requirement as i told so far... Please dont mistake me... If u help me this problem,very helpful for me
Re: Sql Loader issue [message #279666 is a reply to message #279658] Fri, 09 November 2007 09:34 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, I don't understand what you want from me (don't fully understand all you IM speak) and next I have to leave and be back only on Monday.
So no time to work for you.

Regards
Michel
Previous Topic: imp dump with object type ORA-02304
Next Topic: Variable length field exceeds maximum length.
Goto Forum:
  


Current Time: Tue Dec 06 04:16:37 CST 2016

Total time taken to generate the page: 0.12190 seconds