CODEWITHSUNDEEP
oracle-databaseplsql
user2018441
user2018441

Reputation: 63

Boolean attribute in record type, and use the record type in pipelined function

 CREATE or replace PACKAGE pipelined_pkg AS

     TYPE emp_rt IS RECORD
     ( empno    NUMBER(4)
     , ename    VARCHAR2(10)
     , job      VARCHAR2(9)
     , mgr      NUMBER(4)
     , hiredate DATE
     , sal      NUMBER(7,2)
     , comm     NUMBER(7,2)
     , deptno   number(2)
     , presntStatus   Boolean default false
     );

     TYPE emp_ntt IS TABLE OF pipelined_pkg.emp_rt;

     function pipelined_emp
        RETURN pipelined_pkg.emp_ntt PIPELINED;

  end pipelined_pkg;
  /

When I try to compile the above code I am getting

[ Error(17,15): PLS-00630: pipelined functions must have a supported collection return type ]

If I remove the Boolean attribute, it is compling fine.

Is it not possible to have a Boolean attribute in record type, and use the record type in pipelined function?

Upvotes: 0

Views: 908

Answers (2)

Deepak Vn
Deepak Vn

Reputation: 122

Because SQL has no data type equivalent to BOOLEAN, you cannot:

  • Assign a BOOLEAN value to a database table column

  • Select or fetch the value of a database table column into a BOOLEAN
    variable

  • Use a BOOLEAN value in a SQL statement, SQL function, or PL/SQL
    function invoked from a SQL statement

You cannot pass a BOOLEAN value to the DBMS_OUTPUT.PUT or DBMS_OUTPUT.PUTLINE subprogram. To print a BOOLEAN value, use an IF or CASE statement to translate it to a character value (for information about these statements, see "Conditional Selection Statements").

Refer http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/datatypes.htm#LNPLS348 for further info.

Upvotes: 1

asa
asa

Reputation: 4040

As far as i know, there are no BOOLEAN type in oracle when used to define a record/column. For those specific field, we usually use a NUMBER(1) or CHAR(1).

Upvotes: 0

Related Questions