DBMS_SQL.PARSE
Date: 5 Mar 99 15:09:44 GMT
Message-ID: <01be671a$28e290a0$f07974cb_at_default>
Hi folks,
I have the following stored procedure whereby I tried to use INTO from a database which is determined during runtime:
CREATE or REPLACE PROCEDURE func
( db IN VARCHAR2)
IS
dyn_db VARCHAR2(8); cursor INTEGER; dyn_name VARCHAR2(10); dyn_id NUMBER(3); v_name VARCHAR2(10); result INTEGER;
BEGIN
dyn_db := db;
cursor := dbms_sql.open_cursor;
first_statement := 'BEGIN
SELECT name INTO :dyn_name FROM emp_at_'||dyn_db||' WHERE dept=10; END;'; second_statement := 'BEGIN SELECT id INTO :dyn_id FROM emp_at_'||dyn_db||' WHERE dept = 10; END;';
dbms_sql.parse(cursor, first_statement, dbms_sql.v7);
dbms_sql.bind_variable(cursor, ':dyn_name', v_name, 10);
result := dbms_sql.execute(cursor);
dbms_sql.variable_value(cursor, ':dyn_name', v_name);
dbms_output.put_line(v_name);
The same parse and execute steps are repeated for the second_statement.
The problem is I can obtain the dyn_id (NUMBER) value but not the dyn_name
(VARCHAR2).
Can anyone enlighten me on why so for the different data types?
Really appreciate and many thanks!!
Meinaz. Received on Fri Mar 05 1999 - 16:09:44 CET