Home » SQL & PL/SQL » SQL & PL/SQL » Declaring a Variable within a Function (PL/SQL v10)
Declaring a Variable within a Function [message #395178] Tue, 31 March 2009 10:05 Go to next message
mcollins
Messages: 5
Registered: March 2009
Junior Member
Hello,

I was wondering if someone could tell me how declare a variable within a function. The variable only needs to be used within that function.

Thanks.
Re: Declaring a Variable within a Function [message #395184 is a reply to message #395178] Tue, 31 March 2009 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
Re: Declaring a Variable within a Function [message #395187 is a reply to message #395178] Tue, 31 March 2009 10:26 Go to previous messageGo to next message
mcollins
Messages: 5
Registered: March 2009
Junior Member
Thanks, although I did look through that doc before posting and I couldn't find an example to my question which answered it for me...

Below is some sample code I quickly wrote up, I'm not sure how to declare "Variable"

CREATE OR REPLACE FUNCTION SampleFunc
	RETURN VARCHAR
IS
	vSample VARCHAR(20);
BEGIN
	SELECT Column1 into Variable
	FROM SampleTable
	WHERE Column1 = '1';
	IF Variable = '1' THEN
		vSample := 'Hello'
	END IF;
	RETURN vSample;
EXCEPTION
   WHEN NO_DATA_FOUND THEN NULL;
END;

[Updated on: Tue, 31 March 2009 10:32]

Report message to a moderator

Re: Declaring a Variable within a Function [message #395191 is a reply to message #395178] Tue, 31 March 2009 10:31 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
The same way you declared vsample (though you might want to specify a length on that).
But since variable is an oracle reserved word I'd call it something else.
Re: Declaring a Variable within a Function [message #395210 is a reply to message #395178] Tue, 31 March 2009 12:54 Go to previous messageGo to next message
mcollins
Messages: 5
Registered: March 2009
Junior Member
I thought I tried that and it didn't work, maybe I did a typo or something... But it now works great...

I am now trying to use parameters when I try using the following code I get a the following error:

ERROR at line 9:
ORA-06550: line 9, column 11:
PLS-00306: wrong number or types of arguments in call to 'DAYFUNC'
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored

Below is some sample code - does anyone have any idea where I'm going wrong?

CREATE OR REPLACE FUNCTION SampleFunc (vIn in VARCHAR2)
	RETURN VARCHAR
IS
	vSample VARCHAR2;
        Varry VARCHAR2;
BEGIN
	SELECT Column1 into Varry
	FROM SampleTable
	WHERE Column1 = vIn;
	IF Varry = 'Bye' THEN
		vSample := 'Hello'
	END IF;
	RETURN vSample;
EXCEPTION
   WHEN NO_DATA_FOUND THEN vSample := 'Error';
   RETURN vSample;
END;
/

...

SET SERVEROUTPUT ON

DECLARE
	vIn VARCHAR2;
    vSample VARCHAR2;

BEGIN
	vIn := 'Bye';
	vSample := SampleFunc(vIn);
	DBMS_OUTPUT.PUT_LINE(vSample);
END;

Re: Declaring a Variable within a Function [message #395211 is a reply to message #395178] Tue, 31 March 2009 13:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>PLS-00306: wrong number or types of arguments in call to 'DAYFUNC'

Nothing you have posted mentions "DAYFUNC".

What are you really doing & not telling us?

Re: Declaring a Variable within a Function [message #395212 is a reply to message #395210] Tue, 31 March 2009 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
PLS-00306: wrong number or types of arguments in call to 'DAYFUNC'

What is 'DAYFUNC'?

Regards
Michel

Re: Declaring a Variable within a Function [message #395214 is a reply to message #395178] Tue, 31 March 2009 13:41 Go to previous message
mcollins
Messages: 5
Registered: March 2009
Junior Member
That should have referred to SampleFunc... I copied the wrong error.

I've only been posting a sample copy of the code - the actual code deals with days of the week, i.e. determining a week day from a weekend.

After a few hours of playing around with the code I managed to get it working... I'll post the sample code for other's benift, this has been tested and seems to work for me.

create or replace FUNCTION SampleFunc (vIn in VARCHAR2)
	RETURN VARCHAR
IS
	vSample VARCHAR(20);
  Varry VARCHAR(20);
BEGIN
	SELECT Column1 into Varry
	FROM SampleTable
	WHERE Column1 = vIn;
	IF Varry = 'Bye' THEN
		vSample := 'Hello';
	END IF;
	RETURN vSample;
EXCEPTION
   WHEN NO_DATA_FOUND THEN vSample := 'Error';
   RETURN vSample;
END;
/

...

SET SERVEROUTPUT ON

DECLARE
	vIn VARCHAR(20);
    vSample VARCHAR(20);

BEGIN
        vIn := 'Bye';
	vSample := SampleFunc(vIn);
	DBMS_OUTPUT.PUT_LINE(vSample);
END;


Below is the output:

Hello
PL/SQL procedure successfully completed.
Previous Topic: SUM up three rows into one and do it for the whole document. (merged)
Next Topic: DB Link Job error
Goto Forum:
  


Current Time: Sun Dec 11 02:25:24 CST 2016

Total time taken to generate the page: 0.13786 seconds