Convert LONG format to DATE or CHAR

[mzladyd]

The source table has tbldate formated as long and I want it to be date.
I've created another table and set the correct format and have tried to convert to date or char, but I continue to get error messages when I execute my Insert SQL.


TO_DATE(TBLDATE ) gives me this message.
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

TO_CHAR(TBLDATE ) gives me this
ORA-00932: inconsistent datatypes: expected CHAR got LONG

How do I get rid of the LONG format?

ORACLE 10g TOAD 9.1

[amitpatel66]

The source table has tbldate formated as long and I want it to be date.
I've created another table and set the correct format and have tried to convert to date or char, but I continue to get error messages when I execute my Insert SQL.


TO_DATE(TBLDATE ) gives me this message.
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

TO_CHAR(TBLDATE ) gives me this
ORA-00932: inconsistent datatypes: expected CHAR got LONG

How do I get rid of the LONG format?

ORACLE 10g TOAD 9.1

Oracle will not allow you the convert the data type LONG (2 GB or more) to CHAR (2000 bytes).

[amitpatel66]

You can convert LONG to CHAR using PLSQL block:

Check out below code that is used to select the LONG value in to VARCHAR2 variable:

[code=oracle]
declare
a varchar2(20);
begin
EXECUTE IMMEDIATE 'SELECT b FROM tt2' INTO a;
DBMS_OUTPUT.PUT _LINE(a);
end;
/

SQL> /
01-OCT-2007

PL/SQL procedure successfully completed.

[/code]