RE: creating a procedure

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 23 Jan 2002 17:10:50 -0800
Message-ID: <F001.003F9555.20020123163026@fatcity.com>

> -----Original Message-----
> From: Lance Prais [mailto:lprais_at_ts.checkpoint.com]
>
> I want to create a stored procedure that takes the following
> information and
> puts it into a table.  How would I do this?
>
> CREATE OR REPLACE Procedure Used_license as
> Begin
> Select A.Pc_session_id From pt_client_event A where
> A.pc_event_op = 'LAUNCH'
> and A.pc_date >=SYSDATE -1
> minus
> Select B.Pc_session_id From pt_client_event B where
> B.Pc_event_op = 'LOGOUT'
> and B.pc_date >=SYSDATE -1
> End;
>
> I am tying to do it this way but getting errors:
>
> PLS-00103: Encountered the symbol "END" when expecting one of
> the following:
>

>    * & - + ; / for mod rem an exponent (**) and or group having >    intersect minus order start union where connect ||

You are going to have to store the result of a query into a variable. Does the query return one row or more? If you just want to put the results of your query in to a table, use an "insert ... select ..."

Some examples:

create or replace procedure x
as
   my_dummy sys.dual.dummy%type ;
   my_object_name sys.dba_objects.object_name%type ;

   cursor c_obj_name (c_owner in varchar2) is     select object_name from dba_objects where owner = c_owner ; begin
  /* example of query that returns only one row */    select dummy into my_dummy from dual ;

  /* example of query that returns several rows */
   for c_obj_rec in c_obj_name ('SYSTEM')
   loop
      my_object_name := c_obj_rec.object_name ;
   end loop ;

end ;
/ Received on Wed Jan 23 2002 - 19:10:50 CST