[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

how to return received_by volumn from above insert select statement

Posted on 2015-01-01
13
Medium Priority
?
282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 74

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 74

Expert Comment

by:sdstuber
ID: 40527268
no

RETURNING is part of pl/sql
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 20

Author Comment

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

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
 
LVL 74

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 74

Accepted Solution

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

you must use an alternate.
0
 
LVL 35

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 74

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 74

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

649 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