using a select query to create a record in plsql

Question

Probably a really easy question but I created a record that has 3 columns. The third column I am going to assign a value later. The query selects 2 columns for the record. What am I missing? The error that is thrown is that there are "too many values" in my select statment.

create or replace Procedure Pledges3 
(IDdonor In Int, flag Out Varchar)
as 
type allPledges is record(iddonor dd_pledge.iddonor%type, idstatus dd_status.idstatus%type, flag Varchar(25));
allPledges2 allpledges;
Begin
Select dd_pledge.iddonor, dd_status.idstatus 
  into allpledges2 
    from dd_donor
      join dd_pledge on dd_donor.iddonor=dd_pledge.iddonor
        join dd_status on dd_pledge.idstatus=dd_status.idstatus
          where dd_pledge.IDdonor=305;
    if allpledges2.idstatus = '10' THEN 
     Flag := 'True';
      elsif allpledges2.idstatus= '20' THEN 
        Flag := 'False';
    End if;
    dbms_output.put_line(flag);
End;

You can just select null as the third column, and reassign it later. — ruudvan
– ruudvan, Commented Mar 25, 2015 at 20:54

Vinish Kapoor · Accepted Answer · 2015-03-26 06:00:39Z

1

Check the below code, corrected:

create or replace Procedure Pledges3 
(IDdonor In Int, flag Out Varchar)
as 
type allPledges is record(iddonor dd_pledge.iddonor%type, idstatus dd_status.idstatus%type, flag Varchar(25));
allPledges2 allpledges;
Begin
Select dd_pledge.iddonor, dd_status.idstatus, null 
  into allpledges2 
    from dd_donor
      join dd_pledge on dd_donor.iddonor=dd_pledge.iddonor
        join dd_status on dd_pledge.idstatus=dd_status.idstatus
          where dd_pledge.IDdonor=305;
    if allpledges2.idstatus = '10' THEN 
     allPledges2.Flag := 'True';
      elsif allpledges2.idstatus= '20' THEN 
        allPledges2.Flag := 'False';
    End if;
    dbms_output.put_line(allPledges2.flag);
End;

answered Mar 26, 2015 at 6:00

Vinish Kapoor

6791 gold badge7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Luke Over a year ago

So the code compiles with no errors but I do not get the put line message for the variable flag. Is there any reason that this is happening?

Vinish Kapoor Over a year ago

Execute set serveroutput on; command before executing this procedure...then will show...

Luke Over a year ago

Well I add the set serveroutput on; before my create procedure statement and I get the error message "missing or invalid option" and the message points to this line of code. I then call the procedure from new procedure and put set severoutput on; before the new procedure and take that statement out of the calling procedure and I still receive the same error message.

Vinish Kapoor Over a year ago

where are you executing ur statement in sqlplus on in sql developer ? if sqlplus then just run first serveroutput then execute procedure if developer then run as script...

Luke Over a year ago

in sqldeveloper. So all I need to do is run the set serveroutput on statement once at that turns it on for the whole session?

|

Collectives™ on Stack Overflow

using a select query to create a record in plsql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related