Re: Problem with parameter passing

From: padderz <member633_at_dbforums.com>
Date: Fri, 11 Jul 2003 12:06:26 +0000
Message-ID: <3099648.1057925186@dbforums.com>

Interestingly using NOCOPY compiler hint here would cause OUT parameters to be populated, for example...

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production With the Partitioning option
JServer Release 8.1.7.2.0 - Production

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2 l_param VARCHAR2 (30) := 'tata';
  3 l_status INTEGER;
  4
  5 PROCEDURE procedure_name (

  6      l_p IN VARCHAR2,
  7      l_s OUT NOCOPY INTEGER) /* note use of NOCOPY here */

  8 IS
  9 BEGIN
 10 l_s := 0;
 11

 12      IF (l_p = 'tata')
 13      THEN
 14        l_s := -1;
 15        raise_application_error (-20100, 'Different of tata');
 16      END IF;

 17 END;
 18 BEGIN
 19 procedure_name (l_param, l_status);  20 DBMS_OUTPUT.put_line ('l_status = ' || l_status);  21 EXCEPTION
 22 WHEN OTHERS
 23 THEN
 24 DBMS_OUTPUT.put_line ('exception - l_status = ' || l_status);  25 END;
 26 /
exception - l_status = -1

PL/SQL procedure successfully completed.

SQL> However, I am not sure that it would be a good idea to adopt this practice since the compiler can choose to ignore NOCOPY in some cases causing the behaviour to revert to default. These cases are...

1. The actual parameter is an element of an index-by table. This restriction does not apply to entire index-by tables.
2. The actual parameter is constrained (by scale or NOT NULL for example). This restriction does not extend to constrained elements or attributes. Also, it does not apply to size-constrained character strings.
3. The actual and formal parameters are records, one or both records were declared using %ROWTYPE or %TYPE, and constraints on corresponding fields in the records differ.
4. The actual and formal parameters are records, the actual parameter was declared (implicitly) as the index of a cursor FOR loop, and constraints on corresponding fields in the records differ.
5. Passing the actual parameter requires an implicit datatype conversion. The subprogram is involved in an external or remote procedure call.

To be clear, a change as small as constraining the number variable passed into the procedure (e.g. to NUMBER (10)) would be sufficient to prevent NOCOPY behaviour and prevent error information from being returned correctly - I would recommend you take a look at Tony's suggestions.

--
Posted via http://dbforums.com

Received on Fri Jul 11 2003 - 07:06:26 CDT