SQL & PL/SQL :: Select From Dual Versus Equals Operator?

Mar 14, 2011

I have a package function which is wrapped and I cannot see the code.The package function raises an user-defined exception when :

SELECT ABC.*
FROM ABC
WHERE ABC.A = PACK.FUNC(ABC.B,ABC.C)

But it does not raise any exception and the query works absolutely fine generating desired results when :

SELECT ABC.*
FROM ABC
WHERE ABC.A = (SELECT PACK.FUNC(ABC.B,ABC.C) FROM DUAL)

View 6 Replies


ADVERTISEMENT

PL/SQL :: Equals (=) Versus LIKE For Date Data Type

Sep 2, 2013

First, I'm aware that the equals (=) operator is a "comparison operator compares two values for equality."  In other words, in an SQL statement, it won't return true unless both sides of the equation are equal.  For example:

SELECT * FROM Store WHERE Quantity = 200; The LIKE operator "implements a pattern match comparison" that attempts to match "a string value against a pattern string containing wild-card characters." 

For example:

SELECT * FROM Employees WHERE Name LIKE 'Chris%'; 

Here,I query about date type data on ORACLE database, I found the following, when I write select statment in this way:

SELECT ACCOUNT.ACCOUNT_ID, ACCOUNT.LAST_TRANSACTION_DATE FROM ACCOUNT WHERE ACCOUNT.LAST_TRANSACTION_DATE LIKE '30-JUL-07';

I get all rows I'm looking for. but when I use the sign equal =

instead :SELECT ACCOUNT.ACCOUNT_ID, ACCOUNT.LAST_TRANSACTION_DATE FROM ACCOUNT WHERE ACCOUNT.LAST_TRANSACTION_DATE = '30-JUL-07';

I get nothing even though nothing is different except the equal sign.

View 6 Replies View Related

SQL & PL/SQL :: Using Sequence.NEXTVAL From DUAL Versus In INSERT Statement?

Apr 8, 2013

I am trying to understand the difference between using sequence.NEXTVAL from DUAL as against using it direclty in an INSERT statment.

--Sequence Creation
CREATE SEQUENCE SEQ_ID START WITH 1 MINVALUE 1 NOCYCLE CACHE 500 NOORDER;
--Table1 Creation
Create table TABLEA (COL1 number, COL2 varchar2(10),
constraint COL1_PL primary key (COL1));
--Table2 Creation
Create table TABLEB(COL3 number);
alter table TABLEB add constraint COL1_FK foreign key(COL3) references TABLEA(COL1);

-- Option1 - Using sequence.NEXTVAL from DUAL

DECLARE
v_seq_num NUMBER;
BEGIN
SELECT SEQ_ID.NEXTVAL INTO v_seq_num FROM DUAL;
INSERT INTO TABLEA (COL1, COL2) VALUES (v_seq_num, 'test');
INSERT INTO TABLEB (COL3) VALUES (v_seq_num);
END;

-- Option2 - Using sequence.NEXTVAL in INSERT USING RETURNING INTO clause

DECLARE
v_seq_num NUMBER;
BEGIN
INSERT INTO TABLEA (COL1, COL2) VALUES (SEQ_ID.NEXTVAL, 'test') RETURNING COL1 INTO v_seq_num;
INSERT INTO TABLEB (COL3) VALUES (v_seq_num);
END;

View 9 Replies View Related

SQL & PL/SQL :: Select Sysdate From Dual

Aug 30, 2012

when i run this nls qusery i got this error

E:oracleproduct10.2.0db_1BIN>sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 30 11:45:59 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select value from v$nls_parameters where parameter= 'NLS_DATE_FORMAT';

VALUE
----------------------------------------------------------------
DD-MON-RR

SQL> select sysdate from dual;

SYSDATE
----------------
30-????? -12

View 11 Replies View Related

TimesTen In-Memory :: Execute Statement SELECT CURRENT_DATE FROM DUAL On 11.2.2?

Sep 24, 2013

We are trying to execute a statement SELECT CURRENT_DATE FROM DUAL on Timesten 11.2.2  . It throws error unknown referenced column error. Command> select current_date from dual; 2211:

Referenced column CURRENT_DATE not foundThe command failed. But the following doc shows the support. 

TimesTen PL/SQL Support: Reference Summary CURRENT_DATE function

Returns the current date in the session time zone. YIn TimesTen this returns the current date in UTC (universal time). TimesTen does not support local time zones.

View 4 Replies View Related

SQL & PL/SQL :: Select Query Like Operator Getting Wrong Data

Dec 25, 2012

I am using the following query with like 'T_%', i am getting 80 rows out of which the first table_name doesn't even have a beginning part 'T_%'.

the first table name has not started with 'T_', why is it appearing.

*********************************************************************
SELECT 'Truncate table epic500.'||table_name
FROM user_tables where table_name like 'T_%' order by table_name;
*********************************************************************
output:
Truncate table epic500.TEMP_ENC_DEL
Truncate table epic500.T_ACCOMMODATION_CODE

View 4 Replies View Related

Add A Column To Dual Table

May 17, 2012

i'v a problem regarding dual table,i logged to oracle as sys as sysdba and i add a column to dual table and then want to delete that added column but oracle gives "ORA-12988: cannot drop column from table owned by SYS"

i'm not able to drop any user oracle gives "ORA-00604: error occurred at recursive SQL level 1" "ORA-01031: insufficient privileges"

another error:: SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level oracle gives error when i want to switch user all problems starts when i add that column to dual table

View 1 Replies View Related

Dual Table Has Only One Column?

Oct 29, 2010

why the dual table has only column?

View 3 Replies View Related

SQL & PL/SQL :: All DML Operation Can Be Done In DUAL Table?

Apr 29, 2010

What are all the DML operation can be done in DUAL table.?

View 5 Replies View Related

SQL & PL/SQL :: Matching Dates Using Dual?

Jul 7, 2010

What I'm trying to do is make it so that it returns 'MATCH' when I pass a date that matches a date every two weeks starting Jan 01. Like Jan 01, Jan 15, Jan 29, Feb 12, etc. would return as MATCH Jan 02, Jan 03, etc. would return as NO_MATCH

The part in bold is what I'm having trouble figuring out.

select nvl(
(select 'MATCH'
from dual
where 'date' = '2 week intervals starting Jan 01'
), 'NO_MATCH') from dual

View 7 Replies View Related

SQL & PL/SQL :: UNION While Selecting From Dual?

Sep 19, 2013

I would like to SELECT these 3 hardcoded titles from DUAL, and have a blank line under each, on the output in this order from the SQL. But the result does not end up that way

SQL> set heading off;
1 select '#ENCODING WINDOWS-1252' from dual
2 union
3 select ' ' from dual
4 union
5 select 'Language Section EN-US' from dual
6 union
7 select ' ' from dual
8 union
9* select 'Catalog Section Title Date Source' from dual
SQL> /

#ENCODING WINDOWS-1252
Catalog Section Title Date Source
Language Section EN-US

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

Desired Output:
#ENCODING WINDOWS-1252
Language Section EN-US
Catalog Section Title Date Source

View 12 Replies View Related

PL/SQL :: DDL And DML Commands On Dual Table

Feb 8, 2013

Can we perform DDL & DML Commands on dual table????

If yes then how??

View 11 Replies View Related

PL/SQL :: DUAL Table In Subquery Of UPDATE

Apr 26, 2013

Should the following code work? I feel like I have done this before, but I'm getting a "must be a subquery" error now.

update TABLE1
set (FIELD1, FIELD2) =
values(select 'blah','blah' from DUAL)
where PK_FIELD=12345;

If this is invalid, is there another way to hardcode a series of values like this?

View 8 Replies View Related

SQL & PL/SQL :: Creating List Of Month From Dual Table?

Nov 3, 2011

creating sql query for producing result as per below from dual table.

SELECT to_char( sysdate,'MON-RRRR')
FROM dual
where sysdate between '01-APR-2011' and '31-MAR-2012'
order by 1 desc

from above query the result is

NOV-2011

but i need the result as per below

APR-2011
MAY-2011
JUN-2011
JUL-2011
AUG-2011
SEP-2011
OCT-2011
NOV-2011 CURRENT MONTH

View 4 Replies View Related

PL/SQL :: Query A Remote DUAL Causes ORA-01729 Or ORA-02019

Jul 6, 2012

I have ORACLE XE 11g installed on two machines and have been trying my luck to get my local db to query/insert/update a table from the remote db.

I have created the public database link and querying the dba_db_links shows that the links is successfully created (see the bottom of the post). I have also created synonyms for my remote table and queries like select name from jforum_forums succeed, with jforum_forums being a public synonym referring to user.jforum_forums@corona.magic.ubc.ca.

However when I try to insert a row into the remote table, there is a query to the remote DUAL to fetch the last generated ID for a given table similar to the following: SELECT jforum_forums_seq.currval FROM DUAL. Statements of such fail and they either throw an ORA-02019: connection description for remote database not found or ORA-01729: database link name expected. I tried including the database link with the name of the table making it like SELECT jforum_forums_seq.currval FROM DUAL@corona.magic.ubc.ca but that didn't work either.

PS, I should mention that global_names are set to true in both databases and that my database link has the same name as the global_name of the remote database.

OWNER
------------------------------
DB_LINK
--------------------------------------------------------------------------------
USERNAME
------------------------------
HOST
--------------------------------------------------------------------------------
CREATED

[code]...

View 18 Replies View Related

SQL & PL/SQL :: Decode With Like Operator

Jul 12, 2013

I need to get create_user_id for different sale_location_id.Also create_user_id field will be having different values.This is part of my big query.I need to add this stmt in that.So taken that part and figuring it out.

create table it(sale_location_id number,create_user_id varchar2(10));
table IT created.
insert into it values(1,'ISRA')
1 rows inserted.
insert into it values(2,'USFA')
1 rows inserted.

select a.sale_location_id,decode(a.sale_location_id,1,a.create_user_id like 'IS%',a.create_user_id like 'U%') create_user_id from it a

given error as:

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"

How to write this.

View 5 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 :: Alternate To Like Operator

Mar 15, 2013

Any alternate to the following query.

select * from emp where ename like upper(NVL('%mi%',ename));

Basically I want to search based on string or null.

View 11 Replies View Related

SQL & PL/SQL :: Using 2 Union Operator

Jun 28, 2010

I have an query i.e.

I want 3 lines input in 1 line using 2 union operator like

Input:-
'i love playing
football and
volleyball'

i want the output like:-

"i love playing football and volleyball"

solve query using 2 union operator?

View 9 Replies View Related

SQL & PL/SQL :: Minus Operator And NULL

Feb 12, 2013

I have a two tables with same column name , I wanted to find different record in table1 when compared with table2

create table table1(col1 number,col2 number,col3 number,col4 number,col5 number);
create table table2(col1 number,col2 number,col3 number,col4 number,col5 number);

insert into table1 values(1,2,NULL,NULL,NULL);
insert into table2 values(1,2,NULL,NULL,NULL);
commit;

select col1 from (select col1,col2,col3,col4,col5 from table1 minus select col1,col2,col3,col4,col5 from table2);

no rows selected

how come i get no rows selected when col3,col4,col5 is having null values but NULL could be anything so

NULL-NULL cannot be equal to zero how is it possible

View 3 Replies View Related

Forms :: Use Like Operator With If Condition?

Jan 19, 2013

i want to know that how can i use like operator with if condition. i m using oracle10g form builder and it's for search purpose .

for example ...

if search string=string2 then
message('Record found');
end if ;

i want to use like '%search string%'.

View 1 Replies View Related

SQL & PL/SQL :: Escape Operator Symbol?

Feb 28, 2011

1) Can we set a different symbol other than '' for escape operator.
2) If yes, how to see the current escape operator symbol.
3) How to find out the below name with escape operator?

Employee name ----> rama_krishna_raj

View 2 Replies View Related

SQL & PL/SQL :: Difference Between IN And EXISTS Operator

Jan 10, 2012

what the difference between IN and EXISTS operator. Why should we use EXISTS operator?

View 1 Replies View Related

SQL & PL/SQL :: Oracle 10g Quote Operator

Sep 7, 2011

I have a issue on running the query with quote operator . When I am executing the SQL query I am getting error "Quoted String not properly Ended".

select q'[Oracle's world ]'
from dual

But The following query works.

select q'[It's Oracle's world ]'
from dual

View 4 Replies View Related

PL/SQL :: Rownum In UNION Operator

Feb 18, 2013

I have a requirement in SQL that I have to number each row. Hence I thought of using ROWNUM. But the sql query I'm using uses UNION operator. Hence I used like this

select a,b,rownum as 'field1' from table1
union
select c,d,1 as 'field1' from table2

Will the above query solve my purpose?

View 11 Replies View Related

Optimizer Cannot Merge A View That Contains Set Operator

Jul 18, 2012

Query -
SELECT *
FROM sysadm.ps_tmtl_post_vw a
WHERE a.month_prepared_for = 'JUNE,2012'
AND a.ca_status = 'P5 CUST GO AHEAD'

[code]...

When I try for the SQL-Tuning sets its throws error that

ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 2 of the execution plan.
The optimizer cannot merge a view that contains a set operator.

I read earlier forum where it says that optimizer unable to interpret the conditions like order by etc etc.Now there is one view which is getting used in the query when I did select * from vw it took more than 16 hrs to complete. (bad view).

Attached File(s)

 exec_plan.txt ( 2.06MB )
Number of downloads: 1
 view_def.txt ( 14.12K )
Number of downloads: 2

View 5 Replies View Related

Error ORA 920 / Invalid Relational Operator

Jul 30, 2010

The following runs no problem

SELECT
ACCO.SEQUENCE,
ACCO.DESCRIPTION,
MAX (ACCO.AUDIT_DTE)
FROM
PAS.AUDIT_CLINICAL_CARE_OPTIONS ACCO
WHERE
ACCO.AUDIT_DTE < :AuditDate AND

[code]....

However, when I try to add extra conditions to the status' in the sub select i get the error. This is one way I tried:

SELECT
ACCO.SEQUENCE,
ACCO.DESCRIPTION,
MAX (ACCO.AUDIT_DTE)
FROM
PAS.AUDIT_CLINICAL_CARE_OPTIONS ACCO
WHERE

[code]....

I've tried repeating the sub select for each of the extra status parts but everytime i hit the same problem.

View 1 Replies View Related

SQL & PL/SQL :: Using Union Operator And Sort By Month?

Jan 10, 2012

i got the data like

select * from Table1
SNO Name B_MONTH
--------------------
101 A Mar
102 B Jan
103 C Feb
104 D Apr
105 f May
106 G Jun

Select * from Table2

107 H Dec
108 I Aug
109 J Oct
110 L Jul
111 M Sep
112 N Nov

select * from table1 union select * from table2 order by 3

The B_MONTH column is in Varchar2. Expected output should be

Output:

Jan
Feb
Mar
Apr
.
.
.
.
Nov
Dec

View 8 Replies View Related

SQL & PL/SQL :: Replace The Like Operator To Increase The Performance

Jul 16, 2012

How to replace the like operator for increase the performance. Because it is taking more time and not using the index.

SELECT *
FROM emp
WHERE ename like '%AL';

View 30 Replies View Related

SQL & PL/SQL :: Difference Between Concat Function And (||) Operator?

May 6, 2013

Want to understand difference between Concat function and "||" operator. I am getting the same result for both. Below is the test case for your reference.

Select 'H '||' S' From Dual;
--Output H S

Select Concat('H ',' S') A From Dual;
--Output H S

Select Length('H '||' S') A From Dual;
--Output 6

Select Length(Concat('H ',' S')) A From Dual;
--Output 6

View 5 Replies View Related







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