PL/SQL :: Query For Checking Column Having More Than One Type Of Values?
Dec 6, 2012
I have a table and data as below.I need to select all the records if value have both 'M' and 'D'.If there is only 'M' or 'D' then select should not pull any records.
WITH data as (
Select '1' id, 'M' value from dual union all
Select '1' id, 'M' value from dual union all
Select '1' id, 'D' value from dual union all
Select '1' id, 'D' value from dual )
select value from data group by value
I tried below query but it is not working.
WITH data as (
Select '1' id, 'M' value from dual union all
Select '1' id, 'M' value from dual union all
Select '1' id, 'D' value from dual union all
Select '1' id, 'D' value from dual )
select * from data group by value having sum(count(distinct(value))) > 1
View 3 Replies
ADVERTISEMENT
Sep 22, 2009
In my table ,data type of one among 10 columns is defined as varchar2(10).I need to check that column should accept only numeric value(0 to 99) or alphabetic value(a to z or A to Z) .It should not accept Alpha-numeric values.I tried like this
select c3 from demotab where to_number(c3) not between ascii('a') and ascii('z') ;
but I got error like 'Invalid Number'.how to implement this thro sql query.
View 3 Replies
View Related
Nov 12, 2012
need to dynamically assign value of each column of a row type variable:The example is like that:
Table "Student_list" is as follows:
---------
COL1 VARCHAR2(50),
COL2 VARCHAR2(50),
COL3 VARCHAR2(50)
[code]....
Is there any work around where i can dynamically built my variable or table.column name and assign a value to it?
View 7 Replies
View Related
Mar 16, 2013
I have extracted in the following XML document some Mpeg7 visual descriptors from an image and I saved it in an XMLType column. I would like to use XMLQuery to extract the data from the Value node. how write such a query. I could not get the proper Xpath to any node.
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Mpeg7 xmlns="http://www.mpeg7.org/2001/MPEG-7_Schema" xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance">
<DescriptionUnit xsi:type="DescriptorCollectionType">
[code]...
View 1 Replies
View Related
Jan 19, 2013
CREATE TABLE t2
(
id NUMBER,
ename2 VARCHAR2(20),
sal2 NUMBER,
job2 VARCHAR2(20),
conid NUMBER
[Code]...
My requirement is like when I am calling the procedure P1 with some values then it should check the table "t2".And table "t2" is linking with table "t3".
So what ever the column "verify" is there, it should check the incoming values against it. If matches success otherwise reject it.Later the incoming values is stored different tables.I am doing it in the above way by hard coding some value.
BEGIN
p1(1,'MILLER',500,'ADMIN'); --REJECT
p1(1,'MILLER',5000,'ADMIN'); --ACCEPT
P1(2,'MILLER',5000,'SALESMAN');--ACCEPT
END;
View 5 Replies
View Related
Oct 23, 2012
i what to update a column data type am geting this error
ORA-02267: column type incompatible with referenced column type
my query is
when i run
SELECT b.table_name||
' joins to the '||
a.table_name||
' table on '||
c.column_name||
' = '||
d.column_name Table_relationships
[code]....
View 9 Replies
View Related
Mar 8, 2013
I have created the below types and oracle objects.
create or replace type T_EMA_NP_SETDEL_RESP_REC as object
(
respCode number,
respDesc varchar2(255)
)
create or replace type T_EMA_NP_RANGE_LNPTICKET_TAB AS TABLE OF T_EMA_NP_RANGE_LNPTICKET_REC
create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC
The following types are created in the Package specification
type t_resp_rec IS RECORD
(
resp_code number,
resp_desc varchar2(255)
);
--
subtype t_ema_lnpticket is T186_IN_REQ_PARAMETER.T186_EMA_LNPTICKET%TYPE; -- Number
type t_ema_lnpticket_tab is table of t_ema_lnpticket index by binary_integer;
I have the following two procedures
PROCEDURE getEMAReturnResponse(
p_in_call_request_id IN number,
p_ema_resp_rec IN t_ema_np_setdel_resp_rec,
p_ema_range_lnpticket_tab IN t_ema_np_range_lnpticket_tab,
p_endof_event IN varchar)
PROCEDURE Return_Response(p_in_call_request_id IN number,
p_ema_resp_rec IN t_ema_resp_rec,
p_ema_lnpticket_tab IN t_ema_lnpticket_tab,
p_endof_event IN varchar2)
getEMAReturnResponse Procedure:
Accessed by Java application to pass the values. Should call the Return_Response procedure and pass the values received from Java.
Return_Response Procedure
The p_ema_lnpticket_tab is a sort of array that can have multiple values. Please see the example of values. Has all the business rules and validation that should be adhered.
Example of Vaules
p_in_call_request_id = 1
p_ema_resp_rec = 12345, 'Operation Failed'
p_ema_lnpticket_tab = (1,2,4,5)
p_endof_event = Y
View 2 Replies
View Related
Mar 8, 2013
I have created the below types and oracle objects.
create or replace type T_SETDEL_RESP_REC as object
(
respCode number,
respDesc varchar2(255)
)
--
create or replace type T_EMA_NP_RANGE_LNPTICKET_REC as object
(
ticket number
)
create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC
The following type is created in the Package specification
type t_resp_rec IS RECORD
(
resp_code number,
resp_desc varchar2(255)
);
I have the following two procedures
Procedure getResponse(p_call_request_id IN number, p_resp_rec IN t_setdel_resp_rec,
p_range_ticket_tab IN t_range_icket_tab, p_endof_event IN varchar)
PROCEDURE ProcessResponse(p_call_request_id IN number, p_resp_rec IN t_resp_rec,
p_ticket_tab IN t_ticket_tab, p_endof_event IN varchar2)
The get Response procedure is a wrapper procedure exposed to Java to pass values. The Process Response procedure is a main procedure where all logics and business rules are handled.
The Problem is:
How can I pass the values from get Response procedure to Process Response procedure. So that rules and validations are applied. Please note the p_ticket_tab may have many ticket numbers corresponding to p_call_request_id.
Values E.g. :
p_call_request_id = 1
p_resp_rec (1234, 'Error found')
p_range_ticket_tab (1,2,3,4,5)
p_endof_event = 'Y'
View 7 Replies
View Related
Dec 22, 2011
I have a table with multiple rows for the KEY attribute(its not a primary key) and a Rank for each row.
I want a query which fetches one row per KEY attribute.The row with lesser Rank should be considered. But in-case if the value is null for any column the value for next Rank should be considered.
WITH TMP_TBL AS
(
SELECT * FROM (
SELECT 'A' DUN,'1' RNK,'A21' col1,NULL col2,'A41' col3,NULL col4 FROM dual
UNION ALL
SELECT 'A','2','A122','A23',NULL,NULL FROM dual
UNION ALL
SELECT 'A','3','A32','A33',NULL,'A35' FROM dual
[code].......
DUN is the KEY attribute . RNK is the Rank for each Row. COL1... COL4 are data attributes
The results I am expecting is
DUNCOL1 COL2 COL3 COL4
AA21 A23 A41 A35
BB12 B23 B15
CC12 C13 C33 C14
I want this to be done with SQL only. So I tried various ways but none were successful.Finally I created a Multi Row function row_nvl and it worked.
SELECT DUN,
row_nvl(rownvl_param_type(RNK,col1)),
row_nvl(rownvl_param_type(RNK,col2)),
row_nvl(rownvl_param_type(RNK,col3)),
row_nvl(rownvl_param_type(RNK,col4))
FROM TMP_TBL
GROUP BY DUN
But I don't think my manager will allow me to deploy a Multi Row function .
View 2 Replies
View Related
Dec 13, 2012
I have table with the values as below.
C1C2C3C4
NAMEJOHN10ABC
NAMESMITH30DEF
NAMEROBERT60XYZ
I dont want to print the repeated value(NAME) of C1 multiple times as below.
C1C2C3C4
NAMEJOHN10ABC
SMITH30DEF
ROBERT60XYZ
I could do it using the below query using union with the rownum.
select * from (
select rownum rn, c1,c2,c3,c4 from table_new
) where rn =1
union
select * from (
select rownum rn, decode(c1,null,null),c2,c3,c4 from table_new
) where rn between 2 and 3
Is there any other way of displaying using a single sql query.
View 17 Replies
View Related
Jun 11, 2012
How can check that which query is taking how much load or time in execution on server in oracle.I want the soluation as like that, run the query and get the detail in desc order and identify that this query is required to tune or kill the session.
View 1 Replies
View Related
Jul 12, 2011
My requirement is to load the data from feed file into two tables based on the value of a column in feed file.
say column in feed file is "Activity_Value" . If the Activity_value is 10, the data from feed file should be loaded into Table A and If the Activity Value is 20 the data from feed file should be loaded into Table B.
View 3 Replies
View Related
Aug 30, 2013
1) I got column date of joining which accepts date in below format DD-MON-YYYYDD-MON-YYMON-DD-YYYYMON-DD-YYMonth DD,YYYY Question:-
how do i check whether all dates in Date of joining column are in above format or not using sql query? 2) I got one more date column which accepts date in below formatMMDDYYYYYYYYMMDDMM/DD/YYYYMM/DD/YYYYYY/DD/MM Question:-
how do i check whether all dates in date column are in above format or not using sql query?
View 26 Replies
View Related
Jun 12, 2012
Our production DB version is 11.2.0.1 and we do use DBArtisian. Everyday morning we check table space usage using that tool and it generates the below listed query. It used to run in 15 secs. But since one week it is running for 5/6/8 mins. I have updated the statistics on sys objects and tuning advisor created the execution plan. So, now it is running in 2 mins. Nothing has been changed in the DB configuration. I see the same query running in secs in dev environment. understand what might be the issue and how could I improve the performance of this query.
SELECT SUB.TABLESPACE_NAME, SUB.STATUS, SUB.EXTENT_MANAGEMENT, SUB.SEGMENT_SPACE_MANAGEMENT, SUB.TOTAL_SPACE_MB, SUB.USED_SPACE_MB, SUB.FREE_SPACE_MB, SUB.PERCENT_FREE_SPACE, SUB.CONTENTS, SUB.TABLESPACE_GROUP, CASE WHEN SUB.TABLESPACE_NAME = P.VALUE AND SUB.CONTENTS = 'UNDO' THEN 'YES' ELSE 'NO' END
[code]...
View 5 Replies
View Related
Dec 23, 2010
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
View 9 Replies
View Related
Nov 22, 2012
I'm using the odp.net type factory (class IOracleCustomType and the specific annotations...) to map udts to c# classes.By now I had no problems with it, because I've never used "ref" attributes within the udts.Now I need the use of "ref" attributes within udts but it seems that the odp factory cannot handle those references.
For example:_*
A Simple Type:
create or replace type bookcontent_t force as object (
id number(20)
, name varchar2(1000)
);*And a collection with references to objects of this type:*
create or replace type fms_bookcontent_ref_cln force as table of *ref* bookcontent_t;The c# class is generated by odp.net.
When I retrieve data by using odp.net I get a collection with the correct number of elements but the values are null.What can I do to solve this problem with "ref" attributes/lists? How can I get the referenced object instances?
View 0 Replies
View Related
Feb 26, 2013
I have the following database function.
GetRegionDetails(id in varchar2, o_lat out number, o_lon out number);
The problem is, the output values are returning as whole numbers ie. 38.108766567 is being returned as 38 and -78.16423574566 is returned as 78
what data type I should use so that my output is returns all the decimal values?
View 5 Replies
View Related
Jul 25, 2013
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
View 2 Replies
View Related
May 4, 2012
I need to insert values fetched from an object type into an empty table.
-- >> table creation:
create table ASSET
(
ASSETID NUMBER(5),
ASSETTYPE VARCHAR2(20));
-- >> created an object_type
SQL> create or replace type obj1 as object(v_ASSETID NUMBER(20),
2 v_ASSETTYPE varchar2(20));
3 /
Type created
--- >> create the package
SQL> create or replace package test_arr is
2 type nest_tab is table of obj1;
3 procedure insert_data(t_nest IN nest_tab);
4 end test_arr;
5 /
Package created
SQL> create or replace package body test_arr is
2 procedure insert_data(t_nest IN nest_tab) is
3 begin
4 for i in t_nest.first..t_nest.last loop
5 insert into asset(ASSETID,
6 ASSETTYPE) values (t_nest(i).v_ASSETID,
[code]....
-- >> a block to execute the above package:
SQL> declare
2 type ref_tab is table of obj1;
3 ref_tab1 ref_tab;
4 begin
5 ref_tab1 := ref_tab(1,'a');
6 test_arr.insert_data(ref_tab1);
7 end;
8 /
But I am getting the below error when executing the package:
ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 1:
[code]....
View 6 Replies
View Related
Apr 10, 2013
On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.
I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.
So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:
a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?
The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.
---
"Application Express 4.2.1.00.08"
View 2 Replies
View Related
Jul 27, 2012
I am working on APEX 4.1.1 on Linux server. I have created one application in which I have take a HTML region where i have written the following code:
<input type="text" id="APP_ITEM1" name="APP_ITEM1" />
I am able to see the text box in page, but not able to fetch the value from it.
View 9 Replies
View Related
Jun 19, 2012
I want to pass multiple column values of a row in an interactive report page to hidden items in another page through column link. And I did it successfully. However, I found I need to pass more than 3 columns of a row in this report, while a column link only permits me to pass 3 column value at most. Is there anyway that I can pass more column values to hidden items in another page?
View 3 Replies
View Related
Jul 19, 2013
CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
View 4 Replies
View Related
Sep 18, 2011
I have a query in this code?
declare
old_name varchar2(20) not null:='stephen';
new_name old_name%type;
begin
dbms_output.put_line(new_name);
end;
will new_name variable will inherit the datatype only or the default value as well?The output of this code will be stephen or not?
View 7 Replies
View Related
Aug 23, 2011
what is the use of using 'n' in a query type as mentioned below:
select * from scott.emp a where n =
View 11 Replies
View Related
May 9, 2012
I have to create a table which should store data at Week level. The table have the following columns
Product id, Loc id, Business group id, FISCAL WEEK , Revenue,
Fiscal week column will have data as '2011-W01', '2011-W47' etc.
What should be the data type for fiscal week column. Based on this table i have to create a calculated column which should fetch trailing 12 weeks average for each row.
View 5 Replies
View Related
Mar 3, 2011
I am working with Oracle 8i and I have a problem while applying the below query:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
where ENTRYDATE > sysdate-1
and rownum <10
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00
[code].....
The time appears as 00:00:00. I said it's something weird because if I take the where condition off, then something like this appears:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
I am using the same query on a different table and there's no problem on it:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_XX_PROGRAM
where ENTRYDATE > sysdate -10 and rownum <10
03-03-2011 17:06:48
03-03-2011 17:06:48
03-03-2011 16:59:08
03-03-2011 16:59:08
03-03-2011 12:04:21
I checked the DDL of both tables and the only difference is that the TBL_XX_PROGRAM have the ENTRYDATE field defined as not null and the ENTRYDATE on the TBL_BANDWIDTH_READS does not have the same constraint, could it affect?
View 3 Replies
View Related
Sep 5, 2012
I am trying to run select query on xml using xmltable. One of the tag returns date and I want to take it in date format in xmltable column.
select t.* from XMLTYPETEST xt ,
XMLTable ('/TestData/trade' passing xt.data
columns
test varchar2(100) path 'GenTest/Id',
testdate date path 'DateTest/Date')t;
But its throwing error as -
SQL Error: ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine
ORA-01830: date format picture ends before converting entire input string
29958. 00000 - "fatal error occurred in the execution of ODCIINDEXCREATE routine"
*Cause: Failed to successfully execute the ODCIIndexCreate routine.
View 2 Replies
View Related
Feb 19, 2007
how to get the type, length, flags in oracle by query..??
View 1 Replies
View Related
Apr 2, 2012
I am in the need of using a table type object in SQL query.
I have a package which has a spec in which I have declared :
TYPE TESTREC IS RECORD
(
RE_ID NUMBER(9),
RATING_TARIFF_NAME VARCHAR2(40),
);
TYPE TESTTABTYPE IS TABLE OF TESTREC;
TTR TESTTABTYPE;
In one of the package procedures I am collecting data into the above indicated table type object (TTR):
SELECT
RE_ID,
RATING_TARIFF_NAME
BULK COLLECT INTO TTR
FROM TESTPACKTAB;
This works fine. The values get collected into TTR and am able to print them too.
But when I :
SELECT
AA.RATING_TARIFF_NAME
INTO v_name
FROM
TABLE( TTR ) AA ;
in the same procedure immediately after the collection I get the error while running the procedure :
ORA-21700 : Object does not exist or is marked for delete.
View 5 Replies
View Related
