Board index Oracle

Oracle7.2.3-Datatype problem

Oracle7.2.3-Datatype problem

Post by Andreas Muelle » Tue, 29 Jun 1999 04:00:00



Hello!!!

I need to export a table with a column of type LONG to a comma-separated
textfile. I use SQL*PLUS with spooling and the following SQL-query:

   SELECT rtrim(name) ||
          ','         ||
          rtrim(descr)||
          ','         ||
          rtrim(text) ||
          ','         ||
          code        ||  --< this is the LONG-field.
          ';'

I know that LONG-fields cannot be used with operators or functions, but
the output should look like this:

   name,descr,text,code;

I already tried copying the LONG-values to a new table where the
respective column is defined as VARCHAR2(2000), however this seems to be
impossible, too.

Since I only have here Oracle7.2.3 on HP-UX10.20 with SQL*PLUS and no
further tools, I need to know if there's any possibility to convert or
use the LONG-type-field so I can write it in the desired way.

Has anyone an idea??? Any hints are greatly appreciated!!!

Thanks,
Andreas Mueller
SCR Princeton

 
 
 
Top

Oracle7.2.3-Datatype problem

Post by Bhooshan Prabh » Thu, 01 Jul 1999 04:00:00


Option A

    1. U can define a new table with the LONG column now defined as
VARCHAR2(2000)
    2. U can write a simple stored procedure that reads records from old
table and insert into the new
        table
            for cur in (select * from old_table)
            loop
                insert into new_table values (rtrim(cur.name),
rtrim(cur.descr) ....;
            end loop;
    3. now do the SELECT

Option B
    1. package utl_file allows u to write to a flat file. u have to set
utl_file_dir in your init(sid).ora to a
        directory name and the flat file gets created there.
    2. just write a procedure which opens a file in write mode,reads records
from file and just write to
        the file ((problme is there is a limit of 1000+ characters per
line). read utlfile.sql under
        $ORACLE_HOME/rdbms/admin

bye
bhooshan


> Hello!!!

> I need to export a table with a column of type LONG to a comma-separated
> textfile. I use SQL*PLUS with spooling and the following SQL-query:

>    SELECT rtrim(name) ||
>           ','         ||
>           rtrim(descr)||
>           ','         ||
>           rtrim(text) ||
>           ','         ||
>           code        ||  --< this is the LONG-field.
>           ';'

> I know that LONG-fields cannot be used with operators or functions, but
> the output should look like this:

>    name,descr,text,code;

> I already tried copying the LONG-values to a new table where the
> respective column is defined as VARCHAR2(2000), however this seems to be
> impossible, too.

> Since I only have here Oracle7.2.3 on HP-UX10.20 with SQL*PLUS and no
> further tools, I need to know if there's any possibility to convert or
> use the LONG-type-field so I can write it in the desired way.

> Has anyone an idea??? Any hints are greatly appreciated!!!

> Thanks,
> Andreas Mueller
> SCR Princeton


 
 
 
Top

1. Q: DATE datatype in Oracle7

I'm confused about the DATE datatype in Oracle7:

========================================
The Oracle7 Server Concepts Manual says:
========================================

DATE Datatype
-------------

The DATE datatype stores point-in-time values (dates and times) in a table. The
DATE datatype stores the year (including the century), the month, the day, the
hours, the minutes, and the seconds (after midnight). Oracle can store dates
ranging from Jan 1, 4712 BC through Dec 31, 4712 AD.
Unless you specifically specify BC, AD date entries are the default.

Oracle uses its own internal format to store dates. Date data is stored in
fixed-length fields of seven bytes each, corresponding to century, year,
month, day, hour, minute, and second.

============================
HOWEVER (try the following):
============================

create table t_date
(
  d date
)

insert into t_date values
(
  to_date( '9999-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
)
1 row processed.

update t_date set d = d + 1
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

insert into t_date values
(
  to_date( '-4712-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' )
)
1 row processed.

update t_date set d = d - 1
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

delete from t_date

insert into t_date values
(
  to_date( '0000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
)
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

insert into t_date values
(
  to_date( '0001-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
)
1 row processed.

update t_date set d = d - 366
1 row processed.

select SUBSTR( TO_CHAR( d, 'SYYYY-MM-DD HH24:MI:SS' ), 1, 20 ) AS timestamp
  from t_date

TIMESTAMP          
--------------------
 0000-01-01 00:00:00

==============================
Results:
==============================

Oracle (7.3.3.4) accepts years in the range:

 -4713 < YYYY <= 9999

but the documentation says:

 -4713 < YYYY <= 4712

Also, the year 0 causes an error on insert
(I think, that's a correct behavior), but
allows it to be calculated (via update).

Where is the bug, where is the feature?

Steffen

2. Binding ADO RS created in code to a datagrid....

3. Oracle7, Personal Oracle7 on same client

4. SQL 7.0 Temp

5. Oracle7 Server and Oracle7 Workgroup Server for Windows NT

6. pgsql/src/backend/utils/misc guc-file.l

7. DTS Oracle7->MSSQL7 connection problem

8. Delete from table with Identity Column

9. Problems With Accessing Personal Oracle7 in Windows95 through VB

10. Oracle7 & VB5(sp3) problem

11. VB4 Oracle7 ODBC - Problems connecting from VB IDE

12. Problems Printing w/Crystal, VB, Oracle7

13. connect problem w/ oracle7.3


All times are UTC
Board index