Oracle SQL Concat troubled

Question

I have this:

CREATE or replace TYPE type_movie AS object(
idmovie    numeric(6),
title        varchar(50),
genere       varchar(20),
year        numeric(4),
refprojec ref type_projec, 
MEMBER FUNCTION getProjec1 return numeric
);

And

CREATE or replace TYPE type_projec AS object(
idmovie numeric(6),
date        date,
hour        char(5),
refmovie ref type_movie,
MEMBER FUNCTION getData return varchar
);


create table tmovie of type_movie;
create table tprojec of type_projec;

Then, the problem:

create or replace TYPE BODY type_projec AS
  MEMBER FUNCTION getData return varchar is
  all varchar;
  BEGIN
    SELECT concat(to_char(t.idmovie) || t.title || t.genere || to_char(t.year)) INTO all
    FROM tmovie t
    WHERE t.refmovie.idmovie=self.idmovie;

    return all;
  END;
END;

I want to concatenate all the selected items to return a unique varchar of all of them.

It seems that there is an error in the line SELECT concat(to_char(t.idmovie) || t.title || t.genere || to_char(t.year)) INTO all

But supposedly all seems correct?

Any possible answers?

Answer 1

"But supposedly all seems correct?"

Up to a point.

ALL is an Oracle reserved word, so it's a poor choice of variable name. Use something else, even l_all, instead.

CONCAT() takes two arguments; you supply only one. As your are using the || concatenation operator you don't need to call CONCAT(). This will work

SELECT to_char(t.idmovie) || t.title || t.genere || to_char(t.year) INTO l_all

Also, the syntax for referencing the type is wrong. This will compile ...

 WHERE t.idmovie=self.idmovie;

... but it may not be what you want to implement.