CODEWITHSUNDEEP
oracle-databaseplsqltoad
user2124167
user2124167

Reputation: 205

Get the result of Select query into a variable and insert it as a column value in another table

DECLARE count NUMBER;

SELECT max(to_number(param_code))+1 INTO count from abc where interface_code='PQR' ;

INSERT INTO abc
( INTERFACE_CODE
, PARAM_CODE
, DSCR
, PARAM_CHAR
, PARAM_NUMBER
, PARAM_DATE
, TRGA_CODE
)
VALUES
(  'PQR'
,   count 
,   'xyz'
,   ''     
,  ''        
,   ''         
,   '*'
)

I want to execute these 3 statements as a script in Oracle. But this is giving me error.

Please help

Upvotes: 2

Views: 13740

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

DECLARE
  MY_COUNT NUMBER;
BEGIN
  SELECT MAX(to_number(param_code))+1
  INTO MY_COUNT
  FROM abc
  WHERE interface_code='PQR' ;

  INSERT
  INTO abc
    (
      INTERFACE_CODE ,
      PARAM_CODE ,
      DSCR ,
      PARAM_CHAR ,
      PARAM_NUMBER ,
      PARAM_DATE ,
      TRGA_CODE
    )
    VALUES
    (
      'PQR' ,
      MY_COUNT ,
      'xyz' ,
      '' ,
      '' ,
      '' ,
      '*'
    );
EXCEPTION
WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Upvotes: 4

Related Questions