I have a table called Book. This table has 3 columns viz id, price and discount. If price is greater than 200 then discount should be 20%. While inserting data in Book table the discount value should be updated based on price value. How can this be handled when data is inserted or updated in Book table? Please provide all possible solu...
Possible Duplicate: A good reference for Oracle PL/SQL I need make a procedure to fill some tables in oracle, i'm new in oracle, i need receive two ids, with that make a couple of loops, iteratos, or something like that, and then make a few of sql, the procedure will be called into a trigger, but i can't find a good and comple...
Hi, I have used column-based relations a lot in my projects like: CREATE TABLE `user` ( id INT AUTO_INCREMENT PRIMARY KEY, usergroup INT ); CREATE TABLE `usergroup` ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ); however, at work it seems some people do it using table-based relations like this: CREATE TABLE `user` ( id INT...
Hi All,I got a problem with oracle database ,i created a stored procedure and i wanted to pass an array of items' ids to this procedure to select the data according to array of items using "in" clause,the available solution to this as i found was to create a function and pass a string value with all item's ids seperated by a comma ,and t...
When I create an Oracle database it has a lot of tables in it with strange names. What are these tables? Should I keep them? If not, how can I avoid creating them? These are the table names: LOGMNR_UID$ LOGMNR_SESSION_EVOLVE$ LOGMNR_GLOBAL$ LOGMNR_RESTART_CKPT_TXINFO$ LOGMNR_AGE_SPILL$ LOGMNR_SPILL$ LOGMNRC_DBNAME_UID_MAP LO...
hi guys, i tried to clone one of my database in one machine to another machine. the procedure is right i guess its getting mounted but am unable to go to open state.its showing errors as below. ORA-24324: service handle not initialized ORA-01041: internal error. hostdef extension doesn't exist would anyone suggest me what the exact pbl...
Heyho, I need to grab some datas from actions which been done from date A 00:00:00 and date B 00:00:00 (in this case Date A: 16.07.2010 Date B: 20.07.2010) so i wrote this select-statement: Select avg(cnt),fext from ( Select to_char(mytable.dateadded, 'DD.MM.YYYY') dateadded, fext, count(id...
yesterday I've been trying to make this code work inspite the fact it's just working fine with nhibrnate and SQL server but when it come to oracle it generate wrong sql UnitOfWork.Current.CreateCriteria<Bill>().Add(Restrictions.IsEmpty("ReqId")) .SetMaxResults(BatchSize).SetLockMode(LockMode.Upgrade).List<Bill>(); the generated SQL w...
I am trying to send the database Array by using the method below: OracleConnection oracleConnection = (OracleConnection) ((WLConnection)connection).getVendorConnection(); -- Exception in this LINE ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor( "MY_ARRAY", oracleConnection); String vals = {"1", "2"} ARRAY ocbsArray = new A...
I recently learned about CTE's in SQL Server and am attempting to use it in PL/SQL. I do not need the recurive benefits of it, however, I would like to use it in lieu of creating a view and to improve query performance. Just looking for some direction on what code may be similar. ...
Hi guys, I am using java.sql.SQLData interface to map my java objects to Oracle database types. For example, I have an object type Person in Oracle DB defined as: CREATE OR REPLACE TYPE PERSON AS OBJECT ( PERSON_ID NUMBER, PERSON_NAME VARCHAR2(100) ); Corresponding Java type is: public class Person implements SQLData { ...
I have the following function: CREATE OR REPLACE FUNCTION GetVarchar2 (iclCLOB IN Nvarchar2) return NVARCHAR2 IS cnuMAX_LENGTH Constant number := 32767 ; nuLength Number := DBMS_LOB.getlength(iclCLOB); sbBuffer Nvarchar2(32767); begin dbms_lob.read(iclCLOB,nuLength,1,sbBuffer); return sbBuffer; END; when I call it like this: select G...
Hello all, I am rather new to SAS and I have run into a problem that I think probably has a better solution than what I've found so far. I need to update a Oracle db table that has around 1 million rows with data from a SAS data set that has about 10,000 records. I used an update statement within proc sql, but it takes hours to upd...
Is there a way to pass a Java ArrayList object as a parameter to an Oracle Stored Procedure? I have seen examples of passing an Array object to an Oracle Stored Procedure, but not an ArrayList object. Is this possible? ...
We have a slow query that has a low optimizer_cost value but a very high user_io_wait_time value. Does this just indicate that there is an I/O bottleneck? Should we allocate more memory to Oracle? Get faster disks? Note: the stats were gathered by querying V$SQL ...
I have two tables that look a little like this AGR_TERR_DEF_TERRS ID DEF_ID TERRITORY_ID (foreign key links to TERRITORIES.ID) TERRITORIES ID NAME PARENT_ID (parent_id and id are recursive) Given two DEF_IDs, I need a function which checks whether the territories of one is a complete subset of the other. I've been playing...
I have a .net web application that makes heavy use of oracle stored procedures. One of these is problematic - some times it works, some times it doesn't. Is there any way to either attach a debugger to oracle when the sp is called, or step into it directly from Visual Studio? What other debugging techniques are there for a .net/Oracle...
Hello, Tiredness prevents me from finding this one... Say you have the following tables: Parent PARENT_ID (LONG) Child CHILD_ID (LONG) PARENT_ID (LONG, FK) HAS_GRADUATED (BOOLEAN) I want a query to return the following true (1, in the case of Oracle) if the parent has at least one child that has graduated, and false (0, in the ...
I have NHibernate calling stored procedure in Oracle working currently. However, how do I specify a schema prefix in the {call} syntax in the tag? I tried <sql-query name="my_sproc"> <return class="my_sproc_class" /> {call schema2.my_sproc (:p1)} </sql-query> But NHibernate run-time came back with 'schema2' not defined while 'sc...
Recently, I encountered a BEFORE INSERT OR UPDATE trigger on a table. In this trigger, the author relies on the INSERTING and UPDATING functions (both return a BOOLEAN) of the DBMS_STANDARD package to determine if the trigger was fired before an insert or before an update. For example: CREATE OR REPLACE TRIGGER CUSTOMER_TRIGGER BEFOR...