CODEWITHSUNDEEP
sqloracle-databasestored-procedures
Amandeep
Amandeep

Reputation: 13

Insert into table with one column value as select query

My code is something like this

insert into table (A,B,C,D) values
('abb','select appCode,pkid from table where party_id='||party_id,sysdate,user);

The party_id here is varchar and it comes as IN clause in Stored proc, So if partyid comes as 123 in stored procedure Then I want to see the values in database as select appCode, pkid from table where party_id='123'

but thats not happening instead it goes as select appCode, pkid from table where party_id=123

sugestions please.

Upvotes: 1

Views: 8238

Answers (2)

Jacob
Jacob

Reputation: 14741

Try the following approach

CREATE TABLE tab1
(
    t1   VARCHAR2 (100),
    t2   VARCHAR2 (100),
    d1   DATE,
    t3   VARCHAR2 (100)
);

CREATE OR REPLACE PROCEDURE test_proc (param user_objects.object_type%TYPE)
IS
BEGIN
 INSERT INTO  tab1 (t1,t2,d1,t3) values
          ('abc','SELECT object_id  
            FROM     user_objects
          WHERE     object_type = '''||param||'''',SYSDATE,'scott');
END;

and execute procedure 

EXEC test_proc('TABLE')

Upvotes: 0

Chamal
Chamal

Reputation: 1449

Try this

insert into table (A,B,C,D) values
('abb'
,'select appCode,pkid from table where party_id=''' || party_id || ''''
,sysdate
,user);

Upvotes: 2

Related Questions