logs archiveIRC Archive / Freenode / #oracle / 2009 / December / 22 / 1
Lindsea
gn all
sidh
greetings gentlemem
gentlemen
i 've migrated a sql server 2000 database to oracle 11G, on the 11g side , encoding was set on France.French.AL32UTF8, but now i've migrate data i realize all accentuated char are shown with ?
could you help me please
and the NLS parameter in the sqlldr script was set to AL32UTF8 too
so is it just a setting or do i have to do all the migratin again ?
migration
i realize too that it is not related to the windows client i think
because if i sqlplus the database from the db server itself, i see accentuated char with ?
too
i really don't know what to do now, i chose AL32UTF8 for not having problem, but it was an error i think
i tried FRENCH_FRANCE.AL32UTF8 FRENCH_FRANCE.UTF8 FRENCH_FRANCE.WE8ROMAN8 FRENCH_FRANCE.WE8MS1252 NLS_LANG variable environnement on the oracle server, i never display accents correctly
if someone could give me the correct approach , it would be great
usn_work
have all of them been set in the session doing the import, too? I'm talking about environment!
sidh
usn_work: i did the import from a windows session with sqldeveloper
oops
imeant
usn_work
did'nt you say sqlloader recently?
(Action) is confused
sidh
i did the extraction of data from mssql from a windows session with sqldeveloper
usn_work
how did you load it into oracle?
sidh
then i copied the data and the script over a scp session
usn_work
What's the NLS settings of your SQl developer?
         

sidh
then i ran sqlldr scripts generated with sqldeveloper on the linux box
the oracle instance was set to al32utf8
usn_work
And the session's environment?
sidh
usn_work: i didn't look in the sqldevelper settings for that
i thougt it was set in the registry of the windows
on the registry it was set to FRENCH_FRANCE.WE8MSWIN1252
usn_work
And on the linux box?
sidh
usn_work: i realized now sqldeveloper never use registry value , it use its own that is (in my case CP1252)
usn_work
I'm fairly sure that the data was "corrupted" in charset during the walk through the different sessions and tools
try to establish a UTF8 NLS envronment as soon as ou extract the data from MSSQL
the first tool should make the conversion, and then keep it untouched (mak sure the NLS settings are simisimisimisimilar all the time)
sidh
usn_work: on the oracle instance it is FRENCH_FRANCE.AL32UTF8
usn_work
The INSTACE does nearly not matter, I thought I have made that clear
look at the ENVIROBMENT of your SESSIONS and TOOLS
sidh
ok sorry
so at the step of retriving data from sqlserver to flat files (offline capture) , does the CP1252 enconding setting of sqldeveloper influence the flat files encodings ?
usn_work
if you use it to do the export: yes
should be easy to check the raw data in this step, isn't it?
sidh
for exporting to flat files , bcp (sqlserver bulk copy command) scripts are used
usn_work
why not?
sidh
ok i will do again all the migration process, taking care of your advices
really thank you
usn_work
no problem
funky2
hi
mux
hello guys; I'm trying to select distinct columns from a table with a specific order on other columns, that is, I have a SELECT DISTINCT a, b FROM (SELECT * FROM t WHERE ... ORDER BY ...)
where the ORDER BY references different columns than a and b
my problem is that my order is apparently destroyed this way
funky2
I need some help with retriving hierarchial data for a menu on my website. I know there is some thing that has start from and connect by but I'm not sure how to determine what level the data is.
mux could u give a bit more sql detail where is a or b coming from?
I have a table that has a id, name, parent_id. parent_id can be nullable
I know this is returning data. select to_char(opt_nav_id) || link_name from tbl_opt_nav start with parent_io_nav_id is null connect by prior opt_nav_id = parent_opt_nav_id. it
is there a way to have another column with the level....like level 1 where parent_opt_nav_id is null, level 2 if data is directly under level 1, level 3 if its under level 2 etc
figured it out there is a "level" column I can return select a , level from tbl_t start with parent_id is null connect by prior id=parent_id
mux
funky2: I just figured a way to do it; I add a ROW_NUMBER() in my inner SELECT, and then I order by this new column in my outer select
thanks anyways :-)
funky2
no problem.
chemik_
hi
my coleague deleted few lines from one table, it's possible restore this lines or only this only one table ?
from redologs example?
         

hali
chemik_: how long ago?
select * from table as of timestamp to_timestamp(....);
if you are lucky
or else you have to restore it from backup
chemik_
half hour
hali
try the select as of
chemik_
hali: i must be sure, this command perform only select from table?
hali: not writing anything
hali
not writing anything
chemik_
hali: thanks a lot !!!
you safe my life
usn_work
http://www.usn-it.de/index.php/2009/12/22/the-usual-end-of-the-year-stuff-but-honestly/
hali
blimey, new metalink is well handy
funky2
is metalink free?
hali
yes and no
yes it's free if you have a support contract
the support contract costs at least a couple of hundred a year
funky2
oh dear
usn_work
22% of license fee in EMEA
hali
is it that high these days
yes looks like it, i just bought some more SE One licensing
usn_work
sorry, my session died hali
realHans
Hi. Whats the difference between database providers OraOledb.Oracle.1 and OraOledb.Oracle ? Both works here ...
(Action) winks with both hands.
fission6
how are fact tables usually populated? through ETL?
pigoz
how do I select * from a table where length(field_a) == 11?
would select * from table where length(field_a) == 11; work?
usn_work
pigoz, why don't you try?
pigoz
usn_work: unfortunately I don't have an oracle server installed on my home pc, so I can't try right now
usn_work
SQL> select count(*) from willi where length(description)=3;
COUNT(*)
----------
8000000
pigoz
pigoz
thank you
usn_work
by the way pigoz - you should REALLY have an oracle server at home ;)
no problem
pigoz
yeah I know I should install it :)
usn_work
at least XE :)
brutuz
hi.. i need some suggestion..
i need to put comma in a number without breaking the round..
result..
usn_work
?
brutuz
round(1000.123, 2) with result to 1000.12
usn_work
okay, and now?
brutuz
if you want to comma into it.. i use to_char
to_char(RESULT_FROM_ROUND,'9G999G999D9999')
it breaks round
usn_work
SQL> alter session set NLS_NUMERIC_CHARACTERS='.,';
Session wurde geandert.
SQL> select round(1000.123, 2) from dual;
ROUND(1000.123,2)
-----------------
1000.12
SQL> alter session set NLS_NUMERIC_CHARACTERS=',.';
Session wurde geandert.
SQL> select round(1000.123, 2) from dual;
ROUND(1000.123,2)
-----------------
1000,12
SQL>
Theory
brutuz: why 4 nines after the D?
usn_work
brutuz?
brutuz
now before u say.. use D99 instead of D9999
its because some values i need 4 decimal place..
usn_work
dwhy messing around with to_char?
just change the numeric character
Theory
so in those cases, use 4, and in the cases you don't, use 2.
i believe brutuz wants 1,234.56
brutuz
yes exactly..
i have numbers like 0.1121 some 1.23
Theory
so use different format strings for each type...
brutuz
yikes..
that was im afraid of... putting cases depending on the number
Theory
you presumably already do this when you round
brutuz
yes.. and now i have to put nested..
is there a way to put comma with using to_char?
ryan-g
brutuz: use the ascii code and chr(X)
brutuz: so to_char(date,'YYYY'||chr(ascii code of comma));
Kako
Hi, I've started to delete an instance (mistake) by using the dbca-tool, so I killed the process bevor it could delete my data. The instance countinues running. How can I found out what dbca already has done?
(I using oracle10.0.2 on linux)
ayyappan
any one help me what is background process in oracle
TheBonsai
a process not related to a session
mh
wrong
well, "nearly everything" is also wrong ;) lol
« prev next »