PL/SQL :: Create Constraint That Checks To Ensure (Year) Entered Is Less Or Equal To Current?
Aug 6, 2013
, I'd like to create a constraint (when creating a table) that checks to ensure that the 'Year' entered is less than or equal to the current year (based off SYSDATE). Per the code below, I keep getting the same error, "missing right parenthesis". I've spent more than an hour trying different ways to get this to work, but I've been failing miserably. I am using Oracle 11g Express.
CREATE TABLE TEST (Name VARCHAR2(7) PRIMARY KEY, Year NUMBER(4), CONSTRAINT TEST_YEAR_CK CHECK (Year <= (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')))FROM DUAL);
*ERROR at line 4:ORA-00907: missing right parenthesis
ADD_REWARD_sp. Given the identifier of a project, add a new reward for the project. The procedure should return a unique identifier for the reward. The month and year indicated should be greater than or equal to the current month, or an error message should be generated. The pledge amount should be greater than zero. The number of backers, if not NULL, should be greater than 1. If the project is not found, generate an error message.
This is the procedure head:
create or replace PROCEDURE ADD_REWARD_sp( p_proj_id IN NUMBER, p_pledgeAmt IN NUMBER, p_rewardDesc IN VARCHAR2,
[code]...
Basically, what I am struggling with is how to check if th month and year is greater than or equal to current month. I suppose it would be easy with just checking SYSDATE, but in this case I need to use both month and year.
insert into sample values('1','01-JAN-2005'); insert into sample values('1','1-JAN-2006'); insert into sample values('2','2-JAN-2007'); insert into sample values('3','4-JAN-2005'); insert into sample values('4','11-JAN-2004'); insert into sample values('3','2-JAN-2005'); insert into sample values('2','12-JAN-2006'); insert into sample values('1','21-JAN-2005'); insert into sample values('2','11-JAN-2005'); insert into sample values('7','01-JAN-2005'); [code]....
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 .
I use Oracle 10.0.2.0.1.If I create a table with constraint key; after that I create an unique index key, I got an error. Does it mean when I create a table with constraint the unique index are automatically created and I could not create index key as I did as below?
create table test_const(ename varchar2(50) not null, key_num number not null, descr varchar2(100), constraint constraint_test_const unique (ename, key_num)); create unique index test_const_idx on test_const ( "ENAME","KEY_NUM" ) tablespace tmp_data;
Error report: SQL Error: ORA-01408: such column list already indexed 01408. 00000 - "such column list already indexed" added [pre] tags by Sriram
mucking on an Oracle 11.2 database, simple range partitioning issue. Seems using a "complex" formula inside the AT clause annoys it? Or am I doing something wrong?
I create the table with RANGE partition just fine:
CREATE TABLE my_part_tab ( id number, sdate date ) PARTITION BY RANGE ( sdate ) ( PARTITION P2013Q1 VALUES LESS THAN ( TO_DATE('01-jan-2013','dd-mon-yyyy') ), PARTITION P2013Q2 VALUES LESS THAN ( TO_DATE('01-jul-2013','dd-mon-yyyy') ), PARTITION P2013Q3 VALUES LESS THAN ( TO_DATE('01-oct-2013','dd-mon-yyyy') ),
[code]...
Table created.(and yes, I'm aware of INTERVAL option that'll do this next part "automagically", however, INTERVAL and REFERENCE partitioning are incompatible, and the child table is using REFERENCE partition). to make things easier on DBA for future, I'm trying to create a script that makes added a year's worth of partitions less manual.So far, I have the following working:
ACCEPT lYear PROMPT "Add Paritions for which calendar year?" ALTER TABLE my_part_tab SPLIT PARTITION PMAX AT ( TO_DATE('01-apr-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q1,PARTITION PMAX ); ALTER TABLE my_part_tab SPLIT PARTITION PMAX AT ( TO_DATE('01-jul-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q2,PARTITION PMAX );
[code]...
But no luck ...Same issue with other variations:
ALTER TABLE my_part_tab SPLIT PARTITION PMAX AT ( (ADD_MONTHS(TO_DATE('01-jan-&lYear','dd-mon-yyyy'),12)) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX ); ALTER TABLE my_part_tab SPLIT PARTITION PMAX AT ( (TO_DATE('01-jan-'||TO_CHAR(TO_NUMBER('&lYear')+1,'fm9999'),'dd-mon-yyyy')) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX );
i'm new to oracle environment.how can i specify NONCLUSTERD INDEX on Primary cloumn during table creation.By default it will create clusterd index but i need non-clusterd index on it.
I'm using following stmt to create normal primary constarint during table creation,
CONSTRAINT PKFORM_PROPS PRIMARY KEY (FORM_PROPS_PK) USING INDEX TABLESPACE DB123_INDEX
how can i change the above query, so that it should create NONCLUSTERED INDEX on Primary key column.
Using Oracle 11g...We have a table in our database of data with the following information:
MASTER_RECORD, MEMBER_RECORD, BUSINESS_UNIT, GENDER, DOB (date), age [at time of month_record], MONTH_RECORD (date) [31-MON-YEAR for recorded active month]
The table has ~55 million records. Existing index is only on MASTER_RECORD.There is now a need to create a view which is an aggregate count of member records, grouped by business_unit,gender, age per year. eg:
business_unit, gender, age, month_record, num_of_members -> for every combination unit5, F, 25, 31-JUN-2011, 622 unit3, M, 18, 31-MAY-2011, 573
The view can be created now, but, is not fast enough to be reasonably considered a view. This table is re-created every month from a procedure, so there is flexibility on how it is created. Use interval partitioning by year( something I have not experienced using), create an index on the month_record,then create view.
Query in format Query@DB_Link is sometimes dropping transactions. Move it to a package on target dababase "B" called from DATABASE "A" via package.procedure(varaibles);
Is that all I need to do to get the transaction to always go? I tried a commit in the package body, with repeated attempts if it fails. Btu when I use commit, the package doesn't work. (Compiles fine) Why would the commit fail?
My goal is to make that the transaction succeeds.Code for what I tried below. the COMMIT is commented out - if I uncomment it, the transaction fails. I have done this before calling an oracle SP from SQLServer and it worked
Comments:
The loop is to avoid an endless cycle..The concept is to perform the update, commit it, if the commit fails, rollback and try again.I'm not sure if I even need a loop, but I don't know if the unresolved transaction would get fixed by the remote DB, or dropped.
I would like to know that if I enable backup Optimization on then incremental full backup skip any files which was earlier backup? Because we may know that backup Optimization on ensure skip those files which are already taken backup.
I have a one critical remote PROD database that I want to check every 10 mins for its connectivity. If connection fails then an email & SMS is sent to me. My question is what is the best way to check if the remote database is up and running?
Can I use sqlplus system/manager@PROD? But sometimes this took so long and hanging. I want the fastest response time? How do I write a pl/sql proc check connection? That do something like the ff:
I created a table for my tnsname.ora entries.
cursor is c1 select dbname from tnsnames_tbl; begin connect system/manager@c1.dbname; print c1.dbname || 'DB Connection OK'; exception when others; print c1.dbname || 'DB Connection Not OK'; end; end;
I am trying to apply 14275572 this patch on our local database
DB : ORC it's on 11g (11.2.0.3.0) OS : Redhat 5 (x86_64).
14275605 applied succesfully but 14275572 applying this patch getting below errors.
[Jan 5, 2013 7:37:36 PM] --------------------- Prerequisite for apply --------------------- [Jan 5, 2013 7:37:36 PM] Running prerequisite checks... [Jan 5, 2013 7:37:36 PM] Patch "14275572" is ignored as it is not a "Fusion Applications patch". [Jan 5, 2013 7:37:36 PM] Check if patch "14275572" is a no-op patch. [Jan 5, 2013 7:37:36 PM] None of the selected patches are no-op. patches.. [code]...
This patch requires some components to be installed in the home. Either the Oracle Home doesn't have the components or this patch is not suitable for this Oracle Home.OPatch failed with error code 39.
in oracle instalation when Product-Specific Prerequisite Checks window appears it show me theres a warning with checking network configration as shown in pic.theres a sites says that its not necessary to make it succeeded and its ok to leave it. should i leave this warning or it should be succeeded?
i'm writing a job to export data on a weekly basis and archive those data in-case needed to be re-imported in future, its important that we able to import successfully if ever needed.
can impdp perform checks to see if dumps are valid ?how about old imp?if not, will it be safe enough to rely on the export log file? in another word is it safe to assume its all safe if there is no error or warning in the exp log?
I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.
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)
I would like to select test_sub.price as per the below conditions. If test_bb.value_date, test_bb.vehicle matches to test_sub.value_date,test_sub.vehicle then display test_sub.price
If there is no match then as above find the closest test_sub.value_date which is less than test_bb.value_date and select corresponding test_sub.price for the same vehicle combination.
begin insert into test_bb values(10,to_date('12-04-2012','dd-mm-yyyy')); insert into test_bb values(10,to_date('10-02-2010','dd-mm-yyyy')); insert into test_bb values(20,to_date('24-01-2014','dd-mm-yyyy')); insert into test_bb values(20,to_date('22-02-2008','dd-mm-yyyy')); end; /
begin insert into test_sub values(10,to_date('12-04-2012','dd-mm-yyyy'),2); insert into test_sub values(10,to_date('08-01-2010','dd-mm-yyyy'),4); insert into test_sub values(10,to_date('14-04-2014','dd-mm-yyyy'),6); insert into test_sub values(10,to_date('06-08-1947','dd-mm-yyyy'),8); insert into test_sub values(20,to_date('24-01-2014','dd-mm-yyyy'),10); insert into test_sub values(20,to_date('20-02-2006','dd-mm-yyyy'),12); insert into test_sub values(20,to_date('18-02-2004','dd-mm-yyyy'),14); insert into test_sub values(20,to_date('28-02-2006','dd-mm-yyyy'),16); insert into test_sub values(20,to_date('22-DEC-2007','dd-mm-yyyy'),19); end; /
I could write as below but I would like to know if there is a better way of doing it.
select bb.vehicle ,sub.price ,bb.value_date from test_bb bb ,test_sub sub where bb.vehicle=sub.vehicle [code].........
I am working on an application that allows a user to create a KPI formula, and the app dynamically creates a view for this formula. However as we started using it we have found that we are encountering the ORA-10476 (divisor is equal to zero) often. I have read about options of using decode or case to prevent this but it doesn't cover all our options. As a result of the fact that the user can create any equation he wants , and it can be as complicated as they want ( 2 examples:
A/(B/C-D/E) - If C or E or (B/C-D/E) are 0 the whole select will fall A/(B-C/(D-F)) - if (D-F) or ((B-C/(D-F)) is 0 then again the select will fall.
I have seen that in MS SQL they have an option of arithabort which allows the database to return the rows that don't fall under the "divisor by 0".
either by setting something similar to arithabort, or maybe a procedure to check an equation and return the arithmetical steps in order that athey are performed?
The below code gives me error (ORA-01476: divisor is equal to zero)
SELECT ((COUNT(DECODE(SUBSTR(A.ASSETNUM,6,3),'ACS','ACS',0,null))/COUNT(DECODE(SUBSTR(A.PMNUM,1,3),'ACS','ACS',0,null)))*100) FROM WORKORDER A WHERE TO_CHAR(A.REPORTDATE,'MON-YYYY') = :WO_DATE;
im trying to create a trigger that, when i insert a new song in the songs table, it will check the category of the song (song_cat) and increase the respective cat_total (from table categories) by 1.
here is what i've done so far:
drop trigger countcat; CREATE TRIGGER countcat AFTER INSERT ON songs FOR EACH ROW update categories SET cat_total= cat_total +1 WHERE cat_name = (select song_cat FROM inserted);
What to write in the cat_name = (select ...). I have tried lots of stuff but still nothing. when i use this, i get the error that mydatabase.inserted doesnt exist
I have set up a view that pulls news & events records.
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.category, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType FROM(so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID
[Code].....
For events I want only the events that have a p.startDate equal to today. I have tried setting that line up a few different ways.
p.startDate = sysDate didn't provide any records for events p.startDate = TO_CHAR(sysDate, 'MM/DD/YYYY') freaked the system out and nothing appeared for either parts of the union
