XML DB :: Create XML Based On Xsd And Then Validate?
Jul 31, 2013I want to create an XML based on xsd and then i want to validate the same xml with xsd ,
View 1 Replies
ADVERTISEMENT
SQL & PL/SQL :: Create New Column Based On MIN DATE
Jun 26, 2012CREATE TABLE DAN_DATES
(ID VARCHAR2(12),
YEAR VARCHAR2(,
TERM VARCHAR2(,
START_DATE VARCHAR2(12))
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120227','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120626','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110226','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110725','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2012','1201',to_date('20120227','YYYYMMDD'));
Want to take the Start_Date for that year, CREATE A NEW COLUMN and place that START_DATE (which is row 1 for the year (min)) in it. So for ID 1 TERM is 1201 and 1202 BUT we want the top start date (earliest start date) and CREATE a clumn (NEW_START_DATE) and place that date in there wherever year is 2012.
I want to get
IDYEARTERMSTART_DATEMIN_DATE
12012120127-Feb-1227-Feb-12
12012120126-Jun-1227-Feb-12
22011110126-Feb-1126-Feb-11
22011110125-Jul-1126-Feb-11
22011110126-Sep-1126-Feb-11
22012120227-Feb-1227-Feb-12
SQL & PL/SQL :: Create Multiple Records Based On Condition
Oct 10, 2012CREATE TABLE test1
(strt_num NUMBER ,
end_num NUMBER ,
des VARCHAR2(5),
CONSTRAINT pk_strt_num PRIMARY KEY (strt_num)
);
INSERT INTO test1 VALUES (5, 8, 'GC');
INSERT INTO test1 VALUES (10, 25, 'AB');
INSERT INTO test1 VALUES (12, 35, 'PC');
INSERT INTO test1 VALUES (22, 65, 'LJ');
SELECT * FROM test1
STRT_NUM END_NUM DES
-------------- ------------ -------------
5 8 GC
10 25 AB
12 35 PC
22 65 LJ
The requirement is the records should be split based on below conditions
1. Split only those records WHERE (end_num - strt_num) > 10
2. If TRUNC((end_num - strt_num)/10) = n, then n + 1 number of rows should be created for that record
3. While splitting the records,
-> For first record , START_NUM = Original STRT_NUM and END_NUM = START_NUM + 10
-> Second record , STRT_NUM = previous END_NUM + 1 and END_NUM = previous END_NUM + 10
And this should continue for all records except the last record
-> For last record, STRT_NUM = previous END_NUM + 1 AND END_NUM = Original END_NUM
This table has 5 million records. Only for 2000 records (end_num - strt_num) > 10.
Expected Output.
STRT_NUM END_NUM DES
------------- -------- ---------
5 8 GC -- No chage, END_NUM - STRT_NUM < 10
10 20 AB
21 25 AB
12 22 PC
23 32 PC
33 35 PC
22 32 LJ -- STRT_NUM = Original STRT_NUm, END_NUM = STRT_NUM + 10
33 42 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
43 52 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
53 62 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
63 65 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = Original END_NUM
Create A Function Based Index On Bold Highlighted
Jul 2, 2010Using oracle 10g R2 on sun-solaris 10 (sparc-64) Well in the MIS system we have lot of ad-hoc queries coming up. We have proper indexing. Say an example which runs very slow;
SELECT GLKCO, GLDCT, GLDOC, GLDGJ, GLJELN, GLEXTL, GLPOST, GLICU, GLICUT, GLDICJ, GLDSYJ,
GLTICU, GLCO, GLANI, GLAM, GLAID, GLMCU, GLOBJ, GLSUB, GLSBL, GLSBLT, GLLT, GLPN, GLCTRY, GLFY, GLFQ,GLCRCD, GLCRR, GLHCRR, GLHDGJ, GLAA, GLU, GLUM, GLGLC, GLRE, GLEXA, GLEXR, GLUPMJ, GLUPMT, GLBCRC, GLCRRM, GLACR, GLAN8
FROM "PRODDTA"."F0911"
WHERE GLUPMJ <> 0
AND TO_DATE('1 JAN' || (19+substr( GLUPMJ , 1, 1)) || substr( GLUPMJ ,2,2)) + substr( GLUPMJ , 4, 3 ) -1 BETWEEN SYSDATE - 365 AND SYSDATE
[code]....
Here GLUPMJ already indexed so the second query returing an index scan but the first query does a FTS naturally.Now even if I plan to create a function based index on 'the bold highlighted' but how.
create index glupmj_idx on f0911(TO_DATE('1 JAN' || (19+substr( GLUPMJ , 1, 1)) || substr( GLUPMJ ,2,2)) + substr( GLUPMJ , 4, 3 ));..Error If I don't use a FBI my query will result in FTS.
1> how to create a FBI here in this case
2> In MIS systems where 'n' no of ad-hoc queries can come up, how to avoid FTS.
SQL & PL/SQL :: Create External Table Based On Record Identifier?
Oct 21, 2011I have a csv file which is in this format -
qwerty SCHEMATIC FILE
; Version 2007.7.1
Project,qwerty Project,,1,,7,1.5,1.5,1,1,0,,0,1,0,0,0,0,3,1,1,0,1,0,0,0,0,2,3,1,0,1,0,0,0,0,3,3,1,1,1,0,0,0,0,0,0,0,0,
abc,25150-28407.dat,,102,192,42,12632256,1,1,102,192,42,1,12632256,0,6,1,0,32896,1,0,0,0,0,,,-1,-1,-1,-1,0,0,0,-1,-1,-1
xyz, , , 0, 102, 0, 0, 0, 0, 0, 0, 0, 0, 0, , , , , , , , , , , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
Product,00094416505678,19133,"24-36""X80"" FOLD DR VIA MEH",,4.5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0,0,0,
Product,00094416502345,37154,"24-36""X80"" xyz WHITE",,5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0,0,
Product,00094416501111,83120,"24-36""X80"" abc WH",,5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0
Can I create an external table for loading this file based on selection of fields driven by the record identifier which is the first field of each record, except for the firt two?
For e.g In the above file, for records which say 'abc' in it's first field, I want to load the 2,3,6,8 fields delimited by a comma (the values may as well be null in those fields) and for records which say 'Product' in their first fields, I want to load 5,8,9 and 10 fields from the same file. Basically, I want to know if we can use instr function while choosing the fields line by line based on a search criteria in the file. There will be less columns in my table than the number of fields in the csv file, so I guess I got to mention 'MISSING FIELD VALUES ARE NULL' option. There is another challenge too - I have to skip loading the first two lines from the file to the table.
I have written a big pl/sql proc for doing the same, using utl_file.get_line option, but it is still untested and I would be extremely happy to believe that this can be achieved by creating an external table too.
Forms :: Create Two Datablocks Based On One Table In Oracle?
Jun 2, 2011can we create two datablocks(header&line) based on one table in oracle forms.any way to create?
View 7 Replies View RelatedPL/SQL :: Create Reporting View Based On Date Column
Jul 30, 2012My DBA gave me a table with only one date column say Table1.Date. Its in the format of Date and say it is = 7/23/2012.
Now i have to create my own Reporting View(which is used for reporting) based on that date column like below:
It should be a column with values in the following format =
2012-07
2012-06
2012-05 etc....upto
2010-01
So i started out my creating like this:
select
to_char(Table1.Date,'yyyy-mm')
from Table1
Union
[Code]....
.and so on till i get 2010-01.
there has to be a better way to do this.
SQL & PL/SQL :: Create Materialized View With Clob Column Based On Varchar2?
Feb 9, 2011I need to create a materialized view with a clob column based on a varchar2 column of a table.This is because in the mv the clob column data gets appended one after another.
View 2 Replies View RelatedSQL & PL/SQL :: Create Select Query That Will Get Data From 18 Months Ago Based On Sysdate?
Sep 9, 2013I have a table with columns job_id, jan, feb, mar ... , and year
I need to create a select query that will get the data from 18 months ago based on sysdate.
So something like:
Select to_char(add_months(sysdate, -18),'MON') from table1 where job_id = 56947 and year = to_char(add_months(sysdate, -18),'YYYY');
However I need the result of to_char(add_months(sysdate, -18),'MON') to actually act as a column name, not a string result.
Application Express :: How To Create Radio Groups Dynamically Based On Records In A Table
Nov 29, 2012below requirement..
We have certain records like SQL, PL/SQL, Reports, Forms, OAF etc in a table. We wanted to capture rating for each of these criteria. So we want a form to be displayed dynamically..
SQL 1 2 3 4 (1,2,3,4 represent radio buttons)
PL/SQL 1 2 3 4
Reports 1 2 3 4
Forms 1 2 3 4
OAF 1 2 3 4
If we add another row, XMLP in that table additional radio button should be displayed automatically..
SQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy
Oct 15, 2013I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically
ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')
500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498
Forms :: Create View Based On Values Selected In Forms
Jun 7, 2013I am working on a simple form which will get the user to fetch required columns of employee master.
For that i have created a form which will display the column names and select option. When user clicks on query, the form will display the selected columns in a block. After going thru if required the user can download it to excel.
As of now I do not have filtering option. For this I thought of creating view based on the selection and then fetch records from the view and display it in form.
To achieve this I would like to know How I create or replace a view dynamically based on values selected. I remember doing this sort of thing long back, but could not recollect it.
PL/SQL :: To Create Function Based Index For Group Function Columns
Jun 15, 2012Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
SQL & PL/SQL :: How To Make PK Not To Validate
Nov 21, 2011i am writing a script for inserting rows in a table where primary key of that table consists of 4 columns. I want to add a new row where 3 columns of that table will be same but change the value of the last one. this is giving an error saying that i am violating the unique constraint. I tried to disable that constraint/PK by following statement but still this error is coming
alter table sections_budgets modify primary key disable cascade;
OR
alter table sections_budgets modify constraints sec_bdgt_pk disable cascade;
the insert statement which i am using. The first four columns are part of PK
insert into sections_budgets (SELECT sb.bdgt_cd, sb.bdgt_dpf_cd, sb.bdgt_yr, :sec_code, sb.usf_usr_id,
sb.aty, sb.comaty, sb.comytd, sb.expn_ytd, sb.sys_dt, sb.cat,
sb.start_aty, sb.expn_lmt
FROM sections_budgets sb
WHERE sb.bdgt_dpf_cd = :b_d_code AND sb.bdgt_yr = :year);
is there any other way of inserting records in this table without droping the whole table because it contains too many triggers and constraints?
How To Validate SQL Scripts
May 7, 2013How to Validate SQL scripts?
I am having set of sql files i wish to run one by one if there is any error at one file i need to notify to the user.I have created Bat file in-order to execute in a sequence.how to validate in bat file
say
01.sql ---Sucess
02.sql --- Fail -- intimate and not to execute rest of sql files
03.sql
04.sql
How To Validate SQL Run On Database
Feb 26, 2013We are looking for validating our product upgrade changes. As part of product upgrade, there will be changes to the schema.
Like addition of tables, addition of columns to existing tables, change the length of character columns, add new index, etc. We want to verify on the database, that these SQL are run successfully on the database.
Is there a table/view, that can be queried to figure out whether such sql was run and the status of the execution, after some time (day or two) after these are actually run on the database.
SQL & PL/SQL :: Validate Date Format
Feb 27, 2012How can I check if date is in correct format ? I have In parameter for function. It is date. Example:
2011-11-01
How can I check this format ?
Forms :: Set Validate Item From LOV To Yes
May 29, 2011i have a table called equipment containing engine_type_id.in the equipment form that is based on the equipment table, i added a non database item called engine_type.
i added an LOV for the engine type field, so that the user can choose the engine type from the the egine types table. i made the engine type id hidden. and set the validate item from LOV to yes.on post query trigger, i selected the engine type into the nondatabase field.
the form is working properly.
when i push CTRL F11 (query) and scroll, even if i didnt change anything the form builder asks me , do u want to save changes.
SQL & PL/SQL :: Validate Sentence Syntax?
Dec 29, 2010Is there some way to validate the syntax of sql sentence without actually executing it? I dont want to check if the objects exist or not, just want to check the syntax.
View 6 Replies View RelatedForms :: Validate From List
Mar 25, 2009I have a non-database text item (name: profession_temp) on a block and it is a also return item for an lov.
When I change the property "validate from list" of the item(name: profession_temp), to YES, and closing the form without inserting, updating, deleting any record , a message is displayed "do you want to save the changes?".
When I change the above property back to "No", the message is not displayed in this case. I wonder why this message appears while there is no change occurred in the underlying records?
PL/SQL :: How To Validate Table Data
Aug 13, 2012I have a table data like this
"VchrNo" "Credit/Debit type" "Amount"
101 1 100.00
101 2 10.00
101 2 10.00
101 2 51.00
101 2 29.75
This data is wrong. Actual DAta should be in any form. But Sum of VchrNo 101 with Credit/Debit Type 1 should be 100 and VchrNo 101 with Credit/Debit Type 2 should be 100 Now this data is begin entered from so many forms.
Is there any way to stop this and raise any error at database level so that culprit form can be found.
SQL & PL/SQL :: Add Index Enable No Validate
Nov 1, 2011I have a table, it has 3 terabytes of data. We are sure whatever the data we have in that table is valid. I need to add the index to table it should be enabled and need not to be validated for existing data.
I am using following command
CREATE INDEX U_IXN_MTRS_ORGIDMTRNOMTRTYPEID ON MTRS (nlssort(METERNO), METERTYPEID) TABLESPACE OLTP_IDX_TS ENABLE NOVALIDATE;
it is giving me problem
ORA-02158: invalid CREATE INDEX option error
How To Validate A Sum Of Some Records In A Table To Be Exact Value
Apr 9, 2008How to validate a sum of some records in a table to be exact value?
I want to guarantee that sum(val_column)=100 at database level. Check constraints out of question. Trigger before update statement disable any update to that column when incrementing one row and decrement another... (unless with for all...)
Forms :: Use Of Validate From List Property
Jul 19, 2013What is the uses of "Validate From List" Property(Yes/No) in LOV's Properties.
View 3 Replies View RelatedSQL & PL/SQL :: Write A Trigger To Validate V_status For Each ID
Dec 30, 2010I want to write a trigger to validate the v_status for each ID. below is the example
SEQ_NO ID V_STATUS
11 C
21 C
31 F
42 C
52 F
63 C
7 3 F
8 3 C
For each unique id there will be only one F .. if some one tries to update the 2nd F to the same ID it will show the error message that ID is already F.
SEQ_NO ID V_STATUS
11 C
21 C
31 F
42 C
52 F
63 C
7 3 F
8 3 F
Forms :: How To Validate List Item
Nov 22, 2010I am working on form 6i..
I have developed a form with some text items,a list item and a image item...
list item......account_list(values....company,individual)
text_item..... company_name..
text_item.....image_path...
i have attached a pop up menu to image item... Add image and upload image...
When i click on 'Add image' the image path is getting enabled.. it is fine but when i am changing the account_list i need enable and disable the 'Company_name' text item... How can i achieve this??
I have tried it in when-validate-trigger on account_list...
if account_list=individual then
set_item_property('company_name',enabled,property_false);
else
set_item_property('company_name',enabled,property_true);
end if;
But no use of this....
how can i achieve it??
SQL & PL/SQL :: Enabling Constraint With Enable No Validate?
Sep 5, 2013Is there any way to enable the constraint with out validating for the existing information.
As We can accomplish the same functionality when we are adding the constraint for the first time.
Ex: alter table scott.emp add constraint fk_deptno foreign key(deptno) references scott.dept(deptno) enable novalidate
The above SQL statement will enable the constraint with out validating for the existing information. So there is possibility of data will be there only in the child table but not in the parent table for existing information.
But in the future it will not allow to do so,if the constraint is in ENABLE staus.So i am just trying to disable the constraint to insert only into child table & trying to enable it with NOVALIDATE option .
But the following exception is coming .
SQL> alter table scott.emp ENABLE constraint fk_deptno enable NOVALIDATE;
alter table scott.emp ENABLE constraint fk_deptno enable NOVALIDATE
ORA-00905: missing keyword
SQL> alter table scott.emp ENABLE constraint fk_deptno NOVALIDATE;
alter table scott.emp ENABLE constraint fk_deptno NOVALIDATE
ORA-00933: SQL command not properly ended
Instead of dropping & recreating the same constraint , is there any alternate way to do this ?
Forms :: Trigger When Validate Item
Dec 18, 2012i need to pop up an alert using a trigger to test the data type of a field per example if the input is not a number an alert will popup "Please insert a number"
View 5 Replies View RelatedSQL & PL/SQL :: How To Validate Given String For Alphanumeric Character
Feb 14, 2011To built the Pl/SQL function or SQL query to validate the given string for alphanumeric character.
I would pass the string of size 10 character, we need to validate first 5 character as alphabet, next 4 character as numeric and last 1 character as alphabet.
I will pass the each row value to the function, it need to return "T" or "F" based on the condition,first 5 character as alphabet, next 4 character as numeric and last 1 character as alphabet.
Here is the DML and DDL.
[code]
create table abc ( classid varchar(10));
insert into abc values ("abcde1234f");
insert into abc values ("abcde12345");
insert into abc values ("ab1de1234f");
insert into abc values ("abcde1234f");
insert into abc values ("abcd21234f");
[code]
Output:
Input : abcde1234f Output : T
Input : abcde12345 Output : F
Input : ab1de1234f Output : F
Input : abcde1234f Output : T
Input : abcd21234f Output : F
SQL & PL/SQL :: Difference Between Disable Constraint And No Validate
Aug 22, 2010Is there any logical difference between DISABLE CONSTRAINT and DISABLE NOVALIDATE, in order to accept any new values into the table regardless of violating anything?
View 2 Replies View Related