Desktop productivity for business analysts and programmers

How To call functions from oracle database

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

How To call functions from oracle database

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?


Accepted Solutions
Solution
‎09-09-2011 03:25 PM
Contributor
Posts: 29

How To call functions from oracle database

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;

View solution in original post


All Replies
Super User
Posts: 5,386

How To call functions from oracle database

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

Data never sleeps
Contributor
Posts: 31

How To call functions from oracle database

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?

Super User
Posts: 5,386

How To call functions from oracle database

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

Data never sleeps
Super User
Posts: 3,235

How To call functions from oracle database

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

Solution
‎09-09-2011 03:25 PM
Contributor
Posts: 29

How To call functions from oracle database

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 2904 views
  • 0 likes
  • 4 in conversation