Re: PL-SQL questions ? Please give a hand !!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 21 Jun 1999 12:47:30 GMT
Message-ID: <377533dd.6135822@newshost.us.oracle.com>

A copy of this was sent to "Chow Hoi Ka, Eric" <d951686_at_sftw.umac.mo> (if that email address didn't require changing) On Sun, 20 Jun 1999 20:31:11 +0800, you wrote:

>Hello,
>
>1) Does PL-SQL has ARRAY ??? How ??
>

yes, they are called plsql tables. for example:

declare

   type myArray is table of number index by binary_integer;

   array myArray;
begin

   for i in 1 .. 100 loop

      array(i) := i;
   end loop;

   for j in 1 .. array.count loop

      dbms_output.put_line( array(j) );    end loop;
end;

>
>Is it possible to do as following ?
>
>tmpVar := 'procA';
>exec tmpVar;
>

dynamic sql. for example

create or replace procedure execute_immediate( sql_stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

end;
/

create or replace run_procedure( p_proc_name in varchar2 ) as
begin

   execute_immediate( 'begin ' || p_proc_name || '; end;' ); end;
/

>It means that I want to execute another store procedure but the
>procedure name will be changed frequently. Therefore, I want to use a
>variable to store the procedure name.
>Such as the above. tmpVar is storing the procedure name 'procA'.
>So, how to implement this ???
>
>
>
>Best regards,
>Eric

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 21 1999 - 07:47:30 CDT