CODEWITHSUNDEEP
sqloracle-databaseplsql
Delta1x
Delta1x

Reputation: 93

Create View in procedure PL/SQL

i've had a look on the forum but at the moment theres no answer to this question. Basically i'm trying to create a view inside my procedure as its easier than a complicated select statement that doesnt seem to be working because its returning 'ORA-01422 exact fetch returns more than requested number of rows'.I've put the statement at the bottom for reference

However the view option doesnt seem to be working its return another error 'Encountered the symbol "CREATE" when expecting one of the following:'. Is there something i'm doing wrong?

Thanks for having a look.

procedure proRekt (par_id number) is 
  vardec farm.parcel.description%type;
  vland farm.landuse.landuse_id%type;
  v_luse farm.landuse.landuse%type;
  varpl farm.parcel.parcel_id%type;

begin  
 create or replace view Door as
select a.parcel_id,a.description,b.landuse_id,c.landuse
from 
     farm.parcel a,
     farm.parcel_landuse b,
     farm.landuse c
where a.parcel_id = b.parcel_id
  and b.landuse_id = c.landuse_id;

 select parcel_id,description,landuse
    into varpl,
         vardec,
         vland
  from   door
  where  parcel_id = parID;

**** faulty select statement 

select a.parcel_id,c.description,a.landuse_id,b.landuse
into   varpl,
       vardec,
       vland,
       v_luse
from   farm.parcel_landuse a,
       farm.landuse b,
       farm.parcel c
where  c.parcel_id = parID
  and  a.landuse_id = b.landuse_id
  and  a.parcel_id = c.parcel_id;

Upvotes: 0

Views: 8053

Answers (1)

MT0
MT0

Reputation: 168771

Create the view in the SQL scope:

create or replace view Door as
select a.parcel_id,
       a.description,
       b.landuse_id,
       c.landuse
from   tithedb.parcel a
       INNER JOIN tithedb.parcel_landuse b
       ON ( a.parcel_id = b.parcel_id )
       INNER JOIN tithedb.landuse c
       ON ( b.landuse_id = c.landuse_id );

Use it in the PL/SQL scope of the procedure:

procedure proexempt (parID number)
is 
  vardescription tithedb.parcel.description%type;
  varland tithedb.landuse.landuse_id%type;
  varlanduse tithedb.landuse.landuse%type;
  varparcel tithedb.parcel.parcel_id%type;
begin  
  select parcel_id,
         description,
         landuse
  into   varparcel,
         vardescription,
         varlanduse
  from   door
  where  parcel_id = parID;

  -- ... more stuff
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL; -- Handle error here.
  WHEN TOO_MANY_ROWS THEN
    NULL; -- Handle error here (or use "AND ROWNUM = 1" in your query).
END proexempt;
/

Upvotes: 1

Related Questions