Rob, December 13, 2006 - 3:04 pm UTC
What do you mean when you state "the real plans are already in there"? If I do not use the 'explain' parameter I do not see the execution plan in the tkprof output file when I trace a session that executes PL/SQL code.
CREATE PROCEDURE foo
AS
lv_cnt NUMBER (9);
BEGIN
EXECUTE IMMEDIATE ('alter session set sql_trace=TRUE');
SELECT COUNT (*)
INTO lv_cnt
FROM all_objects;
EXECUTE IMMEDIATE ('alter session set sql_trace=FALSE');
END;
begin
foo;
end;
tkprof actp2_ora_7112.trc foo.out sys=no
TKPROF: Release 10.2.0.2.0 - Production on Wed Dec 13 14:00:43 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: actp2_ora_7112.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set sql_trace=TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.01 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 21 (recursive depth: 1)
********************************************************************************
SELECT COUNT (*)
FROM
ALL_OBJECTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.08 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 2.31 2.24 0 64256 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2.39 2.33 0 64256 0 1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 21 (recursive depth: 1)
********************************************************************************
alter session set sql_trace=FALSE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 21 (recursive depth: 1)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 345 0.09 0.10 0 0 0 0
Execute 707 0.07 0.06 0 0 0 0
Fetch 705 2.89 2.80 0 68739 0 705
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1757 3.05 2.97 0 68739 0 705
Misses in library cache during parse: 1
3 user SQL statements in session.
343 internal SQL statements in session.
346 SQL statements in session.
********************************************************************************
Trace file: actp2_ora_7112.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
3 user SQL statements in trace file.
343 internal SQL statements in trace file.
346 SQL statements in trace file.
9 unique SQL statements in trace file.
3499 lines in trace file.
2 elapsed seconds in trace file.
December 15, 2006 - 7:50 am UTC
exit the session so the cursors get closed and the stat records are written.
so
a) go into plus
b) run stuff
c) exit
d) AND THEN tkprof it.
Raghav, September 29, 2008 - 1:38 pm UTC
I have the same problem and the solution suggested by Tom is very useful. what if i can't change my code. is their any other way to fix this issue ?
September 29, 2008 - 1:41 pm UTC
what problem did you have
and what solution did you use
and what issue are you experiencing.
there is more than one thing written on this page - it is not at all clear what you are referring to?
Raghav, September 30, 2008 - 12:01 pm UTC
the issue i was facing is -ORA-00932: inconsistent datatypes: expected DATE got NUMBER.
---Here is the trace
INSERT INTO RPM_FUTURE_RETAIL ( FUTURE_RETAIL_ID, ITEM, DEPT, CLASS, SUBCLASS,
ZONE_NODE_TYPE, LOCATION, ACTION_DATE, SELLING_RETAIL,
SELLING_RETAIL_CURRENCY, SELLING_UOM, CLEAR_RETAIL, CLEAR_RETAIL_CURRENCY,
CLEAR_UOM, SIMPLE_PROMO_RETAIL, SIMPLE_PROMO_RETAIL_CURRENCY,
SIMPLE_PROMO_UOM, COMPLEX_PROMO_RETAIL, COMPLEX_PROMO_RETAIL_CURRENCY,
COMPLEX_PROMO_UOM , LOCK_VERSION) SELECT RPM_FUTURE_RETAIL_SEQ.NEXTVAL,
S.ITEM, S.DEPT, S.CLASS, S.SUBCLASS, LOC.TYPE, S.LOC, :B2 -:B1 ,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM, 0 FROM
RPM_STAGE_ITEM_LOC_CLEAN S, (SELECT STORE AS LOCATION, CURRENCY_CODE, 0 AS
TYPE FROM STORE UNION ALL SELECT WH AS LOCATION, CURRENCY_CODE, 2 AS TYPE
FROM WH) LOC WHERE S.LOC = LOC.LOCATION AND NOT EXISTS (SELECT 'x' FROM
RPM_BULK_CC_PE_IL IL WHERE IL.BULK_CC_PE_ID IN ( SELECT DISTINCT NUMBER_2
FROM TABLE(CAST(:B3 AS OBJ_NUM_NUM_STR_TBL))) AND IL.ITEM = S.ITEM AND
IL.LOCATION = S.LOC)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 219.95 8023.95 669072 601120 6470889 375664
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 219.95 8023.95 669072 601120 6470889 375664
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103 (RETEK) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
375664 SEQUENCE RPM_FUTURE_RETAIL_SEQ (cr=3863 pr=8 pw=0 time=16193862 us)
375664 HASH JOIN ANTI (cr=3481 pr=7 pw=0 time=4145065 us)
375696 HASH JOIN (cr=3478 pr=7 pw=0 time=986185 us)
375696 TABLE ACCESS FULL RPM_STAGE_ITEM_LOC_CLEAN (cr=3466 pr=0 pw=0 time=69 us)
150 VIEW (cr=12 pr=7 pw=0 time=32314 us)
150 UNION-ALL (cr=12 pr=7 pw=0 time=32011 us)
148 VIEW index$_join$_004 (cr=6 pr=3 pw=0 time=31540 us)
148 HASH JOIN (cr=6 pr=3 pw=0 time=31387 us)
148 INDEX FAST FULL SCAN PK_STORE (cr=3 pr=1 pw=0 time=12494 us)(object id 78814)
148 INDEX FAST FULL SCAN STORE_I1 (cr=3 pr=2 pw=0 time=17547 us)(object id 78809)
2 VIEW index$_join$_005 (cr=6 pr=4 pw=0 time=98722 us)
2 HASH JOIN (cr=6 pr=4 pw=0 time=98716 us)
2 INDEX FAST FULL SCAN PK_WH (cr=3 pr=2 pw=0 time=65972 us)(object id 79441)
2 INDEX FAST FULL SCAN WH_I2 (cr=3 pr=2 pw=0 time=32422 us)(object id 79437)
32 VIEW VW_SQ_1 (cr=3 pr=0 pw=0 time=579 us)
32 HASH JOIN (cr=3 pr=0 pw=0 time=541 us)
32 TABLE ACCESS FULL RPM_BULK_CC_PE_IL (cr=3 pr=0 pw=0 time=128 us)
11 COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=37 us)
error during execute of EXPLAIN PLAN statement
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
parse error offset: 544
**********************************************************
to resolve the issue i specified the date format..look for the bind variable B2 (B2 is Date and B1 is Number)
Here is the modified Query
INSERT INTO RPM_FUTURE_RETAIL ( FUTURE_RETAIL_ID, ITEM, DEPT, CLASS, SUBCLASS,
ZONE_NODE_TYPE, LOCATION, ACTION_DATE, SELLING_RETAIL,
SELLING_RETAIL_CURRENCY, SELLING_UOM, CLEAR_RETAIL, CLEAR_RETAIL_CURRENCY,
CLEAR_UOM, SIMPLE_PROMO_RETAIL, SIMPLE_PROMO_RETAIL_CURRENCY,
SIMPLE_PROMO_UOM, COMPLEX_PROMO_RETAIL, COMPLEX_PROMO_RETAIL_CURRENCY,
COMPLEX_PROMO_UOM , LOCK_VERSION) SELECT RPM_FUTURE_RETAIL_SEQ.NEXTVAL,
S.ITEM, S.DEPT, S.CLASS, S.SUBCLASS, LOC.TYPE, S.LOC,TO_DATE(:B2, 'dd-mon-yyyy hh24:mi:ss')-:B1 ,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM, 0 FROM
RPM_STAGE_ITEM_LOC_CLEAN S, (SELECT STORE AS LOCATION, CURRENCY_CODE, 0 AS
TYPE FROM STORE UNION ALL SELECT WH AS LOCATION, CURRENCY_CODE, 2 AS TYPE
FROM WH) LOC WHERE S.LOC = LOC.LOCATION AND NOT EXISTS (SELECT 'x' FROM
RPM_BULK_CC_PE_IL IL WHERE IL.BULK_CC_PE_ID IN ( SELECT DISTINCT NUMBER_2
FROM TABLE(CAST(:B3 AS OBJ_NUM_NUM_STR_TBL))) AND IL.ITEM = S.ITEM AND
IL.LOCATION = S.LOC)
******************************************************
Actually now i need to make this kind of change in a lot of places so i was wondering if their was a way i can resolve this by setting parameter NLS_LANG?
Thanks
Raghav
September 30, 2008 - 1:05 pm UTC
always be explicit, fix it right - use the explicit format and never rely on default formats.
Raghav, September 30, 2008 - 12:24 pm UTC
//Typo
by setting NLS_DATE_FORMAT
ORA-00932: inconsistent datatypes
Steve, February 25, 2009 - 2:20 am UTC
Hi Tom, I have a similar question hope u will answer that.
I have a table emp
with data as follows
SQL> select * from emp;
EID ENAME SALARY HIREDATE
--------- -------------------- -------------------- ---------
1 Paul 5000 06-SEP-06
2 Ram 5000 24-AUG-06
3 Tom 5000 09-SEP-06
and when i am executing this below query i am getting error pls help
SQL> select eid,cursor(select eid,salary from emp e where e.eid = emp.eid) from emp;
ERROR:
ORA-00932: inconsistent datatypes
no rows selected
February 25, 2009 - 6:51 am UTC
"U" isn't available, "U" is dead as far as I know. Look it up, it is true.
http://en.wikipedia.org/wiki/U_of_Goryeo If you want me to run a query and diagnose it, you must provide me a create table, insert into statements and a query in general, however....
ops$tkyte%ORA10GR2> create table emp ( eid number, ename varchar2(10), salary number, hiredate date );
Table created.
ops$tkyte%ORA10GR2> insert into emp values ( 1,'Paul',5000,'06-SEP-06' );
1 row created.
ops$tkyte%ORA10GR2> insert into emp values ( 2,'Ram',5000,'24-AUG-06' );
1 row created.
ops$tkyte%ORA10GR2> insert into emp values ( 3,'Tom',5000,'09-SEP-06' );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select eid,cursor(select eid,salary from emp e where e.eid = emp.eid) from emp;
EID CURSOR(SELECTEID,SAL
---------- --------------------
1 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2
EID SALARY
---------- ----------
1 5000
2 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2
EID SALARY
---------- ----------
2 5000
3 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2
EID SALARY
---------- ----------
3 5000
I don't see what you see, so - post an example that helps us reproduce.
sorry for the incomplete question
Steve, February 25, 2009 - 3:50 am UTC
Tom i want the output in the following way
empno deptno
empno ename
empno sal
empno hiredate with the above said query
February 25, 2009 - 6:55 am UTC
umm, man you do change the question. So we move from "we get an ora error" to "we probably are not getting an ora error but I would like the output formatted a bit differently"
funny thing, deptno - WHERE THE HECK did deptno come from all of a sudden.
Tell you what, when you phrase this as a specification, with a complete example, with creates and inserts and all - then I'll take a look. Pivoting and unpivoting (you are unpivoting) is very easy. Give me something concrete to work with and then we'll talk.
tkprof ora-00932
Jay Yu, March 06, 2009 - 9:27 am UTC
I do not see explain in tkprof output if explain= is not provided. I do not know what Tom is talking about.
This is happening in 10.2.0.4. I think there is some AF (Advanced Feature or bug) of oracle we are dealing with
see notes : 144784.1 on metalink.oracle.com for dealing with this issue.
Thanks
March 06, 2009 - 10:50 am UTC
If you close the cursors, there will be a row source operation in the trace file (10g and before), in 11g - regardless of the cursor state, there will be a row source operation for that.
So, go into sqlplus...
turn on tracing
run a query
exit sql plus
trace it - and then get back to us.
read this
http://asktom.oracle.com/Misc/when-explanation-doesn-sound-quite.html to understand why you don't want to use explain with tkprof.
and you know what is so so very funny to me? That note, it reads:
cause: In the absence of explicit data typecasting, TKPROF assumes that a
bind variable is a CHAR data type. If the SQL statement intends to use the bind
variable as a DATE data type, but TKPROF thought it was a CHAR, the SQL parser
will have a conflict in data types.wait, hold on - I think I've heard that before... hmmm, where - oh yeah:
All bind variables in explain plan are consider VARCHARS. Doesn't matter how
you define them in sqlplus - they are VARCHARS (you don't even *need* to define
them to explain them, explain plan doesn't care)
And in your hard coded example, you used - a string, trunc() doesn't know what
to expect - and it wasn't expecting a string, a string which it implicitly
would try to conver to a number, to be compared to a date - hence the "expected
date, got number"
that was the original answer..... Interesting.
is there a change in dynamic SQL between 9i and 10 gR2
A reader, March 24, 2009 - 10:11 am UTC
Hi Tom
i have tried this hard before asking here, serached your site also. I have this strange problem. (this is just a quick and dirty code to understand the reason of differences between 9i and 10g , so please ignore the coding part..i am trying to understand the difference between the 2 versions for the same code.).
in 9i database :
drop table t1 ;
create table t1 as select * from all_objects where rownum < 1001 ;
SQL> DECLARE
2 TYPE t_cur IS REF CURSOR;
3 emp_cv t_cur;
t_rec t1%ROWTYPE;
4 5 sql_stmt VARCHAR2(200);
6 v_object_id number := 1107 ;
7 v_owner varchar2(100) ;
8 v_object_name varchar2(100) ;
9 BEGIN
10 sql_stmt := 'SELECT object_name,owner FROM t1 WHERE object_id = :j';
11 OPEN emp_cv FOR sql_stmt USING v_object_id ;
12 LOOP
13 FETCH emp_cv INTO v_object_name;
14 EXIT WHEN emp_cv%NOTFOUND;
15 DBMS_OUTPUT.PUT_LINE('id: ' ||v_object_id || ' object_name: ' ||
16 v_object_name);
17 END LOOP;
18 CLOSE emp_cv;
19 END;
20 /
id: 1107 object_name: NLS_SESSION_PARAMETERS
PL/SQL procedure successfully completed.
in 10g database (10g R2)
if I ran the same code....
SQL> drop table t1 ;
create table t1 as select * from all_objects where rownum < 1001 ;
Table dropped.
SQL>
Table created.
SQL> DECLARE
2 TYPE t_cur IS REF CURSOR;
emp_cv t_cur;
3 4 t_rec t1%ROWTYPE;
5 sql_stmt VARCHAR2(200);
6 v_object_id number := 1107 ;
7 v_owner varchar2(100) ;
8 v_object_name varchar2(100) ;
9 BEGIN
sql_stmt := 'SELECT object_name,owner FROM t1 WHERE object_id = :j';
10 11 OPEN emp_cv FOR sql_stmt USING v_object_id ;
12 LOOP
13 FETCH emp_cv INTO v_object_name;
14 EXIT WHEN emp_cv%NOTFOUND;
15 DBMS_OUTPUT.PUT_LINE('id: ' ||v_object_id || ' object_name: ' ||
16 v_object_name);
17 END LOOP;
18 CLOSE emp_cv;
19 END;
20 /
DECLARE
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 13
so something has changed between 9i and 10g with respect to dynamic SQL.
the reason I asked this that some of our dynamic SQL codes has started failing in 10g Test environment, we are trying to upgrade from 9i to 10g (yet in small test environment).
- Thanks
March 29, 2009 - 10:40 am UTC
you had a bug in your 9i code, we had a bug in our 9i code - we fixed ours in 10g - your select list and FETCH LIST must match, it was an error on our part in 9i correct in 10g.
if you want to select more columns than you actually fetch into, you'd have to use DBMS_SQL which has a procedural API to access the i'th column in a select list and doesn't care if you don't access the second column...
Reene, March 26, 2009 - 2:53 am UTC
Hi Tom
i have tried this hard before asking here, serached your site also. I have this strange problem.
(this is just a quick and dirty code to understand the reason of differences between 9i and 10g ,
so please ignore the coding part..i am trying to understand the difference between the 2 versions
for the same code.).
in 9i database :
drop table t1 ;
create table t1 as select * from all_objects where rownum < 1001 ;
SQL> DECLARE
2 TYPE t_cur IS REF CURSOR;
3 emp_cv t_cur;
t_rec t1%ROWTYPE;
4 5 sql_stmt VARCHAR2(200);
6 v_object_id number := 1107 ;
7 v_owner varchar2(100) ;
8 v_object_name varchar2(100) ;
9 BEGIN
10 sql_stmt := 'SELECT object_name,owner FROM t1 WHERE object_id = :j';
11 OPEN emp_cv FOR sql_stmt USING v_object_id ;
12 LOOP
13 FETCH emp_cv INTO v_object_name;
14 EXIT WHEN emp_cv%NOTFOUND;
15 DBMS_OUTPUT.PUT_LINE('id: ' ||v_object_id || ' object_name: ' ||
16 v_object_name);
17 END LOOP;
18 CLOSE emp_cv;
19 END;
20 /
id: 1107 object_name: NLS_SESSION_PARAMETERS
PL/SQL procedure successfully completed.
in 10g database (10g R2)
if I ran the same code....
SQL> drop table t1 ;
create table t1 as select * from all_objects where rownum < 1001 ;
Table dropped.
SQL>
Table created.
SQL> DECLARE
2 TYPE t_cur IS REF CURSOR;
emp_cv t_cur;
3 4 t_rec t1%ROWTYPE;
5 sql_stmt VARCHAR2(200);
6 v_object_id number := 1107 ;
7 v_owner varchar2(100) ;
8 v_object_name varchar2(100) ;
9 BEGIN
sql_stmt := 'SELECT object_name,owner FROM t1 WHERE object_id = :j';
10 11 OPEN emp_cv FOR sql_stmt USING v_object_id ;
12 LOOP
13 FETCH emp_cv INTO v_object_name;
14 EXIT WHEN emp_cv%NOTFOUND;
15 DBMS_OUTPUT.PUT_LINE('id: ' ||v_object_id || ' object_name: ' ||
16 v_object_name);
17 END LOOP;
18 CLOSE emp_cv;
19 END;
20 /
DECLARE
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 13
so something has changed between 9i and 10g with respect to dynamic SQL.
the reason I asked this that some of our dynamic SQL codes has started failing in 10g Test
environment, we are trying to upgrade from 9i to 10g (yet in small test environment).
- Thanks
March 30, 2009 - 2:51 pm UTC
look in the other two places you asked this same exact thing - one of them has your answer, the other says "look in the other place you asked this same exact thing"
you had a bug in your code (you selected 2 columns, fetched into 1 column). We had a bug in our code that permitted this. We fixed our bug - you need to fix yours now.
thanks
A reader, March 31, 2009 - 7:52 am UTC
Hi Tom,
yes it is a bug in our code, true.but the problem is ,we have many programs developed using dynamic SQL now we will have to go through each of them in order to be sure that they will work in 10g as well. i guess if I ask you is there any option, you will say know..but I will still ask if there is any other workaround - if not to fix then at least to find out such issues in the existing code. Hope is a good thing so I asked.
also - where to find all such changes in 10g (from 9i ) .is there a document which has listed all such changes in 10g. kindly give the URL or name of that doc.
Thanks
March 31, 2009 - 9:06 am UTC
but if the select lists mismatch - you'll probably find some bugs in your code as you go.
and look at each dynamic sql one and ask yourself "wow, did it really need to be dynamic sql" - because in my experience, the answer in plsql is mostly "no, it did not"
This would not be considered a 'change', this was a bug fix, of which there are 'many'
[ORA-00932: inconsistent datatypes: expected got ], [], [], [], [], []
Yassin Othman, February 23, 2010 - 7:00 am UTC
Hi All,
I totally agree with Tom.
Yes, the expected date got number, and it should be converted explicitly to the corresponded datatype of the selectivity predicate or you have to change the database parameter cursor_sharing form the force stat to at least similar, this is to avoid the bind variable implicit conversion.
ORA--00932: inconsistent datatypes: expected DATE got NUMBER
Rucha, January 03, 2012 - 6:42 am UTC
Hi tom,
I am getting the same error while taking the explain plan for the query my query is as follows
SELECT cam.STRPREFERREDNAME AgentName,CAMSLIVE.Com_Rep_Get_Client_Addr_Fun(cam.strclientcd) AgentAddress,
CAMSLIVE.Com_Rep_Get_Client_Contact_Fun(cam.strclientcd)AgentTelephone,
CAMSLIVE.Com_Rep_Get_Client_Contact_Fun(cam.strclientcd, 'MOB')AgentMobile,
--SUBSTR(cap.strbankacctnbr,-5,5)AgentBankAccNbr,
--Commented by Yogendra S on17-Mar-2009
cap.strbankacctnbr AgentBankAccNbr,
--Added by Yogendra S on 16-Mar-2009
rais.stragentcd AgentCode,CAMSLIVE.Com_Rep_Get_Desgndesc_Fun(cam.strdesgncd)AgentDesignation,
CAMSLIVE.Cm_Fn_Get_Agent_Preferred_Name(cam.stragencycd)AgencyName,CAMSLIVE.Com_Rep_Get_Office_Fun(cam.strbranchcd, 1)OfficeCode,
CAMSLIVE.Com_Rep_Get_Office_Fun(cam.strbranchcd, 2)OfficeName,
CAMSLIVE.Com_Rep_Get_Addr_Fun(cam.strbranchcd)BranchAddr,
CAMSLIVE.Com_Rep_Get_Client_Contact_Fun(cam.strclientcd, 'OFF')OfficeTelephone,
ccm.strNewICNbr PANNo,rais.stritemdesc Item,rais.nitemseq,rais.strspcd SPCode,rais.strdesgncd DesgnLevel,
rais.strlaname InsuredName,rais.strpolnbr PolicyNo,rais.npolyearinforce InforceYear,
rais.strpolstatus PolicyStatus,rais.strrop ROPFlag,rais.strpmtmode PaymentMode,rais.DTDUE,
SUM(rais.dmodalprm) over(PARTITION BY rais.stragentcd,rais.dtTo,
rais.nitemseq,rais.strpolnbr,rais.stritemdesc ORDER BY rais.stragentcd,rais.dtTo,
rais.nitemseq,rais.strpolnbr,rais.stritemdesc) ModalPremium,
-- added rais.stritemdesc in partition by Saurabh on 03122009
rais.dtTo PaymentCycleDate,rais.drate PercRate,rais.strmisc,
SUM(rais.dfycamt) over(PARTITION BY rais.stragentcd,rais.dtTo,rais.nitemseq,rais.strpolnbr,
rais.stritemdesc ORDER BY rais.stragentcd,rais.dtTo, rais.nitemseq,rais.strpolnbr,rais.stritemdesc)FYC_RYC,
-- added rais.stritemdesc in partition by Saurabh on 03122009
SUM(rais.dfypamt) over(PARTITION BY rais.stragentcd,rais.dtTo, rais.nitemseq,rais.strpolnbr,
rais.stritemdesc ORDER BY rais.stragentcd,rais.dtTo, rais.nitemseq,rais.strpolnbr,rais.stritemdesc) FYP_RYP,
-- added rais.stritemdesc in partition by Saurabh on 03122009
--SUM(DECODE(rais.NITEMSEQ, 1,Com_Rep_Get_Open_Bal_Fun(cam.STRAGENTCD, rais.DTTO,1), rais.ddramt))
--over(PARTITION BY rais.stragentcd,rais.dtTo, rais.nitemseq,rais.strpolnbr ORDER BY rais.stragentcd,rais.dtTo, rais.nitemseq, rais.strpolnbr)DebitToAgent,
--SUM(DECODE(rais.NITEMSEQ, 1,Com_Rep_Get_OpBal_New_Fun(cam.STRAGENTCD, :bv_payment_from_date ,:bv_payment_to_date,1),rais.ddramt))
--over(PARTITION BY rais.stragentcd,rais.dtTo,rais.nitemseq,rais.strpolnbr ORDER BY rais.stragentcd,rais.dtTo,rais.nitemseq, rais.strpolnbr) DebitToAgent,
SUM(DECODE(rais.NITEMSEQ, 1, CAMSLIVE.Com_Rep_Get_OpBal_New_Fun(cam.STRAGENTCD, :bv_payment_from_date ,:bv_payment_to_date ,1),rais.ddramt))
over(PARTITION BY rais.stragentcd,rais.dtTo, rais.nitemseq,rais.strpolnbr,
rais.stritemdesc ORDER BY rais.stragentcd,rais.dtTo, rais.nitemseq, rais.strpolnbr,rais.stritemdesc)DebitToAgent,
-- added rais.stritemdesc in partition by Saurabh on 03122009
--SUM(rais.dcramt) over(PARTITION BY rais.stragentcd,rais.dtTo,rais.nitemseq,rais.strpolnbr ORDER BY rais.stragentcd,rais.dtTo, rais.nitemseq,rais.strpolnbr)CreditToAgent,
DECODE(rais.nitemseq, 103,(SUM(rais.dcramt) over(PARTITION BY rais.stragentcd,rais.dtTo, rais.nitemseq,rais.strpolnbr,
rais.stritemdesc,rais.strmisc,rais.strmisc ORDER BY rais.stragentcd,rais.dtTo,
rais.nitemseq,rais.strpolnbr,rais.stritemdesc,rais.strmisc)),
(SUM(rais.dcramt) over (PARTITION BY rais.stragentcd,rais.dtTo,
rais.nitemseq,rais.strpolnbr,rais.stritemdesc ORDER BY rais.stragentcd,rais.dtTo,
rais.nitemseq,rais.strpolnbr,rais.stritemdesc))) CreditToAgent,
-- added rais.stritemdesc in partition by Saurabh on 03122009
CAMSLIVE.Com_Rep_Get_Comp_Name_Fun CompanyName,CAMSLIVE.Com_Rep_Get_Comp_Regnbr_Fun CompRegNbr,
CAMSLIVE.Com_Rep_Get_Comp_Addr_Fun CompanyAddr,
(SELECT 'For any query,Visit us at ' || ccompm.strcompportal FROM CAMSLIVE.COM_COMPANY_M ccompm WHERE ccompm.STRCOMPCD = '111')CompanyPortal,
cam.CCHANNELTYPE FROM CAMSLIVE.REP_AGT_INCOME_STMT rais,CAMSLIVE.CHM_AGENT_M cam,
CAMSLIVE.CHM_AGENT_PAYMENT cap,CAMSLIVE.COM_CLIENT_M ccm WHERE rais.stragentcd = NVL(:bv_agent_code,rais.stragentcd)AND rais.nisagtorba = 1
--AND( (rais.NITEMSEQ=1 AND rais.DTfrom = TRUNC(:vd_paymentfrom) AND rais.dtto= TRUNC(:vd_paymentto)) or rais.NITEMSEQ !=1)
AND (rais.NITEMSEQ !=1 OR (rais.NITEMSEQ=1 AND rais.dtto = TRUNC(:bv_payment_to_date)))AND TRUNC(rais.DTTO)BETWEEN TRUNC(:bv_payment_from_date) AND TRUNC(:bv_payment_to_date)
AND rais.nitalicuser = NVL(:pi_User_Type, rais.nitalicuser)AND NVL(rais.nbenefittype,-9) <>4
--Added by ManojP#1 to exclude PDA
AND (:bv_office_disp_cd IS NULL OR CAMSLIVE.Com_Rep_Get_Office_Fun(cam.strbranchcd, 1)=UPPER(:bv_office_disp_cd))
AND rais.stragentcd = cam.stragentcd AND((EXISTS (SELECT 1 FROM CAMSLIVE.COM_USER_M WHERE UPPER(STRUSERID) = UPPER(:pi_userid)
AND STRUSERTYPE = 1))OR(EXISTS(SELECT 1 FROM CAMSLIVE.COM_USER_BRANCH_MAP_M WHERE UPPER(STRUSERID) = UPPER(:pi_userid)
AND STRAGNYBRCHDISPCD = CAMSLIVE.Com_Rep_Get_Office_Fun(cam.strbranchcd,1))))
AND cam.stragentcd = cap.stragentcd AND ccm.strclientcd = cam.strclientcd
and this is the perticular line where i am getting the error
TRUNC(rais.DTTO)BETWEEN TRUNC(:bv_payment_from_date) AND TRUNC(:bv_payment_to_date)
AND rais.nitalicuser
plz provide some solution.
vinay, December 05, 2013 - 4:55 pm UTC
Hi thomas,
when Enter this code i got this error.please provide solution..........for this query
1 SELECT ENAME,JOB,SAL EMPSAL,
2 CONNECT_BY_ROOT(SAL) BOSSSAL,
3 CONNECT_BY_ROOT(SAL)-SAL DIFF
4 FROM EMP
5 START WITH ENAME='KING'
6* CONNECT BY PRIOR EMPNO=MGR
SQL> /
SELECT ENAME,JOB,SAL EMPSAL,
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got -