RE: rewriting query without using UNION
select e.ID, e.NAME, d.DEPTNAME from EMP e, DEPT d where e.DEPTNO = d.DEPTNO and (e.NAME = 'JOSE' or d.DEPTNO = 50); -Original Message- Sent: Wednesday, October 29, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Hi, I wonder if there's a better way of writing the query below. Basically, I would like to return employee records where employee name='JOSE' + all employees in deptno=50. My query can have multiple 'OR' criterias where the next criteria maybe returning all employees with salary6 in addition to the above two criterias. Building the query with multiple UNIONs will definitely degrade the query performance. Is there a better way of rewriting the query? Thanks! linda select e.id, e.name, d.deptname from emp e, dept d where e.deptno=d.deptno and e.name='JOSE' union select e.id, e.name, d.deptname from emp e, dept d where e.deptno=d.deptno and d.deptno=50; _ Want to check if your PC is virus-infected? Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Linda Wang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to call unix shell scripts from 'C'?
Unix shell scripts are interpreted by a program, typically either a command shell (C-shell, bash, sh, tcsh, etc.) or by a scripting language program (perl, python, ruby, etc.) For a shell script to be executed from a shell's command line, the shell script needs to have the execute bit set (see man page for chmod(1)) and the first line in the script will be in the form of: #!/bin/sh The important thing is the #! token which tells the shell which interpreter is appropriate for executing the script; in the example above, the intepreter is /bin/sh. So, in answer to your question, your C program should optionally check for the execute bit, fopen() the file and perform a fgets() to read a line, the C program should then parse the line to make sure that it conforms to the #! convention. If it finds a path to an interpreter, then your C program will probably need to fork() itself, and then use one of the exec() variants, passing it the file path to the interpreter as the name of the program to execute, and also handing it the path to the script as a command-line argument. Read the man pages for the particular interpreter to determine if there are any special switches that will have to be passed in order to introduce a script name for the interpreter to automatically run and exit instead of going into interactive mode. If any such switches are needed, you will have to pack it into the exec() call as well. -Original Message- Sent: Wednesday, October 22, 2003 11:45 AM To: Multiple recipients of list ORACLE-L The unix and C forums are pretty inactive. Hope its ok to ask this here. Anyone know how to do this? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to get the source code of the trigger without using any t
Select from DBA/ALL/USER_SOURCE; if the source code looks like a bunch of garbage, then it means the source code has been wrapped, which is Oracle's facility of obfuscate it so that you can't read it. -Original Message- Sent: Friday, September 05, 2003 11:35 AM To: Multiple recipients of list ORACLE-L party tools? Hi List, Could you please help me to get the source code of the trigger without using any third party tools? Thanks in advance, Raj __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle DBA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
What is _NEXT_OBJECT
I recently had to install a couple of things to my 817 database so that I could get UTL_SMTP. I ran ORACLE\javavm\install\initjvm.sql and ORACLE\rdbms\admin\initplsj.sql as SYS and then followed up by use ALTER objectType to recompile all invalid objects. The only thing I have left is a object of name _NEXT_OBJECT of the type NEXT OBJECT, which has an N/A status. Googling for this gave me http://www.experts-exchange.com/Databases/Oracle/Q_20082312.html which was someone who was having some problems with his database and his _NEXT_OBJECT. I, however, don't seem to be suffering any visible ill-effects so far. What is this thing, and how do I fix it (and I don't really want to do a re-imprt per the experts-exchange web page.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Limits on PL/SQL block?
I'm seeing a PLS-00123 program too large error. Oracle's documentation says that the actual limit on the size of the block is dependant on the mix of statements in the PL/SQL block. Does anyone know how Oracle determines this limit? Is it a pure size of PL/SQL block in bytes, or is it number of unique statements in the block, or is it dependant on how much redo that the block may generate? (I know the recommended solution is to modularize and break up the statements into multiple blocks, but I'd like to know what are the limits to give us a better idea of determining where to break up the blocks dynamically, so the answer I'm really looking for is what is the limit or how Oracle determines the size limit, and not workarounds, which we're exploring anyway.) TIA ..Rudy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: tables and views
Title: Message All columns from view and tables appear in ALL_TAB_COLUMNS; to differentiate the source of the columns, you need to join to ALL_TABLES for the table columns, or to ALL_VIEWS for view columns, or if you need to know what type of object the column came from (instead of something like list all the table columns, or list all the view columns) then you should join to ALL_OBJECTS. -Original Message-From: AK [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:19 PMTo: Multiple recipients of list ORACLE-LSubject: tables and views How to diiferentiate views and tables in all_tables and all_tab_columns . which column and what criteria can return only tables ?? -ak
RE: Job to run first Wednesday
What an interesting question. Alright, DBMS_JOB needs a function which returns the date/time on which the job will next run. Part of your function is to take consider the next 31 days (in case the current run of the job is somewhere random in the month and not on the first Wednesday of the current month) and identify the very next Wednesday, that happens to occur in the first 7 days of the month. With that in mind: select run_date from (select to_char(sysdate + rownum, 'dd-Mon-') run_date, to_char(sysdate + rownum, 'w') week_of_month, to_char(sysdate + rownum, 'd') day_of_week from all_objects where rownum = 31) where week_of_month = 1 and day_of_week = 4; The inline makes use of a table/view that more or less is assumed to contain at least 31 objects in order to generate an offset to represent each day for the next month or so, which is added to the current sysdate. The inline view returns the date, week of month, and day of week for the upcoming 31 days (but necessarily excludes today if today happens to be the first Wed of this month, otherwise your job will always identify today as being the next scheduled run time, and never manage to find next month's first Wed). The enclosing query then takes all the date information for the upcoming week, and limits it to the single record that occurs within the first week of the month, and is also the fourth day of the week (which is Wed.) You may need to trunc() the run_date and do some other arithmatic to nail down a specific time of day for that job to run. Go have fun. ...Rudy -Original Message- Sent: Tuesday, July 22, 2003 3:14 PM To: Multiple recipients of list ORACLE-L Greetings, How can I set the interval in my dbms job to have it run on the first Wednesday of every month? Is this even possible? I have been trying to noodle it thru for a week to no avail. tia, Josh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Josh Collier INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Job to run first Wednesday
: Subject:RE: Job to run first Wednesday Josh, With the following functions, you could probably get it to work: select next_day(last_Day(sysdate),'WED') from dual This (today) returns Wed, August 6th. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 22, 2003 3:14 PM To: Multiple recipients of list ORACLE-L Greetings, How can I set the interval in my dbms job to have it run on the first Wednesday of every month? Is this even possible? I have been trying to noodle it thru for a week to no avail. tia, Josh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SHOW ERRORS not working
Not sure how SHOW ERRORS is implemented in SQLPlus, but it may only be looking at USER_ERRORS intead of ALL_ERRORS. So if your developer is altering another schema's procedure (via the alter ANY procedure privilege) those errors will not show up in his USER_ERRORS. BTW, this is just my guess on this; haven't tried it out myself. -Original Message- Sent: Monday, July 21, 2003 11:45 AM To: Multiple recipients of list ORACLE-L I have a developer that has ALTER ANY PROCEDURE rights. He changes a procedure, then compiles it and it says there are errors. But when he does a SHOW ERRORS in SQPlus, it shows nothing. If I do the same thing I can see the errors from the compiled procedure. I assume this is a permissions thing but have not been able to figure this one out. Anyone have any ideas on this? Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: need execute immediate and script help
Your using v_var1,v_var2 shouldn't be appended to the string that represents the dynamic SQL to execute; the using... is part of the syntac for execute immediate. An Oracle error message being what they are, they don't always highlight the correct cause of fault, but will identify some other error located close to where the real fault lies. For your solution, remove the || that occurs after :2 It is also unlikely that you can select * into TOTAL, which has been declared as a NUMBER. Consider count(*) perhaps? -Original Message- Sent: Monday, July 21, 2003 5:00 PM To: Multiple recipients of list ORACLE-L Im calling a script that uses dynamic sql. Im passing in a value as well. I keep getting SP2-0552: Bind variable 2 not declared. Here is a code snipped declare v_var1 Varchar2(30) := 'TEST'; v_var2 VARCHAR2(30) := '1'; total number; begin execute immediate ' Select * ' || ' from user_objects '|| ' where object_name = :1 '|| ' or object_name = :2 '|| using v_var1,v_var2 into total; end; / I call it as follows: @script HELLO -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: need help with execute immediate from a script
Your using v_var1,v_var2 shouldn't be appended to the string that represents the dynamic SQL to execute; the using... is part of the syntac for execute immediate. An Oracle error message being what they are, they don't always highlight the correct cause of fault, but will identify some other error located close to where the real fault lies. For your solution, remove the || that occurs after :2 It is also unlikely that you can select * into TOTAL, which has been declared as a NUMBER. Consider count(*) perhaps? -Original Message- Sent: Monday, July 21, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Im calling a script that uses dynamic sql. Im passing in a value as well. I keep getting SP2-0552: Bind variable 2 not declared. Here is a code snipped declare v_var1 Varchar2(30) := 'TEST'; v_var2 VARCHAR2(30) := '1'; total number; begin execute immediate ' Select * ' || ' from user_objects '|| ' where object_name = :1 '|| ' or object_name = :2 '|| using v_var1,v_var2 into total; end; / I call it as follows: @script HELLO -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: shell question
cat(1) -Original Message- Sent: Thursday, July 17, 2003 3:09 PM To: Multiple recipients of list ORACLE-L hi all , how can I read a text file and print line by line. Rgds. Arslan. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: security without using different usernames
Title: Message Consider DBMS_RLS for row level security (or as in some other Oracle marketing-speak has been called the Virtual Private Database.) Essentially: you create a package/stored procedure function that returns a predicate that can be plugged into a WHERE clause; in your case, in your package instantiation code, you can go off and look at V$SESSION, and then store some magic value in a package variable; your predicate function will then use this package variable and return some comparison that would yield TRUE or FALSE (in the most simple case, you can have it return "1 = 1" or "1 = 0".) Now, use DBMS_RLS to add a security policy on the table, and give this security policy the name of your stored procedure/function. Here's how it works: when a user (any user) performs any DMLs against the table, Oracle looks at the security policy to find the name of the stored procedure/function; Oracle will then evaluate the stored procedure/function and apply the value of the stored procedure/function to the criteria for accessing the table. Voila, you now can instantly dictate at the table-level what records users will have access to. ...Rudy -Original Message-From: Ryan [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 6:29 PMTo: Multiple recipients of list ORACLE-LSubject: security without using different usernames I know this is terrible design, but the GUI was created by a software engineering group that is seperate from the database group. Its not scalable. So Im trying to come up with a more scalable method. I have no power to change their gui. It rides on the database. I have to live with it. This is not a high enough transaction database to warrant seperate instances. We have a variety of customers. Each of them has their own versions of data. However, the schema is exactly the same. These tables can get huge, so we dont want to throw them all into the same schema. Right now, due to the fact that the GUI has a series of logins that are the same across clients, each client has its own instance. This isnt very scalable as we get more business. We have to create another instance and ingest data to it. Id like to find a way to get all the clients in the same instance with just different schemas and tablespaces. One thing I may have control over would be to slightly rename the executable. If you check v$session, in a client-server application the name of the product connecting to the database is recording. I can handle security based off of that. My question is what would be the best way? Cant do synonyms for this since its the same login. I think I saw somewhere that there is a session based 'set' command where you can say use this schema. I think it was on asktom and in reference to a question about public synonyms. I cant find it. Anyone know it? Also is it viable to base a context off of what is in v$sesion with a logon trigger? How would I 'redirect' all queries to a specific schema? To stress, I cant change the application. Different group with different skillsets.Any suggestions?
RE: ROWNUM is driving me nuts - queries suggested produced no res
Think of it this way: when Oracle builds a result set, it goes into the table and tries to qualify the records in the table by evaluating the where clause; if the record does not qualify, the record will not appear in the result set, if the record does qualify, then the record will be returned in the result set. Once a record is qualified and is eligible to be included in the result set, Oracle assigns it a ROWNUM value, which starts at 1 and is contiguous. In your several examples below, the result set from the subquery is irrelevant because your enclosing query has ROWNUM 10, which is applied to the result set arising from your enclosing query. In a previous email, someone (who I don't remember) explained that you can't select a set of records from a result set unless your criteria for the ROWNUM starts-at and includes ROWNUM 1. (A recap: let's go with your ROWNUM 10: once a record is otherwise qualified, Oracle will assign it ROWNUM of 1 for the first record, then it will have to re-qualify the record base on ROWNUM 10; since the assigned ROWNUM is 1, it fails the ROWNUM 10 so this record is discarded. On the second record, Oracle will re-assign the ROWNUM as 1 because ROWNUMs are contiguous; it does the same evaluation of whether the assigned ROWNUM is greater than 10, and failing which, the record is again discarded. In fact, no records will pass the ROWNUM 10 because every previous record was discarded, so the assigned ROWNUM value is always 1 when Oracle tries to evaluate whether the record's ROWNUM 10) As a result, your reference to ROWNUM 10 will always produce zero records, irrespective of what the subquery may or may not return. The solution you probably want is to alias the ROWNUM column in the subquery, and then reference the aliased column name rather than the ROWNUM from the enclosing query: select r, GENDER from (select ROWNUM r, GENDER from EMP2 where ROWNUM = 20) where r 10; You can see the differences in the ROWNUM with this: select ROWNUM, -- this is from the enclosing query R, -- this is from the subquery GENDER from (select ROWNUM R, GENDER from EMP2 where ROWNUM = 20) where r 10; ..Rudy -Original Message- Sent: Wednesday, July 09, 2003 12:19 PM To: Multiple recipients of list ORACLE-L I definitely dont fully understand ROWNUM yet, and you guys so far provided more info than a couple of books by Oracle, that I have here. For a second I thought I'm beginning to get it, but the queries suggested produced no results... SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL SELECT r, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL SELECT r ROWNUM, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ROWNUM -- HOW ARE ROWS SELECTED?
Oracle does not guarantee the order in which records are retrieved (read in from the disk or data buffers) nor does Oracle guarantee the order in which records are presented to you unless you specify an ORDER BY. The ROWNUM is assigned after Oracle has selected the record to appear in the result set, however the initial selection of the record is in whatever order Oracle deems to be most efficient in retrieving your records, and the ROWNUM is assigned after the record has passed the WHERE clause evaluation, and as a last step, the ORDER BY is applied to sort the order of presentation of the records to you. ..Rudy -Original Message- Sent: Wednesday, July 09, 2003 12:45 PM To: Multiple recipients of list ORACLE-L Its obvious I hadnt fully understood ROWNUM yet, as you see we are learning bits and pieces as we go along. Help me out here, will you? Talk to me like I'm a 10-year old, its ok. MaryAnn, the best way to understand rownum is to do the following: SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) You will quickly see that, no matter how you order the result set, the first record returned is rownum #1, second is rownum #2 etc. The rownum value is assigned as rows are RETURNED or DISPLAYED, not as they are selected. Fine, the rows are numbered as returned or displayed(not selected). BUT THEN HOW ARE THEY SELECTED? My emp2 table has 1+ rows in it, how are only 20 selected? Based on what criteria? How do I get 20 back? I want to understand this first. Then, once I get these 20 back, then fine, they are numbered starting from 1, that part I kind of figured it out, or so I think. The part I dont get, is HOW ARE THEY SELECTED? thx maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: unique constraint violation problem
Unique constraints can be disabled and enabled; there is no facility to disable a unique index besides dropping the unique index, which means that when you want to re-enable a unique index, you will have to rebuild it again, which means that you would need to know what columns make up the index. When a constraint is disabled, it will still show up in USER_CONSTRAINTS, letting you know that perhaps something may need to be fixed; when an index is dropped, unless you have another mechanism for tracking indexes, you won't know that you're missing an index. -Original Message- Sent: Tuesday, July 08, 2003 10:09 AM To: Multiple recipients of list ORACLE-L Thanks for all of you who replied the message. I apologize for not explaining the problem clearer. Actually, the error was reported at 13:35:13pm from the application. There is a column called date_created in the table that records what time the record was created. The value of date_created for the record that caused error message is the same as reported from application. That's why I said that the record was inserted into the table successfully. I know Oracle recommends that we do not explicitly define unique indexes on tables. Why? Anna From: Jacques Kilchoer [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: unique constraint violation problem Date: Mon, 07 Jul 2003 12:59:25 -0800 My explanation would be that the record was NOT inserted successfully into the table. Proof of that would be that there are no duplicate records in the table. -Original Message- From: Anna Li [mailto:[EMAIL PROTECTED] I created a unique index on a table called REGISTRATION_K, but no unique constraint. Last week when the application tried to insert a record into the table, we got following error in the log file: Oracle::st execute failed: ORA-1: unique constraint (REGISTRATION_K) violated However, the record was inserted into the table successfully. There are no duplicate records in the table. Could anyone explain why? Any input will be highly appreciated. Thanks in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anna Li INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which one is faster ???
Well, without knowing how Oracle implemented their PL/SQL engine, and without empirical data (which truthfully I have not bothered to collect) this is a hard question to answer. However, with my little knowledge of compiler design, I'll give you my guess as something to think about and you can run with it. PL/SQL is basically an interpreted language, which means it doesn't get that directly close to the assembly/machine code like a traditional compiled language does. For this reason, I don't think Oracle is bothering to optimize the PL/SQL engine that heavily into mapping variables onto registers; I'll assume that it's all basically allocated from Oracle's heap that it gets from the OS. Since you are contemplating a procedure versus a function where all things being equal, a resonable assumption would be that your procedure must have at least 1 OUT parameter that would take the place of the returned value from the function. Now here, it depends largely on how Oracle has implemented their PL/SQL parameter passing and management of PL/SQL stack. For a function, typically in other languages the return value is computed and stored on the stack or register, and after the function terminates, the return value is copied over to the variable that is on the left-hand-side of the assignment operator. For a procedure with a pass-by-reference variable, the original variable is accessed (since it's pass-by-reference) so there would be no need for a final copy operation. In this scenario, a procedure would be faster depending on the size of the return data (which dictates how much data would need to be copied.) But that is likely to be true ONLY in post 8i databases when the pass-by-reference parameter is marked as NOCOPY; if the pass-by-reference parameter is not NOCOPY, Oracle's parameter passing scheme would be copy-in and copy-out even for OUT parameters. -Original Message- Sent: Tuesday, July 08, 2003 6:04 AM To: Multiple recipients of list ORACLE-L Hi All, If I create a Database Procedure and a Database Function to achieve the same functionality, which one would be faster and why? TIA Regards Dhanvir -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which one is faster ???
of list ORACLE-L Hi All, If I create a Database Procedure and a Database Function to achieve the same functionality, which one would be faster and why? TIA Regards Dhanvir -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: fine grained access
FGA/VPD/RLS(*) is not a candidate solution to your problem. FGA via RLS is more for access control (who has permissions to see the records) rather than keeping track of who has exercised their access rights. Oracle's auditing may not provide the level of detail that you want, so your best bet is to write your own insert and update triggers, and possibly delete triggers that inserts into some other table too if you want to know who deleted what record when. (*) VPD=Virtual Private Database; RLS=Row Level Security. Basically the same stuff based on which Oracle blurb you happen to have read. -Original Message- Sent: Wednesday, July 02, 2003 9:21 AM To: Multiple recipients of list ORACLE-L Hi list We have a dozen authorisation tables who are visibible for end-users and changeable for application owners. What our security officer wants is that he can see who changed something in those tables (including timestamp and update statement) Is FGA the solution for this ? Anyone with a few tips/hints how to do this with FGA ? thanks vr.gr. Geo Kor Sr. System Engineer IDM Db RDW Voertuiginformatie en -toelating Ict Bedrijf Holland * [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: CASE in PL/SQL
If your Jul is 1st quarter, then your offset is should be 6 months instead of the 9 months in your email; or think of it another way, if you Jan is the beginning of the 3rd quarter, it is the beginning of the 2nd half of the year, and half a year is 6 months. With this in mind, you really don't need any decodes at all for just finding out your financial quarter. The following query suffices: select to_char(add_months(sysdate, 6), 'Q') from user_users; My best interpretation of your second query with the if-then is that if a date is in the second half of the calendar, you want to push it to the following year (or perhaps if the calendar date is from second half of last year, you want it reported as being in this year, which is kind of like saying fiscal year ending ) So, here's your solution (not exactly fast, but it's implemented competely with numeric functions provided by Oracle): Let's make Q = to_number(to_char(add_months(sysdate, 6), 'Q')) To figure out how much correction you need to each year based on the quarter the year appears in, add the following to the year: sign((sign(3 - Q) + 1) * sign(3 - Q)) -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 11:49 AM To: Multiple recipients of list ORACLE-L Hello ALL, I am trying to find quarter number from a given date . Here is the description Our Financial year runs from July thru June. So, Given any date between these dates I need to find 3 quarters(9 months) from it. July -Sep - 1st Quarter Oct -Dec - 2nd Quarter Jan -Mar - 3rd Quarter Apr -Jun - 4th Quarter I got this done using the following Select select decode(to_char(add_months(sysdate,9),'Q'), '1','3', '2','4', '3','1', '4','2', '') Decode function is used to change calender quarter to our Quarter. But I am unable to find the Year for that quarter. I was using Case statement to solve my problem,as below Select case when decode(to_char(add_months(sysdate,9),'Q'), '1','3', '2','4', '3','1', '4','2', '') 3 then to_number(to_char(main_rec.termination_date,''''))+1 else to_number(to_char(main_rec.termination_date,'''')) P.S The reason for 3 condition check in CASE Statement is, if a sysdate+9months falls in next Financial year , I need to change Year accordingly. But,this works only in SQL, in Procedures, i cannot do this using CASE Statement Can anybody give some ideas on how to approach this?. I have to use this in a cursor (not in the body of my procedure, so condition checking like If then else after fetching year is not possible) Any help would be greatly appreciated. Thanks, Surendra Tirumala Database Administrator Cabinet for Workforce Development Commonwealth of Kentucky -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: CASE in PL/SQL
then to_number(to_char(main_rec.termination_date,''''))+1 else to_number(to_char(main_rec.termination_date,'''')) P.S The reason for 3 condition check in CASE Statement is, if a sysdate+9months falls in next Financial year , I need to change Year accordingly. But,this works only in SQL, in Procedures, i cannot do this using CASE Statement Can anybody give some ideas on how to approach this?. I have to use this in a cursor (not in the body of my procedure, so condition checking like If then else after fetching year is not possible) Any help would be greatly appreciated. Thanks, Surendra Tirumala Database Administrator Cabinet for Workforce Development Commonwealth of Kentucky -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: retrieving BLOB
Well, heres a script that will dump out contents of blob that I used to test
things out. The script takes 3 arguments:
1) Name of table
2) Name of CLOB/BLOB field
3) ROWID of record with the LOB that you want, OR an asterisk * for all
records
Saving it into the file system is left as an exercise for the reader using
UTL_FILE (mostly because I don't actually have anything that deals with
UTL_FILE.)
YMMV
...Rudy
---Begin script---
exec dbms_output.disable;
set serveroutput off
set serveroutput on
exec dbms_output.enable(200);
set verify off
define BLOB_TABLE=1
define BLOB_FIELD=2
define BLOB_ROWID=3
declare
dType USER_TAB_COLUMNS.DATA_TYPE%type;
maxLen number;
readLennumber;
position number;
lastPosition number := 0;
offset number;
vBuffervarchar2(32767);
rBufferraw(32767);
needFinalCount number := 0;
begin
select DATA_TYPE
into dType
from USER_TAB_COLUMNS
where COLUMN_NAME = upper('BLOB_FIELD') and
TABLE_NAME = upper('BLOB_TABLE');
for tCursor in (
select t.*, ROWID ROW_ID
from BLOB_TABLE t
where 'BLOB_ROWID' = '*' or
ROWID = 'BLOB_ROWID')
loop
maxLen := 32767;
offset := 1;
dbms_output.put_line(' ');
dbms_output.put_line('RowID=' || tCursor.ROW_ID ||
' getLength()=' ||
dbms_lob.getlength(tCursor.BLOB_FIELD) || ':');
if (tCursor.BLOB_FIELD is not null and
nvl(dbms_lob.getlength(tCursor.BLOB_FIELD), 0) 0) then
begin
if (dType = 'CLOB') then
dbms_lob.read(tCursor.BLOB_FIELD, maxLen, offset, vBuffer);
dbms_output.put_line(substr(vBuffer, 1, 255));
readLen := maxLen;
elsif (dType = 'BLOB') then
dbms_lob.read(tCursor.BLOB_FIELD, maxLen, offset, rBuffer);
dbms_output.put_line(
substr(
utl_raw.cast_to_varchar2(
utl_raw.translate(rBuffer,
utl_raw.cast_to_raw(chr(0)),
utl_raw.cast_to_raw('?'))),
1, 255));
vBuffer := utl_raw.cast_to_varchar2(rBuffer);
readLen := utl_raw.length(rBuffer);
end if;
for position in 1..readLen
loop
dbms_output.put(
substr('000' ||
ascii(substr(vBuffer, position, 1)), -3, 3) || '
');
if (mod(position, 20) = 0) then
dbms_output.put_line(' : ' || position);
needFinalCount := 0;
else
needFinalCount := 1;
end if;
lastPosition := position;
end loop;
if (needFinalCount 0) then
dbms_output.put(' : ' || lastPosition);
end if;
dbms_output.put_line(' ');
exception
when others then
dbms_output.put_line(' ?Exception?');
end;
end if;
end loop;
end;
/
undefine 1
undefine 2
undefine 3
---End script---
-Original Message-
Sent: Wednesday, July 02, 2003 4:56 PM
To: Multiple recipients of list ORACLE-L
Hi,
I have a file called file1.doc stored in a BLOB column that I would like to
retrieve and save it to the filesystem. Can someone post a sample PLSQL code
or tell me where I can get the information.
Thanks!
elain
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rudy Zung
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Tech meetings
Title: Message Don't know if what we do in our shop here qualifies for your question or not, but our dev groups do "stand-up meetings." Relatively quick meetings (that can be done standing up; no meeting rooms required) that are usually finished in about 20 minutes. In the stand-ups, we get heads-up for things and specifications that might be coming down the pipeline from the product management and design side. We get a quick update on the state of deployment (what version has rolled into production, what version is in the QA pipeline) and what the next impending set of changes are about to get pushed onto the dev servers. If there's any potential "gotcha"s that have been experienced (especially on the coding front) they get publicized in the stand-ups as well. The main point of our stand-ups are to make sure that all the developers are relatively aware of the scheduling and direction of the product, and to highlight any programming difficulties and workarounds that might arise so that when different developers hit those gotchas, they'll already know that a solution might already. These stand-up meetings are basically within a development team/group. Project leads have their own meetings with the product management group. So essentially, the product manager has his own meetings; then the product manager has meetings with the dev project leads to convey what they want in the next iteration of the product; the project leads then present these to the dev group in a stand-up meeting. -Original Message-From: M.Godlewski [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 12:30 PMTo: Multiple recipients of list ORACLE-LSubject: Tech meetings List, Just wondering if your organization has tech meetings, and what is discussed and what the goals of the meetings are? I've been asked about this, and was wondering if there is a quick list out there any where. TIA
RE: Why didn't datafile autoextend?
Looks like the size of your datafile is already higher than your MAXBYTES. In additional to setting AUTOEXTEND ON, you should also give it a sufficient MAXSIZE for the datafile. Oracle will autoextend the datafile only up to the max size specified for the datafile. In your case, the max size for your datafile is a little less than 2 megs, which means that once your datafile is at 2 megs, Oracle will never AUTOextend it beyond the 2 meg size, although it can be extended beyond 2 meg by hand, as you have done. ...Rudy -Original Message- Sent: Monday, June 30, 2003 12:50 PM To: Multiple recipients of list ORACLE-L This morning I started getting ORA-1654: unable to extend index CIMXDBIN.CIMX_SESSION_STATE_PK by 128 in tablespace CAPP_IDX in my alert log (8.1.7 on Windows 2000). I checked my tablespace is has one datafile with autoextend enabled. FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUSRELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS -- -- -- -- -- - --- -- -- -- --- E:\ORACLE\ORADATA\CIMX\CAPP_IDX.ORA 4 CAPP_IDX 314572800 38400 AVAILABLE4 YES1949696238 3200 314564608 38399 To resolve the error I manually resized the datafile to 300M. Any ideas on why the autoextend didn't work? Thanks, Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Eberhard, Jeff INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Outer join in SQL server - A very simple question
-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Adrian Roe INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to get user's IP address?
This is probably related to the protocol that your client uses
to connect to the Oracle server. If you use Bequeth or Netware
for instance, there really isn't an IP address to speak of.
-Original Message-
Sent: Wednesday, June 25, 2003 4:58 PM
To: Multiple recipients of list ORACLE-L
Hi Anne
I tried this on 9iR1
oracle:jupiter sqlplus system/manager
SQL*Plus: Release 9.0.1.0.0 - Production on Wed Jun 25 20:45:54 2003
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
With the Partitioning option
JServer Release 9.0.1.0.0 - Production
SQL select sys_context('userenv','ip_address') ip,username,machine
2 from v$session
3 where sys_context('userenv','sessionid')=audsid;
IP
USERNAME
--
MACHINE
SYSTEM
jupiter
SQL connect system/[EMAIL PROTECTED]
Connected.
SQL /
IP
USERNAME
--
MACHINE
172.16.240.11
SYSTEM
jupiter
SQL
It looks like you need to use the service name when connecting to get
the IP Address.
hth
kind regards
Pete
Basically, I have a trigger to capture the date, schema name and computer
name or IP address. However, I could not get the IP address or host name
out of Oracle.Can anyone help?
--
Pete Finnigan
email:[EMAIL PROTECTED]
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for
details.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rudy Zung
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
