CREATE OR REPLACE PROCEDURE sp_get_studentdetails_from_ad (
        p_STUDENT_ADNAME IN varchar2,
	l_CURSOR OUT sys_refcursor
)
IS
BEGIN
    -- going to declare variables that are to be returned in SSRS but not
    -- mentioned in the above OUT variables:
    DECLARE
      p_STUDENT_REF number(8);
      p_STUDENT_DETAILS varchar2(255);
    
    -- putting a begin within otherwise I get warning errors (with an end!)
    BEGIN

    -- initial query to get me the student ID based on the inputted parameter
    SELECT
      student_accounts.student_id
    INTO
      p_STUDENT_REF
    FROM
      student_accounts
    WHERE
      student_accounts.student_username = p_STUDENT_ADNAME;

    -- opening cursor for the select query that I want to display in SSRS
    OPEN l_CURSOR FOR
      SELECT INITCAP(b.p_title)
       || ' '
       || INITCAP(b.p_forenames)
       || ' '
       || UPPER(b.p_surname)
       || ' ('
       || INITCAP(To_char(b.p_dob, 'DD-MON-YYYY'))
       || ')' "p_STUDENT_DETAILS",
       NVL(a.student_id, b.person_id) "p_STUDENT_REF"
      FROM
        student_accounts a
      INNER JOIN student_details b ON a.student_id = b.person_id
      WHERE a.student_id = p_STUDENT_REF;

    -- ending that second begin I had after my variable declaration
    END;

    EXCEPTION
        WHEN OTHERS THEN
             RAISE_APPLICATION_ERROR (-20001, p_STUDENT_ADNAME || ':$:' || SQLERRM, TRUE);

END sp_get_studentdetails_from_ad;

1.  CREATE OR REPLACE PROCEDURE sp_get_studentdetails_from_ad ( 
2.          p_STUDENT_ADNAME IN varchar2, 
3.      l_CURSOR OUT sys_refcursor 
4.  ) 
5.  IS 
6.  BEGIN 
7.      -- going to declare variables that are to be returned in SSRS but not 
8.      -- mentioned in the above OUT variables: 
9.      DECLARE 
10.        p_STUDENT_REF number(8); 
11.        p_STUDENT_DETAILS varchar2(255); 
12.   
13.      -- putting a begin within otherwise I get warning errors (with an end!) 
14.      BEGIN 
15.   
16.      -- initial query to get me the student ID based on the inputted parameter 
17.      SELECT 
18.        student_accounts.student_id 
19.      INTO 
20.        p_STUDENT_REF 
21.      FROM 
22.        student_accounts 
23.      WHERE 
24.        student_accounts.student_username = p_STUDENT_ADNAME; 
25.   
26.      -- opening cursor for the select query that I want to display in SSRS 
27.      OPEN l_CURSOR FOR 
28.        SELECT INITCAP(b.p_title) 
29.         || ' ' 
30.         || INITCAP(b.p_forenames) 
31.         || ' ' 
32.         || UPPER(b.p_surname) 
33.         || ' (' 
34.         || INITCAP(To_char(b.p_dob, 'DD-MON-YYYY')) 
35.         || ')' "p_STUDENT_DETAILS", 
36.         NVL(a.student_id, b.person_id) "p_STUDENT_REF" 
37.        FROM 
38.          student_accounts a 
39.        INNER JOIN student_details b ON a.student_id = b.person_id 
40.        WHERE a.student_id = p_STUDENT_REF; 
41.   
42.      -- ending that second begin I had after my variable declaration 
43.      END; 
44.   
45.      EXCEPTION 
46.          WHEN OTHERS THEN 
47.               RAISE_APPLICATION_ERROR (-20001, p_STUDENT_ADNAME || ':$:' || SQLERRM, true); 
48.   
49.  END sp_get_studentdetails_from_ad; 

CREATE PUBLIC SYNONYM sp_get_studentdetails_from_ad FOR base_table_owner.sp_get_studentdetails_from_ad;
GRANT EXECUTE ON sp_get_studentdetails_from_ad TO my_ssrs_user;

1.  CREATE PUBLIC SYNONYM sp_get_studentdetails_from_ad FOR base_table_owner.sp_get_studentdetails_from_ad; 
2.  GRANT EXECUTE ON sp_get_studentdetails_from_ad TO my_ssrs_user;