SQL & PL/SQL :: Checking Whether Record Exists

Jan 22, 2013

Which is the best way to check whether a record exists.

DECLARE
l_count NUMBER;
BEGIN
SELECT COUNT(*) INTO l_count FROM emp WHERE empno=7839;
IF l_count=1 THEN
dbms_output.put_line('exists');
ELSE
dbms_output.put_line('not exists');
END IF;
END;

[Code]...

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Check Record Exists Validation

Jan 18, 2011

1. Below TXN_LOG table Expected to have 0.5 to 1 million records & TXN_LOG_HIST table will have max 3 million records.

2. On daily basis system will move records from TXN_LOG to TXN_LOG_HIST.

3. The below mentioned check will be performed for each transaction, With out this check currently my process is doing 10tps. (transaction per second).

CREATE TABLE TXN_LOG
(
REFNO VARCHAR2(16) PRIMARY KEY,
LOCATION VARCHAR2(3),
AC_NO VARCHAR2(20),
TXN_INIT_DATE DATE,
AMOUNT NUMBER(22,3),

[code]....

The below validation we are currently using which is overhead for our transactions processing. Also the below mentioned check will be performed for each transaction, With out this check currently my process is doing 10tps. (transaction per second).

BEGIN
SELECT COUNT(1)
INTO l_exists
FROMTXN_LOG
WHERE AC_NO = p_acho
TXN_INIT_DATE = p_txn_date
AMOUNT = p_amt

[code]....

Is there any fastest method to check above record exists validation from the both the table?

View 11 Replies View Related

PL/SQL :: How To Check Record Exists Prior To Insert Statement

Aug 12, 2012

Using a cursor and loop method records are inserted into the header and line tables. How do I code the IF statement prior to the INSert statement such that Insert if record does not exist else Update the record. If the record for insert fails in the line, the same record should be deleted from the header table as well and transaction should rollback. The Ora version used is 11.2.0.2.

CURSOR C1 IS
        SELECT H.*
        FROM   rex_head_extract h
        WHERE  TRUNC(h.create_date) BETWEEN v_begin_date AND v_end_date;  
FOR I IN C1 (v_begin_date, v_end_date)
 
[Code]....

--- Need to perform a check if record exists here prior to insert     
             INSERT INTO tran_head
                 (TRAN_SEQ_NBR,        ORG_NUMBER,
                  STORE_NUMBER,        TRAN_DATE,

[Code]....

View 9 Replies View Related

SQL & PL/SQL :: Checking For Skipped IDs?

Jun 7, 2011

We have a problem where a data load script has caused data in one of our tables to be incorrectly loaded. Instead of loading the parent row, it has loaded an offest that was assigned by the application.

The problems is, we have the following table:

=================================
ID RESULTSET PARENT
=================================
...
100 502 -1
101 502 0
102 502 1
103 502 2
104 502 3
105 503 -1
106 503 0
110 503 1
111 504 0
...
=================================

What's the best approach to use to transform the last column as follows:

=================================
ID RESULTSET PARENT
=================================
...
100 502 99
101 502 100
102 502 101
103 502 102
104 502 103
105 503 104
106 503 105
110 503 106
111 504 110
...
=================================

Note: ID column may not always be ordered sequentially

View 6 Replies View Related

SQL & PL/SQL :: Checking Consecutive Numbers

May 11, 2012

I have a table with three columns: terminal, place and batch. How can I check for missing batches?

select * from transactions;

terminal place batch
84812
84813
84814
84816
84821
84823
84824
84825

View 5 Replies View Related

SQL & PL/SQL :: Checking Users Input?

Dec 8, 2010

I want to ask for a password from the user so they can access their account, however I want the password to have constraints on it. For example I want the user input to between 6 and 12 characters in length containing at least one number and one letter.

View 2 Replies View Related

Query Load Checking?

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

SQL & PL/SQL :: Checking Variable Number Of Columns?

Apr 12, 2010

My goal is this:I have a table which is being updated/changed by lay people with certain types of values. Constraints are given to them, but they need not conform to them as they update the table in excel.Now, I want to validate this table every-time before I use it. i.e. implement a script which can be run to verify is all the values are in the right format for further usage.

I have a variable number of columns (i.e. users can add further columns as their requirements change).From columns 3 to 'n' (depending on table given) the values should be 'Yes' or 'No'. How do I check this for a variable number of columns in PL/SQL?

View 11 Replies View Related

PL/SQL :: Creation Of A Table After Checking Whether It Is Existing

Apr 22, 2013

declare
vnum number;
vname varchar2(50):='t1';
begin
begin
select count(*) into vnum from dba_tables where table_name=vname;
dbms_output.put_line('table count '||vnum);
exception

when others then

vnum := 0;
end;
begin
if vnum>0 then
execute immediate 'drop table '||vname;
dbms_output.put_line('table dropped');
end if;
exception

when others then

dbms_output.put_line('table does not exists');
end;
execute immediate 'create table '||vname ||' ( n number)';
dbms_output.put_line('table created');
end;

View 14 Replies View Related

SQL & PL/SQL :: Checking Values Of Procedure Parameters

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

SQL Query For Checking Alpha-Numeric Values

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

SQL & PL/SQL :: Checking If Cursor Is Returning Empty List?

Sep 24, 2010

I am select list of ids;

cursor c1 is
select c_id from ctab1 where login = l_id
union
select c_id from ctab2 where login = i_id;

now I need to return err_code and err_message if this combined list is empty ! Can not use exit%notfound.

View 5 Replies View Related

SQL & PL/SQL :: Retrieve Data By Checking Any Part Of The String?

Jul 8, 2012

I have a table

select * from testing
Quote:
ID NAME REDATA
-------------------- -------------------- --------------------
1 ABC,DEF,GHI,LKJ 000001
2 MNC,GHI,CTF 000002

2 rows selected

select * from testing where name like ('GHI,TCF');

now my output should be as above because GHI is common in both the names.if any of the where condition string matches the Name field then I need that record to be retrieved.

View 9 Replies View Related

Forms :: Checking For Password Verification For 3 Counts?

Dec 22, 2011

I have made a simple form in which user will be entering his username and relevant password to get authorised. My issue is , if the user is entering wrong password for 3 times then both fields should get disabled. I did the form until the point where it checks wether the user is authorised or not. But how to give count on failure of passwords.

View 5 Replies View Related

Security :: Create A Procedure That Will Be Checking Passwords?

Feb 3, 2013

I want to create a procedure that will be checking passwords. A password should contain numbers and letters and be at least 8 characters long.

View 1 Replies View Related

Server Utilities :: Checking Column Value In SQL Loader?

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

Forms :: Checking To See If A Text Item Is Empty

Aug 8, 2010

I was wondering can i check if a text item in another block is empty when a user try to create a record using a button in another block.

/*creating a program that will check to see if the customer id in the customer block is null it will then display the the stop alert and tell the user that he/she cant create a new order without having a selected a customer*/

--this is the block_item im checking to see if it null
IF (:customer.c_id) = NULL THEN
DISPLAY_ORD_STOP_ALERT;
GO_ITEM('CUSTOMER.C_ID');
[code]....

View 5 Replies View Related

PL/SQL :: How To Delete Records Checking Data And Time

Oct 28, 2013

I want to delete the records which are less than or equal to this date 

MIN(TRUNC(sale_week_date)-(1/(24*60*60)))  

For that I have created the following procedure.

 CREATE OR REPLACE PROCEDURE DELETE_DATA_WEEKISMIN_DATE DATE;SELECT MIN(TRUNC(sale_week_date)-(1/(24*60*60))) INTO MIN_DATE
FROM sales_week; DBMS_OUTPUT.PUT_LINE(MIN_DATE); 
DELETE FROM sales WHERE sale_date <= MIN_DATEAND sales_creation_date <= MIN_DATEAND sales_update_date <= MIN_DATE; END;

 If declare as MIN_DATE Date;

it's not taking time. If declare as MIN_DATE Timestamp; It is taking milli seconds also. 18-OCT-13 11.59.59.000000 PM SALE_DATE ,SALES_CREATION_DATE and SALES_UPDATE_DATE in this  format  18-OCT-2013 11.59.59 AM. How  to delete the records which are less than or equal to this date  MIN(TRUNC(sale_week_date)-(1/(24*60*60))) ..

View 9 Replies View Related

PL/SQL :: Checking Correct Data Format Using Sql Query?

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

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 View Related

PL/SQL :: Invalid Identifier While Checking Date Comparison

Jul 25, 2013

I have written one program with dynamic SQL and piece of code is  follows. 

sql_stmt := 'SELECT '||CBID(i)||',BID,'||CBEID(i)||',''NA'',''NA'',''NA''  FROM DIM_ORGNISATION WHERE BID in(select PARENT_B_ID from ORG_DIM_LOD where CHILD_B_ID ='||CBID(i)||') and to_Date(start_Date,''DD/MM/YYYY'') = TO_DATE ( trunc('||Cstart_date_type(i)||'),''DD-MON-YY'',''NLS_DATE_LANGUAGE=ENGLISH'')'; EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO tempBID, tempSBD, tempLBD, tempL3BD, tempL4BD, tempSABD And ,

when i'm executing dynamic SQL gives the error as follows. 

ORA-00904: "JAN": invalid identifierORA-06512: at "LWNER.SHY_CREATE_MAPING", line 184ORA-06512: at line 2 

when displaying with using

DBMS_OUTPUT   DBMS_OUTPUT.PUT_LINE('Cstart_date_type(i)'||Cstart_date_type(i)||)); It's diaplaying it as "01-JAN-70".

View 7 Replies View Related

Checking Real Amount Connections To Oracle Database

Mar 12, 2013

My problem:

+ Oracle 11g
+ Centos 5.5
+ I create many users by shell script (user01, user02, user03 ..... user0n)
+ How can I connect all user to Oracle database in Centos (may be create connect.sh) - command line env ?
......
// user01 , user02 ... ---===> connect Oracles ?
My idea: using fork to create multi sqlplus to connect but it cant o_0

If all users can connect to Orcl db, I can count real user connections.

View 4 Replies View Related

SQL & PL/SQL :: Checking If Month And Year Is Greater Or Equal To Current One?

Mar 7, 2013

This is the description for the procedure:

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.

View 3 Replies View Related

SQL & PL/SQL :: Execution Model For Triggers And Integrity Constraint Checking

Sep 7, 2010

URL....Topic: The Execution Model for Triggers and Integrity Constraint Checking

Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking:

1.Run all BEFORE statement triggers that apply to the statement.
2.Loop for each row affected by the SQL statement.
a.Run all BEFORE row triggers that apply to the statement.
b.Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed.)
c.Run all AFTER row triggers that apply to the statement.
3.Complete deferred integrity constraint checking.
4.Run all AFTER statement triggers that apply to the statement.

View 5 Replies View Related

SQL & PL/SQL :: Integrity Constraint Checking / Trigger Execution Sequence

Mar 25, 2010

i have an understanding about Integrity constraint checking and the trigger execution sequence, which i decsribe below.

Integrity constraint are restiction on DML operation performed by the user. When a user deletes or updates or inserts a rows in a table then oracle performs certain checking to see that the data which is effecting the row abide certain rules. There are certain per-defined set or rules that can be applied on a table such as PRIMARY KEY, FORIEGN KEY, UNIQUE, CHECK, NOT NULL etc, user-defined rules can be applied on tables by using Triggers.

In both the cases the Integrity Constraint Checking is deferred until the complete execution of the statement. All rows are inserted first, then all rows are checked for constraint violations.

So when i see the trigger execution model the following steps are performed by oracle, Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking..This is what the Oracle Documentation Library says [extract from Oracle Database Concepts 10g Release 1 (10.1)].

1. Run all BEFORE statement triggers that apply to the statement.

2. Loop for each row affected by the SQL statement.
a.Run all BEFORE row triggers that apply to the statement.
b.Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed.)
c.Run all AFTER row triggers that apply to the statement.

3.Complete deferred integrity constraint checking.

4.Run all AFTER statement triggers that apply to the statement.

As for step 3 here the checking of the constraints for the statement is performed which where defered till the complete execution of the statement, then what is done in step 2b? what constraints are checked there?

View 33 Replies View Related

RAC/ASM Clusterware Installation :: Checking Free Disk Space On OS And ASM Bug?

Jul 9, 2012

I've installed GI 11.2.0.3 on AIX 7.1.And today I noticed strange ASM's behavior:

-bash-3.2$ getconf DISK_SIZE /dev/rhdisk2
*2285768*
-bash-3.2$ asmcmd
ASMCMD> lsdsk -k
Total_MB Free_MB OS_MB Name Failgroup Failgroup_Type Library Label UDID Product Redund Path
*188616* 68204 *188616* DATA_0000 DATA_0000 REGULAR System UNKNOWN /dev/rhdisk2

I guessed that was because of LUN size (it was exceed 2 TB)After that I dinamically shrinked LUN size on our external storage, rebooted and perfomed cfgmgr command on both nodes. But I still have no enough free space.

-bash-3.2$ getconf DISK_SIZE /dev/rhdisk2
*1992294*
-bash-3.2$ asmcmd
ASMCMD> lsdsk -k
Total_MB Free_MB OS_MB Name Failgroup Failgroup_Type Library Label UDID Product Redund Path
*188616* 68204 *1992294* DATA_0000 DATA_0000 REGULAR System UNKNOWN /dev/rhdisk2

View 2 Replies View Related

Call Interface :: Sql Statement Works In SQLPLUS But Will Not Pass Checking In PRO*C

Apr 22, 2013

I am in the process of converting my skill in oracle and this time PRO*C from Windows to Linux. I have oracle 11g R2 installed on a UBUNTU (12.04) server and have installed the instant client as described in

[URL]

This has a query which will run in SQLPLUS but fails with PRO*C

:~/Projects/proc/proctest1$ proc INAME=proctest.pc SQLCHECK=NONE ONAME=proctest.c LNAME=proctest.lis LTYPE=long
Pro*C/C++: Release 11.2.0.3.0 - Production on Mon Apr 22 21:00:18 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
System default option values taken from: /home/neill.rutherford/instantclient_11_2/precomp/admin/pcscfg.cfg
PCC-W-02109, SQLCHECK=NONE is no longer supported.. using SYNTAX

[code]....

View 0 Replies View Related

SQL & PL/SQL :: Not In Not Exists?

Aug 29, 2012

I have a question i wanted to know that " Is it possible to write the Following Query Using NOT EXISTS

SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT UNIQUE DEPTNO FROM EMP);

And one more doubt is there, can we use join to get same result.

View 9 Replies View Related

Client Tools :: Checking Users Execute Privilege On ALIAS LIBRARY

Nov 19, 2012

I want to check if a user has execute previlege on ALIAS LIBRARY,is there any table in which i can check this

View 7 Replies View Related

Poor Performance With Free Tablespace Checking Query Generated By DB Artisan?

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







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