Re: dbms_sql.variable_value

From: Scott Urman <surman_at_oracle.com>
Date: 1996/06/25
Message-ID: <4qnasb$a68_at_inet-nntp-gw-1.us.oracle.com>#1/1

In article <31CECAD5.79D5_at_umich.edu>, Lixin Yan <yanl_at_umich.edu> writes:

|> Hi:
|>  Can someone give me a small example on how
|> to use dbms_sql.variable_value procedure?
|> I always get the execution error 'bind variable
|> doesn't exist'. My Email is jili_at_umich.edu
|>  Thanks a lot
|> -- 
|> Ji Li

VARIABLE_VALUE is used to get the value of an OUT bind variable. A bind variable is delimited by a colon in the SQL statement or PL/SQL block being executed. Actually, they are pretty much useful only in a PL/SQL block, such as a call to a stored procedure with an OUT parameter. The following example, from Chapter 10 of my book _Oracle PL/SQL Programming_ (see below) illustrates using VARIABLE_VALUE:

CREATE OR REPLACE PROCEDURE DynamicPLSQL (   /* Executes a PL/SQL block dynamicly. The block

     selects from students, and uses p_StudentID as an
     input placeholder. */

  p_StudentID IN students.ID%TYPE) IS

  v_CursorID  INTEGER;
  v_BlockStr  VARCHAR2(500);
  v_FirstName students.first_name%TYPE;
  v_LastName  students.last_name%TYPE;
  v_Dummy     INTEGER;

BEGIN

• Open the cursor for processing. v_CursorID := DBMS_SQL.OPEN_CURSOR;
• Create the string containing the PL/SQL block.
• In this string, the :first_name and :last_name
• placeholders are output variables, and :ID is an
• input variable. v_BlockStr := 'BEGIN SELECT first_name, last_name INTO :first_name, :last_name FROM students WHERE ID = :ID; END;';
• Parse the statement. DBMS_SQL.PARSE(v_CursorID, v_BlockStr, DBMS_SQL.V7);
• Bind the placeholders to the variables. Note that we
• do this for both the input and output variables.
• We pass the maximum length for :first_name and
• :last_name. DBMS_SQL.BIND_VARIABLE(v_CursorID, ':first_name', v_FirstName, 30); DBMS_SQL.BIND_VARIABLE(v_CursorID, ':last_name', v_LastName, 30); DBMS_SQL.BIND_VARIABLE(v_CursorID, ':ID', p_StudentID);
• Execute the statement. We don't care about the return
• value, but we do need to declare a variable for it. v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
• Retrieve the values for the output variables. DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':first_name', v_FirstName); DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':last_name', v_LastName);
• Insert them into temp_table. INSERT INTO temp_table (num_col, char_col) VALUES (p_StudentID, v_FirstName || ' ' || v_LastName);
• Close the cursor. DBMS_SQL.CLOSE_CURSOR(v_CursorID);
• Commit our work. COMMIT; EXCEPTION WHEN OTHERS THEN
  • Close the cursor, then raise the error again. DBMS_SQL.CLOSE_CURSOR(v_CursorID); RAISE; END DynamicPLSQL; /

    ---

Scott Urman Oracle Corporation surman_at_us.oracle.com

---

Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm

---

"The opinions expressed here are my own, and are not necessarily that of  Oracle Corporation"

---

Received on Tue Jun 25 1996 - 00:00:00 CEST