Re: DBMS_SQL - PL/SQL Incompatibility?

>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
>
>A) Forms 5.0.6.8.0 - PL/SQL rel. 2.3.4.0.0
>B) Forms 6.0.5.0.2 - PL/SQL rel. 8.0.5.1.0
>
>C) RDBMS 7.3.4 - PL/SQL 2.3.4.0.0
>D) 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,

I would recommend that you not assign i_DBMS_SQL_NATIVE to 1,, but rather to the standard values (whatever they are). My calls are DBMS_PARSE(..., DBMS_SQL.NATIVE). What might help though is that there are other values for the value, in particular, "V7"...

DBMS_PARSE(...,DBMS_SQL.V7). That might help, but that might have already been done by setting it to 1,2,3... that you tried.

Dan Hekimian-WIlliams Received on Sat May 13 2000 - 00:00:00 CEST