How to embed function in SQL statement without creating as separate object in oracle 12c

Now Oracle 12c has brought out a new feature to use function in SQL statement itself rather than implementing function as a separate database object.

This helps developers to gain insight on function usage with latest technique and simplifies database object maintenance.

Reduces context switching between SQL and PLSQL engine.

Faster the results and efficient coding.


Sample Snippet:

WITH

    FUNCTION get_party_name ( p_cust_account_id   IN NUMBER ) RETURN VARCHAR2 IS

        l_party   hz_parties.party_name%TYPE;

    BEGIN

        SELECT p.party_name

        INTO l_party

        FROM hz_cust_accounts ca, hz_parties p 

        WHERE ca.party_id = p.party_id      

        AND ca.cust_account_id = p_cust_account_id;

               

        RETURN l_party;

    EXCEPTION

        WHEN OTHERS THEN

            NULL;

    END;

SELECT

    trx_number,

    trx_date,

    get_party_name(bill_to_customer_id) party_name

FROM ra_customer_trx_all

WHERE rownum= 1;