DBMS_SQL - PL/SQL Incompatibility?

From: peterlon <andrea.peterlongoNOanSPAM_at_infotn.it.invalid>
Date: 2000/05/09
Message-ID: <05b076a3.be4030e3_at_usw-ex0104-026.remarq.com>#1/1

We developed an application that is compiled and deployed at different sites using different releases of Developer and Oracle RDBMS. In particular we deal with

1. Forms 5.0.6.8.0 - PL/SQL rel. 2.3.4.0.0
2. Forms 6.0.5.0.2 - PL/SQL rel. 8.0.5.1.0
3. RDBMS 7.3.4 - PL/SQL 2.3.4.0.0
4. RDBMS 8.1.5 - PL/SQL 8.1.5

The application executes dinamic sql using the following function :

FUNCTION v_dinsql(p_v_sqlstr VARCHAR2) RETURN VARCHAR2 IS

  v_ret VARCHAR2(2000);
  n_row_processed NUMBER;
  i_source_cursor INTEGER;
  i_dummy INTEGER;
  i_DBMS_SQL_NATIVE INTEGER;
  n_sqlcode  NUMBER;

BEGIN   i_DBMS_SQL_NATIVE := 1;
  i_source_cursor := DBMS_SQL.OPEN_CURSOR;

  BEGIN     DBMS_SQL.PARSE(i_source_cursor,p_v_sqlstr,i_DBMS_SQL_NATIVE);     DBMS_SQL.DEFINE_COLUMN(i_source_cursor,1,v_ret,2000);     n_row_processed := DBMS_SQL.EXECUTE(i_source_cursor);

    v_ret := NULL;

    i_dummy := DBMS_SQL.FETCH_ROWS(i_source_cursor);     IF (i_dummy > 0) THEN
      DBMS_SQL.COLUMN_VALUE(i_source_cursor,1,v_ret);     ELSE
      v_ret := '*';
    END IF;     DBMS_SQL.CLOSE_CURSOR(i_source_cursor);

    RETURN(v_ret);
  EXCEPTION
    WHEN OTHERS THEN

      n_sqlcode := SQLCODE;
      DBMS_SQL.CLOSE_CURSOR(i_source_cursor);
      v_ret := '*';
      RETURN(v_ret);

  END;
END v_dinsql;

According to the 4 cases obtained combining Forms different releases and RDBMS different releases we obtain 3 successful execution and one failure, i.e.:

       Form rel.   |  Database  rel.   ||  Function result
       ---------------------------------------------------
          A        |         C         ||   successful
          B        |         C         ||   successful
          A        |         D         ||   failure
          B        |         D         ||   successful.

In particular the function raises error ORA-6562 when executing

  DBMS_SQL.COLUMN_VALUE(i_source_cursor,1,v_ret).

We have tried even:

1. to change i_DBMS_SQL_NATIVE values using 0, 1 and 2.
2. to change v_ret length
3. to change the sql-string passed to the function (for example select 'x' from dual) but the error persists. We think there is some kind of incompatibility between Form 5 and Oracle 8.1.5. Is there somebody who can help us in this respect? Any suggestion for a workaround to this problem? Thanks in advance,

                 Andrea

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

Received on Tue May 09 2000 - 00:00:00 CEST