SQL & PL/SQL :: Check For Those Ukeys With Multiple Eids

Feb 27, 2013

I want to check for those ukeys with multiple eids and different tds values ('ukey1' and 'ukey3' below), if the correspoding tds value in abdtab is null,so our query *should ignore 'ukey4' (multiple eids but same tds value), and ukey5 (not even mulltiple eids)*points to consider:

1. ukeys from aetab table should have multiple eids
2. for those records, go to abdtab and check if tds value is null or not, join condition for aetab and abdtab is

aetab.bkey = abdtab.addkey;
create table aetab(ukey varchar2(100),eid number, bkey varchar2(100), tds varchar2(100));
create table abdtab(tds varchar2(100), addkey varchar2(100));

insert into abdtab values (NULL, 'ukey1');
insert into abdtab values ('somevalukey3','ukey3');
insert into abdtab values('somevalukey4','ukey4');
insert into abdtab values('somevalukey5','ukey5');
[code]....

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Multiple Check Constraints

Feb 17, 2011

why my code wont work. Am trying to create a constraint so that the only data that can be entered into the size field is a number between 4 and 15.

CREATE TABLE accommodation
(chalet_no NUMBER (3) PRIMARY KEY,
chalet_name VARCHAR2(20) NOT NULL,
size NUMBER CHECK (size >3) AND CHECK (size <16),
priceperweek NUMBER (4,2) NOT NULL);

Does seem to like line 4 of the code.

View 1 Replies View Related

SQL & PL/SQL :: Check If Multiple Column Values Are Same?

Sep 16, 2011

I have a table as follows

create table teststr (indname varchar2(20),
counter1 number,counter2 number,counter3 number,counter4 number);
insert into teststr values('a',10,20,30,30);
insert into teststr values('b',10,20,5,3);
insert into teststr values('c',2,4,5,2);
insert into teststr values('d',1,2,3,4);
insert into teststr values('e',4,5,4,4);

Now i need the output if any of the column values are same.

output should be

select indname from teststr where counter1=counter2
or counter1=counter3 or counter1=counter4
or counter2=counter3 or counter2=counter4
or counter3=counter4

a
c
e

Is ther any other way to write the query instead of the numerous or conditions if i want to compare the column values in a table.

View 6 Replies View Related

Forms :: Multiple Sort Options / Check Boxes On Header Block

Sep 15, 2013

I have a master detail based transaction form, i want to create three check boxes on header block, based on selection sorting must happen on detail block which has three fields, for example there are three fields item_code,item_name,item_qty, if user selects first check box then sorting will be on item_code, if he presses second then sorting should be based on item_name, likewise if he presses check box three then sorting will be based on qty, if choses two or more fields then sorting will according to that combined order.

View 4 Replies View Related

Forms :: Put One Check Box To Check All The Check Boxes?

Jul 30, 2011

I want to put one check box to check all the check boxes.how can i do this?

View 5 Replies View Related

Server Utilities :: Loading Multiple Input Files Into Multiple Tables

Jul 9, 2012

NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'

File :-mauri.export.ngf

NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc

CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......

find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.

View 6 Replies View Related

Server Administration :: Multiple Database With Multiple Instance On Same Machine?

Mar 16, 2011

can we have multiple database version running in a single machine with multiple instances provided there are enough resources.Can we do in RAC only?

View 15 Replies View Related

SQL & PL/SQL :: How To Insert Data In Multiple Bases Using Multiple Database Links

Jan 2, 2013

how to insert the data in multiple bases( Same table structure in different bases) using the multiple database links?

View 4 Replies View Related

PL/SQL :: Merge Multiple Rows Into Single Row (but Multiple Columns)

Oct 17, 2012

How to merge multiple rows into single row (but multiple columns) efficiently.

For example

IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100

where different attributes mean different thing for each Information_type. For example for Information_Type=Location

Attribute_1 means Country
Attribute_2 means State and so on.

For example for Information_Type=Stats

Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.

I want to create a view that shows like below:

IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100

View 1 Replies View Related

SQL & PL/SQL :: Multiple Rows On A Table To Multiple Columns On One Row

Nov 26, 2010

I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below

TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan

My desired result is to be able to show the output as follows

Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan

View 6 Replies View Related

PL/SQL :: Multiple Rows To Multiple Column

May 6, 2013

I have a table TableA containing 2 columns ( Name and Value). Here I know what are the values for column Name

TABLEA
=======
Name Parameter
-------------------------
Nexus 11
GPlay 21
Demo 31

I need a query which provides the below output

Desired Output:
======
First Second Third
11 21 31

I have tried the below query
SELECT
DECODE (name,'Nexus', parameter) First,
DECODE (name, 'GPlay', parameter) Second,
DECODE (name, 'Demo', parameter) Third
FROM (SELECT name, parameter FROM TableA where name in ('Nexus','GPlay','Demo'));

This gives me the output

First Second Third
11 <Empty> <empty>
<empty> 21 <empty?>
<empty?> <empty?> 31

Is there any way to get the output in single line.

View 3 Replies View Related

SQL & PL/SQL :: Uses Of Check Operator

Sep 16, 2010

I have a table in SQL , I am creating a column Of name in it , i want to restrict user to enter name in Capital only ,and i want to create this at table level . I tried Check Operator but failed .

create table my_tab
(U_name varchar2(30) ,
constraint ck_check (U_name = upper(m_name))
/

View 6 Replies View Related

SQL & PL/SQL :: How To Check If Value Is Right Date

Dec 16, 2010

I have a text column having date data. In the format of YYYYMMDD.Some of the records might have invalid date data as well. Is there any function which checks whether it's value is a right DATE? Something like

IS_DATE(my_text_date,'DD-MM-YYYY')

should return TRUE if it's a valid date.

View 3 Replies View Related

Check For Used Features?

Jul 6, 2011

I would like to check and make sure that features like partitioning, Tuning, Diagnostics etc. have NOT been used. In Oracle 10 + we have the dba_feature_usage_statistics.

But how can we check this in 9.2 ?

View 1 Replies View Related

PL/SQL :: Check Given Value Exists Or Not

Jun 18, 2013

i have a procedure like create or replace procedure studrec( a in sid%rowtype)asi sid%rowtype;beginselect sid into ifrom students; i need to check whether sid is exist in the variable i or not

View 5 Replies View Related

PL/SQL :: How To Check CPU Usage

Mar 6, 2013

I have one procedure which is executing daily two times(i.e morning run and evening run) . In morning run it's executing around 150 mins and evening run executing around 25 mins.

It's happening for all procedures executing in longer run in morning. There is NO LOCKS, I have checked LOCKS while executing morning run. I suspects CPU usage in morning run.

How to check CPU usage ? AND also each session how much utilizing CPU. I'm using oracle 8i database, I know it's older version but my company is using oracle 8i. So i have to look in this database version only.

View 6 Replies View Related

How To Check / LARGEADDRESSAWARE Flag In 10g

Sep 17, 2013

I am not a DBA, we are using Oracle 10g DB for our PLM applictaion Enovia, we are now trying to do 4GT in our windows 2003 server where our DB kept, while reading articles about this. we are informed that /LARGEADDRESSAWARE (IMAGE_FILE_LARGE_ADDRESS_AWARE) flag needed to be set to True in the application (here it is Oracle) to use more than 2GB in the RAM.

So My question is "Whether the corresponding flag is set to TRUE or not in Oracle 10g"? Is there any way to check that?"

View 1 Replies View Related

How To Check A Table If Exists

Oct 12, 2005

I have a script like this:

------------------------------------------------------------

DROP TABLE CON_TEST CASCADE CONSTRAINTS ;

CREATE TABLE CON_TEST (
IDI NUMBER(10,0),
USERID VARCHAR2(10),
PWD VARCHAR2(10),
NOTE VARCHAR(100)
)

----------------------

But i think if table CON_TEST doen't exist, an error message will appear. I know that in SQL Server we can check if table exists or not. So, i wonder if we can do that in Oracle?

By the way, is there any way to run a file script that contents TABLES, STORED, ... on a developed PC connect to oracle db server? (in case, i'm developing on PC, using Net Service Name to conect to Oracle DB Server)

View 9 Replies View Related

Check All Object Privileges

Sep 6, 2011

How can i list all the object privileges from n user?

for example:

I granted to USER1:

GRANT ALL on my_table1 to USER1;
GRANT ALL on my_table2 to USER1;
GRANT ALL on my_tableN to USER1;

How can i list all privileges from this user over all tables?

I don't know if this is possible, but i wanna to list:

USER1 ----- UPDATE, INSERT, DELETE ON my_table1
USER1 ----- UPDATE, INSERT, DELETE ON my_table2
USER1 ----- UPDATE, INSERT, DELETE ON my_tableN

Is this possible?

View 1 Replies View Related

Check For Entry Before Submission

Mar 27, 2007

How do i check the oracle database for an entry of the same type before i submit an entry.

basically i am taking information from a form and passing it into php which in turn uses oracle to store it in a database, how can i check that there is no entry of the same type in the database?

View 1 Replies View Related

SQL & PL/SQL :: How To Check RowID Of Locked Row

Sep 12, 2011

session from user U1

C:Documents and Settingsuser>sqlplus u1/u123456@orcl

SQL*Plus: Release 10.2.0.1.0 - Production on Pn Wrz 12 12:56:14 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Po│╣czono z:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL> update u1.tab1 set u1.tab1.col1=3 where u1.tab1.col1=1;

1 wiersz zosta│ zmodyfikowany.

SQL>

and now i can check which table is locked

SQL> SELECT lo.session_id, lo.OBJECT_ID, obj.object_name FROM v$locked_object lo
2 INNER JOIN Dba_Objects obj ON obj.object_id = lo.OBJECT_ID;

SESSION_ID OBJECT_ID OBJECT_NAME
---------- ---------- --------------------------------------------------
34 13197 TAB1

but the question is why can i check row id of locked row?

View 9 Replies View Related

SQL & PL/SQL :: How To Check Type Of File

Jul 23, 2012

I have an SP which takes filename as input parameter and loads the data into table (using UTL_FILE package), it is working fine with flat file, but if it is anything other than flat file (xls, doc, docx, pdf) then in When Others exception I'm showing as UNKNOWN Error.

Requirement: Is there a way to check the type of file (txt, xls, doc or pdf) passed as input to the stored procedure using UTL_FILE package (Only file name is send as input parameter not with file extension)

View 3 Replies View Related

SQL & PL/SQL :: Check For Alphanumeric Values

Sep 8, 2011

i need a function which checks if v_rand carrying a value is alphanumeric if nt this value of 6 alpha numeric characters must be generated again... here is the actual fn.

Function alphanumeric
Return varchar2
is
v_rand varchar2(10) := 0;
Begin
[code]......

View 4 Replies View Related

Server Administration :: How To Check ASM Is Used

Jul 8, 2010

How can I check whether the ASM is used in the environment? how I can check for it and if being used, how to connect to that instance later on.

View 5 Replies View Related

SQL & PL/SQL :: Check Every Table For Data?

Oct 30, 2012

I'm working on a feature that needs to check every table for data. At the moment I am roundtripping every time and running the query like this:

SELECT 1 FROM dual WHERE EXISTS(SELECT 'x' FROM table_name)

Is there a better way that allows me to get the result for all tables in one trip? I've tried a correlated subquery like this:

SELECT table_name, (SELECT 1 FROM dual WHERE EXISTS(SELECT 'x' FROM table_name) FROM all_tables

but I think that doesn't work because Oracle looks for a table called table_name rather than the value from the outer query.

View 13 Replies View Related

SQL & PL/SQL :: How To Check Remote Connection

Mar 15, 2011

I working on data transferring from one database server to another database server. but at starting i want to check weather remote database is connected or not?

for e.g.

a := get_remote_connection; --- Calling function for check remote connection return boolean
if a then
<call my data_transfer_proc>
else
exit;
end if;
reply.

View 4 Replies View Related

SQL & PL/SQL :: How To Do Spell Check Of A Column

Nov 3, 2010

how to run a spell check on a column (contains desriptions) of a table and find out if any spelling mistakes are there in the text of that column. I want to run this in pl/sql.

View 8 Replies View Related

How To Check Locks In Database

Apr 13, 2013

oracleDatabase version:11gR2

Os:solaris

how to check locks on database objects .it might be any lock? any select sql query to check database object levlel locks?what basis locks will occur?

View 3 Replies View Related

SQL & PL/SQL :: How To Check If Primary Key Exists

Jan 19, 2010

I have a table called TRANS, and a primary key field tran_id. How would i check if there is a record matching tran_id 'DUP7927' ?

View 25 Replies View Related

SQL & PL/SQL :: Timezone Check Trigger?

Sep 14, 2012

I have an interesting problem. I have a server in the UK and I have three databases globally - UK (FINUKQ1), NY (FINNYQ1) and TK (FINTKQ1). The NY and TK databases will be migrated to the UK server as part of a consolidation project. Obviously, Time Zones and DST are the biggest concern for me.Here was my plan for the implementation (concentrating on Time Zone/DST issues).

Added to .profile:
--------------------
if [[ "$ORACLE_SID" = "FINTKQ1" ]]; then
export TZ="Asia/Tokyo"
elif [[ "$ORACLE_SID" = "FINNYQ1" ]]; then
export TZ="America/New_York"
else
unset TZ
fi
echo "TZ=$TZ"

Once environment has been set start database and listener.

Change database TZ:
-----------------
alter database set time_zone='Asia/Tokyo';
alter database set time_zone='America/New_York';

Check database TZ:
select dbtimezone from dual;

Add trigger:
------------
CREATE OR replace TRIGGER sys.timezone_check
AFTER startup ON DATABASE
DECLARE

[code]...

The trigger was there to put the database in restricted session if it is started in the wrong TZ and an error written to the alert log. The check was done on the dbtimezone and the dbname. Quite simple. Only issue is that it doesn't work as I wanted as dbtimezone and TZ are separate and disparate entities. i.e. I can start the TK database up in UK time (which will be incorrect for this project), and check the dbtimezone and it will show as 'Asia/Tokyo'.

Is there a better way of checking? Any trigger like this to compare TZs etc?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved