0

I have one sql statement and a list of values.

Statement:

UPDATE SALCSMAIN
SET CSNO = :p0
   ,CSDATE = :p1
   ,SCHEME_DOCNO =:p2
   ,GRSAMT = :p3
   ,DISCOUNT = :p4
   ,NETAMT = :p5
   ,SITE_COSTAMT = :p6
   ,REMARKS = :p7
   ,TIME = :p8

Values

:p0 = 1883                   [Type: Int32 (0)]
:p1 = 9/30/2017 12:00:00 AM  [Type: DateTime (0)]
:p2 = 'CHOGOS0026-17-18'     [Type: String (0)]
:p3 = 5298                   [Type: Decimal (0)]
:p4 = 1900                   [Type: Decimal (0)]
:p5 = 3398                   [Type: Decimal (0)]
:p6 = NULL                   [Type: Decimal (0)]
:p7 = 'naveen'               [Type: String (0)]
:p8 = 10/6/2017 3:03:38 PM   [Type: DateTime (0)]

And I want the result to be like this:

UPDATE SALCSMAIN
SET CSNO = 1883
   ,CSDATE = '30-SEP-2017'
   ,SCHEME_DOCNO = 'CHOGOS0026-17-18'
   ,GRSAMT = 5298
   ,DISCOUNT = 1900
   ,NETAMT = 3398
   ,SITE_COSTAMT = NULL
   ,REMARKS = 'naveen'
   ,TIME = '06-OCT-2017'
   ,LAST_ACCESS_TIME = '06-OCT-2017'

How to automate this task?

CurseStacker
  • 1,079
  • 8
  • 19
Pratim
  • 11
  • 5
  • Why the `c#` and `python` tags? What does this have to do with automation? – Panagiotis Kanavos Sep 05 '18 at 07:51
  • Looks like the real question is how to call parameterized queries in your prefered language. Therea are a *lot* of duplicate questions that show how to do that for various databases and languages. You don't mention a specific language and database though so it's not possible to give a specific answer. It's *not* SQL Server and C# - parameters would have `@` as a prefix in that case. – Panagiotis Kanavos Sep 05 '18 at 07:54
  • I have edited the tags. I have to run the statement on Oracle Toad. – Pratim Sep 05 '18 at 07:57
  • In which case the question is `How do I run a PL/SQL query with parameters`. There are a lot of duplicates – Panagiotis Kanavos Sep 05 '18 at 07:58
  • Possible Duplicate [Variables in TOAD scripts](https://stackoverflow.com/questions/1947280/variables-in-toad-scripts) – Panagiotis Kanavos Sep 05 '18 at 07:59
  • As an aside, are you really wanting to update all rows in your salcsmain table to have the same values? Also, if your csdate and last_access_time columns are DATE (or TIMESTAMP) datatypes, you should be converting your string into a date (or timestamp) via to_date (or to_timestamp), e.g. `to_date('06-OCT-2017', 'dd-MON-yyyy', 'nls_date_language=english')`. Note that I had to include the optional 3rd parameter to ensure the conversion was nls parameter independen (if your session's nls_date_language was set to French, but you're passing in the English month, the conversion would fail otherwise) – Boneist Sep 05 '18 at 09:25
  • I have more than 70 values, and replacing the ":p" values with the above statement manually is time-consuming. That's why I am wondering if there is any script available to automate the task? – Pratim Sep 11 '18 at 13:29

0 Answers0