| Conditional Insert statement in pl/sql [message #635819] |
Thu, 09 April 2015 07:04  |
 |
suji6281
Messages: 134
Registered: September 2014
|
Senior Member |
|
|
SQL> CREATE TABLE PS_PROJ_ACT_TBL (PROJ_ID VARCHAR2(11) NOT NULL, ACTIVITY_TYPE VARCHAR2(10), STATUS VARCHAR2(1) NOT NULL);
Table created.
Insert rows into table.
SQL> INSERT INTO PS_PROJ_ACT_TBL VALUES ('11111', 'TEST1', 'A');
1 row created.
SQL> INSERT INTO PS_PROJ_ACT_TBL VALUES ('11111', 'TEST2', 'A');
1 row created.
SQL> INSERT INTO PS_PROJ_ACT_TBL VALUES ('22222', 'TEST1', 'A');
1 row created.
SQL> INSERT INTO PS_PROJ_ACT_TBL VALUES ('22222', 'TEST3', 'A');
1 row created
could you please help me to write a PL/SQL query to insert other activity_type for the given proj_id's.
Let us assume we have the following proj_id's and activity_type's.
PROJ_ID ACTIVITY_ID
---------- ---------------
11111 TEST1
11111 TEST2
11111 TEST3
11111 TEST4
11111 TEST5
22222 TEST1
22222 TEST2
22222 TEST3
22222 TEST4
22222 TEST5
The query should be something like, it will verify the Activity_type for the proj_id and if it is not there then insert into table with that proj_id and activity_type.
finally, we should have all 5 activity_types for all the proj_ids.
Thank you.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Conditional Insert statement in pl/sql [message #635856 is a reply to message #635849] |
Fri, 10 April 2015 00:56  |
_jum
Messages: 577
Registered: February 2008
|
Senior Member |
|
|
If you want to catch the error, you could use DBMS_ERRLOG:
CREATE TABLE ps_proj_act_tbl
(
proj_id VARCHAR2(11) NOT NULL,
activity_type VARCHAR2(10),
status VARCHAR2(1) NOT NULL
);
/
ALTER TABLE ps_proj_act_tbl
ADD PRIMARY KEY (proj_id, activity_type);
BEGIN
DBMS_ERRLOG.create_error_log(dml_table_name => 'PS_PROJ_ACT_TBL');
END;
/
INSERT INTO ps_proj_act_tbl
VALUES ('11111', 'TEST1', 'A')
LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO ps_proj_act_tbl
VALUES ('11111', 'TEST2', 'A')
LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO ps_proj_act_tbl
VALUES ('22222', 'TEST1', 'A')
LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO ps_proj_act_tbl
VALUES ('22222', 'TEST3', 'A')
LOG ERRORS REJECT LIMIT UNLIMITED;
INSERT INTO ps_proj_act_tbl
VALUES ('22222', 'TEST3', 'B')
LOG ERRORS REJECT LIMIT UNLIMITED;
SELECT ora_err_optyp$,proj_id, activity_type, status
FROM err$_ps_proj_act_tbl;
ora_err_optyp$ proj_id activity_type status
-----------------------------------------------
I 22222 TEST3 B
[Updated on: Fri, 10 April 2015 00:57] Report message to a moderator |
|
|
|
|
|
| Re: Conditional Insert statement in pl/sql [message #635864 is a reply to message #635857] |
Fri, 10 April 2015 04:13 |
 |
vippysharma
Messages: 73
Registered: May 2013
Location: www
|
Member |
|
|
Another simple method :
-----------------------------------------------
create table PS_PROJ_ACT_TBL
(PROJ_ID varchar2(10),ACTIVITY_TYPE varchar2(10),STATUS varchar2(10),primary key(PROJ_ID,ACTIVITY_TYPE));
----------------------------------------------------------------------------------------------------
create or replace procedure p_ora_faq(p_id in varchar2,
p_type in varchar2,
p_status in varchar2) is
begin
insert into PS_PROJ_ACT_TBL values (p_id, p_type, p_status);
end;
----------------------------------------------------------------------------------------------------
begin
p_ora_faq('1', 'a', 'off');
p_ora_faq('1', 'b', 'off');
p_ora_faq('1', 'c', 'on');
p_ora_faq('2', 'a', 'off');
p_ora_faq('2', 'b', 'off');
p_ora_faq('2', 'a', 'off');
p_ora_faq('3', 'a', 'off');
p_ora_faq('3', 'b', 'off');
exception
when others then
dbms_output.put_line('Line number causing Error : ' ||
dbms_utility.format_error_backtrace);
end;
----------------------------------------------------------------------------------------------------
Result:
Line number causing Error : line 7
----------------------------------------------------------------------------------------------------
# If you want exception type/Name in output, use 'sqlerrm' 'sqlerror' .
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
