mahine
asked on
Invalid identifier plsql error
Please urgent help!!!, I have a procedure, but I don't know why I have the error "Invalid Identifier", my declaration is in this way:
strsql VARCHAR2 (4000);
strsql := strsql || 'SELECT * FROM TAB_FIRST WHERE ';
strsql := strsql || 'B.CODSTATUS = D.CODSTATUS AND ';
strsql := strsql || 'A.CODTODO = F.CODTODO AND ';
strsql := strsql || 'A.USERFROM = E.CODI_OPER AND ';
strsql := strsql || 'A.OPERTYPEFROM = E.TIPO_OPER AND ';
strsql := strsql || 'J.TIPO = ''A3'' AND ';
strsql := strsql || 'A.OPERTYPEFROM = J.CODIGO AND ';
strsql := strsql || 'A.CODSTAGE = G.CODSTAGE AND ';
strsql := strsql || 'A.CODSTAGE = H.CODSTAGE AND ';
strsql := strsql || 'A.PERSONTYPE = H.PERSONTYPE AND ';
........
well, is a very large query and when it has a lenght of 1250 characters the next line presents the invalid identifier error for strsql, can you help me? do you know or have any idea of what's happening? I know that the max lenght is 400 for a varchar2 type. Thanks
strsql VARCHAR2 (4000);
strsql := strsql || 'SELECT * FROM TAB_FIRST WHERE ';
strsql := strsql || 'B.CODSTATUS = D.CODSTATUS AND ';
strsql := strsql || 'A.CODTODO = F.CODTODO AND ';
strsql := strsql || 'A.USERFROM = E.CODI_OPER AND ';
strsql := strsql || 'A.OPERTYPEFROM = E.TIPO_OPER AND ';
strsql := strsql || 'J.TIPO = ''A3'' AND ';
strsql := strsql || 'A.OPERTYPEFROM = J.CODIGO AND ';
strsql := strsql || 'A.CODSTAGE = G.CODSTAGE AND ';
strsql := strsql || 'A.CODSTAGE = H.CODSTAGE AND ';
strsql := strsql || 'A.PERSONTYPE = H.PERSONTYPE AND ';
........
well, is a very large query and when it has a lenght of 1250 characters the next line presents the invalid identifier error for strsql, can you help me? do you know or have any idea of what's happening? I know that the max lenght is 400 for a varchar2 type. Thanks
Best way to identify the problem with your query is, to do a DBMS_OUTPUT of the strsql (variable that has your SQL statement), before doing and 'EXECUTE' on it.
Note that there is a limit of 255 characters (in one line) in DBMS_OUTPUT.
Hope this would also help.
Note that there is a limit of 255 characters (in one line) in DBMS_OUTPUT.
Hope this would also help.
ASKER
Well that's right the sql query is malformed but i's because I did not write it all, I put only a piece to have the idea, but in real it's well, I have defined the tables and synonims and everything, I am making debug but when I debug it I can see the invalid identifier too, the dbms_ouput presents just until the line before the error message obviously, I thanks your comments but I need more suggestions if you have some please post a comment thanks
This is the problem, you cannot use double quote for literal:
like in this line:
strsql := strsql || 'J.TIPO = ''A3'' AND ';
you should write it with the single quote instead :
strsql := strsql || 'J.TIPO = '''A3''' AND ';
like in this line:
strsql := strsql || 'J.TIPO = ''A3'' AND ';
you should write it with the single quote instead :
strsql := strsql || 'J.TIPO = '''A3''' AND ';
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can take care of the double quotes problem as suggested by seazodiac
you might want to use the CHR(39), which is same as ' character.
So replace your statement
strsql := strsql || 'J.TIPO = ''A3'' AND ';
to
strsql := strsql || 'J.TIPO = ' || chr(39)||'A3'||chr(39) || ' AND ';
Just in case if you still get the problem, can you try and do the DBMS_OUTPUT and see what's the SQL that is getting generated ?
Since there is a limit of 255 characters in DBMS_OUTPUT, you might want to print all the characters in your strsql in chunks of 255 characters.
If you identify the SQL, you can try and execute the SQL statement from SQL*Plus window.
You might want to break the logic of printing the SQL statement by doing the DBMS_OUTPUT in a loop :
a:=1;
while a<length(strsql)
loop
dbms_output.put_line(strsq l,a,255);
a:=a+255;
end loop;
remember to declare variable a in the declaration section.
You will get invalid identifier, only when you reach the 'EXECUTE ..' statement. You can do this DBMS_OUTPUT before doing the EXECUTE. Once you get the complete SQL statement, try and run it from SQL*Plus. It should tell you exactly which line is having the problem. Then you should be able to find out the cause of real problem. It might be diffcult for us to identify the real problem, without seeing the actual SQL statement that would be executed using the EXECUTE. Moreover to make it harder, you have given us a malformed SQL statement, due to which we are'nt sure whether its a problem of your SQL statement, or something else.
If you continue to face the same problem, Can you post the complete SQL statement after you get it as the output from the above DBMS_OUTPUT statements ?
you might want to use the CHR(39), which is same as ' character.
So replace your statement
strsql := strsql || 'J.TIPO = ''A3'' AND ';
to
strsql := strsql || 'J.TIPO = ' || chr(39)||'A3'||chr(39) || ' AND ';
Just in case if you still get the problem, can you try and do the DBMS_OUTPUT and see what's the SQL that is getting generated ?
Since there is a limit of 255 characters in DBMS_OUTPUT, you might want to print all the characters in your strsql in chunks of 255 characters.
If you identify the SQL, you can try and execute the SQL statement from SQL*Plus window.
You might want to break the logic of printing the SQL statement by doing the DBMS_OUTPUT in a loop :
a:=1;
while a<length(strsql)
loop
dbms_output.put_line(strsq
a:=a+255;
end loop;
remember to declare variable a in the declaration section.
You will get invalid identifier, only when you reach the 'EXECUTE ..' statement. You can do this DBMS_OUTPUT before doing the EXECUTE. Once you get the complete SQL statement, try and run it from SQL*Plus. It should tell you exactly which line is having the problem. Then you should be able to find out the cause of real problem. It might be diffcult for us to identify the real problem, without seeing the actual SQL statement that would be executed using the EXECUTE. Moreover to make it harder, you have given us a malformed SQL statement, due to which we are'nt sure whether its a problem of your SQL statement, or something else.
If you continue to face the same problem, Can you post the complete SQL statement after you get it as the output from the above DBMS_OUTPUT statements ?
hi there
the error is in the line below
strsql := strsql || 'J.TIPO = ''A3'' AND ';
just replace it with this line
strsql := strsql || 'J.TIPO = ' || "A3" || ' AND ';
regards
annamalai;
the error is in the line below
strsql := strsql || 'J.TIPO = ''A3'' AND ';
just replace it with this line
strsql := strsql || 'J.TIPO = ' || "A3" || ' AND ';
regards
annamalai;
Once you do that you should be able to run your query without problems.
Hope this helps