Help With Oracle User Defined Function

Posted on 2013-09-19
Medium Priority
Last Modified: 2013-09-29
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

Question by:Dare626
  • 4
  • 2
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39507635
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.
LVL 25

Assisted Solution

chaau earned 400 total points
ID: 39507639
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
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39507640
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.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Accepted Solution

Dare626 earned 0 total points
ID: 39507643
Thank you Guys i appreciate it.
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1200 total points
ID: 39507680
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

LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39519062
>> 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?

Author Closing Comment

ID: 39530976
Thank you. I appreciate it.

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question