Solved

how to return received_by volumn from above insert select statement

Posted on 2015-01-01
13
223 Views
Last Modified: 2015-05-11
INSERT INTO TEST(SEQ_ID,FLAG,CREATE_DATE,LAST_updated,received_by)  
                              SELECT SEQ_TBL_SQ.nextval,:flag,sysdate,sysdate FROM DUAL
                                    WHERE NOT EXISTS (SELECT 1 FROM TEST WHERE MATERIAL_ID = '1')
                                    
                                    how to return received_by column value from above insert select statement.
0
Comment
Question by:chaitu chaitu
  • 7
  • 5
13 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 40527264
use the RETURNING clause in pl/sql

for example

DECLARE
    v_received_by   test.received_by%TYPE;
BEGIN
    INSERT INTO TEST(SEQ_ID,
                     FLAG,
                     CREATE_DATE,
                     LAST_updated,
                     received_by)
        SELECT SEQ_TBL_SQ.NEXTVAL,
               :flag,
               SYSDATE,
               SYSDATE
          FROM DUAL
         WHERE NOT EXISTS
                   (SELECT 1
                      FROM TEST
                     WHERE MATERIAL_ID = '1')
      RETURNING received_by
           INTO v_received_by;

    DBMS_OUTPUT.put_line('received_by: ' || TO_CHAR(v_received_by, 'yyyy-mm-dd hh24:mi:ss'));
END;

Open in new window



alternately, set all of the values to variables prior to the insert, then insert the variables.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 40527266
without pl/sql block,can't we do with single insert --select statement..
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40527268
no

RETURNING is part of pl/sql
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 40527272
getting compilation error saying that at RETURNING STATEMENT ,orderby clause expected.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40527280
oops, I forgot you can't use the RETURNING with insert/select,  only insert values.

so, use the variable method.

also, your insert/select wouldn't work as written anyway.  
You are inserting into 5 columns, but your select only returns 4 values.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 40527281
what is  variable method?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 40527283
from the first post...

set all of the values to variables prior to the insert, then insert the variables.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 40527286
i need returning value with insert into ..select statment..
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40527288
there is no syntax to support exactly what you want to do.

you must use an alternate.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40527934
How do you expect the "received_by" column value to be set?  Does the table have a trigger that assigns this value?  If that is true, then something like this should work:

(But this example doesn't include your "WHERE NOT EXISTS" check.  That would need to be added, if you need it.)

DECLARE
                 v_received_by   test.received_by%TYPE;
      v_when                date;
      v_seq_id            test.seq_id%TYPE;
BEGIN
      SELECT SEQ_TBL_SQ.NEXTVAL, sysdate
      into v_seq_id, v_when
      from dual;
    INSERT INTO TEST(SEQ_ID,
                     FLAG,
                     CREATE_DATE,
                     LAST_updated)
        values (v_seq_id,
               :flag,
               v_when,
               v_when)
      RETURNING received_by
           INTO v_received_by;
    DBMS_OUTPUT.put_line('received_by: ' || TO_CHAR(v_received_by, 'yyyy-mm-dd hh24:mi:ss'));
END;
/
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40770216
why the penalty grade?
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 40770247
had you given alternate solution then i would have given you A grade.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40771496
I did in the vary first post  2 methods.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now