How To call functions from oracle database wintin SAS

[Anju]

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;

[Patrick]

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. 

[Anju]

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.

[Patrick]

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;

[Anju]

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;

[Tom]

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)
;

[Anju]

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. 

[Anju]

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

[Anju]

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

[Tom]

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;

[Anju]

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

[Patrick]

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.

[Anju]

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. 

[Patrick]

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.