Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904: Entity.Record_type_code: invalid Identifier
ORA-00904: Entity.Record_type_code: invalid Identifier [message #301229] |
Tue, 19 February 2008 13:47  |
mxd198
Messages: 28 Registered: December 2005
|
Junior Member |
|
|
I am trying to insert records from different tables into a temp table. I am using variables for the table names. for ex. v_Table_Name is equal to a table called entity.
v_Rec_Type_Code is equal to a column named record_type_code in the entity table. I keep getting this error:
ORA-00904:"ENTITY"."RECORD_TYPE_CODE":invalid indentifier
ORA-06512: AT "PSU.PSU_B_R_PERFMMONITOR", line 18
ORA-06512: at line 3
Here is the code I am using:
--Insert the data from the entity table into the PSU_TEMP_PERF_MONTR....
SQLInsertstmt := SQLInsertstmt ||'INSERT INTO psu.PSU_TEMP_PERF_MONTR(Table_Name,record_type,Operator,';
SQLInsertstmt := SQLInsertstmt ||'Operator_name,Operator_Office, ';
SQLInsertstmt := SQLInsertstmt ||'Oper_office_desc,oper_user_grp, ';
SQLInsertstmt := SQLInsertstmt ||'Oper_user_grp_desc, run_date, date_add_mod, Rec_Added_Cnt, ';
SQLInsertstmt := SQLInsertstmt ||'Rec_modified_cnt)';
SQLInsertstmt := SQLInsertstmt ||'SELECT ''' || v_Table_Name || ''', ';
if v_Rec_Type_Code is not null then
SQLInsertstmt := SQLInsertstmt || 'TableCnts.' || v_Rec_Type_Code;
else
v_Rec_Type_Code := 'NULL';
SQLInsertstmt := SQLInsertstmt || v_Rec_Type_Code;
end if;
SQLInsertstmt := SQLInsertstmt || ' ,TableCnts.operator_name,TableCnts.name, ';
SQLInsertstmt := SQLInsertstmt || ' TableCnts.office_code,TableCnts.short_desc, ';
SQLInsertstmt := SQLInsertstmt || ' TableCnts.user_group,TableCnts.description, ';
SQLInsertstmt := SQLInsertstmt || ' to_char(''' || sysdate ||''', ''mm/dd/yyyy''),';
SQLInsertstmt := SQLInsertstmt || ' TableCnts.add_or_mod,';
SQLInsertstmt := SQLInsertstmt || ' SUM(CASE WHEN TableCnts.Cnt_type=''Cnt_date_Added'' THEN cnt ELSE 0 END) Cnt_date_Added, ';
SQLInsertstmt := SQLInsertstmt || ' SUM(CASE WHEN TableCnts.Cnt_type=''Cnt_date_modified'' THEN cnt ELSE 0 END) Cnt_date_modified';
SQLInsertstmt := SQLInsertstmt || ' FROM ';
SQLInsertstmt := SQLInsertstmt || '(Select ''' || v_Table_Name || ''', '||v_Table_Name ||'.'|| v_rec_type_code || ', '||v_Table_Name||'.operator_name, ';
SQLInsertstmt := SQLInsertstmt || ' zzu.name, zzu.office_code, tmso.short_desc, ';
SQLInsertstmt := SQLInsertstmt || v_Table_Name||'.date_added add_or_mod, ';
SQLInsertstmt := SQLInsertstmt || 'count('||v_Table_Name||'.operator_name)as cnt, ';
SQLInsertstmt := SQLInsertstmt || 'zzu.user_group, zzug.description,''Cnt_date_Added'' as Cnt_type, ';
SQLInsertstmt := SQLInsertstmt || 'to_char(''' || sysdate || ''', ''mm/dd/yyyy'')';
SQLInsertstmt := SQLInsertstmt ||' From Advance.zz_User zzu, ' || v_Table_Name || ', advance.tms_office tmso, ';
SQLInsertstmt := SQLInsertstmt ||' Advance.Zz_User_Group zzug ';
SQLInsertstmt := SQLInsertstmt ||' Where TRIM(upper('||v_Table_Name||'.operator_name)) = TRIM(upper(zzu.user_name))';
SQLInsertstmt := SQLInsertstmt ||' And zzu.office_code = tmso.office_code ';
SQLInsertstmt := SQLInsertstmt ||' And zzu.user_group = zzug.user_group ';
SQLInsertstmt := SQLInsertstmt ||' AND ('||v_Table_Name||'.date_added between to_date(''12/01/2000'', ''mm/dd/yyyy'') ';
SQLInsertstmt := SQLInsertstmt ||' and to_date(''02/01/2001'', ''mm/dd/yyyy'')) ';
SQLInsertstmt := SQLInsertstmt ||' Group by zzu.office_code,'||v_Table_Name||'.operator_name,'||v_Table_Name||'.' || v_rec_type_code || ', zzu.name, tmso.short_desc, ';
SQLInsertstmt := SQLInsertstmt ||v_Table_Name||'.date_added,zzu.user_group, zzug.description' ;
SQLInsertstmt := SQLInsertstmt ||' UNION ';
SQLInsertstmt := SQLInsertstmt ||' Select ''' || v_Table_Name || ''','||v_Table_Name||'.' || v_Rec_Type_Code || ','||v_Table_Name||'.operator_name,zzu.name ,zzu.office_code, ';
SQLInsertstmt := SQLInsertstmt ||' tmso.short_desc,'||v_Table_Name||'.date_modified add_or_mod,count('||v_Table_Name||'.operator_name)as cnt, ';
SQLInsertstmt := SQLInsertstmt ||' zzu.user_group,zzug.description,''Cnt_date_modified'' as Cnt_type, ';
SQLInsertstmt := SQLInsertstmt ||' to_char(''' || sysdate || ''', ''mm/dd/yyyy'')';
SQLInsertstmt := SQLInsertstmt ||' From Advance.zz_User zzu, ' || v_Table_Name || ', advance.tms_office tmso, ';
SQLInsertstmt := SQLInsertstmt ||' Advance.Zz_User_Group zzug ';
SQLInsertstmt := SQLInsertstmt ||' Where TRIM(upper('||v_Table_Name||'.operator_name)) = TRIM(upper(zzu.user_name)) ';
SQLInsertstmt := SQLInsertstmt ||' And zzu.office_code = tmso.office_code ';
SQLInsertstmt := SQLInsertstmt ||' And zzu.user_group = zzug.user_group ';
SQLInsertstmt := SQLInsertstmt ||' AND ('||v_Table_Name||'.date_modified between to_date(''12/01/2000'', ''mm/dd/yyyy'')';
SQLInsertstmt := SQLInsertstmt ||' and to_date(''02/01/2001'', ''mm/dd/yyyy'')) ';
SQLInsertstmt := SQLInsertstmt ||' Group by zzu.office_code,'||v_Table_Name||'.operator_name,'||v_Table_Name||'.' || v_Rec_Type_Code || ', zzu.name, tmso.short_desc, ';
SQLInsertstmt := SQLInsertstmt ||v_Table_Name||'.date_modified, zzu.user_group, zzug.description ';
SQLInsertstmt := SQLInsertstmt ||' order by 2) TableCnts ';
SQLInsertstmt := SQLInsertstmt ||' GROUP BY TableCnts.operator_name,TableCnts.name,TableCnts.office_code,TableCnts.short_desc, ';
SQLInsertstmt := SQLInsertstmt ||' TableCnts.user_group,TableCnts.description, '||v_Table_Name|| '.' || v_Rec_Type_Code || ', TableCnts.add_or_mod';
SQLInsertstmt := SQLInsertstmt ||' ORDER BY TableCnts.OPERATOR_NAME, TableCnts.record_type_code';
execute immediate SQLInsertstmt;
Commit;
The line that the error is giving me is where it is executed. So, that is not helping me. However, I have the same select statement without the insert on top and it works. I substitute the variables for the table name and column.
Can someone help me. I have been on this for days!!
Thanks in advance!
|
|
|
|
|
Re: ORA-00904: Entity.Record_type_code: invalid Identifier [message #301245 is a reply to message #301229] |
Tue, 19 February 2008 16:23   |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
Run this
CREATE OR REPLACE PROCEDURE my_output(
p_text VARCHAR2
,p_max_line_length NUMBER DEFAULT 255
)
IS
v_text VARCHAR2(32000) := p_text;
v_max_line_length NUMBER := least(p_max_line_length, 255);
v_line_break NUMBER;
BEGIN
WHILE v_text IS NOT NULL LOOP
v_line_break := instr(v_text, chr(10));
IF v_line_break = 0 OR v_line_break > v_max_line_length THEN
v_line_break := least(instr(v_text, ' ', -(greatest((length(v_text) - v_max_line_length), 0))), v_max_line_length);
IF v_line_break = 0 THEN
v_line_break := v_max_line_length;
END IF;
END IF;
dbms_output.put_line(replace(substr(v_text, 1, v_line_break), chr(10), NULL));
v_text := substr(v_text, v_line_break + 1);
END LOOP;
END my_output;
/
Then add this. (I am assuming you know how to turn on serveroutput.) SQLInsertstmt := SQLInsertstmt ||' TableCnts.user_group,TableCnts.description, '||v_Table_Name|| '.' || v_Rec_Type_Code || ', TableCnts.add_or_mod';
SQLInsertstmt := SQLInsertstmt ||' ORDER BY TableCnts.OPERATOR_NAME, TableCnts.record_type_code';
my_output(SQLInsertstmt); -- NEW CODE
execute immediate SQLInsertstmt;
Commit; Copy the output and try to run it by itself. It should become obvious where your problem is.
Here is an example of what I mean.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_sql VARCHAR2(32000);
3 BEGIN
4 v_sql := 'INSERT INTO smackey..t VALUES(SYSDATE);';
5 my_output(v_sql);
6 EXECUTE IMMEDIATE v_sql;
7 END;
8 /
INSERT INTO smackey..t VALUES(SYSDATE);
DECLARE
v_sql VARCHAR2(32000);
BEGIN
v_sql := 'INSERT INTO smackey..t VALUES(SYSDATE);';
my_output(v_sql);
EXECUTE IMMEDIATE v_sql;
END;
ORA-00903: invalid table name
ORA-06512: at line 6
SQL> INSERT INTO smackey..t VALUES(SYSDATE);
INSERT INTO smackey..t VALUES(SYSDATE)
ORA-00903: invalid table name
|
|
|
Re: ORA-00904: Entity.Record_type_code: invalid Identifier [message #301399 is a reply to message #301245] |
Wed, 20 February 2008 08:10   |
mxd198
Messages: 28 Registered: December 2005
|
Junior Member |
|
|
Thanks for the help. Sorry about the formatting. I haven't been in here for awhile. I ran my output from the my_output procedure and it gave me that an advance table called entity.record_type_code was an invalid identifier. So I will have to figure out why. Maybe it needs to have advance.entity.record_type_code. I will have to play around with it some more.
|
|
|
|
|
|
|
Re: ORA-00904: Entity.Record_type_code: invalid Identifier [message #301458 is a reply to message #301444] |
Wed, 20 February 2008 13:06   |
mxd198
Messages: 28 Registered: December 2005
|
Junior Member |
|
|
I think that is what a forum is for to get answers on what you might be struggling with for days in the first place. It is not a class room. That is why I disagree with not putting the answer out there. The first two comments did not help me at all. I had no clue. I would have been where I was before, stuck.
|
|
|
|
Goto Forum:
Current Time: Mon Feb 10 11:58:10 CST 2025
|