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 Go to next message
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 #301230 is a reply to message #301229] Tue, 19 February 2008 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You already wrote 25 posts, you should know you have to format them.
please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

The statement you posted can't give the error you posted (as far I can see in an unformated post).
Check where you call PSU.PSU_B_R_PERFMMONITOR.

Regards
Michel

Re: ORA-00904: Entity.Record_type_code: invalid Identifier [message #301240 is a reply to message #301229] Tue, 19 February 2008 15:16 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Try inserting the generating sql statement into a table. then you should be in a position to identify the fault by yourself.

Regards

Raj
Re: ORA-00904: Entity.Record_type_code: invalid Identifier [message #301245 is a reply to message #301229] Tue, 19 February 2008 16:23 Go to previous messageGo to next message
scottwmackey
Messages: 505
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 Go to previous messageGo to next message
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 #301408 is a reply to message #301245] Wed, 20 February 2008 08:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@scottwmackey,

Try to keep your lines in 80 characters width.
And once again don't post solution, give hint/clue to let OP learn.

Regards
Michel
icon14.gif  Re: ORA-00904: Entity.Record_type_code: invalid Identifier [message #301413 is a reply to message #301408] Wed, 20 February 2008 08:50 Go to previous messageGo to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
I know for myself that I learned how to deal with this type of problem through scott's example. The next time, I will know what to do thanks to scott. Thanks to everyone else for their input also. For myself, I disagree with that last comment.
Re: ORA-00904: Entity.Record_type_code: invalid Identifier [message #301439 is a reply to message #301413] Wed, 20 February 2008 11:25 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Ya, I just ignore him.
Re: ORA-00904: Entity.Record_type_code: invalid Identifier [message #301444 is a reply to message #301439] Wed, 20 February 2008 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Bad idea to go against the rules.

Regards
Michel

[Updated on: Wed, 20 February 2008 12:43]

Report message to a moderator

Re: ORA-00904: Entity.Record_type_code: invalid Identifier [message #301458 is a reply to message #301444] Wed, 20 February 2008 13:06 Go to previous messageGo to next message
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.
Re: ORA-00904: Entity.Record_type_code: invalid Identifier [message #301471 is a reply to message #301458] Wed, 20 February 2008 15:39 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Glad I could help.
Previous Topic: materialized view hlp
Next Topic: Problem with Implicit Cursor
Goto Forum:
  


Current Time: Thu Dec 08 20:33:59 CST 2016

Total time taken to generate the page: 0.12228 seconds