How To call functions from oracle database

[RameshReddy]

hello Friends,

  Kindly any one help me how to call user defined functions from oracle database i tried in all ways but i am not finding the way  ?

is it  possible we can call oracle functions with out keeping values in  table?

[LinusH]

Probably, these functions are primarily available to you in SQL pass-thru mode.

This means that your data you need to use the function on, has to be available in the Oracle session.

What are you intend to do? If your Oracle function does not have an SAS equivalent, maybe you can try to write a corresponding in SAS using PROC FCMP?

/Linus

[RameshReddy]

thanks for your aswer linush,

i am not clear from your answer..

i am again reconfirm my question .there is one function created in oracle when it execute it wil return  some value

my question is how will execute  this function and how we can get that value?

[LinusH]

I'll take that your data is in SAS, and you want the result in SAS.

Not knowing your situation in detail, I assume that you need to upload your data to Oracle (temporary or permanent table), use SQL pass-thru to apply your function to the data. The data could be returned to SAS in the same query using the construct

select * from connections to oracle(your qury goes here...);

A good starting point are these examples, before you dig in the documentation:

http://support.sas.com/kb/41/616.html

/Linus

[SASKiwi]

I am wondering if what you call an "Oracle function" is more commonly called an Oracle stored procedure. If this is the case then this may be helpful:

http://support.sas.com/kb/18/350.html

If you are talking about an Oracle user-built function, then it would be helpful to know how you call it in Oracle's PL/SQL, including the parameters required.

For example the COALESCE function is common to many flavours of SQL and you just call it in a SELECT statement: SELECT COALESCE(col1, col2) FROM table.......

[jonam · Accepted solution]

I  assume you are using oracle with sas.

1. In oracle if you want to call a function without a table.

you can do this in oralce by

" select  function() from dual;" 

This query is valid in oracle  and not in sas. hence you need to use sql - passthrough, i mean inside the select * from connection  to oracle ( your oracle query here).

Use dual "in oracle" when you need to query but not from any datasource/table.

Example :

select 5*5 from dual;