Re-writing an application in SQL Developer

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 = " "
            Try
                --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)
                BuildPCOVRecords(ds)
                 --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.
                     
             Return goodBUild
        End Function


THANKS!!!!!!!!!!!!!!
jknj72Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
I answered how I would do it in your other question:
http://www.experts-exchange.com/Database/Oracle/Q_28668726.html#a40762496
0
 
slightwv (䄆 Netminder) Commented:
>>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...

This will likely cause a lot of confusion for the Experts.  Be prepared...

That said:  I don't understand what you are wanting us to do.

I'm not seeing the need for a temp table.

If you want the contents from the table to a flat file, a simple sqlplus script will work.

If you want it on the database server and called from a stored procedure then UTL_FILE.

All this is covered in your previous question:
http://www.experts-exchange.com/Database/Oracle/Q_28667860.html

What are you looking for here?
0
 
Mark GeerlingsDatabase AdministratorCommented:
I agree with slightwv: I don't see a need to create a new table each time this procedure is used.  That looks like a SQL Server-style approach to an Oracle problem.  In Oracle, if you do need a temporary table, create it just once, as a "global temporary table", then re-use it when you need it.

But, in Oracle, to simply retrieve records (any number of them) from a table (or a view, or multiple tables) and write them to a text file, you don't have to use a temporary table.  In some cases, if a single query to do this becomes very complex to write or slow to execute, a temporary table may be helpful.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jknj72Author Commented:
Well I didn't mean Im not going to explain everything I meant that Im going to just explain the next piece of logic I am looking for. All I want to do is write this the best way I can. The temp tables are being used to hold the values from the first query(source data) and I am looping through that set of data and will be querying other tables off of those records to ultimately come up with a new row of data for every loop, or write a record that doesn't meet the logic in place to a Error table with a description. I can show you what Ive come up with so far if that helps but my first question is really how to architect the main queries records.
My second question is I have to check for an InsuredName, passing  values from source query and if no records come back then Bypass the row and go to the next row...If it has record I do nothing...and so on

Is this a little more clear? I think you know by now I don't always make myself clear and I apologize...

Pseudo code for what Im trying to accomplish....Not complete

Run Select statement
   -Loop thru records

      -CheckInsuredName passing 3 columns from source query
          if no value then
               BYPass
          endif

      -Check if Date is present in field from source query
          if value true set variable from source
          else
             ByPass
          endif

       -Check for Address and build address record
            --a lot of logic here so that's why I thought we should do this in steps?

       -Build key out of values in source

        -Check for ASCII values
       .............and so on
LOOP
0
 
jknj72Author Commented:
Mark - Im all ears !!
Slight - What I really want to focus on is getting the records I need, in the format I need them, into a table and then I will worry about exporting it to a flat file...If I can get the best way to accomplish what I wrote above I will be happy
0
 
Mark GeerlingsDatabase AdministratorCommented:
It looks to me like one PL\SQL procedure can do all of what you described in your pseudo-code, with no temporary table needed.

Start by writing a PL\SQL procedure with just your first cursor, and test that,  Then add in each additional step that you need, and test it at each level.
0
 
jknj72Author Commented:
Ok Slight I will use your logic from my last question

I will give you points and I will just ask questions as they come up..
0
 
slightwv (䄆 Netminder) Commented:
If there isn't anything new in this question I would suggest you delete it.

Otherwise it is pretty much a duplicate question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.