Oracle error 1406 encountered

Recently one of my colleague went into an issue  “Oracle database error 1406: ORA -01406:fetched column value was truncated” while he was trying to create the extract of his tableau workbook.

He approached me and i saw he was creating the extract using the .twbx version. So , thought to first convert the dashboard to .twb and then try creating the extract  .The process started but again failed with the same error

Tableau  Error snapshot

Now , I decided to check his Data source and found  it uses multiple Db Tables joined along with Custom SQL . So when i looked the Custom SQL , it looked like below  .

SELECT distinct to_date(‘1-Feb-2014′) as Start_date,
to_date(’31-Jan-2015’) as End_Date,
to_date(fs.TRADE_DATE) as TRADE_DATE,fs.SID
FROM VIP_HO.F_SUMM FS
where  fs.TRADE_DATE between  ‘1-Feb-2014′ and ’31-Jan-2015’

I suspected the issue with the Custom SQL , so just removed the entire custom SQL and tried to created the extract  . To my excitement  , it was working fine. Now i knew with confidence that something is wrong in the Custom SQL piece

I then went into database and matched the datatype of the columns in the table VIP_HO.F_SUMMARY of custom SQL  to ensure that all data type and conversions are done correctly.   What was strange that , he used to_date(fs.TRADE_DATE)  while the Db Table in oracle already had it defined as Date.  I couldn’t understand the reason , However i changed his query by dropping of the  to_Date function on fs.TRADE_DATE column .

So the final query looked like

SELECT distinct to_date(‘1-Feb-2014′) as Start_date,
to_date(’31-Jan-2015’) as End_Date,
fs.TRADE_DATE as TRADE_DATE,
fs.SID FROM VIP_HO.F_SUMMARY FS
where  fs.TRADE_DATE between  ‘1-Feb-2014′ and ’31-Jan-2015’
and saved the dashboard and tried to generate the extract again  And guess what ,  Voila !! it worked .

Later i also googled on this issue and came to know the issue could also arise if the  ” Database  column buffer area” is less compared to the data fetched in the columns of Select statements .

The db manual mentioned the cause as
Cause: In a host language program, a FETCH operation was forced to truncate a character string. The program buffer area for this column was not large enough to contain the entire string. The cursor return code from the fetch was +3.
Action: Increase the column buffer area to hold the largest column value or perform other appropriate processing.

Some experts said , even the calculated fields in tableau that has float with some decimal places  can also create the trouble and Rounding the calculated variable would fix the issue .

To what i understand , such issues would occur  only in the custom SQL code in the Tableau and chances of issue with the usage of regular Db tables is minimal. At least i haven’t seen it.
Thanks for reading  . Feel free to comment and provide your view

[{«Product»:{«code»:»SSZJPZ»,»label»:»IBM InfoSphere Information Server»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»—«,»Platform»:[{«code»:»PF002″,»label»:»AIX»},{«code»:»PF010″,»label»:»HP-UX»},{«code»:»PF016″,»label»:»Linux»},{«code»:»PF027″,»label»:»Solaris»},{«code»:»PF033″,»label»:»Windows»}],»Version»:»9.1;11.3″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]

Error ORA-1406 Received During Export After Upgrade to 10.2 (Doc ID 367831.1)

Last updated on MARCH 12, 2019

Applies to:

Symptoms

Errors received during export after upgrading to 10G R2:

Auditing is enabled. The initialization parameter AUDIT_TRAIL is set to a value other than NONE or FALSE.

Changes

Oracle version upgraded from 9.2.0.x to 10.2.0.1

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

Full Export Fails With Error ORA-1406 When Exporting 11g Database (Doc ID 553993.1)

Last updated on JANUARY 30, 2022

Applies to:

Symptoms

Full database export using traditional exp utility version 9.2.0.8 fails with errors EXP-8 ORA-1406 during exporting roles from a database version 11.1.0.x.

Example exp command:

The problem affects all the exp utilities with release lower than 11g.

Changes

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

The connector received oracle error code ora 1406

Подниму старую тему — почему-то стали валиться в лог такие ошибки —

Вроде это говорит о нехватке буфера чтения. Увеличил Buffer size — 50 К поставил. Все равно ошибки валятся. Вероятно это не тот буфер 😉
Может кто в курсе, где можно подкрутить еще?
.
ps вообще-то тема больше к администрированию — может модераторы перенесут?

То есть, эта ошибка возникает тогда, когда выбираемая из базы данных строка слишком длинна, и ее приходится обрезать. Предлагаемое действие — увеличить размер буфера, сделав его больше максимально возможной длины строки, выбираемой из базы данных. Для при веденного примера я решил это очень просто — зайдя в Панель управления -> Администрирование -> Источники данных ODBC, запустил настройку нужного источника данных и в новом окне, на вкладке Oracle установил значение параметра «Fetch buffer size» на 128000 вместо 64000 по умолчанию.

Для других приложений, использующих ODBC для подключения к Oracle, проблема должна решаться сходным способом, нужно только найти, где установить новый размер буфера.

Источник

9
Handling Runtime Errors

An application program must anticipate runtime errors and attempt to recover from them. This chapter provides an in-depth discussion of error reporting and recovery. You learn how to handle errors and status changes using the SQLSTATE status variable, as well as the SQL Communications Area (SQLCA) and the WHENEVER directive. You also learn how to diagnose problems using the Oracle Communications Area (ORACA). This chapter contains the following topics:

The Need for Error Handling

A significant part of every application program must be devoted to error handling. The main reason for error handling is that it allows your program to continue operating in the presence of errors. Errors arise from design faults, coding mistakes, hardware failures, invalid user input, and many other sources.

You cannot anticipate all possible errors, but you can plan to handle certain kinds of errors that are meaningful to your program. For the Pro*C/C++ Precompiler, error handling means detecting and recovering from SQL statement execution errors. You can also prepare to handle warnings such as «value truncated» and status changes such as «end of data.» It is especially important to check for error and warning conditions after every SQL data manipulation statement, because an INSERT, UPDATE, or DELETE statement might fail before processing all eligible rows in a table.

Error Handling Alternatives

There are several alternatives that you can use to detect errors and status changes in the application. This chapter describes these alternatives, however, no specific recommendations are made about what method you should use. The method is, after all, dictated by the design of the application program or tool that you are building.

Status Variables

You can declare a separate status variable, SQLSTATE or SQLCODE, examine its value after each executable SQL statement, and take appropriate action. The action might be calling an error-reporting function, then exiting the program if the error is unrecoverable. Or, you might be able to adjust data or control variables and retry the action.

The SQL Communications Area

Another alternative that you can use is to include the SQL Communications Area structure ( sqlca) in your program. This structure contains components that are filled in at runtime after the SQL statement is processed by Oracle.

In this guide, the sqlca structure is commonly referred to using the acronym for SQL Communications Area (SQLCA). When this guide refers to a specific component in the C struct, the structure name ( sqlca) is used.

The SQLCA is defined in the header file sqlca.h , which you include in your program using either of the following statements:

• EXEC SQL INCLUDE SQLCA;
• #include

Oracle updates the SQLCA after every executable SQL statement. (SQLCA values are unchanged after a declarative statement.) By checking Oracle return codes stored in the SQLCA, your program can determine the outcome of a SQL statement. This can be done in the following two ways:

• Implicit checking with the WHENEVER directive
• Explicit checking of SQLCA components

You can use WHENEVER directives, code explicit checks on SQLCA components, or do both.

The most frequently-used components in the SQLCA are the status variable ( sqlca.sqlcode), and the text associated with the error code ( sqlca.sqlerrm.sqlerrmc). Other components contain warning flags and miscellaneous information about the processing of the SQL statement.

SQLCODE (upper case) always refers to a separate status variable, not a component of the SQLCA. SQLCODE is declared as a integer. When referring to the component of the SQLCA named sqlcode, the fully-qualified name sqlca.sqlcode is always used.

When more information is needed about runtime errors than the SQLCA provides, you can use the ORACA. The ORACA is a C struct that handles Oracle communication. It contains cursor statistics, information about the current SQL statement, option settings, and system statistics.

The SQLSTATE Status Variable

The precompiler command line option MODE governs ANSI/ISO compliance. When MODE=ANSI, declaring the SQLCA data structure is optional. However, you must declare a separate status variable named SQLCODE. SQL92 specifies a similar status variable named SQLSTATE, which you can use with or without SQLCODE.

After executing a SQL statement, the Oracle Server returns a status code to the SQLSTATE variable currently in scope. The status code indicates whether the SQL statement executed successfully or raised an exception (error or warning condition). To promote interoperability (the ability of systems to exchange information easily), SQL92 predefines all the common SQL exceptions.

Unlike SQLCODE, which stores only error codes, SQLSTATE stores error and warning codes. Furthermore, the SQLSTATE reporting mechanism uses a standardized coding scheme. Thus, SQLSTATE is the preferred status variable. Under SQL92, SQLCODE is a «deprecated feature» retained only for compatibility with SQL89 and likely to be removed from future versions of the standard.

Declaring SQLSTATE

When MODE=ANSI, you must declare SQLSTATE or SQLCODE. Declaring the SQLCA is optional. When MODE=ORACLE, if you declare SQLSTATE, it is not used.

Unlike SQLCODE, which stores signed integers and can be declared outside the Declare Section, SQLSTATE stores 5-character null-terminated strings and must be declared inside the Declare Section. You declare SQLSTATE as

SQLSTATE must be declared with a dimension of exactly 6 characters.

SQLSTATE Values

SQLSTATE status codes consist of a 2-character class code immediately followed by a 3-character subclass code. Aside from class code 00 («successful completion»,) the class code denotes a category of exceptions. And, aside from subclass code 000 («not applicable»,) the subclass code denotes a specific exception within that category. For example, the SQLSTATE value ‘22012’ consists of class code 22 («data exception») and subclass code 012 («division by zero»).

Each of the five characters in a SQLSTATE value is a digit (0..9) or an uppercase Latin letter (A..Z). Class codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for predefined conditions (those defined in SQL92). All other class codes are reserved for implementation-defined conditions. Within predefined classes, subclass codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for predefined subconditions. All other subclass codes are reserved for implementation-defined subconditions. Figure 9-1 shows the coding scheme.

Figure 9-1 SQLSTATE Coding Scheme

Table 9-1 shows the classes predefined by SQL92.

Источник

ORA-01406 error, not support oracle function ? #62

Comments

binlaniua commented Mar 31, 2015

error

Error: ORA-01406: fetched column value was truncated

The text was updated successfully, but these errors were encountered:

cjbj commented Mar 31, 2015

@binlaniua can you reproduce this with a simpler table and post the CREATE TABLE statement so we can see the data types?

wilkolazki commented Mar 31, 2015

Hi,
I have the same problem. Unfortunatelly I did not manage to reduce the data to locate the exact cause.
Anyway, my query has about 70 columns. I am trying to fetch about 100 000 rows. The same query runs without problem in Pl/Sql Developer.

Tomorow I will try to reduce the column list until I find problematic column. Then I will try to reproduce it on artificial data.

Best regards,
SWilk

cjbj commented Mar 31, 2015

Let us know your NLS environment too.

wilkolazki commented Mar 31, 2015

wilkolazki commented Mar 31, 2015

I have narrowed the issue down to one column.

I will try to create a sample create table script tomorow.

cjbj commented Apr 1, 2015

@wilkolazki how are you fetching 100K rows? Do you bump up maxRows that big?

wilkolazki commented Apr 1, 2015

Not exactly. There is about that many rows in the view i am fetching data from. At the moment I am experimenting and fetching first «random» 100 rows (no ORDER BY clause). I was palnning to try to bump maxRows to that size to fetch all of them when there was no more error. If that would be impossible, I was going to sort the rows and fetch them all with paging. Anyway, I have to somehow get all of them and save them as json

wilkolazki commented Apr 1, 2015

I was able to reproduce this error on artificial data:

The query without the OFFSET clause gives the ORA-01406 error. If I uncomment the offset clause and skip at least one row, then the query works correctly. It does not matter if I use limit the rows from 1 to 100 or from 0 to 99. If the query tries to fetch all rows, then it throws error. If the result set is reduced, then it works.

There is one more weird thing. There is something wrong with charset encoding. The «working» query returns malformed: [ ‘002ZAŻÓŁĆ GĘLĽ JAŃ ZAŻ’ ] instead of [ ‘002ZAŻÓŁĆ GĘŚLĄ JAŹŃ ZAŻÓŁĆ GĘŚLĄ J’ ].

I hope this helps to reproduce the issue.
Sorry for being so chaotic in this post.

cjbj commented Apr 1, 2015

@wilkolazki To run with my AL32UTF8 database, I had to increase the column size so the INSERT worked. I didn’t see a 1406 in various tests. However, with NLS_LANG=POLISH_POLAND.AL32UTF8 and changing the CREATE to use VARCHAR(51), I didn’t get any rows returned by the query nor any error.

I know characterset interaction of Node/v8/node-oracledb is yet to be reviewed. I’ll mark this as a bug so it gets looked at when that review happens, if not sooner.

Can you post your OS, Oracle client library version, and database version? These are always useful.

If anyone else has a testcase, please post it.

wilkolazki commented Apr 2, 2015

@cjbj, here are the system details:

Client library version: Release 11.2.0.3.0 — Production

Anyway, please ignorme my previous comment:

There is one more weird thing. There is something wrong with charset encoding. The «working» query returns malformed: [ ‘002ZAŻÓŁĆ GĘLĽ JAŃ ZAŻ’ ] instead of [ ‘002ZAŻÓŁĆ GĘŚLĄ JAŹŃ ZAŻÓŁĆ GĘŚLĄ J’ ].

I have tested the same query from PHP and the encoding was malformed in the same way. Something went wrong when I have been writing the test insert. I have corrected it, so the test data contains proper polish characters. So character encoding conversion works properly.

Even after I have corrected the data, I still get an error when trying to fetch unlimited result set
The error have something in to do with maxRows option.

So, the table contains 100 rows. If I fetch them all and set maxRows = 100, I get error. If I set the maxRows = 101, the query executes correctly.

Источник