SELECT INTO STATEMENT INSIDE A SQL PROCEDURE THROWING AN ERROR-CodePudding

Hi I want to store a avalue of select statement into a variable and then update my table using that variable within the procedure but there is an error . I still dont know its only returning one column then also below error exists. exact fetch returns more than requested number of rows.

Here is the example of demo code.Can anyone please give me an alternative of what else I can do here to make it work since I have many such plsql statement to populate table columns

create or replace procedure pcountry (country IN Varchar) is
var_date Date;
begin

select date into var_date from countrytable where country=country;

update newtable
set date=var_date
where country=country
commit;

end pcountry;

CodePudding user response：

You need to change the name of your procedure'input argument since you have a column with the same name in your table. Oracle is interpretting your where clause where country = country as where 1 = 1 which is always true. So it returns more rows instead of one row.

create or replace procedure pcountry (country_in IN Varchar) is
var_date Date;
begin

select date into var_date from countrytable where country = country_in ;

update newtable
set date=var_date
where country= country_in 
commit;

end pcountry;

CodePudding user response：

Don't name your PL/SQL variables/arguments with the same name as columns. The SQL engine will look for the country value in the local SQL scope in preference to the outer PL/SQL scope so country=country is (assuming non-NULL values) the same as 1=1 and you will match all rows.

Assuming each country is unique then:

create or replace procedure pcountry (
  v_country IN COUNTRY_TABLE.COUNTRY%TYPE
) is
  var_date COUNTRY_TABLE."DATE"%TYPE;
begin
  select "DATE"
  into var_date
  from countrytable
  where country=v_country;

  update newtable
  set "DATE"=var_date
  where country=v_country
end pcountry;
/

Also, DATE is a reserved word and you cannot use is as an unquoted identifier.

You can combine it into a single SQL statement using MERGE:

CREATE PROCEDURE pcountry (
  v_country IN COUNTRY_TABLE.COUNTRY%TYPE
) is
  var_date COUNTRY_TABLE."DATE"%TYPE;
BEGIN
  MERGE INTO newtable n
  USING (SELECT *
         FROM   countrytable
         WHERE  country = v_country) c
  ON (c.country = n.country)
  WHEN MATCHED THEN
    UPDATE
    SET "DATE" = c."DATE";
END pcountry;
/

CodePudding user response：

Skip select, switch to merge.

CREATE OR REPLACE PROCEDURE pcountry (par_country IN varchr2)
IS
BEGIN
   MERGE INTO newtable n
        USING countrytable c
           ON (c.country = n.country)
   WHEN MATCHED
   THEN
      UPDATE SET n.date_column = c.date_column
   WHERE n.country = par_country;
END;
/

CodePudding user response：

The issue, as has been explained in other answers, is that it makes no sense to expect country in a condition like country = country to mean different things on the two sides of the equal sign. The name country has more than one meaning - then a set of rules is applied to figure out which meaning is to be accepted each time the name is used. That is usually the narrowest context ("scope") in which the name exists; in this case, the name exists in the table referenced in the SQL statement, so that's what country means there.

One solution is simple - use a different name for the parameter used in the procedure. This has also been shown in the other answers.

There is another solution though. It might be preferred if your procedure was already very long, it used a parameter name like country, and now you would need to add some code where you need to use this name in a SQL statement. It would be pretty time-consuming to change the parameter name everywhere. Happily, PL/SQL understands qualified names. country (where you used it in the where clause) is the column name for the table referenced in the query. But if you write pcountry.country on the right-hand side, qualifying the variable name with the name of the procedure, no confusion would arise anymore.

... where country = pcountry.country

will achieve the same result as the other proposed answers in this thread. The right-hand side is the parameter or variable coming from the procedure, not the column name from the table.

Note that you could also qualify the left-hand side:

... where countrytable.country = pcountry.country

and perhaps this would be clearer to future readers.

However, this would not help:

... where countrytable.country = country

can you see why?