PL/SQL :: NVL Statement For All Datatypes

May 2, 2013

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.

nvl(x,'1') or nvl(mydate,sysdate)

View 21 Replies


ADVERTISEMENT

SQL & PL/SQL :: IF / THEN Statements With Different Datatypes

Sep 30, 2011

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

Error report:
SQL Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s"

View 4 Replies View Related

SQL & PL/SQL :: How To Join Two Columns Of Different Datatypes

May 3, 2012

I want to join two columns, one with a Long and another with a Varchar2.

How to achieve that?

View 9 Replies View Related

SQL & PL/SQL :: Dealers Count - Inconsistent Datatypes

Oct 4, 2011

I have a PLSQL block as below :

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

View 9 Replies View Related

SQL & PL/SQL :: ORA-00932 / Inconsistent Datatypes / Expected - Got -

Mar 6, 2012

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) ,

[code].....

View 2 Replies View Related

SQL & PL/SQL :: ORA-00932 - Inconsistent Datatypes - UTL-FILE?

Apr 13, 2011

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;

[code]...

View 2 Replies View Related

SQL & PL/SQL :: ORA-00932 Inconsistent Datatypes - Expected - Got CLOB

Jun 21, 2006

There we have two tables: my_comment and sys_comment.And field comment_desc in table my_comment is CLOB, and in table sys_comment is VARCHAR.

And I can run following two SQL successsfully:
SELECT comment_desc FROM my_comment;
SELECT TO_CLOB(comment_desc) FROM sys_comment;

But when I do UNION of them, execute following SQL:

SELECT comment_desc FROM my_comment
UNION
SELECT TO_CLOB(comment_desc) FROM sys_comment;

Error occurs:
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

View 6 Replies View Related

SQL & PL/SQL :: Query Two Columns With Datatypes As Number And Varchar

Jul 17, 2012

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.

View 4 Replies View Related

SQL & PL/SQL :: Failed - ORA-00932 / Inconsistent Datatypes / Expected - Got -

Aug 19, 2013

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 -".

View 3 Replies View Related

PL/SQL :: ORA-00932 / Inconsistent Datatypes / Expected NUMBER Got -

Dec 26, 2012

I am using below query for collect

select account_id_N,collect(connection_id_v) from My_test_table group by account_id_n;

but its giving me error as below.

ORA-00932: inconsistent datatypes: expected NUMBER got -
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:   
*Action:

View 7 Replies View Related

XML DB :: Variable Datatypes In Select Of Data From XML To Oracle

Jan 30, 2013

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:

select v.instrid, v.endtoendid, v.txid, v.cd, v.ccy,
replace(v.intrbksttlmamt,'.',',') as intrbksttlmamt, v.chrgbr, v.bic1, v.nm1, v.adrline11, v.adrline12, v.ctry1, v.iban1,
v.bic2, v.bic3, v.nm2, v.adrline21, v.adrline22, v.ctry2, v.iban2, v.cdtrref, v.addtlrmtinf
from the_data w,
xmltable(xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:pacs.008.001.01'),
[code]....

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.

View 3 Replies View Related

SQL & PL/SQL :: ORA-00932 Inconsistent Datatypes - Expected NUMBER Got CHAR

Aug 17, 2010

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)

View 9 Replies View Related

How To Compare Data Of Clob And Long Datatypes Using DBLink

Aug 13, 2012

I would like to run below query on all tables, however it doesnt work on clob and long datatypes

select * from owner.table_name
minus
select * from owner.table_name@remote_db;
from dba_tables
where owner in ( '....');

ORA-00932: inconsistent datatypes: expected - got CLOB

How can I compare the data of clob and long datatypes using dblink ?

View 2 Replies View Related

Replication :: Oracle Streams - Column Datatypes In Some Of The Tables On Source And Destination Are Different?

Jul 20, 2009

We are using Oracle Streams for replication.

Column datatypes in some of the tables on Source and Destination are different (Number on Source and Varchar2 on destination).

Do we have to create any rule or dml handler to handle this or Streams will automatically take care of it?We are oracle 10g.

View 1 Replies View Related

Variable Usage In Type Of Table Declaration Statement And Execute Immediate Statement

Aug 10, 2011

HOW to use variable P_TMPLID in following statement

TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;

because its throwing error while compiling

and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION

View 2 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Select Statement Is Blocking A Delete Statement

Jan 11, 2012

I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).

When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:

SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...

From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?

View 10 Replies View Related

SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?

Jan 12, 2011

If i inserted the values in table it gets inserting very few rows only.I dont know y it is?

View 15 Replies View Related

If Statement In Select?

Mar 22, 2013

simply select and works great:

select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,gcrs.Area_name, gcrs.sector_name,

substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,

case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2

from table(f_carat_issues_as_of('31/MAR/2013')) i inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id

where UPPER(ISSUE_STATUS) like '%OPEN%'

Now I want to callte two columns:ISSUE_DIVISION and ISSUE_DIVISION_2

if they are equal in new columns should be value 1 if are not equal should be 0,how can I do it ?

View 4 Replies View Related

SQL & PL/SQL :: How To Get Statement That Causes Exception

Jul 17, 2011

I am using Exception when others then body end;

inside the body i can get SQLCODE and SQLERRM but I also need to get the SQL statement that caused the error and I dont know how.

View 39 Replies View Related

How To Use The NVL() Function In Statement

Mar 24, 2009

I have the following query.

sql

Original

- sql Code

SELECT class, COUNT (class)
FROM nv_table
WHERE l_id IN (
SELECT l_id
FROM n_table
WHERE id IN (1234)
GROUP BY class
ORDER BY class

SELECT class, COUNT (class) 
FROM nv_table 
WHERE l_id IN (          SELECT l_id            FROM n_table           WHERE id IN (1234)   
GROUP BY class   
ORDER BY class

It returns two columns: class and the total number of values in that class.

In some cases, there might not be any values under a certain class, so the query won't return anything.

In such a case I want the query to return 0. So what I want to see is:

class:A, COUNT(class):0

I'm trying to use the NVL function here, but either it doesn't work in this context, or it's not the correct syntax the way I'm writing it.

sql

Original

- sql Code

SELECT class, NVL(COUNT (class), 0)
FROM nv_table
WHERE l_id IN (
SELECT l_id
FROM n_table
WHERE id IN (1234)

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: Group By Statement

Oct 27, 2011

SELECT COUNT(c.country_id)
FROM countries c,
employees e,
locations l,
departments d

[Code]....

I really dont know when and where to use the group by statement from the query above when I added C.country name before the word from I'm always getting this error "ORA-00979: not a GROUP BY expression"

View 12 Replies View Related

SQL & PL/SQL :: Not In Condition In IF Statement?

Sep 19, 2011

I need to check somewhere in the code as " Check the variable with in 20 varaiables , if not excute somes statements"

Example

a1=20,a2=30,a3=40......a20=200; (Like this way i have 20 varaibles with values)
v1=150;
if( v1<>a1 AND V1<> a2 AND V1 <> a3...... AND V1<>20)
execute some insert block;
End if;

But it's writting very diffcult as i need to compare with 20 varaiables.Was there any alternative sloution like taking some VARRAY and checking in IF condition like v1 NOT IN(Va) (Where 'Va' is like VARRAY)

View 13 Replies View Related

SQL & PL/SQL :: DML Statement For Oracle

May 20, 2010

I have a table which has 4 varchar columns( col1, col2,col3, col4). There is data in the table for col1,col2,col3 and col4 is a newly added column. I want to concatenate values from col1,col2 and col3( comma separated) and put it in col4( as col1,col2,col3). Note that col1 or col2 or col3 can have null values and the concatenation should ignore null.

Is it possible to do it in a single SQL statement or should I use a procedure to do this?

View 6 Replies View Related

SQL & PL/SQL :: Insert Statement?

May 13, 2011

I have one query i have create one table T1 in this table i have this three colm (EMPNO,ENAME,HIREDATE) and i have to insert raw from this two table T2 and T3 in T2 table colms are (EMPNO,SAL) and T3 table colm are (EMPNO,MGRID) so which query i have to run

View 4 Replies View Related

PL/SQL :: Creating (If-Else) Statement?

May 2, 2013

We make a 'SELECT' and then create a file with the results and send it by email... what we want to do is , if there are no results from that select , not sending the email neither creating the file..

View 5 Replies View Related

SQL & PL/SQL :: Update Statement (lag)

Aug 10, 2011

I have a table zTest with several products:

create table zTEST
( PRODUCT number,
ID number,
Flag number,
FLAG_L1 number );

[Code]...

The field FLAG_L1 is the field FLAG with a Lag 1 (order by ID, partition by PRODUCT). I want to write the field FLAG_L1 in an update statement, how can I do this?

View 7 Replies View Related

Flow Of Statement Processing?

Apr 18, 2013

SQL Statement Processing Steps:
1. Create a cursor.
2. Parse the statement.

parsing (syntax check, semantic check, privilege check, allocate private sql area, shared pool check (library cache check shared sql area. Here is hard or soft parse)SQL queries submitted to the system first run through the parser, which checks syntax and analyzes semantics. The result of this phase is called a parsed representation of the statement.

This parsed representation is then sent to the optimizer, which handles three main functionality:Transformation, estimation, and execution plan generation.

3. Describe query results.
4. Define query output.
5. Bind variables.
6. Parallelize the statement.
7. Execute the statement.
8. Fetch rows of a query.
9. Close the cursor.

- What stages from the above are included in the 'Query Optimizer Stage'
- Is the parse tree created after doing both syntax check and systematic check as I found notes where they mention it was done before symantic check
- What stages are skipped when doing soft parse vs hard parse
-when mentioned parsed representation, is that the same as parse tree.

I am just trying to understand more the flow of the query processing.

View 1 Replies View Related

SQL Statement To Remove Extension

Oct 15, 2008

I have a file name field in my database that stores each file name with the extension .TXT and almost each file name is different.I would like to remove this extension from all of the file names without using the different file name each time I update. Is there any SQL statement that will allow me to do this? I am using Oracle.

View 1 Replies View Related

Creating A Trigger Within If-else Statement

May 25, 2012

I would like to create a trigger on a table, but only if the table exists.

This is what i've got so far:

create or replace function tableExists (tablename in varchar2) return boolean
is
c int; 
begin    

[Code].....

This would give me the error:

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update

View 1 Replies View Related







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