CODEWITHSUNDEEP
db2
sur
sur

Reputation: 1

How to call User-define function with out schema name in dynamic query in DB2 Procedure

Without schema name its not able to call user defined function.

CREATE PROCEDURE TEST_PROC (
    IN IN_EMP_ID INT
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
ISOLATION LEVEL CS
DISABLE DEBUG MODE
VALIDATE BIND
DYNAMICRULES BIND
QUALIFIER ABC
BEGIN
DECLARE STMT VARCHAR(500) ;
        DECLARE emp_curr CURSOR WITH RETURN FOR TRANSACTIONS;
        SET STMT = '';

        SET STMT = STMT ||'SELECT ' ||
                                      'EMP_ID, ' ||
                                      'ABC.TEST_FUNCTION(EMP_ID), ' ||
                                      'EMP_NAME, ' ||
                                      'SALARY, ' ||
                                      'COMPANY ' ||
                                    'FROM ' ||
                                      'EMP_DETAILS ' ||
                                    'WHERE 1=1 ';
            IF IN_EMP_ID IS NOT NULL THEN
                SET STMT = STMT || ' AND EMP_ID =' || IN_EMP_ID ;
            END IF ;

            PREPARE TRANSACTIONS FROM STMT ;
        OPEN emp_curr;
END;

when i am trying to execute this procedure its working but if i am remove alias name from my function as highlighted so its unable to call user define function. pls help me out thanks in advance.

Upvotes: 0

Views: 1199

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12339

Name resolution for program objects, such as functions, in dynamic SQL is controlled by the CURRENT PATH variable:

The CURRENT PATH (or CURRENT_PATH) special register specifies a VARCHAR(2048) value that identifies the SQL path used when resolving unqualified function names, procedure names, data type names, global variable names, and module object names in dynamically prepared SQL statements.

SET PATH effect on dynamic statement inside the routine example:

--#SET TERMINATOR @

CREATE OR REPLACE FUNCTION S1.TEST_SCALAR() RETURNS INT RETURN 1@

CREATE OR REPLACE FUNCTION S2.TEST_SCALAR() RETURNS INT RETURN 2@

CREATE OR REPLACE PROCEDURE TEST_DYN()
DYNAMIC RESULT SETS 1
BEGIN
  DECLARE C1 CURSOR WITH RETURN FOR S1;

  PREPARE S1 FROM 'SELECT TEST_SCALAR() AS RES FROM (VALUES 1) T(I)';
  OPEN C1;
END@

SET PATH = "S1", USER, SYSTEM PATH@
CALL TEST_DYN@

SET PATH = "S2", USER, SYSTEM PATH@
CALL TEST_DYN@

The result is:

SET PATH = "S1", USER, SYSTEM PATH
DB20000I  The SQL command completed successfully.

CALL TEST_DYN


  Result set 1
  --------------

  RES
  -----------
            1

  1 record(s) selected.

  Return Status = 0

SET PATH = "S2", USER, SYSTEM PATH
DB20000I  The SQL command completed successfully.

CALL TEST_DYN


  Result set 1
  --------------

  RES
  -----------
            2

  1 record(s) selected.

  Return Status = 0

Upvotes: 1

mao
mao

Reputation: 12287

You can use SET PATH statement to help resolve unqualified function names (and data type names), and this works on all platforms, with some variations in rules and syntax. So choose your Db2-server platform from one of the links below.

The idea is that you tell Db2 how to search for the unqualified function name. Notice that this is different from unqualified tables/views etc.

For Db2 for Z/OS see the documentation here.

For Db2 for i-series see this page.

For Db2 for Linux/Unix/Windows, see here for details of changing the special register CURRENT PATH.

Upvotes: 1

Related Questions