Layer 7 API Management

Expand all | Collapse all

Why does LAC convert to uppercase the names of the parameters of a procedure in Oracle?

Jump to Best Answer
  • 1.  Why does LAC convert to uppercase the names of the parameters of a procedure in Oracle?

    Posted 07-31-2018 11:14 AM

    In the LAC, to make a call in a procedure in ORACLE, it is necessary to pass all the parameters in uppercase, because the LAC converts the parameters at the moment of the call. Is there any option to disable this conversion?



  • 2.  Re:  Why does LAC convert to uppercase the names of the parameters of a procedure in Oracle?

    Posted 10-25-2018 08:06 PM

    LAC is actually not converting the parameters to upper case. See this article: Schema Object Names and Qualifiers 

     

    "Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive." 

    Per your script example, this would have in fact created the stored procedure with the parameters as upper case. 



  • 3.  Re:  Why does LAC convert to uppercase the names of the parameters of a procedure in Oracle?
    Best Answer

    Posted 10-25-2018 08:28 PM

    Even though you are specifying the parameter names as mixed case when you create your procedure, Oracle does not remember that, and converts them to uppercase. There is no way for LAC to know that your parameter tipoDocumente had that name when you created it -- all it sees is TIPODOCUMENTE. The same goes for the procedure name, by the way. You can verify that by running:

     

    select * from SYS_ALL_PROCEDURES where OBJECT_NAME='SP_ECOVAL_EVALUADOR_LEAD'

    select * from SYS.ALL_ARGUMENTS where OBJECT_NAME='SP_ECOVAL_EVALUADOR_LEAD'

     

    You might argue that LAC should be case-insensitive in this case, since the database is, but most other databases are case-sensitive, so that would be somewhat inconsistent, plus there is the matter described below.

     

    You might ask -- how come I can pull up the code for my procedure, and see that the names of the procedure and parameters are preserved in all their mixed-case glory? That's because Oracle stores the raw source code for your procedure, you can see it in the SYS.ALL_SOURCE table:

     

    select * from SYS.ALL_SOURCE where NAME='SP_ECOVAL_EVALUADOR_LEAD'

     

    But that's not really what Oracle is actually running -- it converted all the names to uppercase.

     

    Now, if you *really* want mixed case, you can force Oracle (and therefore LAC) to respect your wishes by enclosing the names of the procedure and parameters in double quotes:

     

    CREATE OR REPLACE PROCEDURE EDYFICAR."MyProcedure"

    (

        "tipoDocumento" IN VARCHAR2,

        etc...

     

    but be advised that this is pretty much universally considered to be a bad practice, since you will then be forced to use double quotes any time you want to call this stored procedure. In other words, the effective name for this procedure would not be MyProcedure, it would be "MyProcedure". Pretty much all Oracle DBA guidelines I've ever seen discourage this practice, but it is an option.



  • 4.  Re:  Why does LAC convert to uppercase the names of the parameters of a procedure in Oracle?

    Posted 11-01-2018 04:11 AM

    Hi

    I believe your questions has been answered, I will mark this as the correct answer.

    When your question is not answered or you still have additional questions please let us know.

     

    With Kind Regards

    Conny