[no subject]
SET ORACLE-L NOMAIL Regards, Deepa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
[no subject]
which -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Creation of MDI in D2k v6/6i
Hello, How do we create an MDI form in D2k v6/6i ? Deepa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
User Log-Machine Name
Hello A user log is maintained for a table.I want to log all updations made to this table. I have written a database trigger (After Update) to log the changes to another table.My requirement is to find the machine name from which the updation has occurred and log the machine name also . How to get the currently working session ID so that machine name can be retrieved fromV$session view or any other method to retrive the machine name uniquely. Is it that one Oracle user can have multiple active sessions on different machines? Eg. User Name Status Module Session Machine XXACTIVESQL*Plus 10 AA XXACTIVESQL*Plus 19 BB Regards, Deepa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Limitations of table partitioning.
Hello What are the limitations of partitioning a table in Oracle. Regards, Deepa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Table Design
Hello We are doing database design for a project. We have 10 distinct transactions types and the total number of records is expected to be around 5,00,000 taking all transactions together.We have normalised the tables and decided to store all of them together in a single table identified by the transaction type and other unique fields. We would like to know which option would be the best so that we can retrieve data most efficiently Option-1. Maintain 10 different tables for each transaction type (i.e 50 records will be split among 10 tables) Option-2 Store all of them together in a single table identified by the transaction type and other unique fields. Regards, Deepa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
SQLPROMPT
We want to set the sql promp as username@database > We set this in Glogin.sql as : column global_name new_value gname select user||'@'||host_name global_name from v$instance,global_name; set sqlprompt '&gname>' We are able to get this prompt when we login to sql for the first time . When we change connection at the prompt using connect statement we are not able to get the new user name and database as prompt eg : RR@BKP>conn test/test@o8 Connected. RR@BKP> Can anyone suggest a method to do this. Thanks, Deepa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Re: Scripts for creating Tables/schema's
Hello This is possible using the freeware TOAD (Tools for Oracle Application & development). Regards, Deepa. |+---> || "Santosh | || Varma" | || | || | || 25/10/2002 | || 12:33 PM | || Please | || respond to | || ORACLE-L | || | |+---> >| || | To: Multiple recipients of list ORACLE-L | | <[EMAIL PROTECTED]> | | cc: (bcc: PK Deepa/VGIL) | | Subject: Scripts for creating Tables/schema's| >| i wanted a script which contains the create table/schema's for a existing database. How to get this script ? any utitlity or which option to be selected for achieving this after i install Oralce ? i am using oracle 8.1.7. Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
ORA-01008:not all variables bound
While executing the following database trigger (update) we are getting the following error message "ORA-01008:not all variables bound". We are working on Oracle 8.0.3 We have written this trigger to get the column name,old and new values of fields of a table (locmast) while updating. We do not want to hard code the field names ,so that in future if a new field is added we need not have to alter the trigger. declare v_rowid varchar2(50); cursor cur_loc is select column_name from all_tab_columns where table_name='LOCMAST'; v_val varchar2(120); cur_id number; v_rec_id number; begin cur_id:=dbms_sql.open_cursor; for rec_loc in cur_loc loop dbms_sql.parse(cur_id, 'select :old.'||rec_loc.column_name||' from dual' ,dbms_sql.native); dbms_sql.define_column_char(cur_id,1,v_val,120); v_rec_id := dbms_sql.execute_and_fetch(cur_id); dbms_sql.column_value_char(cur_id,1,v_val); end loop; end; Thanks Deepa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Oracle Server with Linux client
We are having a client machine with Linux as OS. Our Oracle server is installed on Windows NT. Does Linux support Oracle Developer 2000. If so how do we connect our linux client with oracle server ? Is there any difference in configuration of Net 8 (tnsnames.ora) If yes then pls give the configuration Thank you, Deepa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Re: UPDATE
Try this update x z set id2 = (select id2 from a where z.id1=id1 and id2<>0) where id2=0; Deepa |+---> || Imran Ashraf | || | || | || 23/10/2002 | || 03:03 PM | || Please | || respond to | || ORACLE-L | || | |+---> >| || | To: Multiple recipients of list ORACLE-L | | <[EMAIL PROTECTED]> | | cc: (bcc: PK Deepa/VGIL) | | Subject: UPDATE | >| Hi, I have table x with 2 columns: ID1 ID2 1 0 1 5 2 0 2 10 I want to update ID2, set the 0s to the other value for the same ID1. So, i would have: ID1 ID2 1 5 1 5 2 10 2 10 Whats the best way to do this? Thanks. __ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imran Ashraf 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.com -- 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).
Rowid in before update statement trigger
How to identify the rows being updated in a "before update statement trigger", without using row level trigger ? We tried to use the before update row level trigger to get the rowid of the updated rows . We observed that the statement level trigger is fired before row level trigger. As a result , we could not get the rowid of the rows being updated in the before update statement trigger. Please help us !!! Thanks Deepa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
ROWID in statement trigger
Hello, How to get the ROWID of the rows being updated in an After update statement trigger ? Thanks in advance, Deepa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Re: Database trigger to record user log
Could you please explain with an example . Thanks in advance Deepa |+-> || [EMAIL PROTECTED]| || ardier.com | || | || 17/10/2002 08:14 PM| || Please respond to ORACLE-L | || | |+-> >| || | To: Multiple recipients of list ORACLE-L | | <[EMAIL PROTECTED]> | | cc: (bcc: PK Deepa/VGIL) | | Subject: Re: Database trigger to record user | | log | >| [EMAIL PROTECTED]@fatcity.com on 10/17/2002 03:13:26 AM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: I guess, the problem you're having is that, ':new'||v_column is understood as a string and not as a variable? I have used a standard trigger (actually there was more than one) for a similar requirement (a couple of years ago). We needed a transaction log for about many tables, the table structures were changing (in a controlled way) but frequently... We ignored the :new and :old variables. Instead we read the row again based on tablename (and rowid or something similar) by a generated SQL statement using all_tab_columns; done in a way that that avoided the mutating table error. This effectively gets the :new values. We also read the the row again in another session (connected to the first by dbms_pipes) (but today maybe we could use an autonomous transaction) to get the :old values. Then we compared the the two rows, column by column and wrote the differences to the log. It took a bit of time to design and code, but was maintenance free. Table layouts could be changed, new tables could be added, views could be included, all without worrying about the trx logging portion. BTH don't forget a unique identifier to the original row in your update log. Good luck, Chaim Hi , We want to create a database trigger to maintain the log history of transaction tables (Not the Oracle Archive Log). Our requirement is to create a common Oracle database trigger. Only the table name will be different in these triggers. The column names can be taken from "all_tab_columns" view. When a row is updated in the table, this trigger should fire and the old and new values of updated fields must be saved in the update_log table. It's structure would be : create table update_log ( log_date date, table_name varchar2(20), column_name varchar2(20), old_valuevarchar2(20), new_valuevarchar2(20) ); We have tried out this trigger, but in vain.. --- CREATE OR REPLACE TRIGGER trg_invoice after update on sales.invoice referencing old as old new as new for each row declare cursor cur_log is select column_name from all_tab_columns where table_name='USER_PROG_ROLES'; v_column varchar2(50); begin for rec_log in cur_log loop v_column := rec_log.column_name; if ':new.'||v_column <> ':old.'||v_column then -- -- how to get old and new value without -- -- knowing the actual column name. insert into update_log (log_date, table_name, column_name, old_value, new_value) values (sysdate, 'Sales.invoice', v_column, :old.v_column, :new.v_column); end if; end loop; end; --- Expecting a reply soon... Thanks in advance, Deepa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- 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, inclu
Database trigger to record user log
Hi , We want to create a database trigger to maintain the log history of transaction tables (Not the Oracle Archive Log). Our requirement is to create a common Oracle database trigger. Only the table name will be different in these triggers. The column names can be taken from "all_tab_columns" view. When a row is updated in the table, this trigger should fire and the old and new values of updated fields must be saved in the update_log table. It's structure would be : create table update_log ( log_date date, table_name varchar2(20), column_name varchar2(20), old_valuevarchar2(20), new_valuevarchar2(20) ); We have tried out this trigger, but in vain.. --- CREATE OR REPLACE TRIGGER trg_invoice after update on sales.invoice referencing old as old new as new for each row declare cursor cur_log is select column_name from all_tab_columns where table_name='USER_PROG_ROLES'; v_column varchar2(50); begin for rec_log in cur_log loop v_column := rec_log.column_name; if ':new.'||v_column <> ':old.'||v_column then -- -- how to get old and new value without -- -- knowing the actual column name. insert into update_log (log_date, table_name, column_name, old_value, new_value) values (sysdate, 'Sales.invoice', v_column, :old.v_column, :new.v_column); end if; end loop; end; --- Expecting a reply soon... Thanks in advance, Deepa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
