Select into Using Oracle

SELECT INTO using Oracle

If NEW_TABLE already exists then ...

insert into new_table 
select * from old_table
/

If you want to create NEW_TABLE based on the records in OLD_TABLE ...

create table new_table as 
select * from old_table
/

If the purpose is to create a new but empty table then use a WHERE clause with a condition which can never be true:

create table new_table as 
select * from old_table
where 1 = 2
/

Remember that CREATE TABLE ... AS SELECT creates only a table with the same projection as the source table. The new table does not have any constraints, triggers or indexes which the original table might have. Those still have to be added manually (if they are required).

Select Into Variable and use it into IF (oracle)

Wrong syntax. Should be

SQL> set serveroutput on
SQL> declare
  2    var1 varchar2(100);
  3    check_s1 varchar2(100) := 'select dummy from dual';
  4  begin
  5    execute immediate check_s1 into var1;
  6
  7    if var1 is null then
  8       dbms_output.put_line('var1 is null');
  9    else
 10       dbms_output.put_line('var1 = ' || var1);
 11    end if;
 12  end;
 13  /
var1 = X

PL/SQL procedure successfully completed.

SQL>

Oracle PL/SQL SELECT INTO clause thinks it needs another INTO

Issue is in calling statement.

Whenever select statement is used in plsql block it must have into clause to assign return value to variable.

You should remove begin and end from your calling code:

--begin -- remove this
    select test('1', 16000) from dual;
--end; -- remove this

Or if you want to use it in plsql block then add into clause:

Declare
Area_ float(precision);
begin
    select test('1', 16000) into area_ from dual;
    -- use area_ in your code wherever required
    dbms_output.put_line('area: ' || area_);
end;

Cheers!!

Creating new table with SELECT INTO in SQL

The syntax for creating a new table is

CREATE TABLE new_table
AS
SELECT *
  FROM old_table

This will create a new table named new_table with whatever columns are in old_table and copy the data over. It will not replicate the constraints on the table, it won't replicate the storage attributes, and it won't replicate any triggers defined on the table.

SELECT INTO is used in PL/SQL when you want to fetch data from a table into a local variable in your PL/SQL block.

PL/SQL Select After Select Into in Oracle 11g

Change your code so that the last SELECT is used as a cursor, as in:

declare
   v_statusIdActive NUMBER;
   v_requestTypeId NUMBER;
   v_licenseTypeId NUMBER; 
   v_licenseCategoryId NUMBER;

begin
  select RefStatusId 
    into v_statusIdActive
    from RefStatus
    where StatusNameEn = 'Active' and
          rownum = 1;

  SELECT REQUESTTYPEID, LICENSETYPEID, LICENSECATEGORYID 
    INTO v_requestTypeId, v_licenseTypeId, v_licenseCategoryId
    FROM REQUEST
    WHERE REQUESTID = 78 and
          rownum = 1;

  FOR aRow IN (select *
                 from FeeRequestMapping frm
                 inner join Fee f
                   on frm.FeeId = f.FeeId
                 where frm.RequestTypeId = v_requestTypeId and 
                       frm.LicenseTypeId = v_licenseTypeId and 
                       frm.LicenseCategoryId = v_licenseCategoryId and 
                       frm.RefStatusId = v_statusIdActive)
  LOOP
    NULL;  -- add processing for each 'aRow' returned by the cursor here
  END LOOP;
END;

Best of luck.

Select into variables from cte using if oracle

In your code:

-- Start of first SELECT statement.
WITH test_cte AS (
  SELECT *
  from(
    SELECT date_a,lastName,firstName,birthDate, rank() over(ORDER BY date_a desc) rnk
    FROM test_table a
    join test_table b ON b.id = a.bid 
  )a1
  WHERE rnk =1
)
SELECT count(*) into count_a 
FROM test_table a
join test_table b ON b.id = a.bid
WHERE a.code = code_name;
-- End of first SELECT statement.

IF count_a > 0 THEN
  -- Start of second SELECT statement.
  SELECT date_a,lastName,firstName,birthDate
  into date_a_var,lastName_var,firstName_var,birthDate_var
  FROM test_cte;
  -- End of second SELECT statement.
ELSE 
  date_a_var := NULL;
  lastName_var := NULL;
  firstName_var := NULL;
  birthDate_var := NULL;
END IF;

It won't work because the test_cte only exists for the one statement and when you finish the statement's final SELECT statement then it no longer exists for the subsequent statements. (However, you do not use the sub-query factoring clause [a.k.a. CTE] in the SELECT for that statement so it is not clear why you need the WITH clause.)

Instead of trying to use COUNT, just get the data and handle the NO_DATA_FOUND exception if it occurs (also, from Oracle 12, you don't need to use RANK and can use FETCH FIRST ROW WITH TIES instead):

DECLARE
  date_a_var    test_table.date_a%TYPE;
  lastName_var  test_table.lastname%TYPE;
  firstName_var test_table.firstname%TYPE;
  birthDate_var test_table.birthdate%TYPE;
BEGIN
  BEGIN
    SELECT date_a,
           lastName,
           firstName,
           birthDate
    INTO   date_a_var,
           lastName_var,
           firstName_var,
           birthDate_var
    FROM   test_table a
           join test_table b ON b.id = a.bid 
    ORDER BY date_a DESC
    FETCH FIRST ROW WITH TIES;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      date_a_var    := NULL;
      lastName_var  := NULL;
      firstName_var := NULL;
      birthDate_var := NULL;
  END;

  -- Continue processing
END;

(Note: You may also get a TOO_MANY_ROWS exception if there are duplicate dates. Either use FETCH FIRST ROW ONLY or, before Oracle 12, the ROW_NUMBER analytic function.)

Oracle PL/SQL Select into variable using WITH clause

If you have no reasons I can't see, you don't need two tables in your WITH clause; you could simplify it this way:

WITH ALL_VE_ERRORS AS (    
    SELECT *
    FROM ASN.AN_VALIDATION_ERRORS
    WHERE ...    
)
SELECT UNIT_DISCREPANCY
INTO W_UNIT_DISCREPANCY
FROM ALL_VE_ERRORS
WHERE ...

Otherwise, you can use:

WITH ALL_VE_ERRORS AS (    
        SELECT *
        FROM ASN.AN_VALIDATION_ERRORS
        WHERE ...    
    ), FILTER_STATUS AS (    
        SELECT *
        FROM ALL_VE_ERRORS
        WHERE ...    
      )
SELECT UNIT_DISCREPANCY
INTO W_UNIT_DISCREPANCY
FROM FILTER_STATUS 

---

Related Topics