Solved

how to return received_by volumn from above insert select statement

Posted on 2015-01-01
13
234 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 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 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Subquery bad Join 11 59
Insert and update a row at the same time 4 52
Web Service from a stored procdure oracle 10 49
oracle- set role and grant privileges 6 0
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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

920 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

11 Experts available now in Live!

Get 1:1 Help Now