I have folliwng table
CREATE TABLE abcd
(
trans_type VARCHAR2(10) nn,
original_tran_no NUMBER,
original_line_item NUMBER(20),
original_trans_dt DATE);
I have to create a constraint when trans_type = 'R'then (original_tran_no and original_trans_dt ) should be not null else can be null able; is the follwing stmt right?
alter table abcd add constraint t1_chk1
check (
( case when trans_type = 'R' then (original_tran_no is not null
and original_trans_dt is not null))
1. How can we find out what all are the SQL Queries/Procedures/Functions/<Oracle DB Objects> are running long time and affecting the Performance in each schema. If they affecting how to resolve it?
Here Need to Display the Queries and the method to increase the performance of the query.
2. How can we find out what all are the DB links are Out Facing and In facing? ( In Each Schema)
i want to create a table to perform a task when i insert client account details in this table then check the account type is in(S,C,R) and balance should be also check when account type S then balance >=5000, account type C then balance >=10000 and account type R then balance >=5000 but when i run this query then facing error "ORA-02438: Column check constraint cannot reference other columns"
SQL> ed Wrote file afiedt.buf
1 create table kcb_acc_tab 2 ( 3 accno varchar2(20) constraint accno_pk primary key, 4 name varchar2(20) constraint name_nn not null 5 constraint name_chk check((substr(name,1,1) between 'A' and 'Z') and name=upper(name)),
i want to get table name, constraint name, constraint type with join processes in string type. this is what i want: alter table tablename add constraint constraintname constrainttype(columnname)
For each TSKID/IDCODE combination there are 4 FIELDNBRS with some values. For each set of 4, there is one NBRSEQ which can change depending on the order in which the a set of same TSKID/IDCODE is entered in the table. For 1st set of 4 for a given taskid, it will be 1. For second set for same Taskid it will be 2 and so on. But if the second set has a different taskid then the number starts from 1 for the new TASKID.
Now I want to Find all the tasks which has same FIELDVALUE for a complete SET of 4. Lets say I want to find a task which meets following criteria:
I want to know if the query can be restructured to a less complex than this and fetch the same results. In our example it should fetch taskid = T222222222.
I have the following questions you can create a bulk collect in which he has 3 fields and one of them is a type collect failing index or other bulk collect?.If so, how would the procedure for when to insert the first record sub fill the bulk collect. being for example something like this:
----------------------------------------------------------------- index | codigo | nombre | telefono | | |----------------------- | index | telefono ----------------------------------------------------------------- | | | |
I am having 7 tables consider A,B,C,D,E,F,G. whereas A is master table and others are dependent on table A. columnA is referential key for all tables. Table A is having one DateRange column.I have successfully created range partitioning with partitioning key is DateRange. and Refeernce partitioning on other tables with column referring to ColumnA with foreign key. Also created local indexes on partitioned key DateRange.
Problem is that, while fetching complex queries for reports, it is taking more time as compared to non partitioning structure. Is ref partitioning affecting on complex queries, queries returning more rows?
I need to develop a complex report , i have information in following table.vehicle with two fields number date_of_renewal date what i need is to generate a calendar that will show all the vehicles that due for renewal based on the date.On the top it will be years , next is months for that year and below vehicles which needs renewal
There is XMLType table with structural storage. Is there a way to make schema validation disabled on some elements of complex type?
It is unpractical to maintain a schema for the element due to high volatility. Ideally it will be stored in CLOB and extracted as is a whole branch without validation, none of the elements under this complex type will be extracted separately.
1 select s.reg_no,s.course_code, 2 s.section src_sec,a.section a_sec,a.att_date,a.att_flag 3 from attendance a ,src s 4 where a.semester_code=1 5 and a.semester_year=2013 6 and s.semester_code=1 [code]....
create materialized view ino_crm.MV_IDENTIFICATION_DETAILS REFRESH FAST as SELECT billing_account_id, i.s1 KEYS_ , COALESCE(i.s2, i.b, i.d) VALUES_ FROM ba_personal_details pd , xmltable ( '//entry[string = "identification Details"]//entry' passing xmltype(pd.details) columns s1 VARCHAR2 (255) path 'string[1]' ,s2 VARCHAR2(255) path 'string[2]' ,b VARCHAR2(255) path 'boolean' ,d VARCHAR2(255) path 'date' ) i WHERE pd.mandator_key = 'VSO01' AND pd.valid_to = to_date('01.01.4000', 'DD.MM.YYYY'); and
I get an exception like: SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query 12015. 00000 - "cannot create a fast refresh materialized view from a complex query"
*Cause: Neither ROWIDs and nor primary key constraints are supported for complex queries. *Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE option or create a simple materialized view.
(CASE WHEN DATEa=DATEb THEN 0 WHEN DATEa> DATEb THEN NETWORKDAYS(DATEb, DATEa) WHEN DATEa < DATEb THEN NETWORKDAYS(DATEa, DATEb) WHEN STATUS='PENDING' THEN NULL ELSE NULL END) AS RESULTa,
Now what I need to be able to do is place those results in buckets, similar to this:
(CASE WHEN RESULTa < 0 THEN '<0' WHEN RESULTa between -1 AND 6 THEN '<=5' WHEN RESULTa >5 THEN '>5' ELSE '' END) AS BUCKETa
I understand that I can't call an alias from a previous case expresson to get these desired results and how I could combine the two statements to get the desired bucket.
The data in one of the temporary table has been compiled as below. Number of Days elapsed between two transaction dates is required to be computed and multiplied with the balance. There can be multiple number of same transaction dates. When previous date and the current transaction dates are same the resulting number of day difference should be 0. But when they are different the difference between them is to be computed. On the last day of the Financial Year i.e. '31/03' of any year the difference day should be shown as 1 so as to make 365 or 366 days in a year. Simply deducting d1 from d2 on 31st will not be suffice as the difference is one day less.
I'm trying to call a Java function from within a PL/SQL aggregation function with a (array of) complex data type.
a) basic definitions (work and are ok)
CREATE OR REPLACE TYPE eventtype AS OBJECT ( relevance NUMBER, bid1 NUMBER,tid1 NUMBER,prob1 NUMBER, bid2 NUMBER,tid2 NUMBER,prob2 NUMBER, bid3 NUMBER,tid3 NUMBER,prob3 NUMBER, bid4 NUMBER,tid4 NUMBER,prob4 NUMBER, bid5 NUMBER,tid5 NUMBER,prob5 NUMBER, bid6 NUMBER,tid6 NUMBER,prob6 NUMBER ); CREATE OR REPLACE TYPE eventtuple IS TABLE OF eventtype; /
b) The following call works (but is not exactly that what I want):
public class pcompDP { public static NUMBER probComputation(oracle.sql.STRUCT pin) throws java.sql.SQLException { ... } } create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT) return BigDecimal'; / SELECT pcompDPFunc(eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)) from dual;
c) The following call doesn't work (but should have the intended semantics):
public class pcompDP { public static NUMBER probComputation(oracle.sql.STRUCT[] pin) throws java.sql.SQLException { ... } } create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT[]) return BigDecimal'; / SELECT pcompDPFunc(eventtuple(eventtype(2, 3, 0.234, 1, 1, 0.123, 0, 0, 0, 0, 0, 0, 0, 0, 0), eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0))) from dual;
d) What's the error? Do I have to use JPublisher or is it possible without it? I guess an array should be possible without it.
The type of the parameter seems to be wrong. Question is whether in PL/SQL, Java or SQL.
I'm trying to create materialized view, successfully create logs for all the tables involved.
CREATE MATERIALIZED VIEW LOG ON tbJournal WITH ROWID, SEQUENCE (tabid, companyid, storelocid, tabstatus, linetype) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON TBTABS WITH ROWID, SEQUENCE (TSTAMP, COMPANYID, NUMGUESTS, POSITIONID, STORELOCID, TABSSTATUS) INCLUDING NEW VALUES;
[code]....
The result is ora-12015: cannot create a fast refresh materialized view from a complex query
It does work fine if I remove
AND EXISTS (SELECT 1 FROM tbJournal J WHERE j.tabstatus NOT BETWEEN -6 AND -4 AND j.linetype = 1 AND T.tabid = J.tabid AND T.companyid = J.companyid AND T.storelocid = J.storelocid) from the where clause
i have a problem that i have created a table named PROJECT and structure is like- CREATE TABLE PROJECT ( id VARCHAR2(250 BYTE) NOT NULL, NAME VARCHAR2(250 BYTE) NOT NULL, COMPID NUMBER NOT NULL,
[code]...
and this table is created in some number of different database then i want to delete the not null constraint of "id" column and there is 3 not null constraint in my table and i didnot gave name at the time of creation, so we cant delete it by its name in the script because there will be different name in another database for the same constraint. i want to make a script to delete the not null constraint because it is the part of my script.
how should i get proper information for deleting the constraint. and i used "user_constraints" but it shows same information for the all three "not null" constraint.
Create table students ( Sid number(3), Sname varchar(20) not null, Email varchar(20) not null, Doj Timestamp default sysdate not null, Dob date not null,
[code]....
when i run the above code i m getting error like this...I dont understand wat error it is...Because i didnt reference any column in the check constraint...Thn why it says that i m referencing some column..
Error: SQL> SQL> Create table students 2 ( 3 Sid number(3), 4 Sname varchar(20) not null,
I have a parent table called dept and one child table emp which is referenced on deptno and now both have data . Now i want to truncate the dept table and i have truncated emp table after it when i am trying to truncate dept table then oracle throws a error that is-
"ORA-02266: unique/primary keys in table referenced by enabled foreign keys" ,
but when i tried to delete some records 1 by 1 then these have delete but truncate is not still working. why? if i have 10 millon records.
Can I apply Referential Integrity to only selected rows of a particular column? This is the reference key to the primary key to another table. But, the issue is, this reference column is not having mandatory data for all the rows. So, whenever this is null, I dont want it to be referred by parent table.
I want to know constraint name of a particular table if it exists.Here are the queries that i tried to find the constraint name of a particular table but the results were 'no rows selected'.
Queries:
select constraint_name from user_cons_columns where table_name='x'; select constraint_name from user_constraints where table_name='x';
A {op_id, op_name, .} B {b_id, op_id, supplier_id, .} C {c_id, op_id, op_id2, supplier_id, relation, .}
Table B and C have op_id which is foreign key from table A. In table A op_id is primary key, in table B b_id is primary key and in table C c_id is primary key. In table B supplier_id and op_id may have duplicate records. Now I want to add constraint so that if I delete records from Table B for op_id and if a relationship record exists for op_id in table C then it should not allow me to delete. Is it possible through referential integrity constraint ?
1) CREATE TABLE mutu (id NUMBER, text VARCHAR2(20));
2) INSERT INTO mutu SELECT rownum , 'mutu' FROM dual CONNECT BY LEVEL <= 10000;
3) Insert Duplicate row. INSERT INTO mutu VALUES (42, 'DUPLICATE');
4) COMMIT;
5)Create a NON-UNIQUE INDEX's CONSTRAINT as below. ALTER TABLE mutu ADD CONSTRAINT mutu_pk PRIMARY KEY(id) USING INDEX(CREATE INDEX mutu_pk ON mutu(id)) ENABLE NOVALIDATE;
6) Insert Duplicate row. INSERT INTO mutu VALUES (42, 'DUPLICATE');
7) ERROR at line 1: ORA-00001: unique constraint (SYS.ZIGGY_PK) violated
Why it shows the error. Even though I had created a NON-UNIQUE INDEX's Constraint only.
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production
These error codes I'm getting in production.First of all, I've no duplicates present in that table for which this error has been raised.I've checked the index and related columns as well. NO DATA is there.So NO CHANCE for unique constraint violation.
SELECT * FROM ORDER_OCC_REQUISITION_X_REF WHERE LAB_ORDER_OCC_TEST_ID IN(SELECT LAB_ORDER_OCC_TEST_ID FROM LAB_ORDER_OCC_TEST WHERE LAB_ORDER_OCC_ID = 7944858);
no rows selected
Now when I'm trying to insert one row inside this table I'm getting this error, as you are seeing no records for this occurrence_id.
SELECT * FROM USER_INDEXES WHERE INDEX_NAME = 'ORD_OCC_REQ_UQ_TEST_IX_04'; --ORDER_OCC_REQUISITION_X_REF (Table name) --MERGE_DT, LAB_ORDER_OCC_TEST_ID, TEST_ID, ACTIVE_YN (columns for the index 'ORD_OCC_REQ_UQ_TEST_IX_04') As you can see there is no data then this error should not be raised. Update procedure. /******************************************************************************************************************* * Name : UPDATE_REQUISITION_X_REF * Description : This Procedure update ORDER_OCC_REQUISITION_X_REF table with requisition_id * that was generated due to merge process. * In parameters : IN_merge_id NUMBER The order_ref_no of the orders to be merged (comma seperated) ***********************************************************************************************************************/ PROCEDURE UPDATE_REQUISITION_X_REF ( IN_merge_id IN TT_ORD_REQUISITION_WORK_AREA.merge_id%TYPE) IS
I have a table say EMP with 2 fields ID and Name, with ID being the primary key. The name of the primary key constraint is ID_PK.Now my scenario is :
1. Rename the table EMP to EMP_TEMP; 2. Create an empty table EMP 3. If (data successfully loaded into EMP) { Drop EMP_TEMP; } else { Drop EMP; Rename EMP_TEMP to EMP; }
The problem here for me is the primary key constraint. When I am in 2, I cannot create the primary key constraint again since the old one is still in tact. If I go ahead to drop the original pk constraint and re-create a new one in 2, then in case of else part , I need to get the constraint again while renaming the original table.
I'm trying to create a new table which has a date field (date of birth) and I want to include a constraint which will not allow me to add values if they are over 21 years old. I'm using SQLPlus .
