ORA-00909: invalid number of arguments: Error When trying to CONCAT columns together with a comma
Hi, I'm getting an error when running the above code in Oracle Apex that looks like this: ORA-00909: invalid number of arguments However, when I run the code like this for example WITHOUT THE COMMA: SELECT CONCAT(STDFNAME, STDLNAME) AS TEST_CONCAT FROM STUDENTS; The code runs fine, however, my new column looks very ugly without the comma or space separating the names. Why is it without the comma there is no error, but when the comma is there like in the lesson above, the ORA-00909: invalid number of arguments error occurs? I also posted this same question in the approprate SQL lessons on CONCAT function as well.
2/8/2020 12:51:41 AMSTANDARD DEVIATION FUNLAND
6 AnswersNew Answer
(Not an SQL expert) But I think the arguments could only be 2 and not 3.
Ipang Yes that clarifies it a lot. Thanks for the help. Thus it would be great if the lesson incorporates all of the possible syntax. It reallt sucks thag there’s differences in syntax between vendors
STANDARD DEVIATION X2 - Sno Erika TBH, I am also a bit bothered by the differences, but what can I say, the DBMS vendors are giant companies, and I was just another product user : ) I don't know if incorporation of all the syntax differences was possible, there are loads of them. Funnily, some issues also differ by product version. I can imagine how time consuming it would be trying to compile all the syntax differences or incompatibilities and list them in the lesson; only to find that the next release of a certain product renders the lesson materials invalid, yet again XD
It only happens when I do something like this: SELECT concat(STDFNAME,',' , STDLNAME) FROM STUDENTS; I'm not sure why me inserting the comma is resulting in an "invalid number of arguments"
@KnuckleBars You're right! I tried CONCAT 3 columns just now and I was getting that same error. However, I found from https://www.techonthenet.com/oracle/functions/concat.php that to CONCAT MORE THAN 2 ARGUMENTS, including commas, we need to do the following code: SELECT CONCAT(CONCAT(Column1, ', '),Column2) AS TEST_CONCAT FROM Table_name; This means the information in the Custom Columns SQL lesson on CONCAT is outdated and inaccurate. They have to update the information to the code sample I have here in this question. At least for Oracle Apex, the code currently listed in the Custom Columns lesson doesn't work.
IMHO the problem is not with the lesson, the problem is, each major DBMS vendor has their own ideas about what is best for their product users. And they all believe their design and/or policy is the best there is. Due to the differences, it is quite common for one SQL learner to encounter an issue with syntax difference incompatibility amongst major DBMS vendors, like what we have here today, with the CONCAT function. MySQL and MSSQL agreed to use similar syntax for CONCAT function, the function accepts multiple arguments and will produce a new string by concatenating each arguments. But Oracle's choose to agree that CONCAT function should ONLY accept two arguments, which explains the error message you got. The lesson, as it seems to me, was built on the assumption that learners will be using MySQL (or something compatible). Thus, we can't really say the examples are outdated or inaccurate. This is more of a problem in relation to syntax differences amongst DBMS products : )