CODEWITHSUNDEEP
oracle-databaseplsqloracle12c
Nagendra Nigade
Nagendra Nigade

Reputation: 876

PLSQL_V2_COMPATIBILITY Compatibility

I have migrated oracle database from 10g to 12c. From 12c oracle doesn't support PLSQL_V2_COMPATIBILITY parameter.

This parameter is used to : https://www.safaribooksonline.com/library/view/oracle-database-administration/1565925165/re157.html

One of the highlighting point in above url is :

The PL/SQL compiler will allow OUT parameters to be used in expression contexts in some cases, for example, in dot-qualified names on the right-hand side of assignment statements. This behaviour is restricted to fields of OUT parameters that are records, and OUT parameters referenced in the FROM list of a SELECT statement.

Due to this change many of our packages went into error which previously using functions with record as out parameter.

CREATE OR REPLACE Package SJMUSER.nagendra AS

    TYPE r_standard_url IS RECORD     (
                    v_loginurl        VARCHAR2(1000),
                    v_changepasswordurl    VARCHAR2(1000),
                    v_newloginurl        VARCHAR2(1000)
                    );

    TYPE t_standardurl_tbl IS TABLE OF r_standard_url
    INDEX BY BINARY_INTEGER;

    t_standardurlstype      t_standardurl_tbl;

        FUNCTION  Producestandardurls
                (
                tp_myuserid     IN  USERS.User_Id%TYPE,
                v_scrntime      IN  VARCHAR2,
                v_scrntoken     IN  VARCHAR2,
                v_wwikey        IN  VARCHAR2
                )
    RETURN   t_standardurlstype;


end nagendra;
/

Error :

PLS-00488 't_standardurlstype' must be type.

Whether this will required entire code changes ? like not using record as out parameter ? whether there is any solution for this ?

Upvotes: 3

Views: 238

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

For the example you've shown, you just need to change the return type (for this function; potentially OUT parameter types for others) from:

    RETURN   t_standardurlstype;

to either:

    RETURN   t_standardurlstype%type;

or more simply:

    RETURN   t_standardurl_tbl;

Your are currently trying to return the variable t_standardurlstype, which is an instance of the t_standardurl_tbl type. With the PLSQL_V2_COMPATIBILITY setting it's allowing the type to be inferred from the variable, but you can't do that any more. You may not actually be using the variable anywhere else - if not you can remove its declaration, if you use the second form above.

So yes, you will need to make code changes, but only to how return and OUT parameters are declared, in both the package specification and body. You can continue to use a record as an OUT parameter, it just needs to be declared correctly.

There are other implications of that setting though. See My Oracle Support note 47354.1 for details, but summarising what that says, you also need to watch out for these changed behaviours, as PL/SQL now:

  • correctly enforces the read-only semantics of IN parameters and does not let index table methods modify index tables passed in as IN parameters.

  • does not permit OUT parameters to be used in expression contexts.

  • will not allow OUT parameters in the FROM clause of a SELECT list, where their value is read.

  • will return an error on the illegal syntax return expression which should be return type (which is what the code in your question is hitting).

  • does not allow the passing of an IN argument into another procedure as an OUT.

There is no quick fix or magic wand for these; if your code relies on any of the old behaviour in any of those categories then it will have to be modified to obey the 'new' syntax rules.

Upvotes: 4

Related Questions