SQL & PL/SQL :: How Does Operator Works For Varchar2 Datatype Columns
Nov 22, 2011I have a table called Student and a column as name now if i write a Query
select * from student where name < 'BRIAN D'
How does the comparison will be done.
ADVERTISEMENT
PL/SQL :: How Rownum Works For The Column Having Data Type As Varchar2
Jan 29, 2013bookshelf_test table structure
title varchar2(100) y
publisher varchar2(20) y
categoryname varchar2(20) y
rating varchar2(2) y
my query is,
select ROWNUM AS "Rank",title,publisher from (select rating,title,publisher from bookshelf_test order by rating desc ) where ROWNUM <=3
returns result ,
1 1 MY LEDGER KOCH PRESS
2 2 TO KILL A MOCKINGBIRD HARPERCOLLINS
3 3 THE MISMEASURE OF MAN W.W. NORTON
But inner query (select rating,title,publisher from bookshelf_test order by rating desc ) returns,
1 5 WONDERFUL LIFE W.W.NORTON
2 5 THE MISMEASURE OF MAN W.W. NORTON
3 5 TO KILL A MOCKINGBIRD HARPERCOLLINS
4 5 MY LEDGER KOCH PRESS
5 4 TRUMAN SIMON SCHUSTER
6 4 GOSPEL PICADOR
7 4 HARRY POTTER AND THE GOBLET OF FIRE SCHOLASTIC
8 4 INNUMERACY VINTAGE BOOKS
9 4 JOHN ADAMS SIMON SCHUSTER
10 4 JOURNALS OF LEWIS AND CLARK MARINER
11 4 LETTERS AND PAPERS FROM PRISON SCRIBNER
12 4 PREACHING TO HEAD AND HEART ABINGDON PRESS
13 4 THE SHIPPING NEWS SIMON SCHUSTER
14 4 THE GOOD BOOK BARD
15 4 THE DISCOVERERS RANDOM HOUSE
16 3 THE COST OF DISCIPLESHIP TOUCHSTONE
17 3 SHOELESS JOE MARINER
18 3 KIERKEGAARD ANTHOLOGY PRINCETON UNIV PR
19 3 EMMA WHO SAVED MY LIFE ST MARTIN'S PRESS
20 3 EITHER/OR PENGUIN
21 3 CHARLOTTE'S WEB HARPERTROPHY
22 3 BOX SOCIALS MARINER
23 3 ANNE OF GREEN GABLES GRAMMERCY
24 3 WEST WITH THE NIGHT NORTH POINT PRESS
25 3 UNDER THE EYE OF THE CLOCK ARCADE PUB
26 3 TRUMPET OF THE SWAN HARPERCOLLINS
27 2 COMPLETE POEMS OF JOHN KEATS VIKING
28 1 POLAR EXPRESS HOUGHTON MIFFLIN
29 1 GOOD DOG, CARL LITTLE SIMON
30 1 MIDNIGHT MAGIC SCHOLASTIC
31 1 RUNAWAY BUNNY HARPERFESTIVAL
why final queries top 3 rows r different than inner query ?
SQL & PL/SQL :: Passing IN Parameter Of Datatype Varchar2
Feb 16, 2010I have a stored proc which takes IN parameter of datatype varchar2.When I am trying to run the proc it is throwing error that "input buffer too small".The datatype assigned to IN parameter is of varchar2(200) but actually the length of the parameter passed is around 500 characters.the way to increase the length of Input parameter to 500 characters??
View 16 Replies View RelatedForms :: Calculation In Timing With Varchar2 Datatype
Jul 16, 2011I'm facing a problem where I create a form and I want to feed time in "HH:MI" format, Firstly I choose the "Date" datatype, but during the feeding of time i.e "00:30", means if I want to feed only mints, then it doesn't accept, at the other hand if I feed "01:30" means mints with hr then it accepts.
To get rid of this problem I changed datatype "Date" to "Varchar2".
I create 3 columns on my form "TTL_WORKING_TIME" - "TTL_RUN_TIME" = "BRK_DWN_TIME". If I get any value in "BRK_DWN_TIME" column thn it has to be distribute in 3 or 4 reason of B/D, means if I get 00:30 mints brkdwn thn 00:15 mints for "reason1" and 00:15 mints for "reason2".
How I make calculation where I use "Varchar2" datatype.
SQL & PL/SQL :: Change Column Datatype From CLOB To Varchar2
Jun 22, 2012I have to change the datatype of a column from CLOB to varchar2, without changing the order of the columns. The table has no data.
I could find any other way other than dropping the CLOB columns and then adding new columns with varchar2 datatype. But this changes the order of the columns in the table.
SQL & PL/SQL :: Write A Function That Receive As Parameter A Datatype Varchar2?
Jul 18, 2012here is the situation, I have to write a function that receive as parameter a datatype varchar2 and validate if the letter is less than "A" or greater than "Z", it must print do not correct, or correct depending on the situation, no problem so far, the only problem I've got is: If the symbol is ";" is valid, but i do not know how to write it, It does not depend on the PL/SQl languaje but depends on the logic itself.
DECLARE
v_nombre varchar2(20):='john;%';
vv varchar2(1);
begin
for i in 1..length(v_nombre) loop
[code]...
PL/SQL :: Automatic Rounding Off Number Datatype While Converted To VARCHAR2
May 29, 2013I am facing issue related to Number data while it is being converted to Varchar2, it is automatically getting rounded off after 32 decimal place.My database version is "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production".
1) create table test18 ( col1 varchar2(10), val Number);
create table succeeded.
2) insert into test18 values ('First', -347026.6408499652467480885711448714129679); -- After decimal 34 digits
1 rows inserted
insert into test18 values ('Second', -347026.64084996524674808857114487141296); -- After decimal 32 digits
1 rows inserted
3) select * from test18;
COL1 VAL
---------- ----------------------
First -347026.6408499652467480885711448714129679
Second -347026.64084996524674808857114487141296
4) As per the requirement, all the columns would need to be concatenated as a single string along with comma delimiter
select col1 || ',' || val as record_string
from test18;
RECORD_STRING
---------------------------------------------------
First,-347026.64084996524674808857114487141297
Second,-347026.64084996524674808857114487141296
"First" string got rounded off to 97 (last 2 digits) instead of 9679 but for "Second" record it holds the actual value.Only thing which I could figure out while the number is getting type casted to String, it is getting rounded off to 32 decimal place.throw off some light on it and provide the solution how the original record can be kept intact without rounding off.
Expected Output_
RECORD_STRING
---------------------------------------------------
First,-347026.6408499652467480885711448714129679
Second,-347026.64084996524674808857114487141296
Table Partitioning - Range On Datatype Number (21, 7) And Varchar2
Nov 15, 2013Database Version : DB : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionOS : HP-UX nduhi18 B.11.31 U ia64 1022072414 unlimited-user licenseAPP : SAP - ERP I have to RANGE partition on UPDATED_ON or PROFILE either one table which is having below
structure : Name Null? Type
-------------------- -------- --------------------------------
MANDT NOT NULL VARCHAR2(9) MR_ID NOT NULL VARCHAR2(60) PROFILE NOT NULL VARCHAR2(54) REGISTER_ID NOT NULL VARCHAR2(30) INTERVAL_DATE NOT NULL VARCHAR2(24) AGGR_CONSUMPTION NOT NULL NUMBER(21,6) MDM_VERS_NO NOT NULL VARCHAR2(9) MDP_UPDATE_DATE NOT NULL VARCHAR2(24) MDP_UPDATE_TIME NOT NULL VARCHAR2(18) NMI_CONFIG NOT NULL VARCHAR2(120) NMI_CONFIG_FLAG NOT NULL VARCHAR2(3) MDM_DATA_STRM_ID NOT NULL VARCHAR2(6) NSRD NOT NULL VARCHAR2
[Code]....
As per my knowledge, RANGE is better suited for DATE or NUMBER. and INTERVAL partition is possible on DATE or NUMBEr . Column PROFILEIts is of VARCHAR2 datatype. I know still I can partition as Oracle internally convert varchar2 to number while inserting data. But INTERVAL is not possible. How to RANGE partition on PROFILE ? Column CREATED_ON :It is of NUMBER with decimal
SQL & PL/SQL :: Datatype Of Some Columns Is NVARCHAR2
Feb 21, 2010I have a table which have almost 90000 rows.
1. The datatype of some columns is NVARCHAR2. I want to change it to VARCHAR2 and NUMBER for some.
SQL & PL/SQL :: Two Columns Can Have Long Datatype In Table?
Apr 27, 2010I have question related to LONG datatype. Actually from google and get to know that one table can have only one LONG datatype when i searched for reason . i got these resons:-
With 9i (I believe) and later versions, Oracle deprecates using the long datatype in favor of the lob (clob, nclob and blog) datatypes. It is only supported for backward compatibility.
Restriction:- It can not be used in create type as an attribute of the defined type.
It can not be used in where conditions.
There can be no indexes on long columns.
Regular Expression are not possible.
long can not be returned from a stored function.
SQL can not call functions that have an attribute of type long.
And even more restrictions.
So I want to know that is only reason because of that Oracle doesn't allow us to make two Column or is there any strong reason which make it more logical Like storing of data in Row blocks or some thing else.
PL/SQL :: Created A Table With CLOB As Datatype For One Of Columns
Aug 5, 2013<ORACLE VERSION : 11.2.0.2.0> i have created a table with CLOB as datatype for one of the columns, I am trying to store a string ( I am not sure about the length of the string) , when i am querying on my table for the CLOB column,instead of the actual string "(HUGECLOB)" is coming. How to get the actual string in case the problem is with the SIZE.
View 1 Replies View RelatedVARCHAR2 (100) Changes Column To VARCHAR2 (100 Byte)
Oct 24, 2007i am fairly new in the oracle arena, but what would cause a statement such as
ALTER TABLE TEST_TABLE MODIFY text_field1 varchar2(100) DEFAULT 'testval' NULL
to change a column's type from VARCHAR2(100) to VARCHAR2(100 byte)? i found a few mentions of the 100 byte concept online but nothing that jumped out at me.
PL/SQL :: Conversion Of BLOB Datatype To CLOB Datatype
Aug 17, 2012I would need to convert the column datatype from BLOB to CLOB. currently in the table, the BLOB column has the data. the requirement is to convert this column from BLOB to CLOB datatype.
How to convert from BLOB datatype to CLOB datatype ?
SQL & PL/SQL :: Decode With Like Operator
Jul 12, 2013I 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.
SQL & PL/SQL :: Uses Of Check Operator
Sep 16, 2010I 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))
/
SQL & PL/SQL :: Alternate To Like Operator
Mar 15, 2013Any 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.
SQL & PL/SQL :: Using 2 Union Operator
Jun 28, 2010I 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?
SQL & PL/SQL :: Minus Operator And NULL
Feb 12, 2013I 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
Forms :: Use Like Operator With If Condition?
Jan 19, 2013i 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%'.
SQL & PL/SQL :: Escape Operator Symbol?
Feb 28, 20111) 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
SQL & PL/SQL :: Difference Between IN And EXISTS Operator
Jan 10, 2012what the difference between IN and EXISTS operator. Why should we use EXISTS operator?
View 1 Replies View RelatedSQL & PL/SQL :: Oracle 10g Quote Operator
Sep 7, 2011I 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
PL/SQL :: Rownum In UNION Operator
Feb 18, 2013I 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?
SQL & PL/SQL :: How Does Loop Works
Jun 27, 2011[CODE]
DECLARE
D1 DATE:='&D1';
D2 DATE;
BEGIN
WHILE D1<=D2 LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D1,'DAY DD-MON-YYYY'));
D1:=D1+1;
END LOOP;
END;
Here I want to display all the seven days. One more question is here I'm d1<=d2 but d2 is null. So how does <= works here a null. A null is always null. What will be there in d2 how does the loop works with this comparision.
SQL & PL/SQL :: Query Works In 10g But Not In 11g?
Apr 29, 2012I have the query below that works absolutely fine in Oracle 10g but is not running in Oracle 11g. Couple of things I notices about this query.
1. If I remove the alias 'FACILITY_W_CODE' in the ORDER BY clause, query works fine in 11g. If I replace 'FACILITY_W_CODE' with the actual fields - 'F.SERVICE_AREA || F.DISTRICT_OFFICE || F.DO_GROUP || F.DO_SSC' even then it works fine.
2. If I remove the GROUP BY clause in the inside query but keep the alias 'FACILITY_W_CODE' in the outside ORDER BY clause, the query works fine.
I am not sure where the issue is, GROUP BY or the ALIAS. Also, the alias issue seems to be only where the alias is used for multiple fields.
SELECT F.SERVICE_AREA || F.DISTRICT_OFFICE || F.DO_GROUP || F.DO_SSC FACILITY_W_CODE, PF.FAC_TYPE FACILITY_TYPE, PF.FAC_IDENT LOCATION_IDENTIFIER, P.DISTRICT_CODE USER_W_CODE, P.EMAIL_ADDRESS, PF.CERTIFICATION_AUTHORITY, PF.CALLBACK_TECHNICIAN, PF.CALLBACK_ENVIRONMENTAL
FROM PEOPLE P
JOIN (
SELECT I_PF.ID, I_PF.FAC_TYPE, I_PF.FAC_IDENT,
[code]....
PL/SQL :: How Where Condition Works
Jun 1, 2013How oracle retrieves data from a table.like let say a table product as
create table product(id number,name varchar2(10))
table product dont have any index on it
now if this table conatain 500 rows and we have two query
1. select * from product where id=26
2. select * from product
does both query take same time or first query will execute in less time since it has where condition
Optimizer Cannot Merge A View That Contains Set Operator
Jul 18, 2012Query -
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
Error ORA 920 / Invalid Relational Operator
Jul 30, 2010The 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.
SQL & PL/SQL :: Using Union Operator And Sort By Month?
Jan 10, 2012i 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
SQL & PL/SQL :: Replace The Like Operator To Increase The Performance
Jul 16, 2012How 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';