| Help me on Syntax of PL/SQL [message #646314] |
Thu, 24 December 2015 09:21  |
 |
royal4111
Messages: 2
Registered: December 2015
Location: Bangalore
|
Junior Member |
|
|
Hi,
I am a beginner to PL/SQL.Designing a UNIX CRON job to fetch the row count from four SELECT queries and compare the four outputs to be greater than zero.Below is the PL/SQL code and when i execute this, syntax error is popping up,could someone help me out whether the syntax is correct or not..Thanking you in advance
WHENEVER SQLERROR EXIT 1
ROLLBACK;
SET serveroutput ON
DECLARE
count_1 NUMBER:=0
count_2 NUMBER:=0;
count_3 NUMBER:=0;
count_4 NUMBER:=0;
PROCEDURE write_log(p_logmessage IN VARCHAR2) AS
BEGIN
BEGIN
DBMS_OUTPUT.PUT_LINE(p_logmessage);
EXCEPTION
WHEN others THEN
IF SQLCODE = -20000 THEN
DBMS_OUTPUT.disable();
DBMS_OUTPUT.enable(1000000);
DBMS_OUTPUT.PUT_LINE(p_logmessage);
END IF;
END;
END write_log;
BEGIN
write_log('Calclulation of missing Forecasted Site/Articles process started');
DBMS_OUTPUT.enable(1000000);
BEGIN
select sum(count) into count_1 from
(
select count (*) from table_a
);
select sum(count) into count_2 from
(
select count (*) from table_a
);
select sum(count) into count_3 from
(
select count (*) from table_a
);
select sum(count) into count_4 from
(
select count (*) from table_a
);
DBMS_OUTPUT.PUT_LINE('count is:'||count_1);
DBMS_OUTPUT.PUT_LINE('count is:'|| count_2);
DBMS_OUTPUT.PUT_LINE('count is:'|| count_3);
DBMS_OUTPUT.PUT_LINE('count is:'|| count_4);
write_log('oprocess completed');
---Validating the record Count -----
IF (count_1>0 OR count_2>0 OR count_3>0 OR count_4>0) THEN
raise_application_error(-20001,'records are present'||SQLCODE||' -ERROR- '||SQLERRM);
Else
write_log('No records present');
END IF;
EXIT;
/
|
|
|
|
|
|
|
|
| Re: Help me on Syntax of PL/SQL [message #646362 is a reply to message #646316] |
Mon, 28 December 2015 12:29  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Try the following
DECLARE
FLAG VARCHAR2(1);
BEGIN
select nvl(max('N'),'Y')
INTO FLAG
FROM DUAL
WHERE (select count(*) from table_a) > 0
OR (select count(*) from table_b) > 0
OR (select count(*) from table_c) > 0
OR (select count(*) from table_d) > 0;
IF FLAG = 'N' THEN
raise_application_error(-20001,'records are present');
Else
DBMS_OUTPUT.PUT_LINE('No records present');
END IF;
END;
/
[Updated on: Mon, 28 December 2015 12:30] Report message to a moderator |
|
|
|
|
|
| Re: Help me on Syntax of PL/SQL [message #646367 is a reply to message #646366] |
Mon, 28 December 2015 16:04 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Your totally correct, it should be
DECLARE
FLAG VARCHAR2(1);
BEGIN
select nvl(max('N'),'Y')
INTO FLAG
FROM DUAL
WHERE exists (select null from table_a)
OR exists (select null from table_b)
OR exists (select null from table_c)
OR exists (select null from table_d);
IF FLAG = 'N' THEN
raise_application_error(-20001,'records are present');
Else
DBMS_OUTPUT.PUT_LINE('No records present');
END IF;
END;
/
Nice catch Michel.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
