Toad World® Forums

CASTING : SELECT FROM TABLE (...)


#1

Hello,

this topic is hardly to describe.
Please just take a look at the attached stuff try it out and let us about this later on. The schema where the code is supposed to be installed needs some advanced privs. But I’m sure you will check it out.
Yes, I do know - this all sounds a bit mystically. And indeed it is (as least for me)

Thanks Andre

object_casting_problem_20070508.sql (29.1 KB)
object_casting_problem_20070508.sql (29 KB)


#2

xp_gc package.
Ok I compiled and run, and what wrong with that? Could you say some more words about your problem?

Hi, include


#3

Hi Piter,

sorry for the xp_gc stuff …
I have attached a new .sql and a .jpg file with screen shot too.

The problem is, that the call to xp_data.cols_as_rows_vc2_n_d
(xp_data_y.cols_as_rows_vc2_n_d is just a modified version without a lot of calls for other common xp_… stuff)
on some db’s works and on other db’s not.
As fare as I could found this has something to to with the option installed on the db’s.
On “normal/simply” db’s (without partitioning and/or OLAP) it works perfect, but not on the others.
I can not understand this.
If you have the oportunity please try it on different db’s with/without extra option.
All db’s are 9.2.0.6.

Andre
object_casting_problem_20070508.sql (29.1 KB)


#4

Hi Piter,

sorry for the xp_gc stuff …
I have attached a new .sql and a .jpg file with screen shot too.

The problem is, that the call to xp_data.cols_as_rows_vc2_n_d
(xp_data_y.cols_as_rows_vc2_n_d is just a modified version without a lot of calls for other common xp_… stuff)
on some db’s works and on other db’s not.
As fare as I could found this has something to to with the option installed on the db’s.
On “normal/simply” db’s (without partitioning and/or OLAP) it works perfect, but not on the others.
I can not understand this.
If you have the oportunity please try it on different db’s with/without extra option.
All db’s are 9.2.0.6.

Andre
casting.jpeg


#5

Hi
It works for me on 10.2 and 10.1 enterprise edition with OLAP databases . I do not have 9, so i can’t test it for you.

Try change this type from numbers without precision to integers:
CREATE OR REPLACE
TYPE xp_ot_col_as_row_vc2_n_d AS OBJECT (
row_num integer,
column_name VARCHAR2 (30),
column_type_varchar2_4000 VARCHAR2 (4000),
column_type_number integer,
column_type_date DATE,
typecode_number integer
)
/

;

…etc.
Sometimes Ora 9 has problems with collections filled by rowtype.

I think it could be an oracle bug for this database, try patch your database to hgher version, 9.2.0.7.

ora_type_mapp_aa_fill you have:
gv_ora_type_mapp_aa (lv_rec.idx) := lv_rec;

try change this into:
gv_ora_type_mapp_aa (lv_rec.idx).idx := lv_rec.idx;
gv_ora_type_mapp_aa (lv_rec.idx).typecode_name := lv_rec.typecode_name;

Or

in


#6

Hello Piter,
(and Devs too)

thank you for your efforts.
It’s good to know that there are other people with a different point of view helping to make things more clear.

Please take a look at the attached XLS.
There you can see the reason why SQL Nav failed with the OBT-DB but works fine with MIGDB.
There are diffs between DATA_LENGTH / CHAR_COL_DECL_LENGTH and the value for CHAR_LENGTH.

I can not explain that and will ask my DBA for help tomorrow.

Thanks again.
Andre
dba_tab_columns_obt_zu_migdb_xp_protokoll.xls (28.5 KB)


#7

Hi,
I don’t understand what dependency is between your example and XP_PROTOKOLL table


#8

Hi Andre,

We tried on our test databases.But we are unable to reproduce the issue.

regards
sekhar


#9

Hi Peter,
(and Devs too)

I think I found the reason.
If I modufy my object types:

DROP TYPE xp_ot_col_as_row_vc2_n_d
/

CREATE OR REPLACE
TYPE xp_ot_col_as_row_vc2_n_d AS OBJECT (
row_num NUMBER,
column_name VARCHAR2 (30),
– column_type_varchar2_4000 VARCHAR2 (4000),
column_type_varchar2_4000 VARCHAR2 (1000),
column_type_number NUMBER,
column_type_date DATE,
typecode_number NUMBER
)
/

SQL Nav can work with the incoming data from the db.

In the meantime I also realized that the diff between the db’s is also the character set - multibyte or not.
I do believe that SQL Nav reads some internal information from the dictionary column that in multibyte db has the 3 times higher value than on nonmultibyte dbs.
You can see this in the XLS sheet I send yesterday.
I think that have to be fixed.
I have tested the same case with other tools (sqlplus, TOAD and PLSQL DEVELPOER, and Oracle SQL Developer). Neither of these tools have the same problem like SQL Navi has in this case.

So I think: Devs - it’s up to you :slight_smile:

Andre


#10

Hi Andre,

We will investigate further with our dev team.Mean time could you please provide more information from select * from nls_database_parameters.

regards
sekhar


#11

Hi Sekhar

here are the requested infos:

Regard
Andre

– Here SQL Navi run into an error
OBT NLS_LANGUAGE AMERICAN
OBT NLS_TERRITORY AMERICA
OBT NLS_CURRENCY $
OBT NLS_ISO_CURRENCY AMERICA
OBT NLS_NUMERIC_CHARACTERS .,
OBT NLS_CHARACTERSET UTF8
OBT NLS_CALENDAR GREGORIAN
OBT NLS_DATE_FORMAT DD-MON-RR
OBT NLS_DATE_LANGUAGE AMERICAN
OBT NLS_SORT BINARY
OBT NLS_TIME_FORMAT HH.MI.SSXFF AM
OBT NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
OBT NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
OBT NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
OBT NLS_DUAL_CURRENCY $
OBT NLS_COMP BINARY
OBT NLS_LENGTH_SEMANTICS BYTE
OBT NLS_NCHAR_CONV_EXCP FALSE
OBT NLS_NCHAR_CHARACTERSET UTF8
OBT NLS_RDBMS_VERSION 9.2.0.6.0

– Here it works
MIGDB NLS_LANGUAGE AMERICAN
MIGDB NLS_TERRITORY AMERICA
MIGDB NLS_CURRENCY $
MIGDB NLS_ISO_CURRENCY AMERICA
MIGDB NLS_NUMERIC_CHARACTERS .,
MIGDB NLS_CHARACTERSET WE8ISO8859P1
MIGDB NLS_CALENDAR GREGORIAN
MIGDB NLS_DATE_FORMAT DD-MON-RR
MIGDB NLS_DATE_LANGUAGE AMERICAN
MIGDB NLS_SORT BINARY
MIGDB NLS_TIME_FORMAT HH.MI.SSXFF AM
MIGDB NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
MIGDB NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
MIGDB NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
MIGDB NLS_DUAL_CURRENCY $
MIGDB NLS_COMP BINARY
MIGDB NLS_LENGTH_SEMANTICS BYTE
MIGDB NLS_NCHAR_CONV_EXCP FALSE
MIGDB NLS_NCHAR_CHARACTERSET UTF8
MIGDB NLS_RDBMS_VERSION 9.2.0.6.0


#12

Hello,
Another hint in addition.
Maybe it helps anyway.
There was a bug in sqlnav_5-1-0-635-beta-update_exe releated to infos SQL Navi uses from the dictionary data. Please see attachment.

Regards
Andre
bug_sqlnav_5-1-0-635-beta-update_exe_005.doc (36.5 KB)


#13

Hi Andre,

Thank you very much for the information.We will investigate with our dev team and will update at earliest.

regards
sekhar


#14

Hi Andre,

According to Oracle documentation from version 9i on we have to use CHAR_LENGTH instead of DATA_LENGTH for some of the data types.This issue will be fixed for one of our future release of SQLNAV.

Here is the information i have send to our dev team.

CHAR_LENGTH should be used for the data length for 9i and above in all the following data dictionary queries >>>>>>>

  1. ALL_ARGUMENTS
  2. ALL_IND_COLUMNS
  3. ALL_REPCOLUMN
  4. ALL_TAB_COLUMNS
  5. DBA_IND_COLUMNS
    6)DBA_REPCOLUMN
    7)DBA_TAB_COLUMNS
    8)USER_ARGUMENTS
    9)USER_IND_COLUMNS
    10)USER_REPCOLUMN
    11)USER_TAB_COLUMNS

#########################################################

CHAR_LENGTH

Displays the length of the column in characters. This value only applies to the following datatypes:

CHAR
VARCHAR2
NCHAR
NVARCHAR

#########################################################

CHAR_USED
B | C. B indicates that the column uses BYTE length semantics. C indicates that the column uses CHAR length semantics. NULL indicates the datatype is not any of the following:

CHAR
VARCHAR2
NCHAR
NVARCHAR2

#########################################################

DATA_LENGTH NUMBER NOT NULL Length of the column (in bytes)

Thank you very much for your feedback.

regards
sekhar


#15

Hi Sekhar,

I’m glad that it was helpful and now I’m looking forward to to the next version.

Regards
Andre


#16

Hi sekhar,

to cut a long story short:
This is still an issue in build 800.

Regards
Andre

and is still an issue in build 817.

Regards
Andre

Message was edited by: andreml


#17

Hi Andre,

We haven’t actually implemented a fix for this… Sekhar is still investigating the issue… that is why it is still an issue in the new builds

We’ll let you know when a fix is implemented, so you can confirm it for us…
Thx for the feedback!!

  • Jaime -

#18

Hi there Downunder,

please check this in Beta 6 again. Seems to be still an issue.
I think it should have been possible to fix this (small issue) over the last month.

Thank you.

Andre


#19

Hi there Andre,

Yes, you are quite correct, this is still an issue.
We have a CR in our system to implement the changes advised by Sekhar…
…but the CR is targeted for the 6.1 release.

The change, although simple, has deep ramifications. So we cannot implement it for 6.0.

  • Jaime -

#20

Hi Jaime,

well, I take that as a promise and wish good luck!
Is there already a delivery date for 6.1?

Best Regards!
Andre