OraFAQ Forum: Forms » How to pass table name by reference to Function?

Home » Developer & Programmer » Forms » How to pass table name by reference to Function? (Oracle Form and SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to pass table name by reference to Function? [message #525345]

Sat, 01 October 2011 05:19

m3nhaq
Messages: 20
Registered: September 2011
Location: Pakistan

Junior Member

Hello,

I have to create a function which count the max id in the provided table.
For example: I have two table cutomer and book

and
create or replace function Row_Count(tab_nam) return varchar2 is

CONTR_NO varchar2;

begin
select NVL(MAX(t.contract_num), 0) + 1
INTO CONTR_NO
FROM tab_nam t;

return(CONTR_NO);
end Row_Count;

when I call
Function( customer) ;
or
Function( book) ;

It should tell the max number with addition of one.

Kindly help me. I am stuck badly. Please.......

Report message to a moderator

Re: How to pass table name by reference to Function? [message #525347 is a reply to message #525345]

Sat, 01 October 2011 05:45

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

Quote:

Begin
Select NVL(MAX(t.contract_num), 0) + 1
INTO CONTR_NO
FROM tab_nam t;
return(CONTR_NO);
End Row_Count;

Both table Customer & Book have same column name (contract_num).
Also describe your's tables structure.

Regards,
Irfan

Report message to a moderator

Re: How to pass table name by reference to Function? [message #525348 is a reply to message #525347]

Sat, 01 October 2011 06:05

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

Create or replace function Row_Count(tab_nam in varchar2) 
return number is
Contr_No Number;
begin
IF tab_nam='CUSTOMER' THEN

Select nvl(MAX(contract_num), 0) + 1 INTO Contr_No
FROM CUSTOMER;

else

Select NVL(MAX(contract_num), 0) + 1 INTO Contr_No
FROM BOOK;

END IF;

Return(Contr_No);
end Row_Count;

Then you can see result.

Select row_count('CUSTOMER') FROM DUAL;
Select row_count('BOOK') FROM DUAL;

Hope it'll help you.

Regards,
Irfan

Report message to a moderator

Re: How to pass table name by reference to Function? [message #525350 is a reply to message #525348]

Sat, 01 October 2011 07:08

m3nhaq
Messages: 20
Registered: September 2011
Location: Pakistan

Junior Member

Thanks Irfan for reply.
Yes, this soltion works. But, I thought there may be any method of passing table name which behave like table_object when use in

select * from table_object

as similar like data types varchar2 and number.

I was thinking of making a function in which I just enter a table name and it return max id +1 , assuming all tables have same fields id , name

But, its a good solution.

Regards
Muteen

Report message to a moderator

Re: How to pass table name by reference to Function? [message #525364 is a reply to message #525350]

Sat, 01 October 2011 10:47

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is a very bad design.
You MUST use a sequence and not this "max(contract_num)+1" that will not work in multi-user environment unless you lock the table in exclusive mode druing the whole that is serialize ALL transactions and so is not viable in real environment.

Regards
Michel

Report message to a moderator

Previous Topic:	To get list values starting with a particular alphabet
Next Topic:	how to filter data by list item

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 07 12:58:11 CDT 2024