CODEWITHSUNDEEP
sqloracle-database
am2
am2

Reputation: 371

oracle owner type in package: How to use it in SQL?

Maybe it is a boring question for you, but I did not find an answer until now.

I defined a Vector Type Record in package. How can I use this in SQL?

CREATE OR REPLACE PACKAGE PKG_MATH AS 
TYPE VECTOR IS RECORD (
    X NUMBER,
    Y NUMBER,
    Z NUMBER
); 
FUNCTION ARC(A VECTOR, B VECTOR) RETURN NUMBER;
END;

How can I use this in SQL

SELECT PKG_MATH.ARC(PKG_MATH.VECTOR(1,1,0),PKG_MATH.VECTOR(1,-1,0)) FROM DUAL;

If I do this I get

ORA-06553 PLS-222: no function with name 'VECTOR' exist

Upvotes: 0

Views: 163

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21085

You must define the TYPE in SQL using CREATE TYPE 

CREATE TYPE VECTOR IS OBJECT (
    X NUMBER,
    Y NUMBER,
    Z NUMBER
); 
/

Than you can use it in the PACKAGEd function:

CREATE OR REPLACE PACKAGE PKG_MATH AS 
 FUNCTION ARC (A VECTOR, B VECTOR) RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_MATH AS 
 FUNCTION ARC (A VECTOR, B VECTOR) RETURN NUMBER AS
BEGIN
  return (-1);
END; 
END;
/


select PKG_MATH.ARC(VECTOR(1,1,1), VECTOR(0,90,0)) from dual

PKG_MATH.ARC(VECTOR(1,1,1),VECTOR(0,90,0))
------------------------------------------
                                        -1

Upvotes: 1

Related Questions