2 Replies Latest reply on May 7, 2013 1:08 PM by Torben Lorentzen

    DatabaseProcedure with return type prefixed with schema name

    Torben Lorentzen
      Hi (Paco)

      I have a question about the DatabaseProcedure class. We are using Oracle proxy users for our database connections.
      Everything is accessed via a database role that are granted to the logged on user. All our database objects, tables etc are protected with this database role.
      When I want to call a database function/procedure I need to add the schema name as a prefix to the custom database object that we uses for parameters/return types.

      So far so good. I can also define a parameter prefixed with schema name via the DatabaseProcedure.registerArrayType ...
      But when I try to define a function call that uses this parameter I get an error saying "Declaration is not valid".
      The problem is located to the PROCEDURE_DEFINITION regular pattern:
      private static final Pattern PROCEDURE_DEFINITION = Pattern.compile("\\s* (FUNCTION|PROCEDURE) \\s+ ([\\w.$]+) \\s* (?:\\((.*?)\\))? \\s* (?:RETURN\\s+(\\w+))? \\s* ;? \\s*", CASE_INSENSITIVE | COMMENTS | DOTALL);
      The return type cannot be prefixed with the schema name.

      Any good suggestions or workarounds?!

      I actually did change the pattern runtime via reflection to make it work - but I really don't like this solution in the long run!


      Edited by: Zonic on 2013-05-07 10:52
        • 1. Re: DatabaseProcedure with return type prefixed with schema name
          Paco van der Linden
          Hi Torben,

          I think I have a workaround for the issue that might work for you. If you look at the source of <font face="courier">DatabaseProcedure.registerArrayType</font> you find that it actually calls <font face="courier">DatabaseProcedure.registerCustomParamType</font>.
          public static void registerArrayType(String name)
            registerCustomParamType(name, Types.ARRAY, Array.getORADataFactory(), name);
          As a workaround you could replace your calls to <font face="courier">DatabaseProcedure.registerArrayType</font> with calls to <font face="courier">DatabaseProcedure.registerCustomParamType</font> as follows.
          // Instead of DatabaseProcedure.registerArrayType("NAME.WITH.DOTS") call:
          DatabaseProcedure.registerCustomParamType("anyNameWithoutDots", Types.ARRAY, Array.getORADataFactory(), "NAME.WITH.DOTS"); // Don't forget to use uppercase here.
          DatabaseProcedure dp = DatabaseProcedure.define("procedure my.procedure(param1 in out anyNameWithoutDots)");
          DatabaseProcedure.ParamType type = dp.getParamDef(0).getType();
          System.out.println(type.getName() + " is " + type.getTypeName()); // ANYNAMEWITHOUTDOTS is NAME.WITH.DOTS
          This way you don't have to use the "illegal" name in the DatabaseProcedure definition.

          Paco van der Linden
          • 2. Re: DatabaseProcedure with return type prefixed with schema name
            Torben Lorentzen
            Hi Paco!

            Thanks for the quick - and very helpful answer. Works perfectly!

            I was fooled by the fact the all the types in the definition are only pointers to the real type :)
            But the time wasn't completely wasted - I got a brush up in reflection and regular expressions :)

            Thanks again!