Help With Oracle User Defined Function

Good Evening Experts,

I'm beggining to write PL/SQL however im stuck at this function where im trying to get the count of a employee to appear in the output however currently i just have 1's showing. If someone can please tell me whats wrong please. I've given an visual representation on the attached PDF, i would appreciate your time on this. Thanks. Below is the code for the function.

CREATE OR REPLACE FUNCTION get_naming_convention(v_mem_no IN VARCHAR2)
	CURSOR c1(v_mem_no VARCHAR2) IS
	SELECT c.mem_no, c.lname, c.fname
	FROM careware.members c
	WHERE c.mem_no=v_mem_no;
	v_memberid		 VARCHAR2(10);
	v_first_name 	 	 VARCHAR2(30);
	v_last_name			 VARCHAR2(30);
	v_result				 		VARCHAR2(100);
	v_counter 				PLS_INTEGER:= 0;
  v_memberid:= substr(v_mem_no,1,instr(v_mem_no,'*')-1)||'01';
	FOR rec IN c1(v_mem_no) LOOP
		SELECT COUNT(rec.mem_no) INTO v_counter FROM dual;
		v_last_name:= REPLACE(rec.lname,' ','_');
	  v_first_name:= REPLACE(rec.fname,' ','_');
		v_result:= v_memberid||'_'||v_last_name||'_'||v_first_name||'_'||v_counter||'.pdf';
	 RETURN v_result;
END get_naming_convention;

Open in new window

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:
As you go through the loop record by record:
SELECT COUNT(rec.mem_no) INTO v_counter FROM dual;

Will always return 1.  For each iteration of the loop you have one rec.mem_no, so the count will be 1.

I'm not sure you can create a function that returns a running number the way you are wanting.

If no other Expert answers this before I can get to it, let me take a better look at the sample data you posted and I'll see what I can come up with.
I think you want this line:
SELECT COUNT(rec.mem_no) INTO v_counter FROM dual;

Open in new window

to be replaced with this:
SELECT COUNT(mem_no) INTO v_counter FROM careware.members WHERE mem_no=v_mem_no;

Open in new window

Otherwise your counter is always defaults to 1
slightwv (䄆 Netminder) Commented:
I don't think you need a function.  I believe you can get by with using ROW_NUMBER in the select.  Just move all the replaces in-line.

Give me a few minutes to set up a test case while you read up on ROW_NUMBER.  There is a ton of information out there on it.
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.

Dare626Author Commented:
Thank you Guys i appreciate it.

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
slightwv (䄆 Netminder) Commented:
Here's the test case I came up with.

Since you posted a picture, I didn't rekey all the data.  I used a couple of samples.

If it doesn't do what you want it to do, please add to the test case and explain why it no longer works.

drop table tab1 purge;

create table tab1(
	mem_no varchar2(20),
	lname varchar2(20),
	fname varchar2(20)

insert into tab1 values('125292*01','Arevalo','Maria');
insert into tab1 values('134294*01','Rivera','Maricela');
insert into tab1 values('134294*01','Rivera','Maricela');
insert into tab1 values('955254*01','Aguirre','Adam');
insert into tab1 values('955254*01','Aguirre','Adam');
insert into tab1 values('955254*01','Aguirre','Adam');

select mem_no,
	replace(replace(mem_no,'*') || '_' || lname|| '_' || fname,' ','_') || '_' || 
       		row_number() over(partition by mem_no order by mem_no) || '.pdf' naming_convention
from tab1

Open in new window

slightwv (䄆 Netminder) Commented:
>> requested that this question be closed by accepting Dare626's comment #a39507643 (0 points)

Any reason you chose to accept your own post as the solution?
Dare626Author Commented:
Thank you. I appreciate it.
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.