CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
WorksOnMyLocal
WorksOnMyLocal

Reputation: 1689

Stored procedure throwing errors while compiling: Oracle sql developer

I have been working with SQL Server for many years now. But there was a requirement in my current project where I had to convert the procedure written in SQL Server to Oracle (in SQL Developer tool).

But there seems to be a lot of minutes syntax changes which I am unable figure out with the error messages thrown.

Here is my stored procedure:

CREATE OR REPLACE PROCEDURE PROC_MyPROC
(
  PAGENUMBER IN NUMBER 
, PAGESIZE IN NUMBER 
, SEARCHTERM IN VARCHAR2 
, TOTAL IN NUMBER 
) 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;

  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)||'%'   
  ORDER BY MasterAccountName    
  OFFSET PAGESIZE * (PAGENUMBER - 1) ROWS   
  FETCH NEXT PAGESIZE ROWS ONLY; 

END PROC_MyPROC;

On compiling this i get these errors, not sure what they mean. Can someone help me what is wrong with my procedure?

enter image description here

Upvotes: 0

Views: 538

Answers (1)

Popeye
Popeye

Reputation: 35930

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;
/

Upvotes: 3

Related Questions