Re: Calling stored procedures from SQL*PLUS

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 11 Jan 1999 21:00:25 GMT
Message-ID: <369c64f4.11766088@inet16.us.oracle.com>

On 11 Jan 1999 02:31:27 GMT, timkit_at_aol.com (TimKit) wrote:

>
>More precisely, my question regards calling procedures which return PL/SQL
>tables. For example, say I have compiled the following simple package and
>procedure:
>
>CREATE OR REPLACE PACKAGE my_package
>IS
> TYPE my_char_table IS TABLE OF varchar2(100) INDEX BY BINARY_INTEGER;
>
> PROCEDURE my_procedure ( my_parameter my_char_table );
>END my_package;
>
>CREATE OR REPLACE PACKAGE BODY my_package
>IS
> PROCEDURE my_procedure ( my_parameter my_char_table )
> IS
> BEGIN
> . . . code to fetch into my_char_table . . .
> END my_procedure;
>
>END my_package;
>
>Now, how would I declare an input variable to test this package.procedure via
>SQL*PLUS? I can't seem to find any examples and simply declaring an equivalent
>table of varchar2's doesn't work. I'd certainly appreciate any help anyone
>could give me on this one.
>

You can make an anonymous pl/sql block from sql*plus.

eg.

Assuming the package body looks like...

  1 CREATE OR REPLACE PACKAGE BODY my_package   2 IS

  3      PROCEDURE my_procedure ( my_parameter my_char_table )
  4      IS
  5      BEGIN
  6        for i in 1 .. my_parameter.count loop
  7          dbms_output.put_line( my_parameter(i) );
  8        end loop;
  9      END my_procedure;

 10* END my_package;

then ...

SQL> declare
  2 inputVariable my_package.my_char_table;   3 begin
  4 inputVariable(1) := 'Foo';
  5 inputVariable(2) := 'Bar';
  6 my_package.my_procedure( inputVariable );   7 end;
  8 /
Foo
Bar

PL/SQL procedure successfully completed.

hope this helps.

chris.

>Thanks,
>Tim Kitchens

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jan 11 1999 - 15:00:25 CST