My table looks like this ->
Dp_value
124325
2434
3536
3536
Code is ->
(case when CL.DECIMALPLACES = 0 and CL.FIELDTYPE = 'D' and cl.DATATYPE = 'N'
then trim(dp."Value")*1000
else dp."Value" end) as dp_value,
What I am trying to do ->
From another table cl I want to say if decimal place is 0, fieldtype is decimal (D/C) and datatype is number (N/T)then multiply the number with 1000.
If not then leave it as it is.
When I run my code I get the error:
ORA-00932 : inconsistent datatypes: expected NUMBER got CHAR
for else dp."Value" end)
I have writen PL/SQL packages for data loging through pipe lined function for better peformance.The below packages has been compiled sucessfully but during the run time it shows an error like "ORA-00932: inconsistent datatypes: expected - got -".
CREATE OR REPLACE PACKAGE pkg_mkt_hub_load AS PROCEDURE sp_final_load_mkt_hub; FUNCTION fnc_pipe_tot_lvl_idx_mon_hub (pi_input_cur IN SYS_REFCURSOR) RETURN tot_lvl_idx_mon_tt PIPELINED;
[code]...
SHOW ERRORS
Error:
ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got - ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 33 ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 55 ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 92 ORA-06512: at line 1
types scripts:
create or replace type tot_lvl_idx_mon_ot as object (SSIA_INDEX_ID VARCHAR2(60), start_date date, CURRENCY VARCHAR2(10), LEVEL1 NUMBER(31,11), TYPE VARCHAR2(31) ,
I am fetching more than one column data from a table repetedly(By different query string each time) through ref cursor using concatenate function successfully. But I can see if one concatenate function is getting missed due to human intervention, it's causing failure for entaire process and returning oracle exception as "Failed - ORA-00932: inconsistent datatypes: expected - got -".
I am working on a data migration project where I am extracting data from Oracle8i database and writing it to a Text file using File_UTL utility. In one extract procedure, I need to write a LONG datatype with some VARCHAR2 datatype in extract file. Procedure compiled fine but I am getting the "ORA-00932: inconsistent datatypes" error while executing the final procedure to write data to a file.
below the code snipplet. I am trying t write Account_Primarykey, Account_name and it's AText (LONG datatype) together in a extract file:
create or replace procedure FORCE_W_ACCOUNT as Begin declare file_handel UTL_FILE.FILE_TYPE;
I am getting this error on a varchar2(30) data column that I am trying to convert to number. I am creating a new view against some tables that house converted legacy data.The table is defined with a line_type column and a line_value columns. The line_type will contain either 'AMT', 'DATE' or 'STR'. The line_value column is defined as varchar2(30). Based on the line_type, that will determine how I format the line_value data. I have created the select for my view to translate this using a case statement:
CASE line_type when 'AMT' then round(to_number(line_7_value),2) when 'DATE' then to_date(line_7_value, 'YYYY-MM-DD') ELSE line_7_value END
I can't figure out what I am doing incorrectly. It does not like the "When 'AMT'" line, gives me an error of 'inconsistent data type, expected NUMBER, got CHAR". The data in line_7_value is defined as varchar2(30), I attempting to use To_number to convert it to a number, then round the number. I don't know why it says it is 'expecting number'.
DECLARE cursor c1 is select /*+ INDEX(NI04.NI_DPR_DEALER IX_DPR_DEALER) */ DEALER_CODE from ni_dpr_dealer where not exists (select null from dealer_processed where ni_dpr_deale r.DEALER_CODE = dealer_processed.DEALER_CODE); type cur_type is REF CURSOR; [code].......
show errors;
I am getting errors as below
Entering Dealers count ::13236 entering conditions Dealer name at 1 => HOL202 DECLARE * ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got - ORA-06512: at line 27
Have table with two columns with datatypes as number and varchar and the values in A column like 1,2,3 AND B column values like a,b,c. Now need to display data in a single column as 1,a,2,b,3,c.
I want to pass english character as a parameter and search a string that having swedish character, this needs to be done for all the swedish characters.
test _t table having the below 3 values
päiväp metervara flerfärgad
1. If user searches based on english char like below then they should get all the 3 values bcz fist & last have swedish ä and second one having english a.
Query : select name from test_t where name like '%a%'
Regsult: päiväp metervara fräg
2. If user searches based on swedish char like below then they should get only 2 values bcz fist & last have swedish ä and second one having english a.
select name from test_t where name like '%ä%'
päiväp fräg
Is any in-built function available, for Text alternative string search. If not how to search the string based on the query.
is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.My application has a querie like this:
If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.This table has 4.000.000 rows. Oracle 11.2.0.2SO
I am Having below query which is having total 664 records and for WHERE Clause (accountno ='13987135') it is having 3 records but when i am taking count it is returning 3 at first time and again returning 4 every time from then onwords.
I am receiving two large export files from a vendor, so I have no control over the contents. I need to import these into our database. The two export files are very similar, except the one has slightly differenet columns in it. So, export file 1 may have a table:
COLUMN_A COLUMN_B COLUMN_C
The second file may have:
COLUMN_A COLUMN_B COLUMN_D
At the destination, I have a table that has:
COLUMN_A COLUMN_B COLUMN_C COLUMN_D
Is there a parameter that would let me interchangably import either (or both) files into this destination table? This is my first attempt at data pump - but I know using import this has caused me issues. Not sure if the same limitations exist? Will the missing columns cause it to fail?
I have a column in a database that contains both numerical and char data. I would like to be able to do two different things (two different queries)
1. divide the numerical data in the column by 10 and leave the char data alone (just return it)
2. detect the numerical data in the column and treat is as a different value so I can run averages & counts on it while disregarding the char data
I'm not at all sure how to do number 2. I thought a CASE statement would work for number 1, but then I realized CASE doesn't like different datatypes:
select case when '1234' = 'checked' then 'checked' when '1234' = 'gen.nograde' then 'gen.nograde' when '1234' = null then null else '1234'/10 end as "GRADE" from dual
On DB 11.2. I am dynamically building a DB after update trigger that compares old/new column values. I wanted to include a nvl statement in my if conditions as some field values may be blank.
Since I have many different datatypes (varchar,date,number,blob,clob), is there a single nvl comparison I can use that will work against all of these? I was hoping not to have to check the datatype and change my nvl comparison depending on what type it is. i.e.
I'm new to XML to Oracle data integration. I do have clob column that has the XML data and trying to bring it into the Oracle relational tables, as a part of it we are developing PL/SQL procedure that calls this CLOB column, I want to know if we can variablize the data types ( Just like tablename. column name%type) in the select of the XML data:
I do define the data types after every column name , the problem is incase there is change in datatype I do need to manually edit the procedure to change the datatype is there a way I can variablize them something like tablename.columname%type.
Below is the sample code working fine in 10g and not working now in 11g.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS import java.sql.SQLData; import java.sql.SQLException; import java.sql.SQLInput; import java.sql.SQLOutput; import java.util.List; [code]....
we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class
Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
with t1 as ( select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual union all select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual union all select 'initial val' param_name, 'mb256_type' param_type,'30' param_value from dual) select param_name,param_type,param_value from t1;
desired output:
need output in a row in three different columns
param_value 01-01-1970 31-12-9999 30
I tried below query
SELECT * FROM ( with t1 as ( select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual union all select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
I am having some difficulties with this trigger. It keeps giving me the error "ERROR at line 5: PL/SQL: ORA-00923: FROM keyword not found where expected" when I am not even using a SELECT before the line it says the error is on? Here is the trigger that I am attempting to create.
CREATE OR REPLACE TRIGGER ClassRestraint BEFORE INSERT ON Enrolled FOR EACH ROW DECLARE numCourses NUMBER :=0; myException EXCEPTION; BEGIN [code]...
I have this script which should find tablespaces and their size, joined with free bytes. Trying to run this gives me the SQL Error: ORA-00923: FROM keyword not found where expected.
I have two questions:
1. Where should the FROM be?
2. Is there something wrong with the join.
============================================== set linesize 120 col "TOTAL (KB)" format 99999999999999999 col "FREE (KB)" format 9999999999999999 col TSNAME format a35 col "% FREE" format a10;
SELECT a.tablespace_name TSNAME, sum(a.bytes/1024) "TOTAL (KB)", Sum(b.bytes/1024) "FREE (KB)" To_char(round((sum(a.bytes/1024)/sum(a.bytes/1024))*100),2), 'FM99990D999999') || ' % ' "% FREE" FROM dba_data_files a, dba_free_space b Where a.tablespace_name = b.tablespacename Group by a. tablespace_name [/i] =============================================
I used the script from [URL]
It worked great but I'm not sure how to use the arithmetic functions to show me MB instead of bytes.
begin insert into plc_dw_dry_run_fic_rfsh values (424740,'1','','LTML','48000'); insert into plc_dw_dry_run_fic_rfsh values (424736,'1','','LTML','32000'); insert into plc_dw_dry_run_fic_rfsh values (424738,'1','','LTML','128000'); insert into plc_dw_dry_run_fic_rfsh values (424783,'1','','LTML','96000'); insert into plc_dw_dry_run_fic_rfsh values (424789,'2','','LTML','96000'); insert into plc_dw_dry_run_fic_rfsh values (424750,'1',198,'LTML','10000'); insert into plc_dw_dry_run_fic_rfsh values (424760,'1',199,'LDFM','20000'); insert into plc_dw_dry_run_fic_rfsh values (424770,'1','','LTML','192000'); end;
Rules --------- 1) First next_cpn_lvl_id1 should be considered (next_intrvl_code can be anything), if next_cpn_lvl_id1 is 198 or 199 then new_ltr_code should have 1a.
2) If next_cpn_lvl_id1 is not 198 or 199, then next_intrvl_code should be considered
a) if next_intrvl_code is LTML and mod of next_intrvl_value and 96000 is zero then new_ltr_code should be 1a b) if next_intrvl_code is LTML and mod of next_intrvl_value and 48000 is zero then new_ltr_code should be 1b
3) If 1 & 2 are not satisfied, ltr_code should be assigned to new_ltr_code.
I am trying to compile this block for updating a record. In the P_ADD_LOV_SQL column, I have to update the following select statment, but when ever I am compiling it it shows error in the Select statement as : ORA-00923: FROM keyword not found where expected. rearrange the select statement so that it doesn't show the error.The coding is :
