logs archiveIRC Archive / Freenode / #oracle / 2010 / May / 17 / 1
marparma
I need to wite an sql script that prompts the user for input twice; e.g. user will give order number at a prompt, a sql query will return information on that order, then prompt the user for an action option like delete the order, update the order etc. The actual query, delete, update parts of the script is no problem for me, but I am running into a problem trying to figure out how to prompt the use
r for input repeatedly based on previsouly answers. I know about the ACCEPT comand but I need help integrating it into a more comprehensive script than what I can find examples for on Google. Is there anybody here that would be willing to help point me in the right direction?
brb
back
Trengo
marparma afaik, nobody does sql scripts for user interfacing
marparma
quit
usn_work
good morning
phimic
usn_work: good morning
usn_work: sudo crsctl disable crs <-- is this enough to disable crs autostart on sles10 sp3 or must i manually edit the /etc/inittab file?
DiscordianUK
dunno
why don't you ask on a SLES channel
usn_work
phimic, hi
phimic
usn_work: good morning
usn_work
disabling it by crsctl prevents the cluster services from starting up, but the services will loop without really starting
but no harm with that
Are yu atalking about 10g?
         

phimic
usn_work: yes 10.2.0.4
usn_work
so you have crsd, cssd and evmd entries
phimic
usn_work: ok but the cluster do not startup
usn_work: do you use rdac drivers for sles10?
usn_work
no I don't
no, the cluster does not start up
RDAC stinks IMHO - try to simply use device mapper
phimic
usn_work: did the normal device mapper manage mutlipath fc as well?
unk2k
Hello all
I have a table with two columns (pk, name) volumes of 1 million records. Type VARCHAR2 length from 3 to 20 characters in each cell. i need Stemming of the table. the problem is speed, I need a response no later than 50 ms
KeiKurono
morning
unk2k
oracle 11g, system is debian 64x, 16gb RAM, 8x2x2.9Ghz
What can you advise?
hali
broken
it's running debian, unsupported! </redflag>
1 millien rows is nothing on that hardware unless your disk config is crap
how is it queried, always referenced on pk?
if not, is the second column indexed
unk2k
hali oracle work good on Debian
hali
may work, but oracle can/will reject all your support tickets
unk2k
pk - is primary key
i dont's send any tickets to oracle support
what you mean ? ' 1 millien rows is nothing on that hardware unless your disk config is crap' ??
hali
1 million rows shouldn't take any time at all to query, especially with so few columns
unk2k
what index you mean ?
hali
is the table indexed except for the primary key?
unk2k
the table - a list of all the streets of my country, I need to do a search on them
hali
^^
unk2k
no. i have a index on PK
hali
and do you ever query on like col2 like '%streetname%'
instead of just i.e pk=454
unk2k
yes. this query is not morphologic and this query not use any index and this query work is vere slow (140-150ms)
if i search 'Meneson' or i search 'Mineson' or 'Neneson' (but i mean Menison)
i need morphologic search
         

hali
oracle has got a function called soundex() for that
and you can create a functional index on that function
but
i would expect you want full wildcard search in that
so a better option might be CTX
unk2k
ctx ?
oracle-text ?
hali
yes
you can do full text indexing and more fine grained matching
unk2k
oracle-text & oracle11gr2 & Russian word ?
can you give me more example or what i need search ?
hali
it's not trivial... http://pastebin.com/BrK52RsW
ygip
I have two big tables. i want to export 100k rows from oen table. In the other table I want to export the rows from the second with a foreign key to the first table. Any way I can do this with the exp utility?
hali
ygip: you can specify a where clause to exp/expdp
DoubleString
yo
RJarett
yo
hali
yo
mtst
Anyone know normalization?
zetan
(Action) denormal
mod_cure
where is the file that sqlplus uses for the history of the commands ?
name of file
mator
mod_cure, i'm not aware of existance of such file
mod_cure
how does it remember all the sql commands ?
i would assume it would be in a txt file
Trengo
circular list in memory?
mod_cure
i rebooted system, still have history
mator
mod_cure, "strace -e trace=file $executable" and see what files it opens
mod_cure
how does one display result vertically in sqlplus ?
mator, cant find the strace command
DiscordianUK
what os are you on mod_cure
mod_cure
centos
DiscordianUK
su - to become root
do you have strace now
mod_cure
no
DiscordianUK
is it a VPS?
mator
mod_cure, yum install strace
mtst
if i want to normalize database and have value patientNo, Name, FirstSeen, SocialWorker, VisitDate, VisitTime, Reason for visit, New Symptoms, level of pain
How would 1nf normalform look?
Ramen
when I insert a row with a seq defined on a field do I have to tell it what sequence number to use or should it auto generate?
RJarett
the point of a sequence is to alway grow
Arwen
Anyone knows how can I find out the size of each Oracle datatype?
drdimitri
Arwen: select vsize(col1) from table
unk2k
hali you there ?
do you remember me ?
i create context index. my query now. select name from streets where contains(name, '%eneral-roa%')>0;
Arwen
drdimitri.. Thks a lot !
unk2k
it's work vere fine, (but i hope some problems)
if my query (eneral-roa) contain mistake (example: en0r0al-roa) it's not work
RJarett
soundex()
unk2k
how can i fix the ?
i use russian character soundex not work
:(
RJarett any ideas ?
select soundex('russian-text') -> return (None)
RJarett
does fuzzy fail with russian?
unk2k
fuzzy?
soundex fail with russian
soundex = fuzzy ?
RJarett
no
unk2k
what you mean ?
RJarett
http://www.adp-gmbh.ch/ora/plsql/fuzzy_search.html
http://download.oracle.com/docs/cd/B10501_01/text.920/a96518/cqoper.htm#29140
normal fuzzy search doesnt include russian though
unk2k
.
:((((
idont understand the.
grey
Whats the best way to have a check constraint that has to reference other rows in the same table? I have a recursive FK, but need to check a condition on the parent, trigger? check against a function?
ratcheer
grey: In a normalized design, those rows would be in separate tables.
grey
bleh, that may be true, but in this case would be pretty ugly,
I'll ponder the whole design I guess, I'm not sure the best way to do this
drdimitri
grey: The only clean method I can imagine: Do the check in your application. Select ... for update the row(s) you need to check, then insert your data and commit. Of course this can also be done in a PL/SQL Procedure called by your application.
grey
that wouldn't be especially bad, but our application language sucks balls, so it's not easy to seperate log like that out from the application itself, and this is something that needs to be interacted with from multiple places
or, maybe not, I guess it only has to be veiwed from multiple places
maybe someone can recommend some reading or examples? I'm not a total newb but this is something that's got me stumped, but I'm sure someone else has solved it already,
one sec while I type
I need to track the structure of a team as it changes over time, There are multiple Agents who report to one of several team leaders in different departments, who each report to the single director of that dept.
my current solution is a position table, that stores the name of the dept, the type of position, and a recursive FK indicating reporting structure, then a seperate 'history' table that uses a composite key to relate a 'person' to a position, and the dates they started/ended in that position
drdimitri
And which condition should be checked by your "custom" constraint?
grey
I'm trying to add a recursive relationship to that history table, to be parallel to the position table, as otherwise one cannot determine who which PARTICULAR team lead leader an agent was reporting to at that time without that (There could be 4-5 people in the same 'position' at the same time)
what I need to check, is that when a row is added or updated, the 'parent' is still active (ie, has not been marked as having an end-date)
that's what the check is for, but I'm thinking maybe the overall structure is flawed, and the start/end dates, as well as the recursive heirarchy should be seperated away from the history, but I can't think of how to do that in a somewhat graceful way
mtst
anyone know 1nf, 2nf, 3nf normalform?
ratcheer
mtst: "The key, the whole key, and nothing but the key."
TheBonsai
:)
grey
heh, I like that
ratcheer
It is old. I think it was said by either Codd or Date.
kaylee_work
if I want to pull the count for rows where activity_date is > 30 days and > 60 and >90 days... what's the best way to do that?
is there an easy way to get all 3 counts with one query?
alester
kaylee_work: which three counts, I just came in
what are the three queries?
kaylee_work
(1:51:51 PM) kaylee_work: if I want to pull the count for rows where activity_date is > 30 days and > 60 and >90 days... what's the best way to do that?
alester
I'm pretty ure you can do count(activity_date>30), count(activity_date>60) etc
kaylee_work
oh, I'll give that a try
thanks!
alester
or rather, count(activity_date < now()-30)
because you want to compare activity_date to a constant that can be calculated once.
otherwise, you'll be doing date math on every row.
kaylee_work
using " now() " or sysdate?
alester
yeah, sysdate
I'm used to Postgres]
ratcheer
mtst: 1) Table represents a relation and has no repeating groups. 2) No non-prime attribute in a table is functionally dependent on a part (subset) of the candidate key. 3) Every non-prime attribute is non-transitively dependent on every key of the table. From http://en.wikipedia.org/wiki/Normal_forms
alester
havne't done Oracle in years, just now getting back into it for a project
kaylee_work
alester: doesn't look like it wants it
alester
details?
kaylee_work
ORA-00907: missing right parenthesis
alester
hmm
kaylee_work
it doesn't like the <
alester
ight
right
here ya go
http://www.dba-oracle.com/t_sql_patterns_cond_summation.htm
so on my example
select sum( case when dateupdated < sysdate - 730 then 1 else 0 end ) from currency;
and then you can have multiple sum()s
kaylee_work
oooh
interesting
ooh, that seems to maybe be working
thanks!!
alester
you're welcome
let's see your query that you're using.
« prev next »