Re: Calling stored procedures from SQL*PLUS

In article <19990110213127.08218.00010839_at_ng-fb1.aol.com>,   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.
>
> Thanks,
> Tim Kitchens
>

To see the output in SQL*Plus:

declare out_var my_package.my_char_table; nJ BINARY_INTEGER;
begin
my_package.my_procedure(out_var);
FOR nJ IN 1..out_var.COUNT LOOP
  dbms_output.put_line(out_var(nJ));
END LOOP;
end;
/

Oh, yes - don't forget to 'set serveroutput on' in PL*SQL, so that you'll be able to see the results of dbms_output.put_line().

Igor

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Jan 12 1999 - 15:34:07 CST