question

PriyankaKaushik avatar image
PriyankaKaushik asked

Reading text from a file , evaluting it and updating table

I am stuck on a step in below script. It is reading from a text file , picking up a value, evaluating it and based ont the value returned , updating oracle table ( using SQLplus ) with one of the value returned. All thing apart from BODY of IF and ELSE is working. So issues are either with connection or with datapassing Please help me dim fs, txt, line, yesno , cust_id set fs = CreateObject("Scripting.FileSystemObject") set txt = fs.OpenTextFile("E:\batchfiletest\Eapp3\scotia1.txt", 1, false) ' loop through all the lines do while not txt.AtEndOfStream line = txt.readLine ' read the character and store it in a variable yesno = Mid(line, 127, 1) cust_id = Mid(line, 1,20) ' execute the correct query if yesno = "Y" then set WshShell = CreateObject("WScript.Shell") set oEnv=WshShell.Environment("Process") cmdString = "E:\oracle\product\10.2.0\db_1\BIN\sqlplusw.exe -S sysman/csaadmin@convcsd UPDATE csa_sli_all.T_CONV_quote set HOLD_CODE = 'CAQ' where quote_id = cust_id ;" ELSE set WshShell = CreateObject("WScript.Shell") set oEnv=WshShell.Environment("Process") cmdString = "E:\oracle\product\10.2.0\db_1\BIN\sqlplusw.exe -S sysman/csaadmin@convcsd UPDATE csa_sli_all.T_CONV_quote set HOLD_CODE = 'PVQ' where quote_id = cust_id ;" end if loop
oraclevbscript
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
I do not know how this script is going to work. But I can see you assigning value to the cmdString, but no execution? I remember one of the ways to do this, is/was to use ADODB.Connection, connection string etc. After opening connection to Oracle, execute the specific command, close the connection and so on. I guess you can google to find it somewhere.
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Use SSIS to open and read the file, or [`bcp`][1] to get the data into a table, then you can use standard T-SQL to update depending on the values. [1]: http://msdn.microsoft.com/en-us/library/ms162802(v=sql.90).aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

PriyankaKaushik avatar image PriyankaKaushik commented ·
We dont have Microsoft SQL server 2005, we are using Oracle SQL*Plus
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
You tagged your question 'sql-server-2005', so I answered in context. Sorry. I'll retag 'oracle'
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.