| Oracle error for Invalid identifier in Execute immediate stmt. [message #502193] |
Tue, 05 April 2011 03:29  |
vinayrajn
Messages: 4
Registered: September 2008
|
Junior Member |
|
|
Hi Everybody,
I have a procedure as below. To sum up the procedure in one line it dynamically forms a string to get the values of the type which is passed as an input to the procedure.
I call the procedure as
exec exec_imm( exec_imm_t(1,'asd','1/2-34'));
ERROR:
Error starting at line 9 in command:
exec exec_imm( exec_imm_t(1,'asd','1/2-34'))
Error report:
ORA-00904: "P_TYPE_DATA"."ADDRESS": invalid identifier
ORA-06512: at "PTK_ADM.EXEC_IMM", line 26
ORA-06512: at line 1
00904. 00000 - "%s: invalid identifier"
NOTE:
When I try to execute the procedure with the execute immediate statement I get the above error. But when I execute the select statement which is nothing but the value in v_type_data directly (as seen in the comments in the code below) there is no error. But when the same v_type_data is used in execute immediate, I get an error.
CREATE OR REPLACE procedure exec_imm(p_type_data exec_imm_t)
AS
v_type_str CLOB := NULL;
v_type_data CLOB := NULL;
v_type_name VARCHAR2(25) := NULL;
BEGIN
v_type_name := upper('exec_imm_t');
v_type_data := v_type_data || '''<' || v_type_name || '>''||' || 'chr(13)' || '||';
FOR type_rec IN (SELECT ATTR_NAME
FROM user_type_attrs
WHERE lower(type_name) = 'exec_imm_t'
order by ATTR_NO)
loop
v_type_data := v_type_data || '''<' || type_rec.attr_name || '>''' || '||'|| 'p_type_data.' || type_rec.attr_name || '||' ||'''</' || type_rec.ATTR_NAME || '>''||' || 'chr(13)' || '||';
END loop;
v_type_data := v_type_data || '''</' || v_type_name || '>'' ';
/*SELECT '<EXEC_IMM_T>'||chr(13)||
'<NUM>'||p_type_data.NUM||'</NUM>'||chr(13)||
'<NAME>'||p_type_data.NAME||'</NAME>'||chr(13)||
'<ADDRESS>'||p_type_data.ADDRESS||'</ADDRESS>'||chr(13)||
'</EXEC_IMM_T>' INTO v_type_str from dual;*/
EXECUTE IMMEDIATE 'select ' || v_type_data || ' from dual' INTO v_type_str;
end;
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle error for Invalid identifier in Execute immediate stmt. [message #502201 is a reply to message #502196] |
Tue, 05 April 2011 03:43  |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So you've built this string:
SELECT '<EXEC_IMM_T>'||chr(13)||
'<NUM>'||p_type_data.NUM||'</NUM>'||chr(13)||
'<NAME>'||p_type_data.NAME||'</NAME>'||chr(13)||
'<ADDRESS>'||p_type_data.ADDRESS||'</ADDRESS>'||chr(13)||
'</EXEC_IMM_T>' INTO v_type_str from dual
The SQL executed by execute immediate can not see any variables/parameters from the procedure unless they are directly passed to it as binds.
It doesn't know what p_type_data is.
You need to either:
a) use bind variables
b) concatenate the contents of the p_type_data items into the string rather than their names.
|
|
|
|
| Re: Oracle error for Invalid identifier in Execute immediate stmt. [message #502319 is a reply to message #502201] |
Tue, 05 April 2011 17:15 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following contains minimal corrections:
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE exec_imm_t AS OBJECT
2 (num NUMBER,
3 name VARCHAR2 (4),
4 address VARCHAR2 (8))
5 /
Type created.
SCOTT@orcl_11gR2> CREATE OR REPLACE procedure exec_imm
2 (p_type_data exec_imm_t)
3 AS
4 v_type_str CLOB := NULL;
5 v_type_data CLOB := NULL;
6 v_type_name VARCHAR2(25) := NULL;
7 BEGIN
8 v_type_name := UPPER ('exec_imm_t');
9 v_type_data := v_type_data || '''<' || v_type_name || '>''||' || 'chr(13)' || '||';
10 FOR type_rec IN
11 (SELECT attr_name
12 FROM user_type_attrs
13 WHERE LOWER (type_name) = 'exec_imm_t'
14 ORDER BY attr_no)
15 LOOP
16 v_type_data := v_type_data || '''<' ||
17 type_rec.attr_name || '>''' || '||' ||
18 ':b_type_data.' || type_rec.attr_name || '||' || '''</' ||
19 type_rec.ATTR_NAME || '>''||' || 'chr(13)' || '||';
20 END LOOP;
21 v_type_data := v_type_data || '''</' || v_type_name || '>'' ';
22 EXECUTE IMMEDIATE
23 'SELECT ' || v_type_data || ' FROM DUAL'
24 INTO v_type_str
25 USING p_type_data, p_type_data, p_type_data;
26 DBMS_OUTPUT.PUT_LINE (v_type_str);
27 END exec_imm;
28 /
Procedure created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> EXEC exec_imm (exec_imm_t (1, 'asd', '1/2-34'))
<EXEC_IMM_T>
<NUM>1</NUM>
<NAME>asd</NAME>
<ADDRESS>1/2-34</ADDRESS>
</EXEC_IMM_T>
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
