Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » Community » Newbie corner » A trouble with "select" from Oracle DB
A trouble with "select" from Oracle DB [message #29396] Mon, 18 October 2010 15:52 Go to next message
porto is currently offline  porto
Messages: 51
Registered: March 2007
Member
I have a problem with selecting data from the DB (Oracle ver. 10.2). The database has a table that contains 14,124 rows. When I try to fetch data with this code:
Oracle8 oracle;
oracle.Open("user/pass@server/db", false));
Sql sql(oracle);
sql.Execute("select * from IKIS_ERS.INSUR_ANKT_A");
while(sql.Fetch())
Cout() << sql[0] << "\n";

I get only 345 rows. When I try to select data from the database using the same SQL query with "SQLPlus Worksheet" (its a tool from Oracle), I get 14,600 (why not 14,124 I don't know).

When I tried to insert settrace(), in the log I got:
Error: ORA-01406: fetched column value was truncated
This is what a found about this error:
Quote:

ORA-01406:fetched column value was truncated

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.


How can I increase the column buffer?

Sorry for bad English.

[Updated on: Mon, 18 October 2010 20:21]

Report message to a moderator

Re: A trouble with "select" from Oracle DB [message #29403 is a reply to message #29396] Tue, 19 October 2010 09:28 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

porto wrote on Mon, 18 October 2010 16:52

I have a problem with selecting data from the DB (Oracle ver. 10.2). The database has a table that contains 14,124 rows. When I try to fetch data with this code:
Oracle8 oracle;
oracle.Open("user/pass@server/db", false));
Sql sql(oracle);
sql.Execute("select * from IKIS_ERS.INSUR_ANKT_A");
while(sql.Fetch())
Cout() << sql[0] << "\n";

I get only 345 rows.


I fetched from a table more than 1000 rows. it was not problems.
I will try to fetch more than 10 000 rows to test your problem.
The problem can be:
If you run in terminal with non GUI then in terminal you can view only last 3XX rows.
Try to put your output in file and count rows.
porto wrote on Mon, 18 October 2010 16:52


When I try to select data from the database using the same SQL query with "SQLPlus Worksheet" (its a tool from Oracle), I get 14,600 (why not 14,124 I don't know).



seems your oracle db or client has problems. Check it twice.

[Updated on: Tue, 19 October 2010 09:35]

Report message to a moderator

Re: A trouble with "select" from Oracle DB [message #29404 is a reply to message #29403] Tue, 19 October 2010 09:43 Go to previous messageGo to next message
porto is currently offline  porto
Messages: 51
Registered: March 2007
Member
Quote:

If you run in terminal with non GUI then in terminal you can view only last 3XX rows.
Try to put your output in file and count rows.

Thanks, but I think that is not the problem. Because when I do it:
int i = 0;
while(sql.Fetch())
i++;
Cout() << i;

I get the same result: i == 345.
I am almost certain that the problem is in the column buffer size. But where and how to increase it, I have no idea.

[Updated on: Tue, 19 October 2010 09:46]

Report message to a moderator

Re: A trouble with "select" from Oracle DB [message #29405 is a reply to message #29396] Tue, 19 October 2010 10:02 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

I tested with 193066 rows without any problem.

The code:
Oracle8 oracle_conn;
String result;
	TimeStop v_timer;
	if(oracle_conn.Login("user", "password", "host:port/SID", true)){
		result << "\n connection time: " << v_timer;
		v_timer.Reset();
		Sql sql(oracle_conn);
		sql.Execute("select count(*) from AP_INVOICES_ALL");
		while(sql.Fetch())
			result << "\n total rows: " << sql[0];
		result << ", timer: " << v_timer;
		v_timer.Reset();
		sql.Execute("select INVOICE_ID, INVOICE_DATE from AP_INVOICES_ALL");
		int i = 0;
		while(sql.Fetch()){
			++i;
			result << "\n" << i << ". " << sql[0] << " - " << sql[1];
		}
		result << ", timer: " << v_timer;
		v_timer.Reset();
	}else
		result << "error to connect";


and output:
Quote:



connection time: 0.312
total rows: 193066, timer: 0.047
1. 10000 - 12/31/1998
2. 10001 - 12/31/1998
3. 10002 - 12/31/1998
4. 10003 - 12/31/1998
5. 10004 - 12/31/1998
6. 10005 - 12/31/1998
......................
193065. 246828 - 07/01/2010
193066. 246897 - 07/30/2010, timer: 9.781



Result:
1. the problem can be with your oracle DB
2. or if you run the code in terminal then you can see only last 3XX rows.

Best Regards,
Ion Lupascu(tojocky).

Added: Oracle DB: 10.2.0.4
Server: Linux RedHat 4
Client: Windows XP SP3.
Tested with string columns - no problems!

[Updated on: Tue, 19 October 2010 10:15]

Report message to a moderator

Re: A trouble with "select" from Oracle DB [message #29408 is a reply to message #29405] Tue, 19 October 2010 10:35 Go to previous messageGo to next message
porto is currently offline  porto
Messages: 51
Registered: March 2007
Member
The client applications works without problems (I did not developed them). If there is a problem in the database itself, I can not fix it, because it's a big database of state organization. Therefore, correction of the database itself is impossible. Especially since all the software if this organization works with the DB without problems.

I think the problem is not the number of rows, but that is columns of some rows have to large values (text values in my case have 255 unicode characters maximum) that do not fit in the some buffer, which must be increased. Encountering a value greater than the buffer size, the fetching is terminated.
I quote again from the error log:
ORA-01406:fetched column value was truncated

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.

[Updated on: Tue, 19 October 2010 10:36]

Report message to a moderator

Re: A trouble with "select" from Oracle DB [message #29409 is a reply to message #29408] Tue, 19 October 2010 10:46 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

What return to your instance the sql code:
select * from NLS_DATABASE_PARAMETERS

?
porto wrote on Tue, 19 October 2010 11:35

The client applications works without problems (I did not developed them). If there is a problem in the database itself, I can not fix it, because it's a big database of state organization. Therefore, correction of the database itself is impossible. Especially since all the software if this organization works with the DB without problems.

I think the problem is not the number of rows, but that is columns of some rows have to large values (text values in my case have 255 unicode characters maximum) that do not fit in the some buffer, which must be increased. Encountering a value greater than the buffer size, the fetching is terminated.
I quote again from the error log:
ORA-01406:fetched column value was truncated

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.


Re: A trouble with "select" from Oracle DB [message #29411 is a reply to message #29409] Tue, 19 October 2010 10:53 Go to previous messageGo to next message
porto is currently offline  porto
Messages: 51
Registered: March 2007
Member
Quote:

What return to your instance the sql code:
select * from NLS_DATABASE_PARAMETERS




PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               CL8MSWIN1251
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.1.0

[Updated on: Tue, 19 October 2010 10:54]

Report message to a moderator

Re: A trouble with "select" from Oracle DB [message #29412 is a reply to message #29411] Tue, 19 October 2010 11:33 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

From SQLPLUS/TOAD/SQLDeveloper and U++ if you write sql:
1. SELECT COUNT(*) FROM IKIS_ERS.INSUR_ANKT_A
2. SELECT ROWNUM FROM IKIS_ERS.INSUR_ANKT_A

What are results?
Re: A trouble with "select" from Oracle DB [message #29415 is a reply to message #29412] Tue, 19 October 2010 13:15 Go to previous messageGo to next message
porto is currently offline  porto
Messages: 51
Registered: March 2007
Member
Quote:

1. SELECT COUNT(*) FROM IKIS_ERS.INSUR_ANKT_A


  COUNT(*)
----------
     14600

1 row selected.


Quote:

2. SELECT ROWNUM FROM IKIS_ERS.INSUR_ANKT_A


    ROWNUM
----------
         1
         2
         3
     .....
     14598
     14599
     14600

14600 rows selected.
Re: A trouble with "select" from Oracle DB [message #29416 is a reply to message #29415] Tue, 19 October 2010 13:35 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

porto wrote on Tue, 19 October 2010 14:15

Quote:

1. SELECT COUNT(*) FROM IKIS_ERS.INSUR_ANKT_A


  COUNT(*)
----------
     14600

1 row selected.


Quote:

2. SELECT ROWNUM FROM IKIS_ERS.INSUR_ANKT_A


    ROWNUM
----------
         1
         2
         3
     .....
     14598
     14599
     14600

14600 rows selected.


This are from SQLPLUS?

What are results from U++?

I have attached a simple code. change code by put your user name, password, host, port and SID(service Name).
Re: A trouble with "select" from Oracle DB [message #29417 is a reply to message #29416] Tue, 19 October 2010 13:53 Go to previous messageGo to next message
porto is currently offline  porto
Messages: 51
Registered: March 2007
Member
Quote:

This are from SQLPLUS?

Yes.
Quote:

What are results from U++?

 connection time: 0.140
 total rows: 14600, timer: 0.000
1. 1
2. 2
3. 3
.....
14598. 14598
14599. 14599
14600. 14600, timer: 0.329

Re: A trouble with "select" from Oracle DB [message #29418 is a reply to message #29416] Tue, 19 October 2010 14:28 Go to previous messageGo to next message
porto is currently offline  porto
Messages: 51
Registered: March 2007
Member
I thought if I will reduce length of the column with this code:
sql.Execute("select substr(IAN_NAME1, 1, 30) from IKIS_ERS.INSUR_ANKT_A");
int i = 0;
while(sql.Fetch())
i++;
Cout() << i;

it will help, but somehow it does not work. I get: i == 0. But it works if I get numberic value from the table:
sql.Execute("select substr(IAN_NUMIDENT, 1, 3) from IKIS_ERS.INSUR_ANKT_A");

Although normally works in the SQLPlus.

[Updated on: Tue, 19 October 2010 14:34]

Report message to a moderator

icon7.gif  Re: A trouble with "select" from Oracle DB [message #29419 is a reply to message #29416] Tue, 19 October 2010 14:44 Go to previous messageGo to next message
porto is currently offline  porto
Messages: 51
Registered: March 2007
Member
YES, I DID IT!!! Very Happy
This line has helped me:
oracle.DisableUtf8Mode();

But anyway, thank you very much for your help.
Re: A trouble with "select" from Oracle DB [message #29420 is a reply to message #29419] Tue, 19 October 2010 15:15 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

porto wrote on Tue, 19 October 2010 15:44

YES, I DID IT!!! Very Happy
This line has helped me:
oracle.DisableUtf8Mode();

But anyway, thank you very much for your help.



It must work without disabling UTF8.

We need to debug to end.

Please, repeat the test on u++ with replace ROWNUM by IAN_NAME1.

Write the result.

ADD: Don't forget to attach the log file (in the same directory of exe file)

[Updated on: Tue, 19 October 2010 15:19]

Report message to a moderator

Re: A trouble with "select" from Oracle DB [message #29425 is a reply to message #29420] Tue, 19 October 2010 15:34 Go to previous messageGo to next message
porto is currently offline  porto
Messages: 51
Registered: March 2007
Member
I replaced original text whih "sometext" string.
connection time: 0.141
 total rows: 14600, timer: 0.000
1. sometext1
2. sometext2
3. sometext3
................
343. sometext343
344. sometext344
345. sometext345


[Updated on: Tue, 19 October 2010 16:01]

Report message to a moderator

Re: A trouble with "select" from Oracle DB [message #29428 is a reply to message #29425] Tue, 19 October 2010 16:21 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

porto wrote on Tue, 19 October 2010 16:34

I replaced original text whih "sometext" string.
connection time: 0.141
 total rows: 14600, timer: 0.000
1. sometext1
2. sometext2
3. sometext3
................
343. sometext343
344. sometext344
345. sometext345





Please, change the class constructor code like this:
OracleTest::OracleTest(){
	CtrlLayout(*this, "Window title");
	Sizeable().MaximizeBox().MinimizeBox().Maximize();
	String result;
	TimeStop v_timer;
	if(oracle_conn.Login("user", "pass", "host:port/SID", true)){
		result << "\n connection time: " << v_timer;
		v_timer.Reset();
		Sql sql(oracle_conn);
		if(sql.Execute("SELECT COUNT(*) FROM IKIS_ERS.INSUR_ANKT_A")){
			while(sql.Fetch())
				result << "\n total rows: " << sql[0];
			result << ", timer: " << v_timer;
			v_timer.Reset();
		}else result << "error: " << sql.GetLastError();
		
		if(sql.Execute("SELECT IAN_NAME1 FROM IKIS_ERS.INSUR_ANKT_A")){
			int i = 0;
			while(sql.Fetch()){
				++i;
				result << "\n" << i << ". " << sql[0];
			}
			
			result << ", timer: " << v_timer;
			v_timer.Reset();
		}else result << "error: " << sql.GetLastError();
	}else
		result << "error to connect";
	LogCtrl.Set(result);
}


replace connection parameters!

Write your result and attach the log file.

ADDED: change the code. add GetLastError().

[Updated on: Tue, 19 October 2010 16:26]

Report message to a moderator

Re: A trouble with "select" from Oracle DB [message #29429 is a reply to message #29428] Tue, 19 October 2010 16:31 Go to previous messageGo to next message
porto is currently offline  porto
Messages: 51
Registered: March 2007
Member
I got the same result.
.LOG file is attached.
Where I need to add GetLastError()? I tried to add it the end of the class constructor, bat .log file is not changed...

[Updated on: Tue, 19 October 2010 16:36]

Report message to a moderator

Re: A trouble with "select" from Oracle DB [message #29431 is a reply to message #29429] Tue, 19 October 2010 17:00 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

porto wrote on Tue, 19 October 2010 17:31


Where I need to add GetLastError()?

the method "GetLastError" already added in code. just refresh your code execution.

[Updated on: Tue, 19 October 2010 17:05]

Report message to a moderator

Re: A trouble with "select" from Oracle DB [message #29432 is a reply to message #29431] Tue, 19 October 2010 17:14 Go to previous messageGo to next message
porto is currently offline  porto
Messages: 51
Registered: March 2007
Member
The same result. AND looks like the .log file is not changed.
Re: A trouble with "select" from Oracle DB [message #29433 is a reply to message #29432] Wed, 20 October 2010 09:15 Go to previous messageGo to previous message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

porto wrote on Tue, 19 October 2010 18:14

The same result. AND looks like the .log file is not changed.


But what is result text?
Previous Topic: tar library for u++
Next Topic: Maybe stupid qustions for UPP developers...
Goto Forum:
  


Current Time: Fri Mar 29 11:44:32 CET 2024

Total time taken to generate the page: 0.01567 seconds