Solved

Oracle ODBC -- call failed error

Posted on 2004-09-28
12
1,939 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 3

Expert Comment

by:oratim
ID: 12170256

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
ID: 12170280

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
ID: 12170371
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 12

Expert Comment

by:geotiger
ID: 12170566

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
ID: 12170598
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
ID: 12170734

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
 
LVL 1

Author Comment

by:colin_palmer
ID: 12170752
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
ID: 12170798
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
ID: 12170810
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
ID: 12170966
sorry about the double post, treid to make it more legible, but no joy.

tim
0
 
LVL 12

Expert Comment

by:geotiger
ID: 12171084
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
ID: 12177601
Many thanks, I upgraded to a newer ODBC for Oracle and is working now!
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

690 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