topic Re: How To call functions from oracle database wintin SAS in New SAS User

How To call functions from oracle database wintin SAS

Anju — Tue, 10 Mar 2020 00:17:47 GMT

I'm trying to call a function called schema.function_name using pass thru similar to calling a Stored procedure which I've done before. But this isn't working. What is the right syntax to call functions within sas ?

proc sql;
        CONNECT TO oracle (user=xxx
         password=xxx
                 path=xxx);
         exec
        (EXECUTE schema.table ('schema.function_name')

) by oracle;

DISCONNECT FROM oracle;
quit;

Re: How To call functions from oracle database wintin SAS

Patrick — Tue, 10 Mar 2020 01:16:05 GMT

Isn't a function normally returning a value and though you'd use it in a Select statement?

I'd first get the Oracle SQL working using an editor like SQL Developer, DBeaver ..... and only when working wrap SAS code around.

Re: How To call functions from oracle database wintin SAS

Anju — Tue, 10 Mar 2020 01:18:57 GMT

Hello Patrick,

Yes it works fine in SQL developer and it will return a value of 'Y' or 'N'. Are you saying I can use in a select statement. Can you help me with the syntax pls ? Thanks.

Re: How To call functions from oracle database wintin SAS

Patrick — Tue, 10 Mar 2020 01:31:25 GMT

Can you post your working Oracle SQL so I better understand what you have.

Just on a "conceptual" level something along the line of below:

proc sql;
  connect to oracle (...);
  select * from connection to Oracle 
    (
      select schema.function_name(<parameter>) as result
      from dual
    );
  disconnect from oracle;
quit;

Re: How To call functions from oracle database wintin SAS

Tom — Tue, 10 Mar 2020 01:31:40 GMT

If it returns something try running it via SELECT instead of EXECUTE.

proc sql;
CONNECT TO oracle ...;
select * from connection to oracle
(EXECUTE schema.table ('schema.function_name')
);
quit;

Sometime you have to wrap procedure calls in Oracle into a block.

proc sql;
CONNECT TO oracle ...;
execute by oracle
(begin EXECUTE schema.table ('schema.function_name') end
);
quit;

Re: How To call functions from oracle database wintin SAS

Anju — Tue, 10 Mar 2020 11:51:53 GMT

CREATE OR REPLACE FUNCTION xxx(p_limit NUMBER DEFAULT 1000000,p_min_pop_id NUMBER DEFAULT NULL,p_max_pop_id NUMBER DEFAULT NULL) RETURN CHAR AS
l_min_pop_id NUMBER;
l_max_pop_id NUMBER;
l_cnter NUMBER := 0;
l_next_pop_id NUMBER;

BEGIN
IF p_min_pop_id IS NOT NULL AND p_max_pop_id IS NOT NULL THEN
l_min_pop_id := p_min_pop_id ;
l_max_pop_id := p_max_pop_id ;
ELSE
SELECT min(mdm_pop_id) min_pop_id,max(mdm_pop_id) max_pop_id
INTO l_min_pop_id ,l_max_pop_id
FROM mdm.mdm_pop_tt
WHERE mdm_valid_to_dttm is not null;
END IF;

l_next_pop_id := l_min_pop_id;
WHILE l_next_pop_id <= l_max_pop_id LOOP
l_cnter := l_cnter + 1;

INSERT INTO mdm.mdm_pop_tt_history
SELECT tt.*,SYSDATE FROM mdm.mdm_pop_tt tt
WHERE tt.mdm_pop_id BETWEEN l_next_pop_id AND (l_next_pop_id + p_limit) AND tt.mdm_valid_to_dttm IS NOT NULL;

DELETE FROM mdm.mdm_pop_tt tt
WHERE tt.mdm_pop_id BETWEEN l_next_pop_id AND (l_next_pop_id + p_limit) AND tt.mdm_valid_to_dttm IS NOT NULL;
--dbms_output.put_line(l_cnter || '- Iteration :-' || l_next_pop_id || '-' || (l_next_pop_id + p_limit));

COMMIT;

l_next_pop_id:= l_next_pop_id + p_limit + 1;

END LOOP;
DBMS_STATS.gather_table_stats(ownname => 'MDM',tabname => 'MDM_pop_TT',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'for all indexed columns', cascade => true);
DBMS_STATS.gather_table_stats(ownname => 'MDM',tabname => 'MDM_pop_TT_HISTORY',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'for all indexed columns', cascade => true);
RETURN 'Y';
EXCEPTION
WHEN OTHERS THEN
--dbms_output.put_line(l_cnter || 'ERROR:-' || SQLCODE || '- ' || SQLERRM);
RETURN 'N';
END;

Re: How To call functions from oracle database wintin SAS

Anju — Tue, 10 Mar 2020 11:53:06 GMT

Here is the code which perfectly works in sql developer and I still can't get to call this function into sas

Re: How To call functions from oracle database wintin SAS

Tom — Tue, 10 Mar 2020 12:18:06 GMT

Hard to read that since you pasted it as if it was your comments. Make sure to use the INSERT CODE button in the forum editor when inserting code or data.

It does not look to me that you showed how you are CALLING that function. Just the definition.

Since it returns a character value I would assume you need to call it with something like:

select xxx(...) as results from dual;

In which case to do that from SAS just wrap the Oracle select inside a SAS select.

select * from connection to oracle
(select xxx(...) as results from dual)
;

Re: How To call functions from oracle database wintin SAS

Anju — Tue, 10 Mar 2020 12:24:34 GMT

This is not a stored proc but a function; so do we need a from ??? I'm confused. I pasted the full code from functions tab in Oracle.

Re: How To call functions from oracle database wintin SAS

Anju — Tue, 10 Mar 2020 13:02:24 GMT

This is my recent try:

proc sql;
CONNECT TO oracle (user=xxx
password=xxx
path=xxx);
create table test as
select * from connection to oracle
(execute schema.xxx)

;

DISCONNECT FROM oracle;
quit;

ERROR: ORACLE prepare error: ORA-24333: zero iteration count. SQL statement: execute schema.xxx

Re: How To call functions from oracle database wintin SAS

Anju — Tue, 10 Mar 2020 13:54:26 GMT

This query worked for me:

proc sql;
CONNECT TO oracle ...;
create table job204 as
select * from connection to oracle
(select xxx from dual);

DISCONNECT FROM ORACLE;
quit;

where xxx is the name of the function which was defined in oracle

Re: How To call functions from oracle database wintin SAS

Patrick — Wed, 11 Mar 2020 08:04:19 GMT

Does that mean you've got your solution and this question is answered? If so then please mark the contribution which helped you most as solution.

Re: How To call functions from oracle database wintin SAS

Anju — Wed, 11 Mar 2020 13:00:40 GMT

Not fully. The code I pasted is able to read the functions and it comes with an output 'N'. It is not executing the code.

Re: How To call functions from oracle database wintin SAS

dobby — Sun, 20 Jun 2021 11:53:59 GMT

Re: How To call functions from oracle database wintin SAS

Patrick — Sun, 20 Jun 2021 11:42:25 GMT

Please don't piggy-back on other peoples questions but ask a new question. Eventually add a link to the old question if you want to reference it.

Re: How To call functions from oracle database wintin SAS

dobby — Sun, 20 Jun 2021 11:54:23 GMT

Noted. Thank you.