Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

It is possible to access object member variable in execute immediate

wermiAug 25 2010 — edited Aug 26 2010
Hi,
if have the following situation: I have a simple object with one member variable and one member function. In the function I call execute immediate and want to use the member variable inside the execute immediate. Is this somehow possible without the use of using? I could not use using cause I don't know how many using variables I need.

My simple code:
CREATE OR REPLACE
TYPE TEST_OBJ AS OBJECT
(
s_name VARCHAR2(30),
MEMBER FUNCTION test RETURN VARCHAR2
)

CREATE OR REPLACE
TYPE BODY TEST_OBJ
AS
MEMBER FUNCTION test RETURN VARCHAR2
IS
loc_name varchar2(30);
BEGIN
execute immediate 'SELECT s_name from dual' into loc_name; -- is it somehow possible to use s_name here - without using clause
RETURN loc_name;
END;
END;

-- test script
set serveroutput on
declare
o_test TEST_OBJ := TEST_OBJ('testText');
begin
dbms_output.put_line('s_name: ' || o_test.test);
end;

I get the following error if I call test function like above:
"ORA-00904: "S_NAME": ungültiger Bezeichner
ORA-06512: in "TEST.TEST_OBJ", Zeile 19
ORA-06512: in Zeile 4
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:"

The background behind this example is, that I want to implement a dynamic equals() function in an object (parent) to compare to objects (childs) and return if they are equal. This equals() function should be used from some childs of the parent object and should accept an instance of the child object as parameter. In the function I want to compare the passed child instance argument with the SELF object. Because everything will be dynamically I don't know how many using variables I need, so using is no option.

If this is not possible I have to implement every equals() function in every object separately.

Thanks in advance
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 22 2010
Added on Aug 25 2010
6 comments
901 views