1 Reply Latest reply: Apr 30, 2013 7:28 PM by davidp 2 RSS

    PLS-S-00487, Invalid reference to variable while compiling PRO*C program

    856103
      Hi,

      I am writing a PRO*C code and get an error in compilation.

      Code is as below:

      +#include <stdio.h>+
      +#include <string.h>+

      EXEC SQL INCLUDE sqlca.h;

      int main(int argc , char **argv)
      +{+

      int i;
      EXEC SQL BEGIN DECLARE SECTION;
      VARCHAR connString[100];

      struct emp_record
      +{+
      char name[25];
      char dept[10];
      int id;
      int salary;
      int comm;
      int tot_sal;
      +}e1;+

      EXEC SQL END DECLARE SECTION;

      strcpy (connString.arr, "abcd/efgh@ijkl");
      connString.len = strlen ( connString.arr);

      +printf("\n Connection is [%d][%s]",connString.len,connString.arr);+

      +EXEC SQL CONNECT :connString;+
      +if(sqlca.sqlcode!=0)+
      +{+
      +printf("\nFailed to Connect, sqlcode is %d\n",sqlca.sqlcode);+
      +printf("\nOracle Message = %s", sqlca.sqlerrm.sqlerrmc);+
      +exit(-1);+
      +}+
      +else+
      +{+
      +printf("\nConnected\n");+
      +}+

      +EXEC SQL EXECUTE+
      +BEGIN+

      +emp_calc ( :e1.name,+
      +:e1.id,+
      +:e1.salary,+
      +:e1.tot_sal+
      +);+

      +END;+
      +END-EXEC;+

      +if(sqlca.sqlcode!=0)+
      +{+
      +printf("Oracle Error:[%d][%s]",sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);+
      +}+
      +}+

      Procedure code is:
      +CREATE OR REPLACE PROCEDURE "EMP_CALC" (+
      +pi_empname in emp.ename%type,+
      +pi_empno in emp.empno%type,+
      +pi_sal in emp.sal%type,+
      +po_totsal out NUMBER)+
      +is+
      +begin+

      +po_totsal := 1.1 * pi_sal;+

      +dbms_output.put_line('Total Salary is:' || po_totsal);+

      +end;+

      *My main aim is to use elements of a structure in procedure call as parameters*
      *But when I compile I get error on such usage as follows:*

      Pro*C/C++: Release 11.2.0.3.0 - Production on Tue Apr 30 16:59:22 2013

      Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

      System default option values taken from: /mtsapp1/ora11g/product/11.2.0.3/dbhome_1/precomp/admin/pcscfg.cfg

      Error at line 52, column 20 in file singleStructOra.pc
      emp_calc ( :e1.name,
      ...................1
      PLS-S-00487, Invalid reference to variable 'E1'
      Error at line 52, column 1 in file singleStructOra.pc
      emp_calc ( :e1.name,
      1
      PLS-S-00000, Statement ignored
      Semantic error at line 50, column 1, file singleStructOra.pc:
      BEGIN
      1
      PCC-S-02346, PL/SQL found semantic errors


      *I would like to know how to resolve this compilation error and how do I use members of structures in such a procedure call.*

      Thanks....
        • 1. Re: PLS-S-00487, Invalid reference to variable while compiling PRO*C program
          davidp 2
          Sadly PL/SQL blocks do not understand structs (or arrays). DML statements do understand them.
          This is not very clearly stated in the manual, but it does say:
          http://docs.oracle.com/cd/E11882_01/appdev.112/e10825/pc_08arr.htm#autoId33
          Restrictions on Arrays of Structs
          The following restrictions apply to the use of arrays of structs in Pro*C/C++:
          Arrays of structs (just as with ordinary structs) are not permitted inside an embedded PL/SQL block.

          http://docs.oracle.com/cd/E11882_01/appdev.112/e10825/pc_07pls.htm#autoId13
          Do not use C pointer or array syntax in PL/SQL blocks. The PL/SQL compiler does not understand C host-variable expressions and is, therefore, unable to parse them.

          Although the example uses an array of structs, simple structs also fail. I have always had to copy the fields to and from simple variables when calling stored procedures.

          regards,
          David