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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.