Dynamic SQL to select into a variable

Question

I am trying to create a function that will taken in a table name, take that table name and turn in into a column name as this is the design of the table. The function also take in a code, PK of the table and uses that to construct a Dynamic statement that would select the value into a variable return it.

I am getting errors like:

SP2-0552: Bind variable "LOC_CODE_TAB" not declared.

so my main question is when to use a derived value as a bind variable and when not to.

Here is the example I am trying:

DECLARE
    loc_stmt      VARCHAR2(200);
    loc_return    VARCHAR2(30) := null;
    loc_code_tab  VARCHAR2(30);
    P_TABLE_NAME VARCHAR2(100) := 'BILLG_FRQNCY_TYPE';
    P_CODE      NUMBER := 1;
BEGIN
    loc_code_tab := SUBSTR(P_TABLE_NAME,1,LENGTH(P_TABLE_NAME)-3);

    loc_stmt := 
        'SELECT ' || :loc_code_tab || '_DESC' ||
         ' INTO ' || loc_return ||  
         ' FROM ' || :loc_code_tab || 
        ' WHERE ' || P_TABLE_NAME || ' = ' || :P_CODE;

    EXECUTE IMMEDIATE loc_stmt
        INTO loc_return
        USING IN loc_code_tab, IN loc_code_tab, IN P_CODE;
    DBMS_OUTPUT.PUT_LINE(loc_return);
END;
/

XING · Accepted Answer · 2017-09-29 13:57:01Z

3

You cannot use placeholders for table columns in dynamic query. However you can use concatenate to achieve your requirement. See below:

DECLARE
   loc_stmt       VARCHAR2 (200);
   loc_return     VARCHAR2 (30) := NULL;
   loc_code_tab   VARCHAR2 (30);
   P_TABLE_NAME   VARCHAR2 (100) := 'BILLG_FRQNCY_TYPE';
   P_CODE         NUMBER := 1;
BEGIN
   loc_code_tab := SUBSTR (P_TABLE_NAME, 1, LENGTH (P_TABLE_NAME) - 3);

   loc_stmt :=
         'SELECT  '
      || loc_code_tab
      || '_DESC'
      || ' FROM '
      || loc_code_tab
      || ' WHERE '
      || P_TABLE_NAME
      || ' = '
      || P_CODE;

   EXECUTE IMMEDIATE loc_stmt  INTO loc_return ;

   DBMS_OUTPUT.PUT_LINE (loc_return);
END;
/

answered Sep 29, 2017 at 13:57

XING

9,9264 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kacper Over a year ago

OK your syntax looks better so I'll upvote you. I've taken the original one which looks a bit more messy

Wernfried Domscheit Over a year ago

Note for p_code you can (actually you should) use placeholder and bind variable.

Kacper · Accepted Answer · 2017-09-29 13:57:08Z

1

Remove : from name of varaibles and move into in proper place of execute immediate.

DECLARE
    loc_stmt      VARCHAR2(200);
    loc_return    VARCHAR2(30) := null;
    loc_code_tab  VARCHAR2(30);
    P_TABLE_NAME VARCHAR2(100) := 'BILLG_FRQNCY_TYPE';
    P_CODE      NUMBER := 1;
BEGIN
    loc_code_tab := SUBSTR(P_TABLE_NAME,1,LENGTH(P_TABLE_NAME)-3);

    loc_stmt := 
        'SELECT ' || loc_code_tab || '_DESC' ||
         ' FROM ' || loc_code_tab || 
        ' WHERE ' || P_TABLE_NAME || ' = ' || P_CODE;

    EXECUTE IMMEDIATE loc_stmt
        INTO loc_return;
    DBMS_OUTPUT.PUT_LINE(loc_return);
END;
/

answered Sep 29, 2017 at 13:57

Kacper

4,8182 gold badges23 silver badges35 bronze badges

4 Comments

XING Over a year ago

Pretty much similar to what i posted below.

Kacper Over a year ago

@XING yup. I was just a few seconds faster ;) The idea is the same

programmerNOOB Over a year ago

Thank you very much, that is it. Next problem to solve is how to going into an associative array with Dynamic. would this same logic work there?

Kacper Over a year ago

In that case you may need bulk. stackoverflow.com/questions/21117021/… or dba-oracle.com/plsql/t_plsql_dynamic.htm may help you

Collectives™ on Stack Overflow

Dynamic SQL to select into a variable

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related