How to insert values from Variable and table in single query

vinod_pawar1 · July 1st, 2010, 10:47 AM

Hello All,

I am stuck with a problem. Could somebody show me as to how to accomplish following thing in Oracle 10g?

I need to insert 4 different claim number from a table along with a constant value from variable.

for example:

'some value', 123456
'some value', 223344
'some value', 555555
'some value', 123457

Possible Solution:
=================

DECLARE
XCONST VARCHAR2(20);
BEGIN
XCONST:= 'some value';
INSERT INTO A_TABLE(SOME_VALUE, CLAIMNUMBER)
SELECT (SELECT XTEST FROM DUAL), CLAIMNUMBER FROM TBLVOUCHERS WHERE ROWNUM <=4;
END;

Also I would want to avoid using any loop or cursor here. Is it possible?

Thank in Advance
Vinod

vinod_pawar1 · July 2nd, 2010, 12:40 AM

DECLARE
XCONST VARCHAR2(20);
BEGIN
XCONST := 'some value';
EXECUTE IMMEDIATE 'INSERT INTO A_TABLE(SOME_VALUE, CLAIMNUMBER) SELECT (SELECT :b1 FROM DUAL), CLAIMNUMBER FROM TBLVOUCHERS WHERE ROWNUM <=4'
USING XCONST;
END;

In the code :b1 is a bind variable and "USING" replaces bind variable value with the passed value (passed variable value).

Thanks

joefawcett · July 2nd, 2010, 02:54 AM

I haven't programmed with Oracle for ages but I can't believe you need to use dynamic SQL for this simple task. With SQL Server the code would resemble (allowing for Oracle's syntax):

Code:

SELECT :XConst, CLAIMNUMBER FROM TBLVOUCHERS WHERE ROWNUM <=4;

vinod_pawar1 · July 2nd, 2010, 12:12 PM

ugh,

Not sure, I guess I was doing something wrong when I tried to select variable along with other fields of a table and it showed errors all the time.

BTW, thank you for your response, this saved me a lot of painful time.

You Rock!

Thanks
Vinod