?
Solved

how to return received_by volumn from above insert select statement

Posted on 2015-01-01
13
Medium Priority
?
287 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

850 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