I have to write a stored procedure/function which has to generate the combination of numbers
For eg: IF I/p is an array of numbers a(i) = [1,2,3]
I want to get various combinations of numbers with these three digits.
writing the stored procedure generating the output for this
I need to fire the KEY-DUPREC trigger as I click on the key combination Shift+P therefore; I added the following line of code in the frmweb.res file.
80 : 1 : "Shift+P" : 64 : "Duplicate Record"
It worked iff no error was raised. So, if I have a raise form trigger failure in the KEY-DUPREC trigger, a capital P will appear in the text field that called the trigger.
I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :
TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209
My expected results should look like :
ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209
Is there any API to generate XSDs from Oracle tables? DBMS_XMLSCHEMA.GENERATESCHEMA accepts only Types and not the Table names. I want to generate XSD from the tables including length, null and default validations.
View 6 Replies View RelatedI am working on SQL Developer 1.5.1, i need to prepare hourly record of the activity on the database, for that i have a sql query that gives me the report as per selected columns on hourly basis.I need to prepare 24 reports a day.
Each time i have to go to the query , change the date as per hours like form 22:06:2011 10:00:00 to 22:06:2011 11:00:00 and get the report and export it in excel.
I want to automate the script so that whenever i run the script , it just asks me the date and runs the script 24 times and fetch me the hourly report of whole day.
the query syntax is something like this
Select
............................
...........................
..........................
from
..........................
........................
........................
where
And.......................
And...................
And......................
And req date between to-date( 22:06:2011 10:00:00) And to-date(22:06:2011 11:00:00)
Order by 7,1,2,3,4,5
Is there any possibility that i can automate the script to automatically change the hour itself and generate a report in excel 24 times?
I have two control block i.e. class_register and student_info. In which student_info is multi-record block which contains stud_id, stud_name, stud_roll_no. Based on the class_register block, stud_id and stud_name is generated in the student_info table. Now I want to generate stud_roll_no automatically until the last record is present.
View 4 Replies View RelatedI have a requirement in this format.
These are the scripts for the 2 input tables
CREATE TABLE TMP_split
AS
SELECT 1000 AS grp_id, 'abc' AS ATTRIB, 60 AS PCT FROM DUAL
UNION ALL
SELECT 1000 AS grp_id, 'pqr' AS ATTRIB, 40 AS PCT FROM DUAL;
[Code].....
The output needs to be in this format
key | val | grp_id | pct
------------------------------------------
6~7~8 |DIM_11~DIM21~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM21~DIM31 | 1000 | 40
6~7~8 |DIM_11~DIM22~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM22~DIM31 | 1000 | 40
6~7~8 |DIM_11~DIM23~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM23~DIM31 | 1000 | 40
Is there a good way to dynamically obtain this output either by PL/SQL or by query?
This is a case where combination needs to be used. However, I am not able to arrive at the right algorithm for this
I need a "solution", guidance to a problem I have to solve. I have different letters with a value associate to it like
A:10, B:20, ..., G:250 and I have a Target to reach TARGET= 90
and I need to find among all letters I have which combination is equal or closest to my target.
A:10
B:20
C:20
D:20
E:30
F:40
G:250
IF there is more than one solution possible, the first found is perfect. With my example I can reach 90 with different combinations:
A+B+C+F
A+B+D+F
A+C+D+F
B+C+D+E
B+E+F
C+E+F
D+E+F
To complicate the things I have up to 10 different letters that need to combine to match my target. If there is no combination that exactly match my target, the closest higher combination is picked.
I have a requirement here. User wants when he pressed 'CTRL+E' on the form item, he must get another form. Actually 'CTRL+E' is defined for EDITOR. Then how could I achieve this?
View 2 Replies View RelatedI have contents like below:
BREAD,BISCUIT
BREAD,MILK,BISCUIT
COKE,MILK
MILK,SUGAR
Now combination from each row will be like below (Just for understanding):
BREAD,BISCUIT --> [COMBINATION] {BISCUIT} {BREAD} {BISCUIT,BREAD}
BREAD,MILK,BISCUIT --> [COMBINATION] {BISCUIT} {BREAD} {MILK} {BISCUIT,BREAD} {BISCUIT,MILK} {BREAD,MILK} {BISCUIT,BREAD,MILK}
COKE,MILK --> [COMBINATION] {COKE} {MILK} {COKE,MILK}
MILK,SUGAR --> [COMBINATION] {MILK} {SUGAR} {MILK,SUGAR}
Now the ultimate aim is to find unique combinations (even if the same combination is present in different rows, we have to consider it as one combination), along with its frequency, result will be like below:
BISCUIT [occurence in 2 transactions and total 4 transactions] = 2/4 = .5
BREAD [occurence in 2 transactions and total 4 transactions] = 2/4 = .5
COKE [occurence in 1 transactions and total 4 transactions] = 2/4 = .25
MILK = 3/4 = .75
[Code]....
writing query to find unique combination like above? What I have tried is as below:
create table test (row_no number, col_no number, item varchar2(50))
/
insert into test values (1,1,'BREAD');
insert into test values (1,2,'BISCUIT');
insert into test values (2,1,'BREAD');
[Code]....
But I am not able to form the exact query. let me know if this can't be done through a single query.
How to achieve "Prev_Value" column as shown below without using ORACLE analytic functions
I have records stored in table for various categories and based on ID / Name / Office / Product / Category combination I want to achieve previous value column through efficient SQL query
Test Scripts as below
CREATE TABLE TEST_Prev
(
ID1 NUMBER(3),
Name1 VARCHAR2(10),
OFFICE VARCHAR2(20),
PRODUCT VARCHAR2(20),
Overall VARCHAR2(20),
DATE1 DATE,
VALUE1 NUMBER(1)
);
commit;
[code]......
Expected output as in attached sheet.
My need is to locate an occurrence of symbols starting from "s." (non-capital letter), following by word (with any capital letter at the beginning) and ending with ", " (comma and space symbols).
Ex:
select 'jeklghje, s.Glkgje, u.slgjwek, 904869' as tt from dual union all
select 's.Tklgj, u.slgjwek, 23578, elslgjs' as tt from dual union all
select 's.klgj, u.ekgjes, 238573, dlsjkgj' as tt from dual
I'm looking for occurrence of "s.Glkgje, " and "s.Tklgj, ".
I think some combination of REGEXP_INSTR and REGEXP_SUBSTR should be useful, but I'm not familiar with these functions so good yet.
I have data like:
Col1 Col2 Col3
1 1 N
1 1 N
1 1 Y
2 1 N
2 1 N
2 1 N
I need in output only combination of 2 (col1 value) and 1 (col2 value). i.e. consider only those records where all records of key combination (col1, col2) have 'N'
How can I delete the duplicate combination of records from the below table.
CREATE TABLE test
(
gidNUMBER(10),
pidNUMBER(10)
);
INSERT INTO test VALUES (10,20);
INSERT INTO test VALUES (20,10);
INSERT INTO test VALUES (25,46);
[code]....
The condition is if GID = PID and PID = GID then only one combination of these records should be retained. For example Out of 10-20 and 20-10 only one record should be retained.
Expected result after deletion
GID PID
---------- ----------
10 20
25 46
89 64
15 16
19 26
i'm trying to import data to QUANTUM "oracle database" from Oracle database import assistant using OCDia.exe with SQL statement below and i'm getting this error message "Part Number and manufacturer combination must be unique"
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE ACE_LISTPRICE_LEADTIME (P_IMP NUMBER)
IS
C DIA_RL_PKG.CURSOR_TYPE ;
[Code]....
Given a table with some columns and data associated with that. Need to find out a column or a combination of some columns, so that the values or combination of values will be unique in the table.The table and number of columns and the columns will be dynamic.
View 10 Replies View RelatedI have constructed a cross join query, with the test case below
create table ajit_sites (
site_id char(1));
insert into ajit_sites values ('A');
insert into ajit_sites values ('B');
insert into ajit_sites values ('C');
COMMIT;
sql below is constructed to display combination of all sites (cross-join), it also removes records where "origin" is the same with "dest"
select
a.site_id origin, b.site_id dest
from
(select site_id from ajit_sites) a,
(select site_id from ajit_sites) b
where
a.site_id <> b.site_id b
Is there any way i could remove records with the behavior below
Origin , Dest
A , B
B , A
For instance from the example above, i want to only retain one of the records since record (A, B) or record (B, A) means the same.
I have 3 tables in the Oracle database( emp, employee, emp1) which has following record values in it.
empidenamejob
7369, 'SMITH', 'CLERK'
I would like to list these 3 tables thru SQL/PLSQL, having the above record values combination. Also, the name of the columns could be different in all the tables i.e. name could be 'ename' in Emp table , and 'name' in Employee table. Is there way to do this in SQL or PLSQL ?
In my forms(version 9i), list item values showing with ALT + DOWN key combination but I want to change it to only DOWN key.I search this combination in FMRWEB.RES file but there isn't any entry of ALT + DOWN.
View 1 Replies View RelatedI have a very simple table with 2 columsn. As_of_date is one of the column. This column is "Date" data type.
When I use distinct clause inside a to_char function it gives the following error:
ORA-00936: missing expression
00936. 00000 - "missing expression"
The Sql is
select to_char(distinct(as_of_date),'mm-dd-yyyy') from sales
I can't see any syntax error in the sql..but forsome reason, it doesn't work.
I am facing the following issue while generating xml using a sql query. I get the below given table using a query.
CODE ID MARK
==================================
1 4 2331 809
2 4 1772 802
3 4 2331 845
4 5 2331 804
5 5 2331 800
6 5 2210 801
I need to generate the below given xml using a query
<data>
<CODE>4</CODE>
<IDS>
<ID>2331</ID>
<ID>1772</ID>
</IDS>
<MARKS>
[code].....
NOTe: IDS which are distinct needs to be displayed. ALL MARKS should be displayed though there are duplicates.
In the below data, a container is moving from one city to another. 1,2 ,3 can be any number which i want to generate and use as keys to group the cities. Eg: AUH, JEB, CIW belong to the same key=2; SIN, IKT belong to a new group 4. The City where difference between the Seq# is greater than 1 (eg between S8W and AUH), a new group starts.
Conotainer #CitySeqI want this
-------------------------------------------
Container1S8W5251
Container1S8W5261
Container1AUH5362
Container1AUH5372
Container1JEB5382
Container1JEB5392
[code]....
Below is the procedure I created to generate csv file from oracle procedure.
PROCEDURE proc_csv(p_path varchar2 , p_separator varchar2 default ',')
AS
CURSOR c_data
IS
select * FROM product ;
v_file UTL_FILE.file_type;
BEGIN
[code]...
I have two doubts about this procedures
1) In one schema this procedure is generating empty csv file with zero bytes, while in other schem It's working fine, It's showing data. I am unable to make what's the difference.
2) The csv file is not displaying column headings. I want column headings to be displayed on csv file.
Im generating some xml from a table which works fine. Here is an example of what I'm doing.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
create table "VTEST"(
"PROJECT_NUMBER" VARCHAR2(50 BYTE),
"ORDER_NUMBER" VARCHAR2(50 BYTE),
"PART_NUMBER" VARCHAR2(50 BYTE),
[code]....
so each section would list its unique values.
generating oracle table definitions from XML .xsd files?
View 5 Replies View RelatedI know I am using a outdated Oracle what to do my company are not willing to upgrade because of the economic situtation.
I need generating weekly report.
I have a condition for monthly like this
invoice_date BETWEEN TO_DATE('01-JAN-13 00:00:00', 'dd-mon-yy hh24:mi:ss')
AND TO_DATE('31-JAN-13 23:59:59', 'dd-mon-yy hh24:mi:ss')
now if I want a weekly report Monday - Saturday, How to go about it?
I have data like this:
a1 a2 a3 a4
ABC ABC xx zz
ABC xx mm mm
fg ui ok pl
I want to give them unique codes like :
Code for column
a1 Code1
ABCC-1
ABCC-1
fg C-2
then column
a2 Code2
ABC C-1
xxC-3
uiC-4
then column
a3 Code3
xxC-3
mmC-5
okC-6
then column
a4 Code4
zzC-7
mmC-5
plC-8
I can add these `columns in the table Code1 , Code2 etc columns to update.
I have emp table
Empno from_date to_date ScaleBonus_pct Salary
101 31/01/2010 14/02/2010Grade120,10,10,15,25,205000
Reqd. o/p
Emp nofrom_dateScaleEach_WeeK_Sal
10131/01/2010Grade11000(from 20 %X5000
1017/2/2010Grade1500(from 10 %X5000
10114/02/2010Grade1500(from 10 % X5000
10121/02/2010Grade1750(from 15 % X5000
10128/10/2010Grade11250(from 25 % X5000
1017/2/2010Grade11000(from 20 % X5000
we have to split the date by 7 days,should be on Sunday...and if we split by 7 days we have upto 31/01,07/02 and 14/02...but we have to generate 6 days because 20,10,10,15,25,20 (since comma separated value is 6 ).
In weblogic server Axis2 is deployed and it is showing Active.And we have uploaded ODI invoke service also in AXIS2 succesfully and it is showing Active status also...By using the soap its generating the XML with below mentioned error..
some info:- We rae using ODI 10g.
Weblogic 10.3.5.0
XML GENERATING:--_
<xs:element name="inPhasesUptoAndIncludingPostDispatch" nillable="true" type="xs:anyType" />
<xs:element name="key" nillable="true" type="xs:anyType" />
<xs:element name="localPolicyAssertions" nillable="true" type="xs:anyType" />
[code]...
The XML page cannot be displayed Cannot view XML input using XSL style sheet. correct the error and then click the Refresh button, or try again later.
The operation completed successfully. Error processing resource [URL]
</xs:schema></wsdl:types><wsdl:message name="invokeScenarioMessage"><wsdl:part name="part1" element="ns...
I Have got a requirement where we have to generate an xml conforming to a particular XSD.
What i will be having already in place is an XSD document.
We are thinking of making a procedure which will be recieving the set of inputs required for creating that xml.(Data content of that XML.)We would have to make the XML according to that XSD.
Everytime Parsing the XSD is not feasible.We are looking at a solution which would be easy to make the xml from xsd at runtime.These values (DATA) will be available at run time.