logs archiveIRC Archive / Freenode / #oracle / 2010 / January / 4 / 1
dagnachew
hi all
hihihihi
jagadish
How to know if one of the privileged user has been changed some of the rows in some tables in Database?
Dormir : How to know if one of the privileged user has been changed some of the rows in some tables in Database?
Dormir
turn on auditing or create a trigger to track changes
or possibly logminer
jagadish
ok thank you
jirumeku
hello
stede
hi .. im new to pl/sql-functions in oracle and have a problem/question with a task i have to solve: http://pgsql.privatepaste.com/d3f9e2eb30 thanks
Mike_Knight
stede: functions must to return some value of type defined in function header
"FUNCTION RFM_SELECT_PARAM(param_name in varchar2) RETURN ????" You have to define some type insteand of ???? for example "RETURN NUMBER"
stede
thats why I write in comment that the code ist not compilable ;) the ??? are placeholders for something i dont know. because the return type depends on "param_name" .. further on the used cols for calculation
Mike_Knight
If you wanna to return one value - don't use PIPELINED. You should use PIPELINED when you want to return table wich you may to use in 'from clause' of select statement
         

stede
but that is a problem i can solve by my self
Mike_Knight: yea ... i want to use it on FROM clause
Mike_Knight
stede: it is far from template of pipelined function. Do you have PL/SQL Guide?
stede
Mike_Knight: i using the quides and examples on oracle.com
Mike_Knight: it's not clear to me how i can work with the column-values from the data i must get from the parent select-clause
Mike_Knight: that must be like this: "SELECT RFM_SELECT_PARAM('audioerror') FROM rfm_meas WHERE ..." (for example) ?
Mike_Knight: and how can i use the rows which are affected from the where-clause inside the function
Mike_Knight
stede: I think I don't understand you ) Here is an example of pipelined function : http://www.adp-gmbh.ch/ora/plsql/pipeline.html
select * from table(pipelined_function(param1, param2));
stede
Mike_Knight: ok, so that is i don't want ;) i want it like above "SELECT FUNC_NAME() FROM rfm_meas WHERE ..." thats not possible? if i use it like u mean, i always hafe to get all data of a row (because of SELECT *)? is that a god idea for the performance?
that might be several houndred MBs
oops ... ok .. now i get it
with select * i select the returned values from the function only !?
Mike_Knight: better like this http://pgsql.privatepaste.com/401a863b90 ? now I need "only" a suitable loop from line 6-11 and a working sql-select-clause (line 8)
Mike_Knight: ... i switched the order of into/from ... the problem is: Error(2,10): PLS-00630: pipelined functions must have a supported collection return type
Mike_Knight: okay ... i made it without pipelined-function
Mike_Knight: no, that dont work either o_O
because the funtion dont uses only the rows affected by the parent sql-query
i'll paste it
here we go: http://pgsql.privatepaste.com/c6d648f515
i have to give the (explicit) cursor from the select-statement as a parameter to the function? no other way?
i'm still stuck with that problem ... don't find a solution after 2hours o_O .. will have a lunch now ... feel free to help ;)
krz
hi
anyone here good with database design?
ive got an ERD and looking for improvvements
stede
if y function parameters contains a string (varchar), how can i use this string inside the function? e.g. foo(tab_name varchar2) ..... select * from tab_name ... somethign like this
srm
Hi, I'm reading through the docs about index creation. I'm stuck at the 'Creating a large index'
...section where one should consider creating a bigger temp tablespace. What is considered a "(extremely) large index" ?
I know that my index will be of apr. 200mb big.
I'm not really experienced with this kind of DBA stuff.
hali
large in relation to your temp tablespace
you need about 3x the size of the index available as temp tablespace
mfilipe
Is there any way to fix this "SP2-0027: Input is too long (> 2499 characters) - line ignored"? I want run a sql file, but I can't because your sql is very big.
is a simple sql but it is big
FANDER
what's wrong with my query: "Select employee_ID, First_Name, Last_Name Into myID, Fname, Lname From employees Where employee_id = 100;" ?
stede
nayone has a solution: i need a function which calculates a result from 2 colums so i can use the function in a select?!
or any step by step tutorial which may help?
usn
what do you need exactly?
stede
e.g. select func() from tblname where id>a and id<b; but func() should tale 2 columns and calculate somethign with that values for each row, which are selected by the where-clause ... i tried do get something to work which can do that (teh last 7 hours o-O)
i dont know how i can work with the selected rows inside the function
usn: something like this: http://pgsql.privatepaste.com/59b84ce7e5
usn
.
why has it to be a function?
Why not in SQL?
stede
stede
usn: because i want to have the calculation in a function - it is highly possible that this calculation changes and i dont want to change the code at several places
usn: even the colum-values i need for calculation can vary (if the spezification changes), that is why i want that in a extra function
usn: if not i can use eg: select a/b*1.246 from rfm_meas where ...
usn
can't you give the parameters to the function
?
stede
usn: and i dont want to select * because the select result might be big. there are 61 columns and in regular cases a few thousand rows are affected
usn
e.g. select rfm_select_param(a,b) from rfm_meas where box_id=4;
stede
usn: then i have to change any code-position if i need another parameter
if i do it like that i dont need a funtion at all
         

usn
you don't want to change the code, can you change the function if the columns change?
stede
usn: and i want to add some more calculatable values as the one
sure .. and i hafe to change it ONE time and be sure taht everywhere the right value is used
usn
okay
Dormir
FANDER: what are you trying to do with that select statement?
srm
thanks hali for index-size clarification
stede
usn: i tried it with a piplined function too, but i dont get that to work - because i didn't somethign like that before and found no good/easy tutorial
usn: g2g now .. maybe tomorrow ... thanks so far, byby
FANDER
problem solved
guys what's wrong with: "Declare depID NUMBER(4,0) := ( Select department_id From departments Where department_id = 20 );" ?
I'm using PLSQL
ah sorry problem sovled
Abs_T
hello
I installed Oracle 10g. Can I modify the tables via my navigator without using SQL+ commandes line ?
Dormir
my navigator? what's that?
do you mean sql navigator?
Abs_T
Dormir firefox !
by connecting to http://127.0.0.1:8080/apex/
Dormir I mean Web Browser
Dormir
oh. you should be able to
haven't played around with APEX much
Abs_T
how ? where ? I can't find it
Dormir
you want to modify tables without entering SQL? just point and click?
Abs_T
yes Dormir
Dormir
do you know how to modify them with SQL?
Abs_T
yes Dormir
Dormir
use the object browser
one of the choices under SQL Workshop
Abs_T
thanks Dormir
winegoddess
how can i check the version of oracle to see what is being used ?
a sql command?
dagnachew
hello winegoddess how are you ?
TheBonsai
winegoddess: check v$banner or, if you're interested in a machine readable thing, DESC v$instance;
winegoddess
dagnachew Hello
TheBonsai Thank you!!! :)
TheBonsai
winegoddess: welcome
dagnachew
where can I get Oracle Forms stand alone ?
is it possible to install forms without weblogic ?
winegoddess
when youinstall oracle, what is installed if you choose create starter database?
zasz
the database gets created after the software installation options have been chosen
in other words, after you choose standard or enterprise, and after you've customized the install or just did the default options
you can check what components are installed with dba_registry after the fact
dagnachew
winegoddess, dbca
hello zasz
ezyang
I'm having difficulty logging into oracle using sql*plus as well as the web interface using the root password I specified during configuration.
This is my second reinstall, going on third.
dagnachew
ezyang, hello my frein d
I tried 6 or more time and 5 days !!!!!!!!!!!!
finaly I installed
ezyang
dagnachew: Did you do anything different?
dagnachew
it was my dns that caused me errors because I kept getting ora 12547 connection lost
ezyang
hmm, that's not the error I'm getting.
dagnachew
I install on archlinux
so far installation of oracle 11g release 1 succes , connection to database with sql-developer success
ezyang, on what platform are you installing ?
ezyang
CentOS
dagnachew
it's should more easier tham me since it's based on redhat
ezyang, you have everything oracle user and groups and permissions and offcourase not the least lis right
ezyang, paste output of sqlplus / as sysdba
ezyang
I was under the impression that you only needed that for SElinux enabled installs?
Can do, after I finish this install
dagnachew
ezyang, when you mean root password is the root password of oracle ?
ezyang
as in, password for sys/system
dagnachew
ezyang, what do you get when you do sqlplus / as sysdba ?
ezyang, you have a router ? if so is port 1521 opened
?
ezyang
I'm running sqlplus from the same machine
I've also tried using apex web interface
it's configuring, I'll give you info in a sec.
Aha!
It looks like I was using an invalid password character, and it wasn't telling me about it.
dagnachew
ok
next »