Solved

Oracle ODBC -- call failed error

Posted on 2004-09-28
12
1,889 Views
Last Modified: 2012-05-05
Hi,

Our server recently went down and so we are now using the backup server, which is supposed to be an exact image. Since the changeover I have been getting an error message, whereas before I didn't.

When stored procedures runs (I think all of them) in Oracle 9i, I get the following error message:

ODBC -- call failed
[Oracle][ODBC][Ora]ORA-06550: line 1, column 6:
PLS-00103: Encountered the symbol "" when expecting one of the following:

begin case declare exit for goto if loop mod null pragma
raise return select updare while with <an identifier>
<a double-quoted delimited-identifier><a bind variable><<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQl string> pipe (#6550)

---------------------------------------------------

Could this be a permissions things? Any ideas?

Please help!
0
Comment
Question by:colin_palmer
  • 5
  • 4
  • 3
12 Comments
 
LVL 3

Expert Comment

by:oratim
Comment Utility

a bit of the source would help. sounds like you have a bug/typo in the pl/sql block.
is it possible you have old code on the backup server that wasn't in production?

tim
0
 
LVL 12

Expert Comment

by:geotiger
Comment Utility

Would the two reasons in this discussion applies to your situation? http://wb4oracle.webboard.com/default.asp?action=9&read=11549&fid=536

And this: http://dbforums.com/t591541.html
0
 
LVL 1

Author Comment

by:colin_palmer
Comment Utility
Hi,

This stored procedure used to work with the server before and none of the data has changed so I know it cant me a bug/typo, I suspect it could be a permissions thing.

Here is one of the stored procedures:

CREATE OR REPLACE procedure pre_inv_spon
is
    v_batch# number(10);
    v_person number(10);
    v_sponsor varchar2(10);
    v_invoice# number(10);
    v_amount number(15,2);
    v_batch_date date;
    resource_busy EXCEPTION;
    PRAGMA EXCEPTION_INIT( resource_busy, -54 );

    cursor sponsor_pay is
      select fes_person_code,fes_sponsor_code    --added fes_person_code XXXX
      from fees_list fl1
      where inv_run_number is null
      and fes_bal_to_update=2
        and fes_sponsor_code is not null
      and fes_who_to_pay in (select who_to_pay from who_to_pay where transfer_allowed='Y')
      group by fes_person_code, fes_sponsor_code;  --added fes_person_code XXXX
begin
    begin
      lock table fees_list in exclusive mode nowait;
    exception when resource_busy then
      dbms_output.put_line( 'Unable to lock FEES_LIST table.' );
      return;
    end;
    -- select inv_run_seq.nextval into v_batch# from sys.dual;
    v_batch_date := sysdate;
    -- Do self pay first


    -- Do sponsor pay
    loop
      open sponsor_pay;
      loop
          fetch sponsor_pay into V_person, v_sponsor;            -- added v_person XXXX
          exit when sponsor_pay%NOTFOUND;
          -- select invoice_seq.nextval into v_invoice# from sys.dual;
          update fees_list
              set inv_run_number= -1, -- THIS DOES NOT COPY ON EDITED FEES
                description='@ '||description  -- THIS COPIES ON EDITED FEES
          where fes_person_code = V_person  -- added v_person XXXX
            and fes_sponsor_code = v_sponsor
          and fes_bal_to_update=2
            and substr(description,1,1) != '@'
            and inv_run_number is null
            and fes_who_to_pay in (select who_to_pay from who_to_pay where transfer_allowed='Y');
          exit when sponsor_pay%ROWCOUNT > 100;
      end loop;
      commit;
      lock table fees_list in exclusive mode;
      dbms_output.put_line( 'dropout' );
      exit when sponsor_pay%NOTFOUND;
        close sponsor_pay;
    end loop;
    close sponsor_pay;
    commit;
end;
/
0
 
LVL 12

Expert Comment

by:geotiger
Comment Utility

Will you be able to run the procedure in sqlplus instead of running through ODBC connection in your application?
0
 
LVL 3

Expert Comment

by:oratim
Comment Utility
Check the odbc drivers and make sure you have the same ones installed now that you were using for the main server.
0
 
LVL 12

Expert Comment

by:geotiger
Comment Utility

If you can run the proc in sql*plus without problem, then you have problem in your client side - either ODBC driver or the configuration of ODBC.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:colin_palmer
Comment Utility
Yes it runs in SQlPlus and in the editor I use, which is called 'TOAD'.

It could be the ODBC Driver, however I can create link tables and view all the data in Access via the Link Tables etc. I have got reports running in Access, which work fine. I have tried refreshing all my link tables and re-creating the indexes, however I still get the same error message.
0
 
LVL 3

Accepted Solution

by:
oratim earned 500 total points
Comment Utility
this is from Metalink.

Doc ID:  Note:185744.1
Subject:  ORA-06550, PLS-00103 Issuing Multi-line PL/SQL Through Oracle ODBC Driver
Type:  PROBLEM
Status:  PUBLISHED
 Content Type:  TEXT/PLAIN
Creation Date:  23-APR-2002
Last Revision Date:  20-JAN-2004
 

Problem Description ------------------- If a PL/SQL statement is passed as a multi-line string through the odbc driver, to appear as:  "begin insert into testodbc817 values('Miller''s Plumbing');  end;"  The statement fails with run time error: Run-time error '-2147467259(80004005)':   [Oracle][ODBC][Ora]ORA-06550: line 1, column 6:  PLS-00103: Encountered the symbol "" when expecting one fo the following:   begin case declare exit for goto if loop mod null pragma raise return  select update while with <an identifier> <a double-quoted  delimited-identifier> <a bind variable><<  close current delete fetch lock insert open rollback savepoint set  sql execute commit for all merge <a single-quoted SQL string> pipe  The symbol "" was ignored. Ora-6550: line 2, column 38: PLS-00103 (#6550)  If the statement is passed as a single-line string, such as" " begin insert into testodbc817 values('Miller''s Plumbing'); end;"  the statement succeeds.  Actual code sample that produces this error is:  --begin code-- Dim con As New ADODB.Connection Dim cd As New ADODB.Command Dim sql As String  sql = "begin" & vbCrLf sql = sql & "insert into testodbc817 values ('Miller''s Plumbing');" & vbCrLf sql = sql & "end;"  con.Open "dsn=orcl817", "scott", "tiger" cd.ActiveConnection = con cd.CommandText = sql cd.Execute  con.Close Set con = Nothing --end code--  Solution Description -------------------- There are two solutions to this problem: 1. Edit the oraodbc.ini file (located in  the winnt folder) and set RemoveControlChars=YES.     2. Upgrade to the latest Oracle ODBC Drivers. This was tested out with version 8.1.7.8.0, 9.0.1.5.0, and 9.2.0.3.0. Which worked correctly.  Explanation -----------  This functionality changed in the 8i versions of the ODBC driver.  In  Oracle 7 versions a statement such as this would have succeeded without  error.  Due to enhancments in the drivers functionality, The oraodbc.ini file must be modified to allow the backwards compatability with  statements being issued in this manner.   References ----------  BUG:2333456    Additional Search Words -----------------------  PLSQL
.
0
 
LVL 3

Expert Comment

by:oratim
Comment Utility
This is from Metalink.

<pre>
Doc ID:  Note:185744.1
Subject:  ORA-06550, PLS-00103 Issuing Multi-line PL/SQL Through Oracle ODBC Driver
Type:  PROBLEM
Status:  PUBLISHED
 Content Type:  TEXT/PLAIN
Creation Date:  23-APR-2002
Last Revision Date:  20-JAN-2004
 

Problem Description ------------------- If a PL/SQL statement is passed as a multi-line string through the odbc driver, to appear as:  "begin insert into testodbc817 values('Miller''s Plumbing');  end;"  The statement fails with run time error: Run-time error '-2147467259(80004005)':   [Oracle][ODBC][Ora]ORA-06550: line 1, column 6:  PLS-00103: Encountered the symbol "" when expecting one fo the following:   begin case declare exit for goto if loop mod null pragma raise return  select update while with <an identifier> <a double-quoted  delimited-identifier> <a bind variable><<  close current delete fetch lock insert open rollback savepoint set  sql execute commit for all merge <a single-quoted SQL string> pipe  The symbol "" was ignored. Ora-6550: line 2, column 38: PLS-00103 (#6550)  If the statement is passed as a single-line string, such as" " begin insert into testodbc817 values('Miller''s Plumbing'); end;"  the statement succeeds.  Actual code sample that produces this error is:  --begin code-- Dim con As New ADODB.Connection Dim cd As New ADODB.Command Dim sql As String  sql = "begin" & vbCrLf sql = sql & "insert into testodbc817 values ('Miller''s Plumbing');" & vbCrLf sql = sql & "end;"  con.Open "dsn=orcl817", "scott", "tiger" cd.ActiveConnection = con cd.CommandText = sql cd.Execute  con.Close Set con = Nothing --end code--  Solution Description -------------------- There are two solutions to this problem: 1. Edit the oraodbc.ini file (located in  the winnt folder) and set RemoveControlChars=YES.     2. Upgrade to the latest Oracle ODBC Drivers. This was tested out with version 8.1.7.8.0, 9.0.1.5.0, and 9.2.0.3.0. Which worked correctly.  Explanation -----------  This functionality changed in the 8i versions of the ODBC driver.  In  Oracle 7 versions a statement such as this would have succeeded without  error.  Due to enhancments in the drivers functionality, The oraodbc.ini file must be modified to allow the backwards compatability with  statements being issued in this manner.   References ----------  BUG:2333456    Additional Search Words -----------------------  PLSQL
.
</pre>
0
 
LVL 3

Expert Comment

by:oratim
Comment Utility
sorry about the double post, treid to make it more legible, but no joy.

tim
0
 
LVL 12

Expert Comment

by:geotiger
Comment Utility
Check whether you have the exactly the same versions on the both server and the exact setting in the oraodbc.ini file.

If not, then Oratime may have found the solution for you.

GT
0
 
LVL 1

Author Comment

by:colin_palmer
Comment Utility
Many thanks, I upgraded to a newer ODBC for Oracle and is working now!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now