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?
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;
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
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?
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
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.......
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.