CASE expects data type consistency, DECODE not expecting.
Obviously both functions handling data types are different let it be
SQL> select decode(2,1,1, 2,'2', ' three' )"RESULT" from dual;
RESULT
---
2
SQL> select case 2
2 when 1 then 1
3 when 2 then 2
4 else 3
5 end "RESULT" from dual;
RESULT
2
SQL> select case 2
2 when 1 then 1
3 when '2' then 2
4 else 3
5 end "RESULT" from dual;
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
know cause of error here????I mean " every time case exp checking data type consistency"
my thought is
I am trying to get same output but different methods. Yes, clearly states this is one of the Oracle bug!
I want to know how oracle handles here ???? i mean 3rd query.Purely i am testing this function with dual(dummy) table...
obviously, no possibilities for different data type.next one i am not sure about Oracle compares int variables to int variables, char variables to char variables,
I think so .... that's why oracle throws error. am i right ??ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
see this query
SQL> select case 2
2 when 1 then 1
3 when '2' then 2
4 else 3
5 end "RESULT" from dual;
// clearly i am stating what's error on 3rd line ?????Even if I refer reference books some concepts are blind to understand.
Data Type Consistency CASE and Decode...CASE expects data type consistency, DECODE not expecting.Obviously both functions handling data types are different..let it be
SQL> select decode(2,1,1, 2,'2', ' three' )"RESULT" from dual; [color=red]RESULT --- 2 SQL> select case 2 2 when 1 then 1 3 when 2 then 2 4 else 3 5 end "RESULT" from dual; [code]...
I am trying to get same output but different methods. Yes, clearly states this is one of the Oracle bug! Oracle compares int variables to int variables, char variables to char variables, If any discrepancy between the two data types, then the query will fail but here not different data types.
Even if I refer reference books some concepts are blind to understand.
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)
my query is some thing like this but having more column in select. when i am firing this query it is giving result but that is not proper,\.
my problem is , like if there are 3 more values for uh.sflowtype (0,1,2) then group by is not working for them and those are coming in different row , i need them to be combined
query is : select substr(uh.sstartdatetime,1,8) DateTime, ( case when uh.sflowtype=7 then 'sms' when uh.sflowtype=9 then 'mms' when uh.sflowtype=10 then 'gprs'
I am using oracle 11G database,I have to check length of name column value from employee table and if length(name) > 39 then value should be substr(name,0,39) else value should be name only. i tried below code
select CASE when length(name) > 39,substr(name,0,39) else name END
from employee but its not working ..can I do this using decode too ? ,,which one would be better or this is not a right way ?
During developing Domain Index for Oracle 11.2.0.1.0 (problem also appears in 12с) i was faced with misunderstanding of parameter types of function ODCIIndexInsert in case of creating indextype WITH ARRAY DML option.According to Oracle documentation [URL}...
In case of WITH ARRAY DML option Oracle will invoke ODCIIndexInsert with following signature
FUNCTION ODCIIndexInsert( ia ODCIIndexInfo, ridlist ODCIRidList, newvallist varray_of_column_type, env ODCIEnv) RETURN NUMBER
In my case indexed column has datatype NUMBER so i defined varray_of_column_type as SYS.ODCINumberList STATIC FUNCTION ODCIIndexInsert(ia in sys.ODCIIndexInfo, ridlist in sys.ODCIRidList, newvallist in sys.ODCINumberList, env in SYS.ODCIEnv) RETURN NUMBER
Indextype was created as
CREATE INDEXTYPE test_index_type FOR test_eq(number, number) USING index_methods WITH ARRAY DML(number, sys.ODCINumberList)
[code]...
(problem occurs in all cases) CREATE TABLE test_table (id NUMBER (19,0)); CREATE INDEX test_index ON test_table(id) INDEXTYPE IS test_index_type;
When attempting to insert data in the table insert into test_table values (1);
oracle raise exception
CODEError starting at line 53 in command: insert into test_table values (1) Error at Command Line:53 Column:1 Error report:
[code]...
So my question is.Is it normal behavior of oracle (according to documentation)?What is correct signature of ODCIIndexInsert function in case of INDEXTYPE creation with 'WITH ARRAY DML' option and fact that indexed column has NUMBER datatype?
By the way if i define indextype without 'WITH ARRAY DML' option signature is clear, and working. But this approach doesn't satisfies our performance needs.Also if i define index type with option 'WITH ARRAY DML WITHOUT COLUMN DATA' and use signature static function ODCIIndexInsert(ia sys.odciindexinfo, ridlist sys.odciridlist, env sys.ODCIEnv) return number
Everything works too. But this approach doesn't satisfies our business needs.Is it a way to define ODCIIndexInsert parameter types (in case of indexing number column) so that batch inserting works according to documentation ?
FUNCTION ODCIIndexInsert( ia ODCIIndexInfo, ridlist ODCIRidList, newvallist varray_of_column_type, env ODCIEnv)
I am attaching full sql script to recreate environment and reproduce the problem.Type definition:
CREATE OR REPLACE TYPE index_methods AS OBJECT ( step number, STATIC FUNCTION ODCIGetInterfaces(ifclist OUT SYS.ODCIObjectList) RETURN NUMBER, STATIC FUNCTION ODCIIndexCreate (ia SYS.ODCIIndexInfo, parms VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER, STATIC FUNCTION ODCIIndexAlter (ia sys.ODCIIndexInfo, parms IN OUT VARCHAR2, altopt number, env sys.ODCIEnv)
We have audit logs of a transaction in audit files, however we do not see any changes in the table that the transaction affects.We use point-in-time recovery and flashback feature to figure out the changes in the table . DML Audit Granularity is "ACCESS".The transaction is java application transaction and we use hibernate.
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,
I have a sample table where I am arranging data in required format using 'decode' by hardcoding all values, how can we do same using pivot method, can pivot be only used when we want aggregate values in oracle?Also can't we do what i did, using generic max(decode) with select query, if it were a large table (i don't know which would be pivot column here?) ?
I have a table users which contains the column password and the datatype for this is raw(64) ,which will be the hashed one coming from the application. When Inserting the application is inserting with the lower case but while storing the table data is storing int the uppercase .Is it the expected behavior of this datatype. I am using Oracle 11g and attaching the ddl and dml pertaining to the column
CREATE TABLE users_new(USER_ID NUMBER(10), PASSWORD RAW(64) ); insert into USERS_NEWvalues(1,'ba660ed7b382f2c57754c4cd0d5289cc60ee455244847268d88b555168b28b23156f5a016b2764966a15674bec4eb0fb2220be736bfc21282be957910b9e80cf');
But after selecting from the database I am getting as below BA660ED7B382F2C57754C4CD0D5289CC60EE455244847268D88B555168B28B23156F5A016B2764966A15674BEC4EB0FB2220BE736BFC21282BE957910B9E80CF
Want to filter a data using CASE statement in WHERE clause for the following scenario.
Need to Filter tb1.fallback_keyword if the fallback_flag is "Y' or 'N' and pg_number is null.Else no partial search of keyword.
where CASE WHEN (fallback_flg = 'Y' OR fallback_flg = 'N') and (pg_number is NULL ) THEN tb1.fallback_keyword = SUBSTR(key_word,1, INSTR(key_word,'#',-2)) ELSE (tb1.keyword = key_word ) AND (tb1.keyword like regexp_replace(key_word, '[*]+', '%')) END
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?
I have a database for which I use RMAN for backup & recovery. The database is in archivelog mode. Online redo log files are multiplexed and I have more than one destinations for archive logs. If, due to a disk crash, all data files and redo log files are gone - and I have archive logs, one member of each redo log group and a full database backup available, I can recover till the last archived log. But I want to ask that what about the transactions which were not archived at the time of media failure? How can I recover committed transactions which were not archived at the time of crash, and I have a mirrored copy of log group which was current at the time of failure?
i have Oracle 10g data guard set up on windows environment.....i need to know the what are the right steps to perform failover in case the primary database gets fail.
I have one requirement. We have a Package which consists of 2 Stored Procedures which has a RecordType output Parameter.
FUNCTION GET_NFE_INFO(O_status_code IN OUT NUMBER, O_error_message IN OUT VARCHAR2, O_message IN OUT "OBJ_FM_NFE_DOCHDR_REC", I_fiscal_doc_id IN FM_FISCAL_DOC_HEADER.FISCAL_DOC_ID%TYPE) return BOOLEAN is
I am working on a one Stored Procedure which would display the output from the above function as,
Here for each and every data value i am Printing it using DBMS_OUTPUT, L_message record has more than 100 columns. Is there a easy way of handling it instead of using DBMS_OUTPUT ?
I have to create a table which should store data at Week level. The table have the following columns
Product id, Loc id, Business group id, FISCAL WEEK , Revenue,
Fiscal week column will have data as '2011-W01', '2011-W47' etc.
What should be the data type for fiscal week column. Based on this table i have to create a calculated column which should fetch trailing 12 weeks average for each row.
I am trying to create & use a new data type but keep getting a ora-0902 invalid data type error running on 10g express. The create type and select statements execute fine and select confirms the ssn_t is a type. The create table statement fails with the invalid data type error.
Here is an example.
CREATE TYPE ssn_t AS OBJECT ssn_type CHAR(11)); SELECT object_name, object_type FROM user_objects WHERE object_type = 'TYPE'; CREATE TABLE Z (A CHAR(4), B SSN_T);
I want to insert varying type of data from one table to another like i want to insert name from one table and salary from another,sysdate too.How should i consider doing it?
RAW datatype in oracle,any problems we will face using this datatype,Reason is we have column which stores session id in RAW datatype using sys_guid, drawbacks in using this datatype.