asked on

Oracle Query error-wrong number or types of arguments in call

Hello,
I am trying to run the below query and Iam getting the error:ORA-06553: PLS-306: wrong number or types of arguments in call to 'V'
Not sure what is it that Iam doing wrong

WITH DVTB AS
(
	SELECT 'AAP052' as VEND from dual
)


SELECT x.*
FROM (
SELECT V.CREATED as "CREATEDDATE",V.REMARKS as "COMMENTS",        
              V.value as "VENDORCODE",     
             V.NAME AS "VENDOR_NAME",
            CO.DOCUMENTNO AS PO_NUM,CO.EM_SC_PROGRAM_CODE AS "Program",
           CO.DATEPRINTED AS "PO_CREATEDATE",
           u.name  AS "BUYER",
			     row_number() over(partition by V.value order by CO.DOCUMENTNO) as rn
  FROM sc_vendor V 
LEFT join c_order CO  on  co.c_bpartner_id = V.C_BPARTNER_ID
left join ad_user u on co.SALESREP_ID = u.AD_USER_ID
WHERE
 CO.ISSOTRX='N' AND  V.created > '01-NOV-2014'
 and V.value in (SELECT VEND FROM DVTB))  x
where x.rn = 1
order by x.vendorcode

Open in new window

johnsone

The only thing I can think of is a difference in datatypes on the join columns. Make sure that the data types match. If one is number and one is character the optimizer may be trying to convert the character to number and forcing that error.

Star79

ASKER

I checked the 2 left joins both of them have the same datatypes.
Please let me know anywhere else you are referring to

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

Check your query for a COMMA where it should be a PERIOD.

If I take my code above and change:
SELECT V.CREATED as "CREATEDDATE",V.REMARKS as "COMMENTS",

to:
SELECT V,CREATED as "CREATEDDATE",V.REMARKS as "COMMENTS",

Note the comma between V and CREATED.

I get the error because Oracle sees V as a function.

Some '.' is a ',' somewhere it shouldn't be.

Who knew Oracle has a built-in function named 'V'? It comes with APEX.

awking00

Try changing you alias for sc_vendor to sc and see what happens. Also, why do you need the common table expression? Why not just change this -
and V.value in (SELECT VEND FROM DVTB)
to this -
and V.value = 'AAP052'

Star79

ASKER

I changed the query for the alias name:

WITH DVTB AS
(
	SELECT 'AAP052' as VEND from dual
)

SELECT x.*
FROM (
SELECT 
sc.CREATED as "CREATEDDATE",
sc.REMARKS as "COMMENTS",        
              sc.VENDOR_CODE as "VENDORCODE",     
sc.NAME AS "VENDOR_NAME",
CO.DOCUMENTNO AS PO_NUM,CO.EM_SC_PROGRAM_CODE AS "Program",
 CO.DATEPRINTED AS "PO_CREATEDATE",
 u.name  AS "BUYER",
			     row_number() over(partition by sc.VENDOR_CODE order by CO.DOCUMENTNO) as rn
  FROM sc_vendor sc 
LEFT join c_order CO  on  co.c_bpartner_id = sc.C_BPARTNER_ID
left join ad_user u on co.SALESREP_ID = u.AD_USER_ID
WHERE
 CO.ISSOTRX='N' AND  sc.created > to_date('01-NOV-2014','DD-MON-YYYY')
 and sc.vendor_code in (SELECT VEND FROM DVTB))  x
where x.rn = 1

order by x.vendorcode

Open in new window

I just changed the alias name to sc, the query works, not sure what makes it to work

slightwv (䄆 Netminder)

>>not sure what makes it to work

Maybe the tool you were using has a bug in the syntax parser? Did it generate the same error if you executed it from sqlplus?

Star79

ASKER

the tool is the same.I was using the wrong column sc_vendor.value and value is not present in the table.

slightwv (䄆 Netminder)

>>the tool is the same

I know that. I was asking if you get the "wrong number or types of arguments" error with the original alias, V, when you ran it in sqlplus?

>>I was using the wrong column sc_vendor.value and value is not present in the table.

That shouldn't cause the "wrong number or types of arguments" error you originally posted.

awking00

Interesting that the query worked after changing the alias from v to sc, which is what I suggested. I still believe that's because of apex. What do you get from the following query?
select max(username) from all_users where username like 'APEX%';

Star79

ASKER

I get APEX_PUBLIC_USER
and the problem with the query was I was using the wrong column sc_vendor.value and value is not present in the table.
But the error message that sql developer showed was unrelated

slightwv (䄆 Netminder)

This is one of those really cool questions that make you think...

>> the problem with the query was I was using the wrong column sc_vendor.value and value is not present in the table.

It was sort of a combination of the chosen alias and missing column.

Functions can have methods. It appears the "V" function in APEX has a "value" method.

When Oracle parsed the SQL and saw there was no value column in the table, it took the "V" alias as the APEX function.

You can generate the missing parameter error with:
select v.value from dual;