BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RameshReddy
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
jonam
Calcite | Level 5

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

5 REPLIES 5
LinusH
Tourmaline | Level 20

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
RameshReddy
Calcite | Level 5

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
Tourmaline | Level 20

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
SASKiwi
PROC Star

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
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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