Like the title says, Im writing the application processes in SQL Developer in a Procedure but I think it will ultimately end up in a package(which I haven't dealt with yet) to be run everyday.
The end result is to create a Table to be extracted to a text file and picked up by a process from a 3rd party vendor.
Soooo I have to mimic a VB.NET app (actually is a Windows Service) in SQL Developer and I want to separate the logic into Functions/Procedures to avoid the redundancy that's going on in the app. There are a lot of function calls in the code so a lot of connections to an Oracle db are being open and closed as the process runs....
I think its only fair that I create a new question for each piece of logic so I can give the points for each one. I want to write it efficiently so I will explain the code below and will ask my first question and when I get the answer will ask the next question and so on...And I wont explain everything after this question. I will just go to the next piece of logic and explain what I have to try and accomplish...
--First function being called
Public Function BuildPCOVFile(ByVal callDuration As Integer, ByRef threadId As Integer)
Dim ds As New DataSet
Dim da As New DataLayer.DataAccess
Dim goodBUild As String = " "
--Writes to Log file (C:\Log\Log.txt)
LogIt.LogWrite("Started Policy Coverage Master Table Build: " & Now, "Info")
--First Calls function below GetOracleDataForPCOV and it assigns SQL below to a Dataset 'ds' that returns 1.5 million rows :
--The BringBackGoodDate function brings back a date(ADD_MONTHS(Current_Date,-72)) for the last 72 months and its passed to the function; which = '08-MAY-09'
--SQL = "Select * from COVERAGE WHERE COV_EXPDT > '08-MAY-09' AND (STATUS != 'V') ORDER BY CARRID ASC, COV_EFFDT ASC"
ds = da.GetOracleDataForPCOV(BringBackGoodDate)
--Calls this method and passes in the dataset 'ds' returned from function above with all the rows found(1.5m)
--This function is large so I wanna go through the steps in order.
--First thing to do is create a table 'NAMEDKEYS'(I used a TYPE in SQL) and eventually assign the values to the fields with additional logic I will go over...
"POL-RECKEY" as VARCHAR2
"POL_NBR" as VARCHAR2
"FILENUMBER" as VARCHAR2
"COVEFFDATE" as VARCHAR2
"COVEXPDATE" as VARCHAR2
"LCF_IND" as VARCHAR2
"CARRID" as VARCHAR2
--So lets make this my first question. How would I architect what I have shown so far
--I actually have done this already but I want to see what responses I get and go from there.