DB Links as Parameters in a Function

Question

I'm creating a function that accepts two parameters. And one of my parameter will serve as the database link for my statement. I've tried concatenating it. How will I be able to achieve this?

It shows this error

ORA-00923: FROM keyword not found where expected ORA-06512 at "NOINK.CHECK_SECOND_REF_DIE", line 13.

Below is the code.

drop function check_second_ref_die;

create or replace function check_second_ref_die(lotNumber in VARCHAR2, db_link in VARCHAR2)
    return varchar2
is

    row_count NUMBER; 
    sql_statement VARCHAR2(300);


BEGIN 
    sql_statement := 'SELECT COUNT(*) FROM wcrepo.WCR_WAFER_REFERENCE@lepftds.itg.ti.com 
    WHERE waferconfigfile = (SELECT waferconfigfile FROM program_setup_rev@' || db_link ||
    'WHERE device = (SELECT device FROM noink.lot WHERE lot_num = ' ||lotNumber || ') 
    AND setup_cnt=0) AND status =' || 'Approved' || 'AND ref_die_type =' || 'Secondary';

    execute immediate sql_statement into row_count;

    IF (row_count != 0) THEN
        RETURN 'TRUE';
    ELSE
        RETURN'FALSE';
    END IF;

  END;

This is the code when I try to call the function

SELECT CASE
           WHEN check_second_ref_die ('8019572', 'rfabtwdb.dal.make.ti.com') = 'TRUE'
           THEN 'EXISTS' ELSE 'NOT EXISTS'
       END
           AS RESULT
  FROM DUAL

*"it doesn't work"* isn't very clear. Please say what happens, including any error messages you get. Remember, we haven't got your environments, we can't run your code. We only know what you tell us. — APC, Apr 04 '19 at 06:23

APC · Answer 1 · 2019-04-04T06:31:50.110

AND status =' || 'Approved' || 'AND

This is wrong. Remove the concatenation operators and we have ...

AND status =ApprovedAND

... which is not valid SQL. To reference string literals you need to escape single quotes. The simplest way is to use two of them:

AND status =''Approved'' AND

You'll need to fix all the string literals in your code.

Dynamic SQL is hard because it turns compilation errors into runtime errors. You can make it easier to debug your code by including some simple instrumentation. If your code had this line before the EXECUTE IMMEDIATE you could have seen the executed statement and probably spotted the bloomer for yourself.

dbms_output.put_line(v_sql);

DB Links as Parameters in a Function

1 Answers1