Approach to replacing procedure code

I have a procedure I am trying to simplify with a more simplified approach. I am reading from a source, manipulating the data and eventually inserting into another table.
So I am going to ask my first question which is to build my Key which consists of the following
l_FINAL_tabKey := Build_Table_Key(l_FINAL_EffYr, l_FINAL_EffMo, l_FINAL_EffDa, l_FINAL_CVGID, l_FINAL_SeqNum, l_FINAL_polNbr, l_FINAL_Cono);

In my code now I call a function Buiuld_Table_Key and pass a bunch of variables and have them all concatenated and return the string of values concatenated.
So if Im trying to eliminate the loping through of the source data how would I go about building this key

Heres my Insert and what Im trying to do is generate the first value
Im just going to put the value in the Insert statement that I am trying to figure out first.....So the other columns are not there for now(One thing at a time)..I have included the Select statement because that will have the values I need to build the Key
function is in bold.
My question is how can I generate the l_SeqNum (5th param in bold) to increment for each record? Should I do something in the function to increment the number. It needs to be 6 charaters in length starting with '000001' and increment each time?

Insert Into TEMP_BASE_PCOV_POLICY
(POLICY_KEY,
Values (
util.Build_Table_Key(util.GET_DATE_PART('y', c.COV_EFFDT), util.GET_DATE_PART('m', c.COV_EFFDT), util.GET_DATE_PART('d', c.COV_EFFDT),
c.CVG_ID, LPAD(l_SeqNum + 1, 6, '0'), RPAD(c.POLNBR, 18, ' '), SUBSTR(c.CARRID, 3, 3))

Here is the source query I want to use in the Insert statement
Select
c.POLNBR,
c.COV_EFFDT, c.COV_EXPDT, c.CVG_ID, CVG_POL_SEQ,
c.CARRID, car.NAME, c.CARR_ISSUING_STREET, c.CARR_ISSUING_CITY, c.CARR_ISSUING_STATE_CD, c.CARR_ISSUING_ZIP, c.CARR_ISSUING_ADDR_TYPE, c.LCF_IND, c.TXN_CODE,
c.WRAP_UP_IND, '' POLICY_SOLE_PROPRIETOR_IND
Bulk Collect Into l_POLICY_TAB
from COVERAGE c
INNER JOIN CARR car ON c.CARRID = car.CARRID
WHERE COV_EXPDT > l_CONST_covExpDate  AND (STATUS != 'V') ORDER BY CARRID ASC, COV_EFFDT ASC;
jknj72Asked:
Who is Participating?
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:
Is the generated key something 'special' required by policy or just something you made up to get a key value?

For the additional later processing, do you get the key values based on data in the key itself?

In other words, something like:  Get all the 'key' values with an 'X' as the 6th character...

>>how can I generate the l_SeqNum

Use something like ROW_NUMBER which will be based on the result set
or
Use an Oracle Sequence:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6015.htm#SQLRF01314


>>njcrib_util.GET_DATE_PART

I'm guessing at what this does and do you really need a user-defined function to do it when to_char is already written?
0
jknj72Author Commented:
>>Is the generated key something 'special' required by policy or just something you made up to get a key value?

This is required for the policy. It is a unique key made up of values that will give the record a unique identifier and this is somthing I have to produce and have no say with

Get_Date_Part is a function I wrote that depending on what you pass in 'M', 'D' or 'Y' I return the Month(2 chars), Day(2 chars) or Year(4 chars)
0
slightwv (䄆 Netminder) Commented:
>> It is a unique key made up of values that will give the record a unique identifier and this is somthing I have to produce and have no say with

You have to love requirements that make no sense...  You can get a unique identified with a single SYS_GUID() call.

>>I return the Month(2 chars), Day(2 chars) or Year(4 chars)

Again, why write a function when one already exists?  Granted it likely takes milliseonds extra to run but you are looking to shave time.  I would shave it any place I could.

select to_char(sysdate-16,'DD') from dual;
should do the same thing as
select util.GET_DATE_PART('d',sysdate-16) from dual;

And if all util.Build_Table_Key does is concatenate the values, remove it as well.  Again, likely milliseconds, but it is something and less code you have to maintain!


Anyway:
Take a look at using ROW_NUMBER() to get the 'sequence' to get you out of the loop and loop-counter.
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
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:
ok sounds good...Thanks
0
jknj72Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for jknj72's comment #a40837033

for the following reason:

Thanks
0
jknj72Author Commented:
I meant to accept this question and give points...Sorry
0
jknj72Author Commented:
hope this works!!
0
slightwv (䄆 Netminder) Commented:
>>I meant to accept this question and give points

No problem.  You can just 'accept' as normal.  It will override the other accept.
0
jknj72Author Commented:
yes I think I did it right ...

I have gotten through the Insert and most of its values but I am starting the address logic now so Im not sure how Im gonna do it but I will show you what I have in a new question shortly...
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.

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.