Hi Gurus,
I am stuck with the step where i need to fetch the location & sales from the below procedure by taking it from table field using the for loop. any idea how this can be done in unix. From one column both the location and sales are taken out.
create or replace procedure newyork
as
cursor rec
IS
select * from location_queue where location like daily.mmm%';
V_LOC_CT NUMBER;
v_location_id VARCHAR2(50);
v_sales NUMBER;
BEGIN
for i in rec
loop
select substr(i.location,12,4) INTO v_location_id from dual;
select substr(i.location,17,4) INTO v_sales from dual;
DBMS_OUTPUT.PUT_LINE ('locationid is '||v_location_id);
DBMS_OUTPUT.PUT_LINE ('sales is '||v_sales);
SELECT COUNT(*) INTO LOC_CT FROM TOTAL_COUNT WHERE location_ID = v_location_id AND sales= v_sales;
IF V_CHK_COUNT > 0 THEN
DELETE FROM TOTAL_COUNT WHERE location_ID = v_location_id AND sales= v_sales;
END
Hello Arun,
We would like to know following answers please on your request.
1st: What you have tried so far?
2nd: Do you want anything to fetch from the output of above queries?
3rd: If yes for above, then please do show us the output (Please make it is as a habit to show input and expected output).
4th: If any other information which is necessary to be added should be included too.
Thanks,
R. Singh
Hi Ravi/All,
I have written a oracle procedure where i am not able to send email from oracle due to priviliges issue. any idea how to call it from unix shell side
Output Required :
If job is success/failure need to trigger an email from unix.
Please help me out how to call the below oracle function from unix & send the email from unix
cursor rec
IS
SELECT * FROM location_id_queue where location like '%DETAILS.LLL%';
V_value_COUNT NUMBER;
V_proc_name VARCHAR2(50);
V_valuexxx NUMBER;
V_valuexxx2 NUMBER;
V_valuexxx3 NUMBER;
v_location_id VARCHAR2(50);
v_sales NUMBER;
BEGIN
for i in rec
loop
select substr(i.location,12,4) INTO v_location_id from dual;
select substr(i.location,17,4) INTO v_sales from dual;
v_proc_name := 'JOBS';
DBMS_OUTPUT.PUT_LINE ('Program name '||V_proc_name);
DBMS_OUTPUT.PUT_LINE ('outletid is '||v_location_id);
DBMS_OUTPUT.PUT_LINE ('sales is '||v_sales);
DBMS_OUTPUT.PUT_LINE ('Now Getting the count from the table for the outlet '||v_location_id);
SELECT COUNT(*) INTO V_value_COUNT FROM COUNT WHERE location_id = v_location_id AND sales= v_sales;
IF V_value_COUNT > 0 THEN
DELETE FROM COUNT WHERE location_id = v_location_id AND sales= v_sales;
ELSE
DBMS_OUTPUT.PUT_LINE ('No Data found from the table TOTAL_Count '||v_location_id);
END IF;
IF v_location_id IN ('chen','bang') THEN
select GET_COUNTS.GET_bang_chen_COUNT(v_location_id,v_sales) INTO V_valuexxx from dual;
DBMS_OUTPUT.PUT_LINE ('chen/bang functions'|| V_valuexxx);
ELSIF v_location_id IN ('sale','tric','padi','madu','PUDU') THEN
select GET_COUNTS.GET_OTHER_OUTLET_COUNT(v_location_id,v_sales) INTO V_valuexxx from dual;
DBMS_OUTPUT.PUT_LINE ('sale/tric/padi/madu/PUDU function '|| V_valuexxx);
ELSIF v_location_id IN ('TIRU') THEN
select GET_COUNTS.GET_TIRU_COUNT(v_location_id,v_sales) INTO V_valuexxx from dual;
DBMS_OUTPUT.PUT_LINE ('TIRU function'|| V_valuexxx);
END IF;
IF V_valuexxx = 0 THEN
select GET_COUNTS.GET_CRT_COUNT(v_location_id,v_sales) INTO V_valuexxx2 from dual;
select GET_COUNTS.GET_PANEL_COUNT(v_location_id,v_sales) INTO V_valuexxx3 from dual;
END IF;
BEGIN
IF V_valuexxx = 0 and V_valuexxx2 = 0 and V_valuexxx3 = 0 THEN
DBMS_OUTPUT.PUT_LINE ('Job Sucess'||v_location_id);
ELSE
DBMS_OUTPUT.PUT_LINE ('Job Failed'|| v_location_id);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Program errors while sending email due to:'||SQLERRM);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Program errors due to:'||SQLERRM);
END;