DATA Step, Macro, Functions and more

Convert PLSql components into a SAS program

Reply
Contributor
Posts: 36

Convert PLSql components into a SAS program

Hi,
I have two quires in Oracle. 1st one is a main query. It contains joins on multiple tables and in this one of the table column value is passing into the Oracle function and it returns value to the main query.

Please suggest me how can write passing value into a Oracle function and returning values into a main query in SAS. Thanks InAdvance
Super User
Posts: 5,426

Re: Convert PLSql components into a SAS program

Please shaee an example on how you this today, as simplified as possible.
Also, would you like to populate the paramter?
Data never sleeps
Contributor
Posts: 36

Re: Convert PLSql components into a SAS program

I have join as below.

QUERY:

select a.country_name,

          b.state_name,

          c.city_name,

          d.product_type,

          call_func(d.product_id) as product_name 

from country a,state b,city c,product_info d

where a.country_id=b.country_id and

           b.state_id=c.state_id and

           c.product_type_id=d.product_type_id;

 

In the above quey tables are taken from different users.

 

FUNCTION:

CREATE or REPLACE FUNCTION call_func(product_id IN NUMBER) 
   RETURN VARCHAR2 
   IS acc_bal NUMBER(11,2);
   BEGIN 
      SELECT a.product_name as produc_name 
      INTO product_name 
      FROM products a,categ b,price c
      WHERE a.product_id = product_id
and a.categ_id=b.categ_id
and b.price_id=c.price_id; RETURN(product_name); END;

Please ignore in case of any syntax errors but query pupose is same.

Please let me know how can I do in SAS.

As of now, we copied required database tables in SAS and then doing SAS joins in proc sql.

So please suggest me in which ways how can i achieve above one.

 

Thanks In Advance. 

 

 

Super User
Posts: 3,252

Re: Convert PLSql components into a SAS program

This post has a clear explanation of how to run unmodified Oracle SQL in SAS:

 

https://communities.sas.com/t5/SAS-Enterprise-Guide/Urgent-help-in-converstion-of-oracle-to-AS/m-p/3...

 

Super User
Posts: 19,772

Re: Convert PLSql components into a SAS program

Search SQL Pass Thru

Super User
Posts: 5,426

Re: Convert PLSql components into a SAS program

As @Reeza says, you need to use explicit SQL pass thru from SAS. ASFAIK SAS implicit pass thru cannot call UDF's.
If you wish to SAS SQL skip calling the function. To me it looks like it could be solved by using the products table in a join instead. Probably my ignorance of PLSQL, but where does b.categ_id and price_id come from?
Data never sleeps
Contributor
Posts: 36

Re: Convert PLSql components into a SAS program

Hi Linush,

 

if I have only one PL/SQL procedure or function then I can call as mentioned below.

 

proc sql;

connect to oracle (user="oracleUserName" password="pass" path="serverPath" CONNECTION=GLOBAL CONNECTION_GROUP = SASAML);
       execute( execute MyPackage.MyProcedure(&param1, &param2) ) by oracle;
disconnect from oracle;

quit;

 

If you see my query, Scenario is different.

For example, I have 10 variables in select clause and one of the variable value is passing to Oracle function and then doing

some business logic overe there and function returns back value into main query and rest of 9 columns are populated directly from other 9 tables by joining the tables.

 

Ex: select a,b,c,call_func(d) e,f,g from s1,s2......etc..

 

Please suggest me how can i achieve this in sas. 

Super User
Super User
Posts: 7,039

Re: Convert PLSql components into a SAS program

Don't use EXECUTE just use SELECT ... FROM CONNECTION TO ... 

select * from connection to oracle
(/* Place ORACLE code inside the () */
select ......
);
Contributor
Posts: 36

Re: Convert PLSql components into a SAS program

Hi Linush, thanks for your reply. If input tables exist in different databases and those tables are involved in joining condition.inthese case how can I use explicit SQL pass through.Thanks.
Super User
Posts: 19,772

Re: Convert PLSql components into a SAS program

The same way you did before. It happens on the server, so if the server could run the code before it still can now. 

 

If your data structure has changed that's a different story. 

Contributor
Posts: 36

Re: Convert PLSql components into a SAS program

Thanks Reza, Just want to confirm you one thing, Can I include Oracle function query in place of call_func(d.product_id) as product_name. I hope in Oracle we can perform select clause in place of column name in Select statement. Please advice.
Super User
Posts: 19,772

Re: Convert PLSql components into a SAS program

If it works on the server it will work in pass through. Either way, test it out, very easy to test. 

Respected Advisor
Posts: 4,173

Re: Convert PLSql components into a SAS program

@Banu

With pass-through SQL you can run anything that works directly in Oracle (i.e. using SQL Developer) out of SAS.

 

SAS sends the code in the pass-through block "as-is" to Oracle for exection. So if your code works out of SQL Developer then it will work out of a pass-through block in SAS.

 

If you want SAS to execute something in Oracle without returning a result then wrap the Oracle code into an EXECUTE() block, else if you want a result set back then use SELECT * from connection to <oracle> ( <your Oracle SQL> );

Super User
Posts: 5,426

Re: Convert PLSql components into a SAS program

You got the answer from @Tom.
The from connection to construct is also considered as explicit SQL pass through, and is used when you want to return something to the SAS session.
Data never sleeps
Ask a Question
Discussion stats
  • 13 replies
  • 257 views
  • 0 likes
  • 6 in conversation