0

I have access to an environment where I can run SELECT statements but cannot call functions. I know what all the code is inside the functions.

The functions are merely lookups (SELECTs and not INSERTs or UPDATEs)

Is there a way to simulate or declare the functions in a local context to get around this problem.?

  • Instead of wasting your time re-writing existing code wouldn't it be better to invest that time in getting EXECUTE privilege on the functions you need? – APC Aug 2 '13 at 10:31
  • It's not allowed, company policy, will not change – adolf garlic Aug 5 '13 at 7:57
  • 1
    Sorry but that's just insane. You don't happen to work for the same company as Dilbert by any chance? – APC Aug 5 '13 at 8:13
  • Has his company also only just ditched IE6 in the last year? – adolf garlic Aug 6 '13 at 7:14
1

If the functions are just doing SELECT statements then you can simulate them with an inline view ...

  select t.*
         , notaf.descr
  from  t
        , ( select code, descr from lookup_tbl ) notaf
  where t.col23 = notaf.code

... or a sub-query factoring clause ...

with notaf as ( select code, descr from lookup_tbl )
select t.*
         , notaf.descr
from  t
       , notaf
where t.col23 = notaf.code

--- or even a simple join (using ANSI syntax for variety!) ...

select t.*
         , lookup_tbl.descr
from  t
      join lookup_tbl
           on  t.col23 = lookup_tbl.code

You can use DUAL if the function does something computational (rather than a data lookup) but you may need to pfaff around with nested views to simulate passing parameters.

0

Yes you can declare functions in anonymous PL/SQL blocks but they're only usable within the same block and you can't use them in regular SQL. You can't use them in SQL executed within the anonymous PL/SQL block either. Only pre-declared PL/SQL functions can be used in regular SQL.

Here's an example of declaring an anonymous function:

DECLARE
   -- Declare an anonymous function:
   FUNCTION foo(x IN VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
      RETURN 'foo: ' || x;
   END;
BEGIN
   -- Invoke the anonymous function:
   DBMS_OUTPUT.PUT_LINE(foo('test'));

   -- "output_var" would be registered as an out param back the
   --  value of the function call:
   :output_var := foo('baz');
END;

If you're programmatically accessing the database (ex: using high level language like Java, Python, Ruby, etc) then you can use an anonymous block like the one above and register the output parameters to retrieve back the results. This will most likely be a pain (and probably less efficient) compared to direct SQL usage but it is possible to execute anything and return back any value.

If you're stuck to using pure SQL then I don't think what you want to do is possible.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.