Stored procedure throwing errors while compiling: Oracle sql developer

There are multiple issues with your code as follows:

1. Your variable TOTAL is an input parameter and it can not be changed. You are trying to change its value using INTO TOTAL. which is not allowed in oracle.

2. THE second SELECT statement in your procedure needs an INTO clause or need to be part of some cursor. You can not write any SELECT query in the procedure just like that. What is expected out of that SELECT query?

3. LTRIM(RTRIM(REQ.T2COLUMN1)) can be replaced with the TRIM(REQ.T2COLUMN1)

4. OFFSET value must be a static integer.

I think you need something like this:

CREATE OR REPLACE PROCEDURE PROC_MYPROC (
    PAGENUMBER   IN           NUMBER,
    PAGESIZE     IN           NUMBER,
    SEARCHTERM   IN           VARCHAR2,
    TOTAL        IN OUT       NUMBER,
    OUT_RESULT   OUT          SYS_REFCURSOR
) AS
BEGIN
    IF PAGENUMBER = 1 THEN
        SELECT
            COUNT(DISTINCT MASTRACCNT.T1COLUMN1)
        INTO TOTAL
        FROM
            TABLE1 MASTRACCNT
            JOIN TABLE2 REQ ON LTRIM(RTRIM(REQ.T2COLUMN1)) = LTRIM(RTRIM(MASTRACCNT.T1COLUMN1))
        WHERE
            LOWER(RTRIM(LTRIM(MASTRACCNT.T1COLUMN1))
                  || ' - '
                  || RTRIM(LTRIM(REQ.T2COLUMN2))) LIKE '%'
                                                       || LOWER(SEARCHTERM)
                                                       || '%';

    END IF;

    OPEN OUT_RESULT FOR SELECT
                           MASTERACCOUNTID,
                           MASTERACCOUNTNAME,
                           TOTALROWS
                       FROM
                           (
                               SELECT
                                   MASTERACCOUNTID,
                                   MASTERACCOUNTNAME,
                                   TOTALROWS,
                                   ROW_NUMBER() OVER(
                                       ORDER BY
                                           MASTERACCOUNTNAME
                                   ) AS RN
                               FROM
                                   (
                                       SELECT DISTINCT
                                           RTRIM(LTRIM(MASTRACCNT.T1COLUMN1)) AS MASTERACCOUNTID,
                                           RTRIM(LTRIM(MASTRACCNT.T1COLUMN1))
                                           || ' - '
                                           || RTRIM(LTRIM(REQ.T2COLUMN2)) AS MASTERACCOUNTNAME,
                                           TOTAL   AS TOTALROWS
                                       FROM
                                           TABLE1 MASTRACCNT
                                           JOIN TABLE2 REQ ON LTRIM(RTRIM(REQ.T2COLUMN1)) = LTRIM(RTRIM(MASTRACCNT.T1COLUMN1))
                                       WHERE
                                           LOWER(RTRIM(LTRIM(MASTRACCNT.T1COLUMN1))
                                                 || ' - '
                                                 || RTRIM(LTRIM(REQ.T2COLUMN2))) LIKE '%'
                                                                                      || LOWER(SEARCHTERM)
                                                                                      || '%'
                                   )
                           )
                       WHERE
                           RN > PAGESIZE * ( PAGENUMBER - 1 )
                           AND RN <= PAGESIZE * ( PAGENUMBER - 1 ) + PAGESIZE
                       ORDER BY
                           MASTERACCOUNTNAME;

END PROC_MYPROC;
/