Create View in procedure PL/SQL

Question

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;

not an oracle user but select into structure is wrong... select into from
or are you trying insert into multiple tables? — RoMEoMusTDiE, Mar 24 '17 at 00:31
Its pl/sql so need to select into variables bit different from sql. — Delta1x, Mar 24 '17 at 00:37
Yor seeing that error coz the query is not returning exact one row. http://stackoverflow.com/questions/19779483/pl-sql-ora-01422-exact-fetch-returns-more-than-requested-number-of-rows — Chetan, Mar 24 '17 at 00:54
If you want to create view then why it should be created via stored proc? You can directly create view and select data from it via stored proc. — Chetan, Mar 24 '17 at 00:57
I didnt think of that i'll try create the view and declare it in the annoymous block and see if that solves the problem. It just seemed a bit inelegant but if it works no problem. — Delta1x, Mar 24 '17 at 01:01
`CREATE VIEW` is an SQL command not a PL/SQL command. If you need to use it in PL/SQL you have to use `EXECUTE IMMEDIATE`. But with what you are doing it appears SQL would be just fine. — MT0, Mar 24 '17 at 01:09
Thanks for the tip, in that case , how do find a workaround for the statement at the bottom. It works fine with no joins using the parcel_id as the where condition.But i need to join another table using the same parcel_id and its returning the exact one row error. — Delta1x, Mar 24 '17 at 01:12
@maSTArHiAn: That `select into` is correct in PL/SQL. To create a new table based on a SELECT statement, you would need to use `create table ... as select ...` — , Mar 24 '17 at 12:23

score 1 · Accepted Answer · answered Mar 24 '17 at 01:16

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;
/

Thanks !!! the expection solved my problem. First ran it and it worked with the expection but for some reason it wasnt returning all the data, it was throwing the too many rows exception for most. So I did as u suggested and put And Rownum < 2 in the query , deleted the exception and its working perfect. — Delta1x, Mar 24 '17 at 02:09

Create View in procedure PL/SQL

1 Answers1