CODEWITHSUNDEEP
sqloracle-databaseplsqldynamic-sql
programmerNOOB
programmerNOOB

Reputation: 123

Dynamic SQL to select into a variable

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

Upvotes: 0

Views: 6679

Answers (2)

Kacper
Kacper

Reputation: 4818

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

Upvotes: 1

XING
XING

Reputation: 9886

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

Upvotes: 3

Related Questions