PLS-00103 - symbol encoutered is different every time

[Burser]

Hello,

Here is a selection of error messages thrown from the call to a stored procedure - I ran each one immediately after the last:

[Oracle][ODBC][Ora]ORA-06550: line 1, column 197:
PLS-00103: Encountered the symbol "Ì3" The symbol "Ì3" was ignored.

[Oracle][ODBC][Ora]ORA-06550: line 1, column 197:
PLS-00103: Encountered the symbol "¿"

[Oracle][ODBC][Ora]ORA-06550: line 1, column 197:
PLS-00103: Encountered the symbol "+"

You get the picture. My first question is, simply, ?????

For a start, I don't have a column 197 on my line 1, that's daft. Here's the top of my procedure:

create or replace
PACKAGE BODY DD_JOURNALS AS
  PROCEDURE AuthorityOnAccount(
    in_vCompany       IN VARCHAR2,
    in_vAccount       IN VARCHAR2,
    in_vCostCentre    IN VARCHAR2,
    in_vDataType      IN VARCHAR2,
    in_nAmount        IN NUMBER,
    out_cAuthority     OUT VARCHAR2,
    out_nMaxPost       OUT NUMBER,
    out_nMaxAuth       OUT NUMBER,
    out_vMessage       OUT VARCHAR2)   
  AS
    cAuth     CHAR;
    nMaxPost  NUMBER;
    nMaxAuth  NUMBER;
    
    cCanPost CHAR DEFAULT '0';
    cCanAuth CHAR DEFAULT '0';
    
    cTest     CHAR;
  BEGIN
    cCanPost := RIGHTS_MANAGEMENT.CHECK_RIGHTS('LEDGER::JOURNAL_POST');

Now - the call to run this stored procedure is being done from an Excel spreadsheet via ODBC (using InstantClient11_1). I've used adVarChar and adDouble as the ADO data types for my parameters. I just can't get the darned thing to execute.

 

[SantaMufasa]

Burser,

When you receive errors like you've received (e.d., 'PLS-00103: Encountered the symbol "¿" '), it is usually due to unseen ASCII characters that have somehow become attached to the line.

I have copied and pasted your code, and made a few adjustments (i.e., added a PACKAGE header, END; statements, an executable block at the end of your package body, and stubbed the RIGHTS_MANAGEMENT.CHECK_RIGHTS function in order to get a clean compile. Both Package Header and Package Body compiled fine:

create or replace package dd_journals as
  PROCEDURE AuthorityOnAccount(
    in_vCompany       IN VARCHAR2,
    in_vAccount       IN VARCHAR2,
    in_vCostCentre    IN VARCHAR2,
    in_vDataType      IN VARCHAR2,
    in_nAmount        IN NUMBER,
    out_cAuthority     OUT VARCHAR2,
    out_nMaxPost       OUT NUMBER,
    out_nMaxAuth       OUT NUMBER,
    out_vMessage       OUT VARCHAR2);
  end;
/

Package created.

create or replace PACKAGE BODY DD_JOURNALS AS
  PROCEDURE AuthorityOnAccount(
    in_vCompany       IN VARCHAR2,
    in_vAccount       IN VARCHAR2,
    in_vCostCentre    IN VARCHAR2,
    in_vDataType      IN VARCHAR2,
    in_nAmount        IN NUMBER,
    out_cAuthority     OUT VARCHAR2,
    out_nMaxPost       OUT NUMBER,
    out_nMaxAuth       OUT NUMBER,
    out_vMessage       OUT VARCHAR2)   
  AS
    cAuth     CHAR;
    nMaxPost  NUMBER;
    nMaxAuth  NUMBER;
    
    cCanPost CHAR DEFAULT '0';
    cCanAuth CHAR DEFAULT '0';
    
    cTest     CHAR;
  BEGIN
    -- cCanPost := RIGHTS_MANAGEMENT.CHECK_RIGHTS('LEDGER::JOURNAL_POST');
    cCanPost := '1';
  end;
  Begin
      null;
  end;
/

Package body created.

As a test, I suggest that you copy and paste the code, line-by-line if necessary, to confirm a clean compile of your code. In fact, you can begin with copying and pasting my code (which we know works), and getting it to run first, then start tailoring it to you specific needs.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Burser]

I should applogise here - I know my code compiles, and I know it runs successfully because I've executed it from OSD and it ran just fine.

It even gave me the correct answer, which was nice

The issues here only appear when I try to make the call via ADO. I'm thinking there is a problem with the ADO RPC call to the database. Thing is, I dont have DBA rights to this server so I can't see what ADO is asking Oracle to do - and I can't work out how to get ADO to tell me what it's calling.

This is driving me mad No matter what I do - I can't seem to find an explanation.

 

[SantaMufasa]

This is driving me mad No matter what I do - I can't seem to find an explanation.

It seems to me that ADO RPC is attaching extraneous charaters to your otherwise-pristine SQL code. If this was happening to me, I would get technical support from ADO RPC involved.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Burser]

Well - it's the standard ADO remote procedure call for calling stored procedures from the ADODB library in VBA (Command type adStoredProcedure). You'd think someone else would have come across this problem, wouldn't you.

The reason I came here is because I'm seeing Oracle error messages - wanted to check that first. So - you think it's playing silly buggers with additional characters, do you? I bet it's an encoding error in the way it handles adDouble - that's the only explanation.

What a mess.

 

[SantaMufasa]

Burser,

If you can save the contents of the erroneous SQL to a flat file, then you can use the following code (which you can run as a script file from SQL*Plus) to treat that flat file as a single-column table. Once the flat file is accessible as an Oracle external table, you can use Oracle SQL functions to identify whether there are spurious characters in your code:

accept ExtTable prompt "Enter the name of the new External Table: "
accept FlatFile prompt "Enter the name of the single-column, source flat file residing on disk in the folder to which directory sqldba points: "
create table &ExtTable (col1 varchar2(2000))
    organization external
        (type oracle_loader
         default directory sqldba
         access parameters
             (records delimited by newline)
         location ('&FlatFile')
        )
    reject limit unlimited;
select count(*) from &ExtTable;

Let us know if this is helpful in diagnosing your problem.


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.