-1

I am trying to use the CAST function to convert the output of a column in a query, from VARCHAR2(255 CHAR) to VARCHAR2 of a variable length, the final number being returned by a sub-query, like this:

SELECT CAST(Title as VARCHAR2(SELECT MAX(LENGTH(Title)) FROM Books)) 
FROM Books;

Does anyone know if it is possible for me to format the output as a VARCHAR2 with the length returned by the sub query? For the example above I keep getting errors but there might be other ways.

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
calin.bule
  • 95
  • 1
  • 15
  • 2
    It does not make much sense casting to an unsized varchar2; if your column already is a varchar2, maybe you need no cast. What do you need to do? Please post relevant code you want to use this query in. – Aleksej May 11 '16 at 08:28
  • 2
    Why are you trying to do this? What's the problem that you're trying to solve by doing this? – Boneist May 11 '16 at 08:32
  • 1
    The interest of `varchar2` is that it varies in length, ain't it? So why want to change its type to another like that? Are you looking for a way to remove extra spaces at the end? You could use `trim(Title)` to do so. – J. Chomel May 11 '16 at 08:58

2 Answers2

0

What you are trying to do is not possible.

SQL by definition has a static type system, i.e. the number of result columns and their names and types are determined before the statement is run (and after the statement has been parsed).

Codo
  • 75,595
  • 17
  • 168
  • 206
0

You can do it with dynamic sql. A solution that works in SQL Developer is:

VARIABLE cur REFCURSOR;

DECLARE
  p_max_length INT;
BEGIN
  SELECT MAX( LENGTH( title ) )
  INTO   p_max_length
  FROM   Books;

  OPEN :cur FOR
  'SELECT CAST( Title AS VARCHAR2(' || p_max_length || ')) FROM Books';
END;
/

PRINT cur;

However, I am having trouble understanding why you would want to do this as the title is already a VARCHAR2(255) (so already has variable length) and reducing the maximum capacity is not going to reduce the size of the data.

You can see they are the same by doing:

SELECT DUMP(Title) FROM Books;

and comparing it to the query above using this instead:

OPEN :cur FOR
'SELECT DUMP(CAST( Title AS VARCHAR2(' || p_max_length || '))) FROM Books';

and you will see that the data returned is identical in both cases.

MT0
  • 143,790
  • 11
  • 59
  • 117