Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06550: Error (ORACLE 9i)
ORA-06550: Error [message #421622] Wed, 09 September 2009 09:39 Go to next message
baulraj
Messages: 48
Registered: January 2009
Member
My script is as follows,

declare
LV_NEWTEXT clob;
begin
dbms_lob.createtemporary(LV_NEWTEXT,true);
dbms_lob.writeappend(LV_NEWTEXT,2,'ss');
update OPERATION set OP_DESCRIPTION =LV_NEWTEXT where OP_SEQU=1542;
dbms_lob.freetemporary(LV_NEWTEXT);
commit;
end;

The above script works fine if we execute at the backend directly,but from the application it raises the following error.


S1000:[Oracle][ODBC][Ora]ORA-06550: line 1, column 23:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
:= . ( @ % ; not null range default character

Kindly help me to fix this problem.

Thanks in Advance.
Baulraj
Re: ORA-06550: Error [message #421625 is a reply to message #421622] Wed, 09 September 2009 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>The above script works fine if we execute at the backend directly,

So problem is not with posted code.

>but from the application it raises the following error.

but with application
Re: ORA-06550: Error [message #421626 is a reply to message #421622] Wed, 09 September 2009 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>The above script works fine if we execute at the backend directly,

So problem is not with posted code.

>but from the application it raises the following error.

but with application

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: ORA-06550: Error [message #421627 is a reply to message #421622] Wed, 09 September 2009 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As your problem occurs in application and not in back-end (which tool?) then the problem is in your application code (which one?) not in Oracle.

Please read 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 (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: ORA-06550: Error [message #421629 is a reply to message #421622] Wed, 09 September 2009 10:05 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Apologies for the inconvenience caused.
I will keep the 'posting guidlines' for future references.

I have used TOAD to execute the code at backend

Frontend Application is our own legacy application using some old programming language.

ORACLE Version is 9.2.0.7.0.

Thank you.

Regards
Baulraj.V



Re: ORA-06550: Error [message #421631 is a reply to message #421629] Wed, 09 September 2009 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So as had been said: the problem is in the application code.

Regards
Michel
Re: ORA-06550: Error [message #421643 is a reply to message #421631] Wed, 09 September 2009 10:31 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi,

I agree with you.
But the same code works fine with other tables without any error in our application.
In this particular table i have altered the particular column 'description' from LONG to CLOB datatype.
Do i need to execute any other command after this type of conversion?
There are no triggers,indexes available for this table.
Thank you.
Baulraj.V
Re: ORA-06550: Error [message #421665 is a reply to message #421643] Wed, 09 September 2009 11:24 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I would try putting a slash (/) at the last line.
Re: ORA-06550: Error [message #421673 is a reply to message #421665] Wed, 09 September 2009 13:18 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Frontend Application is our own legacy application using some old programming language.



Quote:

In this particular table i have altered the particular column 'description' from LONG to CLOB datatype.



All bets are off. The old programming language probably can't handle CLOBs, new Oracle versions don't support LONGs anymore.

So either you will have to stay on an old Oracle/OS and keep the legacy app, or move to a new Oracle/OS and a new application.

[Updated on: Wed, 09 September 2009 13:18]

Report message to a moderator

Re: ORA-06550: Error [message #421703 is a reply to message #421665] Wed, 09 September 2009 23:10 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi,

I tried the same by putting a '/' at the end.
but it is not working.

Really i Thank You all for your valuable time.
Still i wait for your guidance.

Thanks,
Baulraj.V
Re: ORA-06550: Error [message #421706 is a reply to message #421622] Wed, 09 September 2009 23:22 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi All,

Please let me explain the problem,the code which i had mentioned in the forum is working fine with the same application for the CLOB column. but only thing is, the code is not working for the particular table 'Operation'.

We are handling two databases from the application.
For database1 the code is working fine. But for the table 'Operation' which is in database2 the code is not working.
Both databases are available in the same ORACLE server Version is 9.2.0.7.0.

Thanks,
Baulraj.V

Re: ORA-06550: Error [message #421708 is a reply to message #421706] Wed, 09 September 2009 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We still don't have your application code and even don't know its language and version nor client version(s).

Regards
Michel

[Updated on: Wed, 09 September 2009 23:25]

Report message to a moderator

Re: ORA-06550: Error [message #421717 is a reply to message #421708] Thu, 10 September 2009 01:19 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
CREATE OR REPLACE PROCEDURE Updateclob 
     (sqtable      IN VARCHAR2, 
      mychunk      IN VARCHAR2, 
      whereclause  IN VARCHAR2, 
      chunk        IN VARCHAR2) 
AS 
  exec_string  VARCHAR2(2000); 
  result       CLOB := empty_clob; 
  old_buff     CLOB; 
BEGIN 
  dbms_lob.Createtemporary(result,true); 
   
  exec_string := 'select ' 
                 ||mychunk 
                 ||' from ' 
                 ||sqtable 
                 ||' ' 
                 ||whereclause 
                 ||' FOR UPDATE'; 
   
  EXECUTE IMMEDIATE exec_string INTO result; 
   
  dbms_lob.Writeappend(result,Length(chunk),chunk); 
   
  COMMIT; 
END; 
/ 

I have converted the code into a procedure.
still i am getting the same error.

Kindly give me your comments on this.
Hope i am following the posting guidelines this time.

Thanks
Baulraj.V
Re: ORA-06550: Error [message #421718 is a reply to message #421622] Thu, 10 September 2009 01:24 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
BEGIN 
  Updateclob(:PA_TABLE,:PA_CLOB_FIELD,:LV_WHERE_CLAUSE, 
             :LV_CHUNK); 
END; 


The 'Updateclob' procedure is called using the above code from the application.
Re: ORA-06550: Error [message #421719 is a reply to message #421718] Thu, 10 September 2009 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 10 September 2009 06:24
We still don't have your application code and even don't know its language and version nor client version(s).

Regards
Michel


Re: ORA-06550: Error [message #421765 is a reply to message #421719] Thu, 10 September 2009 06:12 Go to previous message
baulraj
Messages: 48
Registered: January 2009
Member
Application code is as follows.
perform sql [SQL_QUERY]


The application code is very simple.
Application Language is OMNIS vesion 7.3.

Finally the problem solved after the particular table is copied into the database1.As i already said in the previous post's we were using two databases.

Both databases are in same ORACLE server.
But i could not find the reason behind it.

Whether the database permissions or settings maybe missing for the second database?
Kindly give me some guidance for this.

I thank you all very much for your precious time.

Regards
Baulraj.V
Previous Topic: Optimization in the where clause
Next Topic: select query failing
Goto Forum:
  


Current Time: Wed Sep 28 14:22:22 CDT 2016

Total time taken to generate the page: 0.21574 seconds