Re: Execute a stored procedure from OCI

From: Rolf C Stadheim <r-stadhe_at_online.no>
Date: Mon, 7 Jan 2002 15:53:56 +0100
Message-ID: <1Fi_7.22015$KQ3.390584@news1.oke.nextra.no>

For the statement 'BEGIN INSERT_ROOM(45) END;' I get the error: ORA-06550: ERROR LINE 1 CHAR -- PLS-00103: END OCCURRED WHERE ONE OF THE FOLLOWING WERE EXPECTED:
:= . ( % ;

The Stored Procedure:

PROCEDURE INSERT_ROM

     (
      romfnrx IN VARCHAR2,
      bruksromnrx IN VARCHAR2,
      fil_idx IN NUMBER,
      acadhandlex IN VARCHAR2,
      geonrx IN VARCHAR2,
      navn1x IN VARCHAR2,
      navn2x IN VARCHAR2
     )
     IS
      idx NUMBER(8,0);
     BEGIN
      SELECT MAX(ID) INTO idx FROM ROM WHERE PROSJEKT_ID=1;
      idx := idx + 1;
      INSERT INTO ROM (ID, PROSJEKT_ID, ROMFNR,BRUKSROMNR, FIL_ID,
ACADHANDLE, GEONR, NAVN1, NAVN2)
      VALUES (idx, 1, romfnrx, bruksromnrx, fil_idx, acadhandlex, geonrx,
navn1x, navn2x);
     END;

The OCI code (snipped from a C++ CORBA server implementation (OmniORB 3.04)):

void
Cursor_impl::parse(const char* sql)
{
 if (oparse(&cda_, (text *) sql, (sb4) -1,    (sword) PARSE_NO_DEFER, (ub4) PARSE_V7_LNG))  {

      oci_error();
      throw Oracle::OraException();

}

 else
 {

      sql_function = cda_.ft;
      // If the statement is a query, describe and define
      // all select-list items before doing the oexec.
      if ((ncols_ = do_binds((text *)sql)) == -1)
      {
       oci_error();
       throw Oracle::OraException();
      }
      if (sql_function == FT_SELECT)
      {
       ncols_ = describe_define();
       //cerr << "Number of columns: " << ncols_ << endl;
       if (ncols_ == -1) {
        cerr << "Error in columns!\n";
        //disconnect(&lda);
        oci_error();
        throw Oracle::OraException();
       }

  }
}

 if (ncols_ > MAX_SELECT_LIST_SIZE)
{
  ncols_ = MAX_SELECT_LIST_SIZE;
}

}

If you would, I can email you the complete sourcefile.

Thank you,
Rolf

"Cliff Dabrowski" <cliffd.ng at bigfoot.com> skrev i melding news:3c36278b$1_at_172.22.1.229...

> 1. Wrap your statement in an anonymous block as in example number 3.
> 2. Bind your statement parameters.
>
> Post full example with code (OCI, Stored Proc) and errors and I would be
> happy to give it a look-see.
>
> Hth,
>
> ~Cliff
>
>
> "Rolf C Stadheim" <r-stadhe_at_online.no> wrote in message
> news:zmlZ7.18743$KQ3.298865_at_news1.oke.nextra.no...
> > How can I execute a stored procedure (a non-select procedure that

inserts

> a
> > row) from an OCI C-routine?
> >
> > I have tried
> >
> > 'EXECUTE INSERT_ROOM(45)',
> > 'EXEC SQL INSERT_ROOM(45)',
> > 'BEGIN INSERT_ROOM(45) END;',
> > 'BEGIN INSERT_ROOM(45);'
> >
> > but all fails with varying error-messages.
> >
> > I'm using the Oracle 7.3 OCI libraries.
> >
> > RCS
> >
> >
>
>

Received on Mon Jan 07 2002 - 08:53:56 CST