cross apply giving missing keyword error

Question

Below query is giving missing keyword error..

select * 
  from its_account aac 
 CROSS APPLY its.fnGetAccountIdentifier(aac.account_key) ;

here is my function:

create or replace FUNCTION fnGetAccountIdentifier
(
  v_AccountKey IN NUMBER
)
RETURN fnGetAccountIdentifier_pkg.tt_fnGetAccountIdentifier_type PIPELINED
AS
   v_temp SYS_REFCURSOR;
   v_temp_1 TT_FNGETACCOUNTIDENTIFIER%ROWTYPE;

BEGIN
   OPEN v_temp FOR
      SELECT * 
        FROM tt_fnGetAccountIdentifier;

   LOOP
      FETCH v_temp INTO v_temp_1;
      EXIT WHEN v_temp%NOTFOUND;
      PIPE ROW ( v_temp_1 );
   END LOOP;
END;

I do not know where I am doing wrong. I am really new to this.

Answer 1

1. The APPLY SQL syntax, whether it CROSS APPLY or OUTER APPLY was introduced in

   Oracle 12c version. Prior versions of Oracle RDBMS do not support APPLY SQL syntax.

2. When selecting from table function you need to use TABLE() function:

Having said that, you could rewrite your query as follows:

For 12c using cross apply.

 select * 
   from its_account aac 
  cross apply TABLE(fnGetAccountIdentifier(aac.account_key)) ;

For 9i and up using cross join.

 select * 
   from its_account aac 
  cross join TABLE(fnGetAccountIdentifier(aac.account_key)) ;

In your case there is no difference - you will get the same result using cross join as you would using cross apply.