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

Star79Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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.
Star79Author Commented:
I checked the 2 left joins both of them have the same datatypes.
Please let me know anywhere else you are referring to
johnsoneSenior Oracle DBACommented:
What is the datatype of SC_VENDOR.CREATED?  If DATE, then you should use an explicit conversion like this:

V.created > TO_DATE('01-NOV-2014', 'DD-MON_YYYY')

Never trust that the client has the proper setting of NLS_DATE_FORMAT.

If that isn't it, then you are going to have to break the query down and find the line that is causing the issue.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
First:  If CREATED is an actual DATE data type, don't do " V.created > '01-NOV-2014'"

Explicitly convert the string to a date:
 V.created > to_date('01-NOV-2014','DD-MON'YYYY')

Get in the habit of explicit data type conversions and you'll have fewer issues down the road.


Now to the question:
I cannot reproduce the error you posted with the query you posted.

Here is my test case:
--drop table ad_user purge;
create table ad_user(ad_user_id number, name number);

insert into ad_user values(1,1);

--drop table c_order purge;
create table c_order(documentno number, em_sc_program_code number, dateprinted number, c_bpartner_id number, salesrep_id number, issoTRX char(1));

insert into  c_order values(1, 1 , 1 , 1 , 1 , 'N');

--drop table sc_vendor purge;
create table sc_vendor(remarks number, value number, name number, c_bpartner_id number, created varchar2(10));

insert into  sc_vendor values(1 , 1 , 1 , 1 , '999');
commit;

WITH DVTB AS
(
	SELECT 1 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
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.
awking00Information Technology SpecialistCommented:
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'
Star79Author Commented:
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) Commented:
>>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?
Star79Author Commented:
the tool is the same.I was using the wrong column sc_vendor.value and value is not present in the table.
slightwv (䄆 Netminder) Commented:
>>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.
awking00Information Technology SpecialistCommented:
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%';
Star79Author Commented:
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) Commented:
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;
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.