ORACLE_SID Is Case Sensitive Or Not?

Jul 21, 2010

I would like to know does oracle_sid is case sensitive or not on Linux platform?also let me know on windows platform also?

View 14 Replies


ADVERTISEMENT

PL/SQL :: Query Sort By Case Sensitive

Oct 1, 2013

I am using oracle database 11g.My use case is I do have a table with following valuesTable name -test

product id     productsortdescription
H58098        ACETAMIDOHYDROXYPHENYLTHIAZOLE
043994         Alloy .MM.INTHICK  My query is  
select * from test order by productsortdescription;  this query gives result as is like
product id     productsortdescription
H58098        ACETA

product id     productsortdescription
H58098        ACETAMIDOHYDROXYPHENYLTHIAZOLE
043994         Alloy .MM.INTHICK
MIDOHYDROXYPHENYLTHIAZOLE
043994         Alloy .MM.INTHICK

but Expected output/result should be like below: 

product id     productsortdescription

043994     Alloy .MM.INTHICKH58098      ACETAMIDOHYDROXYPHENYLTHIAZOLE as All and ACE

in productsortdescriptionl is in small case than C. NLS Session parameters are as following

SELECT * from NLS_SESSION_PARAMETERS;
 NLS_LANGUAGE    AMERICANNLS_TERRITORY    AMERICANLS_CURRENCY    $NLS_ISO_CURRENCY    AMERICANLS_NUMERIC_CHARACTERS    .,
NLS_CALENDAR    GREGORIANNLS_DATE_FORMAT    DD-MON-RRNLS_DATE_LANGUAGE    AMERICANNLS_SORT    BINARYNLS_TIME_FORMAT    HH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT    HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY    $NLS_COMP    BINARYNLS_LENGTH_SEMANTICS    BYTENLS_NCHAR_CONV_EXCP    FALSE 

View 5 Replies View Related

Windows :: Case Sensitive Column Names In Data Reader

Nov 14, 2010

it seems that oracle data reader on some client machine is case sensitive. We are using oracle 10g. 2 client machines connect to the same DB and run the same code - one works and another doesn't. they are both using odac 2.111.7.20.

this is my .NET code (which works fine on every environment it was installed in the last 2 years):

using (OracleConnection sqlConn = new OracleConnection())
{
sqlConn.ConnectionString = connectionString;
using (OracleCommand sqlCmd = new OracleCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "getValues";
[code].......

this is the oracle procedure:

PROCEDURE getValues(
rc IN OUT sys_refcursor
)
IS
BEGIN
OPEN rc FOR
SELECT Name, Value FROM MySchema.MyTable;
END;

The problem that we saw was that on one of the client machines dr["Name"] threw exception :

Error Source: Oracle.DataAccess.Client.OracleDataReader.GetOrdinal
Error Message: System.IndexOutOfRangeException: Unable to find specified column in result set
at Oracle.DataAccess.Client.OracleDataReader.GetOrdinal(String name)
at Oracle.DataAccess.Client.OracleDataReader.get_Item(String columnName)

We modified the code and used NAME instead of Name and it worked!! (dr["NAME"] instead of dr["Name"])

How can it be? from oracle documentation:
A case-sensitive search is made to locate the specified column by its name. If this fails, then a case-insensitive search is made.

My question is what can cause such a behavior? Some ODAC/ODP definitions, .NET configuration?

View 4 Replies View Related

PL/SQL :: Insufficient Privileges / Set ORACLE_SID

Aug 28, 2013

I installed 11g in my system.

Then I used to login as username@admin/password.

It returns error as ORA-12154: TNS:could not resolve the connect identifier specified. 

After that I tried sqlplus "/ as sysdba"

it returns as ORA-01031: insufficient privileges

and then i set ORACLE_SID and then try sqlplus "/ as sysdba"

it returns ORA-12560: TNS:protocol adapter error. 

View 1 Replies View Related

Error / Environment Variable Oracle_sid Not Defined

Aug 5, 2010

I am getting an above errors while starting the emctl start dbconsole..

View 9 Replies View Related

Create Context Sensitive Menu In Oracle Forms 10g

Jan 18, 2010

I'm trying to find out if I can create a context sensitive menu in Oracle Forms 10g. So far I haven't found much in the way of info. where I can find some sample code or a demo I can learn from?

The idea being that when a user right clicks an item they are then presented with relevent options for that item.

View 2 Replies View Related

PL/SQL :: ORA-06592 / CASE Not Found While Executing CASE Statement

Oct 16, 2013

My DB version is

BANNER                                                       
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production                         
CORE 10.2.0.1.0 Production                                       
TNS for Linux: Version 10.2.0.1.0 - Production                 
NLSRTL Version 10.2.0.1.0 - Production      

I'm getting this error while executing a package.But this is unpredictable because sometimes it's coming and sometimes it's not. Everytime I'm passing the value as 'ALERT' for the transaction name. Sometimes it's successful and sometimes it's throwing ORA-06592 

CASE UPPER(IC_TRANSACTION_NAME)
WHEN 'ALERT' THEN
SELECT A.FACILITY_ID INTO VN_FACILITY_ID FROM ALERT A
WHERE A.ALERT_ID = IN_PARENT_NODE_ID;
INSERT INTO TRANSACTION_HISTORY (TXN_HISTORY_ID,

[code]....

View 23 Replies View Related

SQL & PL/SQL :: Use MAX And CASE Together?

Jun 3, 2010

differs on below SQLs and where I need to use "MAX" and "CASE" together?

select trunc(sysdate),max(case when trunc(sysdate)='03jun2010' then 'correctdate' end) dates
from dual
output dates
-------------------------------
6/3/2010 6/3/2010
[code]....

View 6 Replies View Related

SQL & PL/SQL :: IF And Case?

Sep 22, 2010

I'm starting to Oracle and am having a doubt, need to check if a variable is null if I need to make an appointment if you do not need to make another appointment. I thought I was doing fine, but Oracle is pointing this error: "ORA-00900: invalid SQL statement."

- Case

CASE
WHEN 1 = 1
THEN
select *
from BANANA
WHEN 1 = 2

[code]...

View 8 Replies View Related

SQL & PL/SQL :: Case Vs Decode?

Feb 13, 2011

How to use decode and case in "where" and "from" clause of a select statement.

I know the decode can't be used in where clause. In that case how we can use decode and case in from clasue that is: table definition

View 7 Replies View Related

SQL & PL/SQL :: Case Condition?

Nov 21, 2010

i have small requirement

case when type=1 then max(col2) else end
case when type=2 then max(col2) else end

i wanted to have difference 2 timestamp

type(datatype- numeric)
col2(datatype-timestamp)

all it should be in one select condition

View 17 Replies View Related

Using Case Or If To Get Grade?

Feb 7, 2007

Im trying to make a query to get the grades of students, I already figured out how to get the average from the exams. Now I would like to make like a select case or if statements to get the actual grade.

Something like this:
average > 89 then grade = 'A'
average > 79 then grade = 'B'
average > 69 then grade = 'C'
average > 59 then grade = 'D'
else grade = 'F'

View 2 Replies View Related

How To Use CASE When Set Query Empty

Mar 23, 2009

I have a query that will either return one record or zero records. When it returns zero records I want to replace my attributes with a sentinel, like 'N/A'. I tried the CASE statement but couldn't get anything to work

Sample (does not work):
select
(case when exists (select product from tbl_product where productid = '123') then product else 'N/A' end) product
from tbl_product
where productid= '123';
If one record exists it should produce: 'My Widget' (or whatever)
If zero records exist it should produce: 'N/A'

View 3 Replies View Related

Case Statement In Where Clause

Feb 1, 2012

Depending on which month the user is running this select the TAG_YEAR needs to be calculated differently. I have a feeling that I'm over thinking it.

SELECT DOG_MASTER.DOG_MASTER_ID,
DOG_NAME,
TAG_YEAR,
TAG_NUMBER AS PREVIOUSTAGNUMBER,
ISSUE_DATE
FROM DOG_OWNER
[code].......

View 1 Replies View Related

SQL & PL/SQL :: Case Statement In Where Clause

Aug 10, 2011

select empno,ename,deptno,employee_status from emp,dept where emp.deptno=dept.deptno and
( employee_status in(Case employee_status when {?Status}=1 then 'A'
when {?Status}= 2 then 'T'
When {?Status}= 3 then 'A'||','||'T'))
OR ( end_date >= {?START_DATE}
AND end_date <= {?END_DATE}
)
)

Since when i pass employee_status as input 1 it have given me 4 records. When I pass employee_status as input 2 it have given me 3 records. When I pass employee_status as input 3 it should give me 4 records + 3 records=7 records.

4 records for employee_status 'A'
3 RECORDS for employee_status 'T'
7 records for employee_status 'A' AND 'T'

How I should write a query to get 7 records.

View 2 Replies View Related

SQL & PL/SQL :: Display Results Using CASE WHEN?

May 24, 2011

I am trying to create a query that displays the given error message if the result of my COUNT(*) is smaller than 1, but displays the result of my first query (data) if the total count is bigger than 1 (read: the query found data, so it needs to display the rows according to the search).

What do I need to do to display 'data' if 'data2' contains rows?

WITH data
AS (SELECT a.order_id, a.session_id, a.log_id, b.date_of_order, a.operation,
b.funct_prod_code, b.sts_status_code, b.ost_order_situation_code, c.order_situation_oms,

[Code]....

View 11 Replies View Related

SQL & PL/SQL :: DECODE And CASE In WHERE Clause

May 7, 2010

I am facing a problem while retrieving data from table using DECODE/CASE.

Table: PARAM_MSTR

MIN_VALMAX_VALPARAM_CODE DESCRIPTION DATATYPE
AB1000 HARD PARAMETERTEXT
CN1000 SOFT PARAMETERTEXT
0501001 CRYSTAL PARAMETERNUMBER
512001001 STONE PARAMETERNUMBER

Now I want to get the parameter description based upon the PARAM_CODE and a value passed which should be in range of MIN_VAL and MAX_VAL. Means when I pass PARAM_CODE=1000 and :parameter=A, then it should check the DATATYPE of the PARAM_CODE, in our case it is 'TEXT' so it should check the passed value between MIN_VAL and MAX_VAL like

:parameter BETWEEN MIN_VAL AND MAX_VAL and should return 'HARD PARAMETER'.
If I pass PARAM_CODE=1001, then the DATA_TYPE is 'NUMBER', so it will check the :parameter value as Number.
Like :parameter BETWEEN to_number(MIN_VAL) AND to_number(MAX_VAL)

For example:
PARAM_CODE :parametr Result
1000 A HARD PARAMETER
1000 C SOFT PARAMETER
1000 P NULL
1001 25 CRYSTAL PARAMETER
1001 99 STONE PARAMETER
1001 201 NULL

I have written a query using DECODE and CASE statement but it is not working properly.

SELECT * FROM param_mstr WHERE PARAM_CODE=1000 AND :parameter BETWEEN DECODE(DATATYPE,'NUMBER',CAST(MIN_VAL as NUMBER),MIN_VAL)AND DECODE(DATATYPE,'NUMBER',CAST(MAX_VAL as NUMBER),MAX_VAL)

View 3 Replies View Related

SQL & PL/SQL :: Using Sequence In CASE Statement

Oct 11, 2011

Can we use the sequence.nextval,sequence.currval inside case block.

CREATE TABLE EQUALITY_TEST(NUM1 NUMBER, NUM2 NUMBER, SEQ NUMBER);
INSERT INTO EQUALITY_TEST VALUES ( 2 ,0 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 2 ,2 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 2 ,2 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 12 ,2 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 12 ,12 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 12 ,12 , NULL);
CREATE SEQUENCE SEQ_TEMP START WITH 100 INCREMENT BY 1;

Now i need to update SEQ column with SEQ_TEMP sequence. When NUM1,NUM2 values are unequal sequence should be incremented otherwise need to use the same sequence number(CURRVAL)

I have tried like this

UPDATE EQUALITY_TEST
SET SEQ=
CASE WHEN NUM1=NUM2
THEN SEQ_TEMP.NEXTVAL
ELSE
SEQ_TEMP.CURRVAL
END ;
SELECT * FROM EQUALITY_TEST;

Output

NUM1NUM2SEQ
120100
222101
322102
4122103
51212104
61212105

But Required Output

NUM1NUM2SEQ
120100
222100
322100
4122101
51212101
61212101

View 4 Replies View Related

SQL & PL/SQL :: Update With Case Using Select

Oct 30, 2013

Can we use select statement in case statment for an update?

Example

UPDATE x SET field = CASE WHEN
(SELECT field1
FROM Y
WHERE field3 = x.field3
IS NOT NULL
THEN 'B'
ELSE 'C'
where cny# = 112160

View 3 Replies View Related

SQL & PL/SQL :: Select In Case Statement

Feb 11, 2011

I have the following tables:

create table lookups (code varchar2(20), amount number);

insert into lookups values ('Rent' , 500);
insert into lookups values ('Breakpoint' , 10);

create table products (id number, cost number, year varchar2(4));

insert into products values (1, 1000, '2011');
insert into products values (1, 2000, '2011');
insert into products values (2, 100, '2011');
insert into products values (3, 50, '2011');

commit;

I want to write a query which lists the IDs and the sum(cost), and a Y/N indicator which is set to 'Y' IF sum(cost) > ( (lookups.rent value) * (100 - lookups.breakpoint value))/100

I have written this query:

SELECT id,
sum(cost)cost,
year,
CASE
WHEN cost >
((SELECT amount
[code]....... ORDER BY id;

This returns

ID COST YEAR YN
--------- ---------- ---- -
1 1000 2011 Y
1 2000 2011 Y
2 100 2011 N
3 50 2011 N

The YN is correct, but it needs to sum the amounts. So there should only be one row for id1 = 3000.e.g.

ID COST YEAR YN
--------- ---------- ---- -
1 3000 2011 Y
2 100 2011 N
3 50 2011 N

I am not sure how to do this. Or is there a better way of doing this than using CASE.

View 4 Replies View Related

SQL & PL/SQL :: Case In Insert Clause?

Aug 30, 2011

is there a possible to use a case statement in insert clause?

View 5 Replies View Related

SQL & PL/SQL :: Decode / Case Functions?

Aug 19, 2010

I am having some records in the table. If the record num is

1--It should show the month as "Apr"
2--"May"
3--"Jun"
4--"July"
5--"Aug"
6--"Sept"

if it is having other than these 6 should show "0" for the remaining months.

View 10 Replies View Related

PL/SQL :: How To Use CASE Statement In Where Clause

May 16, 2013

How to use CASE stmt in WHERE clause?

View 3 Replies View Related

SQL & PL/SQL :: Query With CASE Where Clause?

May 14, 2012

The query has a case statement in the where clause so that results can be filtered. If I pass "ut" for sso_id then the query returns 21 rows. If I remove the case statement and hard code "a.sso_id like lower('ut'||'%')" then the query returns 41 rows. The query should be returning 41 rows all the time.

Problem:

When passing "ut" as an SSOID parameter to the Procedure the query returns 21 rows.Taking the query and hard coding "a.sso_id like lower('ut'||'%')" the query returns 41 rows.

Result:
query should be returning 41 rows when "ut" is passed an an SSOID parameter.

Returns 21 rows

procedure SSO (SSOID in varchar2 default null,
Name in varchar2 default null,
Campus in varchar2 default null,
Department in varchar2 default null,

[code]...

Returns 41 rows

open Results for
select a.sso_id,
(a.name_last||', '||a.name_first) as name,
b.site,

[code]...

Test Data
CREATE TABLE ID
(
SSO_ID VARCHAR2(60 BYTE),
NAME_FIRST VARCHAR2(100 BYTE),
NAME_LAST VARCHAR2(100 BYTE),

[code]...

Test Data
CREATE TABLE NT
(
LOWER_NT_ID VARCHAR2(60 BYTE),
DEPARTMENT VARCHAR2(100 BYTE),

[code]....

View 3 Replies View Related

SQL & PL/SQL :: Deduping Using CASE Statement

Jan 4, 2012

Table X includes multiple transactional records per household.I need to create a mailing list pulling only one record per household, choosing the one with the most recent date.

Each record within this table will have a Household_ID and a Date, so as an example, the table could have

Household_ID Date
000001 1/1/2011
000001 3/1/2011
000001 12/30/2011
000002 3/15/2011
000002 6/30/2011
000002 9/15/2011

I would want my results to include:

Household_ID Date
0000001 12/30/2011
0000002 9/15/2011

View 5 Replies View Related

SQL & PL/SQL :: NESTED CASE Statement

Jul 13, 2010

I'm working on a nested case statement, can't seem to get it right.We have a table that has injury_codes. What I'm trying to do is come with a nested case statement that will put the codes in a specific_cat, and based on the specific_Cat, assign a Generic_cat.

Example.
I have codes 11,12,13,14,15, 15, 17, 18, 19, 20, 21, 22
Codes 11, 12, 13 have a specific_cat "Head Injury"
Codes 14, 15, 16 have a specific_cat "Spinal Injury"
Codes 17, 18 have a specific_cat "Burns".

All 3 of these specific_cat come under the generic_cat "Trauma"

Codes 19, 20 have a specific_Cat "High Risk Pregancy"
Codes 21, 22 have a specific_cat "Premature Birth".

All 2 of these specific_cat come under the generic cat "OBGYN"...So my case stement should return :

CODESSPECIFIC_CATGeneric_Cat
11Head InjuryTrauma
12Head InjuryTrauma
13 Head InjuryTrauma
14Spinal InjuryTrauma
15Spinal InjuryTrauma
16Spinal InjuryTrauma
[code]...

just a small sample of codes, specific_Cat and generic_cat. I hundreds of these codes I need to categorize.

View 8 Replies View Related

Forms :: Case Statement In 6i

Mar 24, 2011

When I tried to have a Cursor with SELECT CASE statement in Forms 6i , it is not working. But the same query is working in SQL PLUS . We cannot use case in Forms 6i ?

View 5 Replies View Related

SQL & PL/SQL :: How To Use Analytic Functions In Case

Mar 18, 2010

DATA is like this;i want to insert to same set of rows again with increasing the MAIN_SEQ with 4,5,6.

DOCUMENT_ID MAIN_SEQ
VSISLG401 1
VSISLG401 2
VSISLG401 3

DATA is like this;i want to insert to same set of rows again with increasing the MAIN_SEQ with 4,5,6.

DOCUMENT_ID MAIN_SEQ
VSISLG401 1
VSISLG401 2
VSISLG401 3
INSERT INTO TEMP_TEST

[code]...

View 1 Replies View Related

SQL & PL/SQL :: How To Convert Case To DECODE

Oct 20, 2011

I had written follwoing sql with CASE statments, but in oracle 8i i'm unable to use CASE in pl/SQL.

how to convert following sql to DECODE ? Or is there any option other than DECODE ?

SELECT ol.order_no,
ol.transaction_line_id,
ol.pd_actl_tonnage,
ol.real_tonnage_high,
ol.req_tonnage,
il.id,
il.transaction_ref,

[code].....

View 18 Replies View Related

SQL & PL/SQL :: How To Use CASE In SELECT STATEMENT

Jun 24, 2011

select GAM.FORACID,
SOL.SOL_DESC,
GAM.ACCT_NAME,
LHT.LIM_SANCT_DATE,
LHT.SANCT_LIM,
to_char( GAM.CLR_BAL_AMT,'9,99,999.99'),

[code]...

i am getting error like this
ERROR at line 9:
ORA-00911: invalid character

View 4 Replies View Related







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