OraFAQ Forum: SQL & PL/SQL » HOW TO BINBIND VARIABLE IN FUNCTION

Home » SQL & PL/SQL » SQL & PL/SQL » HOW TO BINBIND VARIABLE IN FUNCTION

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

HOW TO BINBIND VARIABLE IN FUNCTION [message #10100]

Mon, 05 January 2004 19:13

satish
Messages: 112
Registered: September 2000

Senior Member

Hi Friends,

I have a small help from you.

My Requirement:
---------------
A function, accepts table name as input and gives No of records that table as output.

Function Written:

CREATE OR REPLACE FUNCTION NO_OF_REC(MTABLE_NAME VARCHAR2)
RETURN NUMBER
IS
NO_OF_REC NUMBER ;

BEGIN

SELECT COUNT(ROWID) INTO NO_OF_REC FROM :MTABLE_NAME ;

IF NO_OF_REC IS NULL THEN
NO_OF_REC:= 0;
END IF ;

RETURN(NO_OF_REC);
END NO_OF_REC;

Error getting:
PLS-00049 Bad bind variable 'MTABLE_NAME'

Solution required:

How to bind the variable?

Please help me.

Report message to a moderator

Re: HOW TO BINBIND VARIABLE IN FUNCTION [message #10101 is a reply to message #10100]

Mon, 05 January 2004 19:45

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

scott@ORA92> CREATE OR REPLACE FUNCTION no_of_rec
  2    (mtable_name VARCHAR2)
  3    RETURN       NUMBER
  4  IS
  5    no_of_rec    NUMBER := 0;
  6  BEGIN
  7    EXECUTE IMMEDIATE 'SELECT COUNT (*) FROM ' || UPPER (mtable_name) INTO no_of_rec;
  8    RETURN no_of_rec;
  9  END no_of_rec;
 10  /

Function created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> SELECT no_of_rec ('emp') FROM DUAL
  2  /

NO_OF_REC('EMP')
----------------
              14

scott@ORA92>

Report message to a moderator

Re: HOW TO BINBIND VARIABLE IN FUNCTION [message #10103 is a reply to message #10101]

Mon, 05 January 2004 19:53

satish
Messages: 112
Registered: September 2000

Senior Member

Thanks Barbara Boehmer
It worked out properly.

Report message to a moderator

Previous Topic:	To get the value returned from a proc/Function with in a shell script
Next Topic:	Recompilation of package

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 01:10:30 CDT 2024