Function doesn't work

[dkwong]

Why can I not assign a SQL statement to a variable? Thanks!

CREATE OR REPLACE FUNCTION Lookup(id_no IN NUMBER, table_name IN VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
	 IF table_name = "vendor"
	 THEN
	 	 return_value := (SELECT vendor_number_tx FROM table_name WHERE vendor_id_no = id_no;)	
		 
	 ELSEIF table_name = "contract"
	 THEN
	 	 return_value := (SELECT contract_num_tx FROM table_name WHERE contract_id_no = id_no;)    

	 END IF;
	 
	 RETURN return_value

END Lookup;

 

[lewisp]

Try this

CREATE OR REPLACE FUNCTION Lookup(id_no IN NUMBER,table_name IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
IF table_name = 'vendor'
THEN
SELECT vendor_number_tx
INTO return_value
FROM vendor
WHERE vendor_id_no = id_no;
ELSEIF table_name = 'contract'
THEN
SELECT contract_num_tx
INTO return_value
FROM contract
WHERE contract_id_no = id_no;
END IF;

RETURN return_value
END;

 

[dkwong]

Thanks! It worked after I added the semi-colon to RETURN return_value and declared return_value in the declaration section.

 

[lewisp]

Yep. You spotted my deliberate mistakes!!