logs archiveIRC Archive / Freenode / #oracle / 2010 / March / 17 / 1
Suva
Hey!
Let's say I have a set of numbers in variable "Numbers" and I want to do: select * from blaah where id in Numbers; how do I do it? :)
usn_work
use &numbers
at least for sqlplus you can use the & and && variables
Suva
Nah, that doesn't work
What I mean, is that I already have defined some collection in PL/SQL
usn_work
I worked with PL/SQL variables in http://www.usn-it.de/index.php/2010/03/08/oracle-how-to-create-a-good-execution-plan/
is it that what you mean?
Suva
I know how to create variables and stuff like that
Nested table for example, which contains a list of ID's for example (1, 2, 3, 4)
What I do want to do is create a SQL clause that does SELECT * FROM blaah WHERE ID in thatArrayVariable;
usn_work
I don't thing that collections are directly supported for a query
Suva
But PL/SQL apparently doesn't let me use IN with collection variable
Yeah
But how should I solve the issue?
usn_work
Does that help? http://dautkhanov.blogspot.com/2007/03/select-where-id-in-plsql-array.html
Suva
Sure does :D
Thanks
And I was googling for ages, using exact same words as in that title :S
         

usn_work
np
MadLemz
http://pastebin.com/bBGZERqk <-- simplified version of a query that I'm hoping to do faster with a Join of some sort rather than a Correlated Subquery. Take a look if you have a minute please. Thanks!
usn_work
MadLemz, please provide a queryplan generated like http://www.usn-it.de/index.php/2010/03/08/oracle-how-to-create-a-good-execution-plan/
Because "fast" is floating, and no general term
And you are ending up with WHERE 0=0 or WHERE 50=0 in the end - is that what you want?
MadLemz
ah, doh. let me clean that up a little bit...
usn_work
okay ...
MadLemz
http://pastebin.com/Ds4nj9eL usn_work: that should help clear things up. I want to select all personID's who have a birthday today and don't have a row in PERSON_CARD with a sentdate = today
seems like correlated subquery can't be fastest way, but can't figure out how to do it with a join when i want to see if it matched NO records in that table
usn_work
How do you know "can't be fastest way"? Did you look at the execution plan?
MadLemz
I'm writing down the times it takes :P I tried to use that snippet you sent me, modified for my query, and it errored on the first line. I probably don't have the right permissions to do it. I don't know :(
usn_work
then use "explain plan for"
anything else is guesswork and useless. If somebody would provide a join, how would you compare? By benchmarking it?
MadLemz
that was my intent, yes.
usn_work
You need to know what the DB does with your query, otherwise you can stop thinking
MadLemz
Ok, it gives back "Explained." Now I just need to find where it spat out that explanation :P
usn_work
execute dbms_xplan.display()
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); to be exact
should provide nice ASCII art
MadLemz
thanks for the exact, was struggling
1 - filter( (SELECT COUNT(*) FROM "PERSON_CARD" "PERSON_CARD" WHERE PERSON_CARD"."PERSONID"=:B1)=0) 4 - filter("PERSON_CARD"."PERSONID"=:B1)
18 rows selected
I have no idea how to do anything with that, though, of course :P
phimic
hi all
is it a problem creating a raw device on top of lvm for oracle-rac asm?
usn_work
phimic, its PITA ;)
Why don't you do with ASM what LVM does?
teite
what does "STARTUP UPGRADE" actually do?
usn_work
it allows to change some dictionary things
phimic
usn_work: my problem is, that i cannot create more than 15 partitions
usn_work
most of the basic packages are locked during normal operation
         

phimic
usn_work: and i need more raw partitions
usn_work
phimic, how does LVM solve that?
and why do you subpartition volumes for ASM?
phimic
usn_work: i created partitions and bind them as raw partitions
usn_work
but ...
why don#t you add the volume to ASM? Do you need 15+ diskgroups?
phimic
usn_work: my problem is now that i have only one partition left and i dont want to create single huge partition
usn_work: all my raw paritions are 8 GB big
usn_work
How much diskgroups do you have?
And how many disks each?
phimic
usn_work: i have one diskgroup DATA with 8 raw partitions assigned
usn_work: and a diskgroup FRA with 2 raw paritions
usn_work: finally the orc and the voting
teite
usn_work: thx for the info (regarding startup upgrade) ;)
phimic: iirc you dont need to use raw devices for oracle asm
since 10.2.0.3 at least
phimic
teite: i just want to keep that setup on this box
teite
i see
phimic
teite: but i remember that dbca see only raw paritions as Datagroup disks canidates
teite
maybe because its the old version after a fresh install
phimic
teite: i upgraded to 10.2.0.4 last month
teite: take a look here http://img201.imageshack.us/i/oraasm.png/
dbca show only raw partitions for adding new disks to a diskgroup
teite
whats your asm_diskstring setting?
you need to include your devices in that parameter, like
asm_diskstring string /dev/oracle/asm/*
in that case we use udev with devmapper
phimic
asm_diskstring hast no value
teite
in the asm instance?
phimic
teite: yes
magmarules
Guys im trying to call a stored procedure but although im preaty sure the name is correct, and i have tryied it with schema prefix , it says its not declared. Could it be that my user doesnt actualy have access to the procedure? Also: "select * from user_objects where object_type = 'PROCEDURES';" doesnt have a single result.
How can i check if my user can execute the procedure? I dont have admin priveliges
This is the result of searching for my procedure: http://pastie.org/874023
how would you call this procedure?
RJarett
exec SSCML_MEDONE_API_PKG.REEMBOLSOS ;
magmarules
it says invalid statement =/
RJarett
well you need to know if it takes params or not
magmarules
RJarett, it does, i can pass it dummy ones =)
ORA-00900: invalid SQL statement
using { exec ... } says exec is not supported
RJarett
begin
SSCML_MEDONE_API_PKG.REEMBOLSOS yourparams;
end;
magmarules
ok
RJarett
magmarules
BEGIN
exec SSCML_MEDONE_API_PKG.REEMBOLSOS("1") ;
END; => java.lang.IllegalArgumentException: No SQL selected for execution. =(
without exec: java.lang.IllegalArgumentException: No SQL selected for execution.
RJarett
your plsql is bad
magmarules
yup i know =)
im used to mysql, sqlserver
RJarett
forget all about those
magmarules
where i use the call =)
RJarett
they arent real databases. and plsql isnt tsql
magmarules
call procedure(args)
RJarett
im sure if you are used to those, your plsql is bad and didnt compile right
magmarules
proabably
im just trying to get to a "wrong arguments error" or something
something that at least tells me i reached the procedure
RJarett
what did you get when you compiled the procedure?
magmarules
RJarett, compiled? when i created it? It wasnt me =/ its already there, just trying to call it =(
RJarett
are you in with sqlplus or a better tool?
magmarules
squirel =/
Its not bad so far it has done the job, just cant call the procedure =/
ohh its alive
ORA-01400: cannot insert NULL into ("SSCML"."SSCML_ERROS_INTERFACE"."INT_GROUP_ID")
something diferent =)
im guessing this must be an error in the stored procedure side =)
RJarett
id say its user error
magmarules
not the query right? the query seems to be calling ok =)
i must be passing it a wrong argument =)
RJarett
calling plsql is not a query
the plsql may contain 0 or more queries
i do not know. thats your plsql
magmarules
Ty RJarett, its strange everything i read says your suppose to use "call" =)
RJarett
no it doesnt
ech0s7
hi
how can i get a list of table of another user ?
with "SELECT * FROM TAB", i get the list of my tables
i have tried with
SELECT * FROM OTHERUSER.TAB
but doesn't works
anyone can help me ?
RJarett
select table_name from dba_tables
ech0s7
RJarett, i get view doesn't exists
RJarett
because you arent a dba
try all_tables
but if you dont have perms on them, you cant see them
ech0s7
ok this works
steelnwool
Hi
the backup/recovery guide and reference for 11g, make nary a reference to expdp. Is this not a preferred utility any more?
or maybe this just isn't the right document.. :)
hokatichenci
steelnwool: RMAN?
RJarett
expdp is not a backup method
steelnwool
RJarett: its a migration method then?
why would you say its not a backup method?
RJarett
because its not
its data transport
and nothing else
steelnwool
so if you make a full dump every night, thats not good?
RJarett
no
steelnwool
can i turn up your verbosity?
Rjaret ++v
RJarett
its not ALL the data in the system
steelnwool
gotcha. so use it for migrating databases, but other wise use the scheduled nice built in stuff + flashback recovery adn archive logs?
RJarett
when restored, did may not be the same format, nor are small stuff like plans, outlines, execution paths etc restored
steelnwool
[once again, i've got a week with oracle, came from postgres and inherited this whole situation[]
RJarett
well anything with verifiable backups
doing basic backups w/ oracle can be incredibly easy to get the basic stuff done
but its incredibly hard to do it right
steelnwool
aye. the setup i inheted has a backup.sh script that does expdp nightly.. but in like chapter 9 of "2 day dba" it talks about about setting up OEM to do backups. so I've no idea what the last guy was thinking at all
actually i know all the things he wasn't thiniking.
RJarett
dont touch oem
#1 i doubt you licensed the managment packs
#2, dont touch it
steelnwool
why not?
RJarett
expensive. and do you want a gui doing your fu*king work for you?
and if it fails?
steelnwool
hey simmer down man, i'm just having a conversation. no need for the IRC as****e hat.
all of the documentation I've read so far, references OEM. it seemed like a solid tool, i'm sorry if i don't have your oracle experience, but i'm just leaning. take a flying fu*k. as****e.
avaia
I said GOOD DAY
RJarett
did you pay a few thousand $ per cpu per management pack to license it?
steelnwool
oddly , the topic of licensing it separatly never came up. It "just works" in the 11g EE i downloaded to use on a non production machine.
now jump all over me for not knowing that.
RJarett
exactly
and are you licensed for EE?
steelnwool
yes.
Well, I hope we get off oracle onto postgres soon, so i don't have to be an oracle admin, and adopt an arrogant di*khead persona. have a good day.
RJarett
hes total fail
DiscordianUK
Ooh err
« prev next »