CODEWITHSUNDEEP
plsqloracle12c
Rajiv A
Rajiv A

Reputation: 943

Oracle 12c With Function in Package Spec not working

I have a cursor( involving multiple table joins) in the package spec. I wrote it because I wanted the return type for pipeline function.But when I compile it gives below error. 

[error] ora-00905 (4: 23): pl/sql: ora-00905: missing keyword
[error] pls-00103 (6: 15): pls-00103: encountered the symbol "BEGIN" when expecting one of the following:

   end function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor d
[error] pls-00103 (25: 14): pls-00103: encountered the symbol "S" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map.

Can I move the cursor in body? but I need the return type for the pipeline function

Below is sample package:

CREATE OR REPLACE PACKAGE pkg_test AS
   CURSOR cur_match IS
        WITH FUNCTION Fun(p_id varchar2) RETURN varchar2 IS
            v_desc VARCHAR2(100);
              BEGIN
                  v_desc := CASE p_id WHEN NULL THEN '123' WHEN 'A' THEN '345' WHEN 'B' THEN '678' 
                            END; 
                RETURN v_desc;   
              END;
             s AS (SELECT sysdate FROM dual)
      SELECT s.*,Fun('X') FROM s;
      TYPE tab_cur IS TABLE OF cur_match%ROWTYPE;

    FUNCTION get_data RETURN tab_cur PIPELINED;
  END;

Upvotes: 1

Views: 458

Answers (1)

TenG
TenG

Reputation: 4004

According to this:

https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1

There is no PLSQL support for WITH FUNCTION, i.e. cannot be used within a PLSQL block.

Perhaps what you could try is creating a VIEW with that query, and referencing the view in the PLSQL block.

Upvotes: 2

Related Questions