Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Unanswered: PL/SQL Machine Model (params, return, etc)

    Hi,

    I'm writing a PL/SQL program that isn't performing so well, and I was wondering if anyone knows of any good references where I can learn more about the machine model PL/SQL is operating under. If not, my specific questions are:

    1) Are parameters passed by value or by reference? (i.e. if I pass a big VARCHAR2 does it do a memcpy of the whole thing?)
    2) Same thing for return values...
    3) How do you typically access big chunks of memory from different points in a PL/SQL program? In C I would just pass around and return a pointer to it, but I don't see an analogous construct here. From experimentation, the REF facility seems very inflexible -- I would like to create a REF to an arbitrary variable in my program, like you would with a pointer in C, but all the examples I've seen assign REF vars their values out of a table.
    4) What's the closest I can get to a dynamically allocated array that I'd like to pass around my program? I was using pipelined tables, but they are incredibly slow. Now I'm using a VARRAY but it scares me because it has a max size.


    Thanks! Sorry for all the questions, but I find it's very hard to guess at what's happening under the surface here.
    Pete

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: PL/SQL Machine Model (params, return, etc)

    You could start with the Oracle PL/SQL Reference:

    http://download-west.oracle.com/docs...a96624/toc.htm

    I can tell you that IN parameters are generally passed by value, but there is a NOCOPY option to use a reference instead, which is normally used only for passing large collections as IN parameters.

    I would use an "associative array" (aka index-by table) rather than a VARRAY. These do not have a declared size like a VARRAY. If you declare the array in a package specification, it can be accessed from anywhere in your code as package_name.array_name.

    PL/SQL is mainly intended as a language for working closely with the database, and in most cases any performance problems come from badly tuned SQL and database designs rather than from the PL/SQL itself. For computationally intensive processes where you spend more time crunching arrays in memory than querying the database, Pro*C may be a better bet. (Or maybe your code can be redesigned to make use of SQL set-based processing rather than array-crunching).

  3. #3
    Join Date
    Aug 2001
    Posts
    66

    Re: PL/SQL Machine Model (params, return, etc)

    Well my experience / understanding is this...

    - IN parameters are passed by reference. NOCOPY does not apply.

    - IN OUT and OUT parameters are passed by value / copy unless NOCOPY directive is used in which case they are passed by reference (provided NOCOPY restrictions do not apply).

    - The RETURN value of a function is copied to the variable to which it is assigned.

    All of which means the most efficient way to pass a large variable is in IN OUT or OUT mode using NOCOPY (provided NOCOPY restrictions do not apply!). You might also consider holding the variable as a package global variable if copying it around is causing a problem (see usual reservations about global variables).
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: PL/SQL Machine Model (params, return, etc)

    Thanks, Padders, I got that exactly the wrong way round! Should have read the manual before opening my gob.

    (FWIW, my incorrect thought process was: for an IN parameter, you only need the value; for an IN OUT or OUT parameter you need a reference so that you know where to write the changes. Seemed so "obvious" I didn't bother to check it!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •