Home » SQL & PL/SQL » SQL & PL/SQL » Confusing Error (10g)
Confusing Error [message #351906] Fri, 03 October 2008 12:38 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
I have a procedure here that returns a cursor:


CREATE OR REPLACE PROCEDURE MATO.cogviews_MV_VarianceReport(p_valuation_date Date,
                                                       cur_out OUT Sys_RefCursor) IS
    v_stmt varchar2(32767);
BEGIN
  /*
      06202007 - COG - MV Variance Report
   */
  OPEN cur_out FOR    
            select  mato.*,
                    case 
                    when ( mato.eti is not null ) and ( crys.secdbid is not null ) then 'BOTH'
                    when ( mato.eti is not null ) and ( crys.secdbid is null ) then 'MATO_ONLY'
                    when ( mato.eti is null ) and ( crys.secdbid is not null ) then 'LDGR_ONLY'
                    end as flag          
            from (
                select  'GL' as GL
                       ,mp.GS_NAME as Product
                       ,mt.VALUATION_DATE as LAST_VD
                       ,to_char( mt.CONTRACT_MONTH, 'YYYY/MM/DD' ) as CM
                       ,mt.EXTERNALDEALID as ETI
                       ,mp.PF_COMMODITY as Commodity
                       ,mp.PRODUCT_TYPE as product_type
                       ,mt.CLR_ACCT as clr_acct
                       ,mt.QUANTITY as sor_quantity                      
                       ,round( mp.LOT_SIZE, 3) as lot_size
                       ,round( mt.LOTS, 3) as lots
                       ,round( mt.lots * mp.lot_size, 3) as quantity 
                       ,round( nvl( mt.ADJ_SPRICE, 0) , 3) as CER_Sprice
                       ,round( nvl( gsd.ADJ_SPRICE, 0) , 3) as GSD_Sprice
                       ,round( ( round( nvl( mt.ADJ_SPRICE, 0) , 3) - round( nvl( gsd.ADJ_SPRICE, 0) , 3) ) * mt.QUANTITY , 3) as "MV Diff"
                       ,round( mt.PRICE, 3 ) price
                       ,mt.SOURCE_EID as SOR
                from me_trade mt 
                    ,me_product mp
                    ,me_exchange me        
                    ,gsd_settle_data gsd
                where 1=1    
                  and mt.VALUATION_DATE = p_valuation_date
                  and mt.PF_CODE = mp.PF_CODE
                  and mt.PF_TYPE = mp.PF_TYPE
                  and mt.EXCHANGE = me.NAME
                  and mp.EXCH_ID = me.ID  
                  and mt.PF_TYPE = 'FUT' 
                  and (mt.MATO_STATUS is null or mt.MATO_STATUS = 'NEW') 
                  and gsd.GS_NAME = mp.GS_NAME
                  and gsd.COB_DT = mt.VALUATION_DATE
                  and gsd.PF_TYPE = mt.PF_TYPE
                  and gsd.CONTRACT_DATE = mt.CONTRACT_month
               union all
                    select  'GL' as GL
                                   ,mp.GS_NAME as Product
                                   ,mt.VALUATION_DATE as LAST_VD
                                   ,to_char( mt.CONTRACT_MONTH, 'YYYY/MM/DD' ) as CM
                                   ,mt.EXTERNALDEALID as ETI
                                   ,mp.PF_COMMODITY as Commodity
                                   ,mp.PRODUCT_TYPE as product_type
                                   ,mt.CLR_ACCT as clr_acct
                                   ,mt.QUANTITY as sor_quantity                      
                                   ,round( mp.LOT_SIZE, 3) as lot_size
                                   ,round( mt.LOTS, 3) as lots
                                   ,round( mt.lots * mp.lot_size, 3) as quantity 
                                   ,round( nvl( mt.ADJ_SPRICE, 0) , 3) as CER_Sprice
                                   ,round( nvl( gsd.ADJ_SPRICE, 0) , 3) as GSD_Sprice
                                   ,round( ( round( nvl( mt.ADJ_SPRICE, 0) , 3) - round( nvl( gsd.ADJ_SPRICE, 0) , 3) ) * mt.QUANTITY , 3) as "MV Diff"
                                   ,round( mt.PRICE, 3 ) price
                                   ,mt.SOURCE_EID as SOR         
                            from me_trade_roll_forward mtrf
                                ,me_trade mt
                                ,me_product mp
                                ,me_exchange me        
                                ,gsd_settle_data gsd
                            where 1=1
                              and mtrf.valuation_date = p_valuation_date
                              and mtrf.me_trade_id = mt.id    
                              and mt.VALUATION_DATE = mtrf.last_vd
                              and mt.PF_CODE = mp.PF_CODE
                              and mt.PF_TYPE = mp.PF_TYPE
                              and mt.EXCHANGE = me.NAME
                              and mp.EXCH_ID = me.ID  
                              and mt.PF_TYPE = 'FUT' 
                              and (mt.MATO_STATUS is null or mt.MATO_STATUS = 'NEW') 
                              and gsd.GS_NAME = mp.GS_NAME
                              and gsd.COB_DT = mt.VALUATION_DATE
                              and gsd.cob_dt = mtrf.last_vd
                              and gsd.PF_TYPE = mt.PF_TYPE
                              and gsd.CONTRACT_DATE = mt.CONTRACT_month 
              ) mato full outer join 
              crys_gl_info crys on crys.as_of_date = p_valuation_date                      
                                  and crys.secdbid = mato.eti;
      --USING p_valuation_date, p_valuation_date, p_valuation_date;    
END;
/


SQL> declare
  2      c sys_refcursor;
  3  begin
  4      cogviews_mv_variancereport('30-sep-08', c);
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00909: invalid number of arguments
ORA-06512: at "MATO.COGVIEWS_MV_VARIANCEREPORT", line 8
ORA-06512: at line 4



When I ran it I'm getting that invalid number of arguments error. What could be parameter I'm lacking?

Also when I convert the sql to NDS, then put the USING clause I get a ORA-600 "internal error code, arguments: [%s],[%s],[%s], [%s], [%s]. Please advised on any solutions. Thanks.
Re: Confusing Error [message #351909 is a reply to message #351906] Fri, 03 October 2008 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ '30-sep-08' is NOT a date it is a string.

2/ ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support

3/ Keep your lines in 80 characters

Regards
Michel

[Updated on: Fri, 03 October 2008 12:46]

Report message to a moderator

Re: Confusing Error [message #351910 is a reply to message #351909] Fri, 03 October 2008 12:49 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Yeah, I did to_date on the parameter but same error of invalid number of arguments. Sadly I don't have metalink. Is there other way to restructure my query that would have same result? When I don't include the outer query with the full outer join part, it works fine, but I need that part to "flag" it.

[Updated on: Fri, 03 October 2008 12:51]

Report message to a moderator

Re: Confusing Error [message #351918 is a reply to message #351910] Fri, 03 October 2008 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I did to_date on the parameter but same error of invalid number of arguments.

Post (copy and paste) what you did.
And don't forget that TO_DATE takes 2 parameters (at least).

Regards
Michel
Re: Confusing Error [message #351922 is a reply to message #351918] Fri, 03 October 2008 13:36 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi, here's what I tried on non-NDS code.


SQL> desc cogviews_mv_variancereport
PROCEDURE cogviews_mv_variancereport
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_VALUATION_DATE               DATE                    IN
 CUR_OUT                        REF CURSOR              OUT

SQL>

SQL> declare
  2      c sys_refcursor;
  3  begin
  4      cogviews_mv_variancereport(to_date('30-sep-2008', 'dd-mon-yyyy'), c);
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00909: invalid number of arguments
ORA-06512: at "MATO.COGVIEWS_MV_VARIANCEREPORT", line 8
ORA-06512: at line 4


[Updated on: Fri, 03 October 2008 13:38]

Report message to a moderator

Re: Confusing Error [message #351940 is a reply to message #351922] Fri, 03 October 2008 15:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-00909: invalid number of arguments
ORA-06512: at "MATO.COGVIEWS_MV_VARIANCEREPORT", line 8

Regards
Michel
Re: Confusing Error [message #351958 is a reply to message #351906] Fri, 03 October 2008 17:40 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I've had ORA-00600 errors before using FULL OUTER JOIN in a complex query.

Change the full to a LEFT. If it works, then it is an error with the FULL OUTER JOIN. If it doesn't work then you have some other error in your code.

If that turns out to be a problem, try converting all of your pre-9i joins into ANSI join syntax. I haven't tried it, but you could factor the complex part of the query (UNION) with a WITH clause - Oracle may then materialise the WITH clause in memory and the FULL OUTER JOIN becomes a join of two simple objects.

Ross Leishman
Re: Confusing Error [message #352064 is a reply to message #351958] Sun, 05 October 2008 14:23 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks for that. I've had some tests. Tried some of your suggestions. It seems it has some "bug" on the full outer join. Although I encountered a different type of error on this sample. Looks like it has a conflict with a "union / union all" and then the full outer join. Here are my test run, please advised if this really has something to do with my error above.


SQL> create or replace procedure p(pd emp.deptno%type, c out sys_refcursor) is
  2  begin
  3      open c for
  4          select e.*
  5          from (
  6          select *
  7          from emp
  8          where deptno = pd
  9          union all
 10          select *
 11          from emp
 12          where deptno = pd
 13          ) e full outer join dept d
 14              on e.deptno = d.deptno and d.deptno = pd;
 15  end;
 16  /

Procedure created.

SQL> declare
  2    x sys_refcursor;
  3  begin
  4      p(20, x);
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at "SCOTT.P", line 3
ORA-06512: at line 4

Using LOJ with a union all works
SQL> create or replace procedure p(pd emp.deptno%type, c out sys_refcursor) is
  2  begin
  3      open c for
  4          select e.*
  5          from (
  6          select *
  7          from emp
  8          where deptno = pd
  9          union all
 10          select *
 11          from emp
 12          where deptno = pd
 13          ) e left outer join dept d
 14              on e.deptno = d.deptno and d.deptno = pd;
 15  end;
 16  /

Procedure created.

SQL> declare
  2    x sys_refcursor;
  3  begin
  4      p(20, x);
  5  end;
  6  /

PL/SQL procedure successfully completed.

Using FOJ without union or union all

SQL> create or replace procedure p(pd emp.deptno%type, c out sys_refcursor) is
  2  begin
  3      open c for
  4          select e.*
  5          from (
  6          select *
  7          from emp
  8          where deptno = pd
  9          ) e full outer join dept d
 10              on e.deptno = d.deptno and d.deptno = pd;
 11  end;
 12  /

Procedure created.

SQL> declare
  2    x sys_refcursor;
  3  begin
  4      p(20, x);
  5  end;
  6  /

PL/SQL procedure successfully completed.

Somehow restructure the query with a WITH clause

SQL> create or replace procedure p(pd emp.deptno%type, c out sys_refcursor) is
  2  begin
  3      open c for
  4          with e as (
  5                  select *
  6                  from emp
  7                  where deptno = pd
  8                  union all
  9                  select *
 10                  from emp
 11                  where deptno = pd
 12                  ),
 13               d as (select * from dept where deptno = pd)
 14          select e.*
 15          from e full outer join d
 16           on e.deptno = d.deptno;
 17  end;
 18  /

Procedure created.

SQL> declare
  2    x sys_refcursor;
  3  begin
  4      p(20, x);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>

Seems to work without filters

SQL> create or replace procedure p(pd emp.deptno%type, c out sys_refcursor) is
  2  begin
  3      open c for
  4          select e.*
  5          from (
  6          select *
  7          from emp
  8          --where deptno = pd
  9          union all
 10          select *
 11          from emp
 12          --where deptno = pd
 13          ) e full outer join dept d
 14              on e.deptno = d.deptno and d.deptno = pd;
 15  end;
 16  /

Procedure created.

SQL> declare
  2    x sys_refcursor;
  3  begin
  4      p(20, x);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>

[Updated on: Sun, 05 October 2008 14:39]

Report message to a moderator

Re: Confusing Error [message #352092 is a reply to message #352064] Mon, 06 October 2008 01:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
No error in 11g:

SCOTT@orcl_11g> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE	11.1.0.6.0	Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SCOTT@orcl_11g> create or replace procedure p(pd emp.deptno%type, c out sys_refcursor) is
  2  begin
  3  	 open c for
  4  	     select e.*
  5  	     from (
  6  	     select *
  7  	     from emp
  8  	     where deptno = pd
  9  	     union all
 10  	     select *
 11  	     from emp
 12  	     where deptno = pd
 13  	     ) e full outer join dept d
 14  		 on e.deptno = d.deptno and d.deptno = pd;
 15  end;
 16  /

Procedure created.

SCOTT@orcl_11g> declare
  2    x sys_refcursor;
  3  begin
  4  	 p(20, x);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Re: Confusing Error [message #352169 is a reply to message #352092] Mon, 06 October 2008 07:59 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi Barbara, thanks also. It seems like it's a bug as noted here.
Re: Confusing Error [message #352174 is a reply to message #351958] Mon, 06 October 2008 08:35 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
I change the query to what rleishman suggested using with clause.


with mato as (
                      select  'GL' as GL
                           ,mp.GS_NAME as Product
                           ,mt.VALUATION_DATE as LAST_VD
                           ,to_char( mt.CONTRACT_MONTH, 'YYYY/MM/DD' ) as CM
                           ,mt.EXTERNALDEALID as ETI
                           ,mp.PF_COMMODITY as Commodity
                           ,mp.PRODUCT_TYPE as product_type
                           ,mt.CLR_ACCT as clr_acct
                           ,mt.QUANTITY as sor_quantity                      
                           ,round( mp.LOT_SIZE, 3) as lot_size
                           ,round( mt.LOTS, 3) as lots
                           ,round( mt.lots * mp.lot_size, 3) as quantity 
                           ,round( nvl( mt.ADJ_SPRICE, 0) , 3) as CER_Sprice
                           ,round( nvl( gsd.ADJ_SPRICE, 0) , 3) as GSD_Sprice
                           ,round( ( round( nvl( mt.ADJ_SPRICE, 0) , 3) - round( nvl( gsd.ADJ_SPRICE, 0) , 3) ) * mt.QUANTITY , 3) as "MV Diff"
                           ,round( mt.PRICE, 3 ) price
                           ,mt.SOURCE_EID as SOR
                    from me_trade mt 
                        ,me_product mp
                        ,me_exchange me        
                        ,gsd_settle_data gsd
                    where 1=1    
                      and mt.VALUATION_DATE = p_valuation_date
                      and mt.PF_CODE = mp.PF_CODE
                      and mt.PF_TYPE = mp.PF_TYPE
                      and mt.EXCHANGE = me.NAME
                      and mp.EXCH_ID = me.ID  
                      and mt.PF_TYPE = 'FUT' 
                      and (mt.MATO_STATUS is null or mt.MATO_STATUS = 'NEW') 
                      and gsd.GS_NAME = mp.GS_NAME
                      and gsd.COB_DT = mt.VALUATION_DATE
                      and gsd.PF_TYPE = mt.PF_TYPE
                      and gsd.CONTRACT_DATE = mt.CONTRACT_month
                   union all
                        select  'GL' as GL
                                       ,mp.GS_NAME as Product
                                       ,mt.VALUATION_DATE as LAST_VD
                                       ,to_char( mt.CONTRACT_MONTH, 'YYYY/MM/DD' ) as CM
                                       ,mt.EXTERNALDEALID as ETI
                                       ,mp.PF_COMMODITY as Commodity
                                       ,mp.PRODUCT_TYPE as product_type
                                       ,mt.CLR_ACCT as clr_acct
                                       ,mt.QUANTITY as sor_quantity                      
                                       ,round( mp.LOT_SIZE, 3) as lot_size
                                       ,round( mt.LOTS, 3) as lots
                                       ,round( mt.lots * mp.lot_size, 3) as quantity 
                                       ,round( nvl( mt.ADJ_SPRICE, 0) , 3) as CER_Sprice
                                       ,round( nvl( gsd.ADJ_SPRICE, 0) , 3) as GSD_Sprice
                                       ,round( ( round( nvl( mt.ADJ_SPRICE, 0) , 3) - round( nvl( gsd.ADJ_SPRICE, 0) , 3) ) * mt.QUANTITY , 3) as "MV Diff"
                                       ,round( mt.PRICE, 3 ) price
                                       ,mt.SOURCE_EID as SOR         
                                from me_trade_roll_forward mtrf
                                    ,me_trade mt
                                    ,me_product mp
                                    ,me_exchange me        
                                    ,gsd_settle_data gsd
                                where 1=1
                                  and mtrf.valuation_date = p_valuation_date
                                  and mtrf.me_trade_id = mt.id    
                                  and mt.VALUATION_DATE = mtrf.last_vd
                                  and mt.PF_CODE = mp.PF_CODE
                                  and mt.PF_TYPE = mp.PF_TYPE
                                  and mt.EXCHANGE = me.NAME
                                  and mp.EXCH_ID = me.ID  
                                  and mt.PF_TYPE = 'FUT' 
                                  and (mt.MATO_STATUS is null or mt.MATO_STATUS = 'NEW') 
                                  and gsd.GS_NAME = mp.GS_NAME
                                  and gsd.COB_DT = mt.VALUATION_DATE
                                  and gsd.cob_dt = mtrf.last_vd
                                  and gsd.PF_TYPE = mt.PF_TYPE
                                  and gsd.CONTRACT_DATE = mt.CONTRACT_month 
                  ),  
                  crys as (select as_of_date, secdbid from crys_gl_info) 
                select  mato.*,
                        case 
                        when ( mato.eti is not null ) and ( crys.secdbid is not null ) then 'BOTH'
                        when ( mato.eti is not null ) and ( crys.secdbid is null ) then 'MATO_ONLY'
                        when ( mato.eti is null ) and ( crys.secdbid is not null ) then 'LDGR_ONLY'
                        end as flag  
                from mato full outer join crys  
                  on crys.as_of_date = p_valuation_date                      
                     and crys.secdbid = mato.eti;



This one worked. And it ran faster. from 30 seconds to average of 8. What's the difference between the WITH and the previous?

Found this Does that mean that the inner query in my first query without the WITH clause gets executed more than once? And that when I used the WITH it got "buffered"? Thanks.

[Updated on: Mon, 06 October 2008 08:41]

Report message to a moderator

Previous Topic: Drop overflow table of IOT
Next Topic: problem storing japanese text
Goto Forum:
  


Current Time: Sat Dec 03 17:51:09 CST 2016

Total time taken to generate the page: 0.09488 seconds