• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ORA-06550: line 1, column 7 (PL/SQL: Statement ignored) Error

 
rajnish patel
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am getting following error for the stored procedure and not able to understand the issue (must be from db side) While googling, I found similar issues but could not get the solution. Can any one help me please find the error in PROCEDURE ??


Error :-




Stored Prodedure(SP_DIST_RETAILER_REMAP) :-










Java Code :-

 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, I dunno, but I reckon this might be a clue:

Try running your Java code with hard-coded values for the various input parameters, then replace these with variables until one of them breaks. Then you'll know which one isn't working.

But you should probably think about whether you really need all these parameters, or whether you might break your procedure up into more manageable procs/functions that each "do one thing and do it well". I don't have time to explore your code, but it looks like there's a lot of branching logic in the PL/SQL, which you could probably do in Java, and let PL/SQL just handle the database stuff. Also, don't do "SELECT COUNT(*) FROM..." if you only want to know if there are any records that match your query, as this has to search your entire table (or index). Do "SELECT 1 FROM..." instead and check the result (or use %FOUND/%NOTFOUND) to see if the query found anything, because this query can stop as soon as it finds a single matching record.

More generally, do you have any idea what you are trying to do with this code? Because it really isn't clear from trying to read it, and I don't think you really understand it either. But I suspect that you could probably get rid of most of the procedural loops and branches, let your data work for you by using cursors, cursor loops or sub-queries, and definitely get rid of all that stringified nonsense in v_ret_codes etc. Throw all this code away, and sit down with a piece of paper and your data model, and work out what data you need to meet your requirements, how to fetch it efficiently and how to use it when you've fetched it. Then write the SQL queries/updates/inserts, and work out how to fit it together with the minimum amount of PL/SQL. Look out for ways to combine DB operations into a small number of SQL statements, which is usually more efficient. For example, you can write "INSERT INTO .... SELECT ..." to create records in a table directly with the results of a query.

Finally, write this on a note attached to your computer screen: Don't use dynamic SQL unless you really have to. And even then, try not to use it. You don't need it here and it makes your code unreadable, unmaintainable and evil.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One more thing you might find useful: You can use a "TABLE" function to convert a PL/SQL collection into something that you can treat like a regular table inside a SELECT statement. Check here under "Selecting From A Collection Based On A VARRAY". This might help you find a way to use your input array inside your SQL, for example.
 
rajnish patel
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
chris webster wrote:One more thing you might find useful: You can use a "TABLE" function to convert a PL/SQL collection into something that you can treat like a regular table inside a SELECT statement. Check here under "Selecting From A Collection Based On A VARRAY". This might help you find a way to use your input array inside your SQL, for example.




I have two databases having same table structure, I tried with another db and its working fine, but while connecting to this db I am getting this error. Any clue ?? –
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The error message tells you what Oracle thinks is wrong:
Are you really running exactly the same Java and PL/SQL code on two databases (can you even be sure, given how unreadable your PL/SQL code is)? If so, then it must be the argument values that are wrong, so check that each parameter is being provided and is being set correctly. Also, try calling the PL/SQL directly using the same values via SQLDeveloper or in your SQL*Plus shell. If it works, then the problem must be on the Java side. Keep breaking the process up into small testable chunks until you find the problem.

But you *really* need to re-write this PL/SQL code, as it is not the kind of thing that any self-respecting Oracle professional should permit in a production system.
 
rajnish patel
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
chris webster wrote:The error message tells you what Oracle thinks is wrong:
Are you really running exactly the same Java and PL/SQL code on two databases (can you even be sure, given how unreadable your PL/SQL code is)? If so, then it must be the argument values that are wrong, so check that each parameter is being provided and is being set correctly. Also, try calling the PL/SQL directly using the same values via SQLDeveloper or in your SQL*Plus shell. If it works, then the problem must be on the Java side. Keep breaking the process up into small testable chunks until you find the problem.

But you *really* need to re-write this PL/SQL code, as it is not the kind of thing that any self-respecting Oracle professional should permit in a production system.



Sir I've found problem of this problem, While executing the query from toad I dont understand what to enter in HIGHLIGHTED area as its an array. The procedure name is same as posted above in the Question. Kindly refer the image attached in below URL.
Toad Query Execution image
 
rajnish patel
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
rajnish patel wrote:
chris webster wrote:The error message tells you what Oracle thinks is wrong:
Are you really running exactly the same Java and PL/SQL code on two databases (can you even be sure, given how unreadable your PL/SQL code is)? If so, then it must be the argument values that are wrong, so check that each parameter is being provided and is being set correctly. Also, try calling the PL/SQL directly using the same values via SQLDeveloper or in your SQL*Plus shell. If it works, then the problem must be on the Java side. Keep breaking the process up into small testable chunks until you find the problem.

But you *really* need to re-write this PL/SQL code, as it is not the kind of thing that any self-respecting Oracle professional should permit in a production system.



Sir I've found problem of this problem, While executing the query from toad I dont understand what to enter in HIGHLIGHTED area as its an array. The procedure name is same as posted above in the Question. Kindly refer the image attached in below URL.
Toad Query Execution image



@sir
Problem solved, I was connecting with the third database I had in file.properties file, connected with right db and solved. By the way the SP was not available in that third db thats why I was getting the issue. Thank you for the help.

 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic