Oracle 11 PL SQL Use Execute Immediate to assign value to variable

Question

I am new to PL SQL and still learning, but I need to solve a problem and I don't know enough about PL SQL to solve my problem quickly.

I'm referencing two tables: users and attributes. I have a procedure that takes 3 arguments: attrib_id, uid, attrib_value.

I first queried the attributes table with the attrib_id to return an attribute name and assign it to a variable. My code works up to this point.

Next I want to use the variable created from the previous select statement in another select statement to query the users table and return the current value associated with the attribute that the variable represents.

CODE:

PROCEDURE value_update_proc_z(attrib_id INTEGER, uid IN VARCHAR2, attrib_value IN VARCHAR2)

IS
    v_old_attrib_name attributes.attribute_name%TYPE;
    v_oldattrib_value varchar2(100);
    v_mymsg varchar2(2000);

BEGIN
    EXECUTE IMMEDIATE 'SELECT attrib_name FROM attributes WHERE indx = ''' || attrib_id || '''' INTO v_old_attrib_name;

    EXECUTE IMMEDIATE 'SELECT' || v_old_attrib_name || 'FROM USERS WHERE USERID = ''' || uid || '''' INTO v_oldattrib_value;

    v_mymsg := v_old_attrib_name || ' ' || v_oldattrib_value;

END value_update_proc_z;

The first query should return a value from the attributes table based on a number passed into the procedure. For example, if attrib_id = 1, the query would return first_name, if attrib_id = 2, then last_name returned, and if attrib_id = 3, the would be returned email. The returned value would be assigned to the variable v_old_attrib_name.

Using the variable v_old_attrib_name in my select statement, I would expect that the second query would return a value, example; last_name would return williams, or email would return bob@somewhere.com. The result of this query would be assigned to the variable v_oldattrib_value.

Currently, the first Execute Immediate works and when I display the message I can see the value of that variable, but when I add the second Execute Immediate, I get a message that the operation could not be completed. This isn't an error generated by the system, it is a message set up by a previous developer.

I am open to suggestions for improvement.

Thanks!

Answer 1

I created Sample tables according to your requirement in my schema. I found 3 changes that you have to make in your code to work fine.

1.) v_old_attrib_name attributes.attribute_name%TYPE changed to v_old_attrib_name attributes.attrib_name%TYPE(According to the code in begin block).

I am saying the above change because in the begin block when you are writing the select query on attributes table your column in select clause is "attrib_name" not "attribute_name".

2.) When ever you are using dynamic SQL you can not replace the column or table_name with the variable directly as they are required by oracle to execute the query at run time. To avoid this you will have to add another variable in which you will form the dynamic query and then use it in execute immediate. This you will see in my code below.

3.) When you have written the dynamic SQL make sure that you give appropriate spaces so that nothing will get concatenated to each other. This also you will see in my code below.

Code below:

PROCEDURE value_update_proc_z(attrib_id INTEGER, uid IN VARCHAR2, attrib_value IN VARCHAR2)
is
v_old_attrib_name attributes.attrib_name%TYPE;
    v_oldattrib_value varchar2(100);
    v_mymsg varchar2(2000);
   v_sql varchar2(4000);
BEGIN
    EXECUTE IMMEDIATE 'SELECT attrib_name FROM attributes WHERE indx = ''' || attrib_id || '''' INTO v_old_attrib_name;

    v_sql:='SELECT ' || v_old_attrib_name || ' FROM USERS WHERE USERID = ''' || uid || '''' ;

    EXECUTE IMMEDIATE v_sql INTO v_oldattrib_value;

    v_mymsg := v_old_attrib_name || ' ' || v_oldattrib_value;

END value_update_proc_z;

Hope this will resolve your issue.

Thanks Ankit.

Answer 2

Dynamic SQL is hard because it turns compilation errors into runtime errors. It is alas not astonishing how many questions posted here using dynamic SQL are simple typos which would have been easy to spot if the statement were written as static SQL. That seems to be the case here.

The standard advice here is to write the SQL in a static form first, so you know the code works. Only then convert it it to template SQL for dynamic execution, paying careful attention to spaces, names, etc. Your second statement is missing spaces in the template SQL either side of the concatenated v_old_attrib_name variable.

Also, don't use dynamic SQL if static SQL works. For instance your first statement can be - and should be - static.

PROCEDURE value_update_proc_z(
    attrib_id INTEGER, 
    uid IN VARCHAR2, 
    attrib_value IN VARCHAR2)

IS
    v_old_attrib_name attributes.attribute_name%TYPE;
    v_oldattrib_value varchar2(100);
    v_mymsg varchar2(2000);

BEGIN
    SELECT attrib_name 
    INTO v_old_attrib_name
    FROM attributes 
    WHERE indx = attrib_id  ;

    EXECUTE IMMEDIATE 
        'SELECT ' || v_old_attrib_name || ' FROM USERS WHERE USERID = :1' 
        INTO v_oldattrib_value
        using uid;

    v_mymsg := v_old_attrib_name || ' ' || v_oldattrib_value;

   dbms_output.put_line(v_mymsg);

END value_update_proc_z;

---

Just noted this line in your question.

"This isn't an error generated by the system, it is a message set up by a previous developer."

Gosh but it seems like this previous developer was the Prince of Bad Code. Not only did they lumber you with a horrible EAV implementation but they are suppressing the error messages too. A generic message such as the operation could not be completed is no good to anybody, least of all us. You need to know the actual PL/SQL error message so you know why the program is failing, which is the key to fixing the problem.

Now a well-designed system would have some form of logging which displays and/or stores the real SQLERRM. It doesn't seem like you're working on a well-designed system but do you have any error logging in place?

Answer 3

Here please find below the working example. Let me know what you don't understand...

Issue 1: I you are fetching attrib_value but declared the variable with v_old_attrib_name attributes.attribute_name%TYPE; [Attribute_Name]. It should have been v_old_attrib_name attributes.attrib_name%TYPE;

Issue 2: As stated above in another answer, you were missing some spaces in the query.

Issue 3: You have no out_variable, IDK what was your plan for seeing the return value.

create table attributes (attrib_name varchar2(20), indx number(2));

insert into attributes values('LAST_NAME',1)

create table USERS (userid number(2), last_Name varchar2(100))

insert into users values (12,'Williams')

Procedure:

create or replace PROCEDURE value_update_proc_z(attrib_id INTEGER, uid IN VARCHAR2, attrib_value OUT VARCHAR2)
IS
    v_old_attrib_name attributes.attrib_name%TYPE;
    v_oldattrib_value varchar2(100);

BEGIN
    EXECUTE IMMEDIATE 'SELECT attrib_name FROM attributes WHERE indx = ''' || attrib_id || '''' INTO v_old_attrib_name;

    EXECUTE IMMEDIATE 'SELECT ' || v_old_attrib_name || ' FROM USERS WHERE USERID = ''' || uid || '''' INTO v_oldattrib_value;

    attrib_value := v_old_attrib_name || ' ' || v_oldattrib_value;

END value_update_proc_z;

Execution: Note there is a out variable which we are printing out

DECLARE 
  ATTRIB_ID NUMBER;
  "UID" VARCHAR2(32767);
  ATTRIB_VALUE VARCHAR2(32767);

BEGIN 
  ATTRIB_ID := 1;
  "UID" := 12;
  ATTRIB_VALUE := NULL;

  VALUE_UPDATE_PROC_Z ( ATTRIB_ID, "UID", ATTRIB_VALUE );

  DBMS_OUTPUT.Put_Line('ATTRIB_VALUE = ' || ATTRIB_VALUE);

  DBMS_OUTPUT.Put_Line('');

  COMMIT; 
END; 

Answer 4

In the second statement

EXECUTE IMMEDIATE 'SELECT' || v_old_attrib_name || 'FROM USERS WHERE USERID...

there should exist at least one space before and after variable v_old_attrib_name like this :

EXECUTE IMMEDIATE 'SELECT ' || v_old_attrib_name || ' FROM USERS WHERE USERID...