DBMS_SQL.PARSE

From: Meinaz <meinaz_at_cyberway.com.sg>
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

Original text of this message