How to get a record using a where clause in Oracle?

Question

I want to get a record from a table whenever the procedure runs. The procedure will take an input of a number, say, employee_number and it will return a whole record - which will contain, say, employee_name, company, date of joining etc. I don't usually work with procedures. I am into analytical SQL.

create or replace procedure getdetails (search_strin table_name.column_1%type,
                      p_recordset out sys_refcursor) as 
begin 
  open p_recordset for
    select column_2, column_3
    from   table_name
    where  column_1= search_str;
end getdetails;

This should work, right? But, I'm getting the following error!

PLS-00306: wrong number or types of arguments in call to 'GET_EMP_RS'

Answer 1

Let's say your table is named EMPLOYEE. To do what you're asking about you'd do something like the following:

CREATE OR REPLACE FUNCTION GET_EMPLOYEE_RECORD(nEmployee_number IN NUMBER)
  RETURN EMPLOYEE%ROWTYPE
IS
  rowEmployee  EMPLOYEE%ROWTYPE;
BEGIN
  SELECT e.*
    INTO rowEmployee
    FROM EMPLOYEE e
    WHERE e.EMPLOYEE_NUMBER = nEmployee_number;

  RETURN rowEmployee;
END GET_EMPLOYEE_RECORD;

Edit

If you need to use procedures and not functions then you'll need to use an output parameter to return your data; thus, you can do something like the following:

CREATE OR REPLACE PROCEDURE GET_EMPLOYEE_RECORD
   (pin_Employee_number IN  NUMBER
    pout_Employee_row   OUT EMPLOYEE%ROWTYPE)
IS
BEGIN
  SELECT e.*
    INTO pout_Employee_row   
    FROM EMPLOYEE e
    WHERE e.EMPLOYEE_NUMBER = pin_Employee_number ;
END GET_EMPLOYEE_RECORD;

You would then invoke this procedure from code looking something like the following:

DECLARE
  nEmployee_number  NUMBER;
  rowEmployee       EMPLOYEE%ROWTYPE;
BEGIN
  nEmployee_number := 123;  -- or whatever value you like

  GET_EMPLOYEE_RECORD(pin_Employee_number => nEmployee_number,
                      pout_Employee_row   => rowEmployee);

  -- Now do something with the fields in rowEmployee...
END;

Answer 2

You should try to execute the function within an anonymous block , i guess you have tried to add the function on a select statement.

DECLARE l_cust_record x_remedy_oracle%ROWTYPE; BEGIN

l_cust_record := get_CUSTOMER('02393','Service');

END;