topic How To call functions from oracle database in SAS Enterprise Guide

How To call functions from oracle database

RameshReddy — Wed, 07 Sep 2011 05:27:39 GMT

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?

How To call functions from oracle database

LinusH — Wed, 07 Sep 2011 08:21:16 GMT

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

How To call functions from oracle database

RameshReddy — Wed, 07 Sep 2011 09:13:36 GMT

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?

How To call functions from oracle database

LinusH — Thu, 08 Sep 2011 07:31:27 GMT

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

How To call functions from oracle database

SASKiwi — Thu, 08 Sep 2011 23:55:06 GMT

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.......

How To call functions from oracle database

jonam — Fri, 09 Sep 2011 19:25:55 GMT

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;