Solved

how to return received_by volumn from above insert select statement

Posted on 2015-01-01
13
267 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 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
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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