Re: Two PL/SQL questions... Help...

Jimmy wrote in message <37A6A23E.29333FC0_at_comp.polyu.edu.hk>...
> 1) I have a procedure as follows:
>
> procedure testing(id in aaa.id%type, code in aaa.code%type) is
> .....
>
> Can I declare the variable id and code is dynamic? i.e. id sometimes
>is the type of aaa.id, sometimes is the type bbb.id, sometimes is
>ccc.id2...

The ...%TYPE is expanded at *compile* time to the underlying Oracle data type for that column. After that, anything of the same *underlying* data type will be acceptable as a parameter.

If aaa.id and bbb.id are of different data types, you can try overloading:

    PROCEDURE Testing( id IN VARCHAR2 ) IS ...     PROCEDURE Testing( id IN NUMBER ) IS

        BEGIN
        Testing( TO_CHAR( id ) ) ;
        END ;

PL/SQL will automatically resolve to the appropriate [Testing] by matching the parameter types with which it is called to those in the specification. This does assume that both [Testing]s are contained within a single PACKAGE.

> 2) If I have a package as follows:
>
> create or replace package testing is
> procedure hello;
> end testing;
>
> create or replace package body testing is
> number1 number;
>
> procedure hello is
> begin
> number1 := 0;
> end start1;
> end testing;
>
> If a user A using SQL*Plus (or something else such as Oracle Forms)
>to exec procedure hello in package testing, at the same time, another
>user B also exec hello in package testing using SQL*Plus or Oracle
>Forms. Is the value of variable number1 has different values in two
>executions? i.e. each user see only his copy of value of variable
>number1, user A change number1 value does not affect the value of
>number1 of user B.

Yes. Each session gets its own copy of the (read/write) data portion of the package. One would hope the (readonly) code is shared.

If you want two sessions to share data, pass it via a table (unless you want to use DBMS_Pipe or some such, I suppose?). Gee, sharing data via a Table: isn't that what a data base is supposed to be for?? :-)

HTH ... Barry Received on Tue Aug 03 1999 - 15:24:10 CDT