CODEWITHSUNDEEP
sqloracle-databaseplsql
oizys
oizys

Reputation: 1

How to pass a parameter once for multiple select

I am currently trying to declare a parameter with which I can use it in several select, when I try to launch the current query it puts me several errors in particular :

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following

Do you have an idea or guide me to the right voice, please?

declare
 Bon  NUMBER(20) := &1; --Example : 12345678
begin
 select * 
   from BonTable
  where BonChamp = Bon
    and BonChamp2 = 'FBON'
    and BonChamp3 is not null;

 select * 
   from BonTable2
  where BonChamp4 = Bon
    and rownum = 1;
end;

I would like this to ask me my parameter then execute both select, currently it asks me the parameter but it sends me back to error

Upvotes: 0

Views: 303

Answers (1)

hotfix
hotfix

Reputation: 3396

if you do a select in plsql, you shoul use into clause. e.g. when your select Statements return always only one row you can declare a variable of type rowtype of your table: BonTable%rowtype.

if your select Statement return more than one row, you should declare a table type of BonTable%rowtype.

declare
 Bon  NUMBER(20) := &1; --Example : 12345678
 rec1 BonTable%rowtype;
 rec2 BonTable2%rowtype;
begin
 select * 
   into rec1  
   from BonTable
  where BonChamp = Bon
    and BonChamp2 = 'FBON'
    and BonChamp3 is not null;

 select * 
   into rec2 
   from BonTable2
  where BonChamp4 = Bon
    and rownum = 1;
end;

Upvotes: 4

Related Questions