Home » Developer & Programmer » Forms » DBMS_OUTPUT Messages on Form (Oracle 9i form 6i)
| DBMS_OUTPUT Messages on Form [message #541714] |
Thu, 02 February 2012 00:14  |
usmanelahi
Messages: 54
Registered: July 2007
Location: Karachi
|
Member |
|
|
I have made a procedure in database level to synchronize data between oracle and mssql database.
i made a button in form to call a procedure from database like
DATA_SYNC(:V_DATE);
Message ('Data Updated');
Message ('Data Updated');
when i run procedure from sql developer its working fine and showing dbms_output messages
But when i run from form no message are appear please tell me how to show dbms_out_put messages on form
how to handle exception handling when internet is down or mssql server not responsding
Please see my procedure and suggest any idea to make better development
---------------------------------------------------------------
create or replace
PROCEDURE DATA_SYNC
(
V_DATE DATE
) AS
COUNTER NUMBER:=0;
COUNTER1 NUMBER:=0;
COUNTER2 NUMBER:=0;
CURSOR C1 IS SELECT 'Karachi','MAURIPUR',B.LINE,A.LINE_CODE,
SUBSTR(A.CONT_NO,1,4)||SUBSTR(A.CONT_nO,6,6)||SUBSTR(A.CONT_NO,13,1) CNT_NO,CONT_NO,
A.SIZES,C.TYPE_DESCRIPTION,A.IN_DATE,A.PRE_REM,
A.P_INSPECTION_STATUS,A.NEW_REM,
A.INSPECTION_STATUS,'Waiting for Estimate' AS SS,A.OUT_DATE
FROM ICS_IMPORT A,ICS_LINE B,ICS_CONTAINER_TYPE C
WHERE A.LINE_CODE=B.LINE_CODE
AND A.CONT_TYPE=C.TYPE_CODE
AND A.CANCEL IS NULL
AND A.EXPORT_STATUS IS NULL
AND A.COUNT_CONDITION IS NOT NULL
AND MSSQL_STATUS IS NULL
UNION
SELECT 'Karachi','MAURIPUR',B.LINE,A.LINE_CODE,
SUBSTR(A.CONT_NO,1,4)||SUBSTR(A.CONT_nO,6,6)||SUBSTR(A.CONT_NO,13,1) CNT_NO,CONT_NO,
A.SIZES,C.TYPE_DESCRIPTION,A.IN_DATE,A.PRE_REM,
A.P_INSPECTION_STATUS,A.NEW_REM,
A.INSPECTION_STATUS,'Repaired' AS SS,A.OUT_DATE
FROM ICS_IMPORT A,ICS_LINE B,ICS_CONTAINER_TYPE C
WHERE A.LINE_CODE=B.LINE_CODE
AND A.CONT_TYPE=C.TYPE_CODE
AND A.CANCEL IS NULL
AND A.EXPORT_STATUS IS NULL
AND MSSQL_STATUS IS NULL
AND A.ACTUAL_REPAIRED_DATE=V_DATE;
CURSOR C2 IS SELECT B.LINE,SUBSTR(A.CONT_NO,1,4)||SUBSTR(A.CONT_nO,6,6)||SUBSTR(A.CONT_NO,13,1) CNT_NO,
A.OUT_DATE,TO_CHAR(A.IN_DATE,'DD-MON-YYYY') IN_DATE,A.P_INSPECTION_STATUS,A.INSPECTION_STATUS,A.ACTUAL_STATUS
FROM ICS_IMPORT A,ICS_LINE B,ICS_CONTAINER_TYPE C
WHERE A.LINE_CODE=B.LINE_CODE
AND A.CONT_TYPE=C.TYPE_CODE
AND A.CANCEL IS NULL
AND A.ACTUAL_REPAIRED_DATE=V_DATE;
CURSOR C3 IS SELECT B.LINE,SUBSTR(A.CONT_NO,1,4)||SUBSTR(A.CONT_nO,6,6)||SUBSTR(A.CONT_NO,13,1) CNT_NO,
A.OUT_DATE,TO_CHAR(A.IN_DATE,'DD-MON-YYYY') IN_DATE FROM ICS_IMPORT A,ICS_LINE B,ICS_CONTAINER_TYPE C
WHERE A.LINE_CODE=B.LINE_CODE
AND A.CONT_TYPE=C.TYPE_CODE
AND A.CANCEL IS NULL
AND A.EXPORT_STATUS IS NOT NULL
AND COUNT_CONDITION IS NOT NULL
AND A.OUT_DATE=V_DATE;
begin
FOR I IN C1 LOOP
COUNTER:=counter+1;
INSERT INTO DATA@MSSQL.WORLD
("Region","Station","Line","Unit No","Size","Type","In Date","Previous Remarks","Previous Status","New Remarks",
"New Status","Status","out date")
VALUES ('Karachi','MAURIPUR',I.LINE,I.CNT_NO,I.SIZES,I.TYPE_DESCRIPTION,I.IN_DATE,I.PRE_REM,
I.P_INSPECTION_STATUS,I.NEW_REM,
I.INSPECTION_STATUS,I.SS,I.OUT_DATE);
--null;
END LOOP;
COMMIT;
FOR K IN C2 LOOP
COUNTER1:=counter1+1;
UPDATE DATA@MSSQL.WORLD
SET "Previous Status"=K.P_INSPECTION_STATUS,"New Status"=K.INSPECTION_STATUS,"Status"='Repaired',"out date"=K.OUT_DATE
WHERE "Unit No"=K.CNT_NO
AND "In Date"=K.IN_DATE
AND "Status" <> 'Repaired';
COMMIT;
END LOOP;
FOR J IN C3 LOOP
COUNTER2:=counter2+1;
UPDATE DATA@MSSQL.WORLD
SET "out date"=J.OUT_DATE
WHERE "Unit No"=J.CNT_NO
AND "In Date"=J.IN_DATE;
-- AND "out date"is NULL;
--null;
COMMIT;
END LOOP;
UPDATE ICS_IMPORT
SET MSSQL_STATUS='Y'
WHERE MSSQL_STATUS IS NULL;
COMMIT;
DBMS_OUTPUT.PUT_LINE (COUNTER||' Records Inserted '|| COUNTER1||' Records Repaired Scuceesfully '|| COUNTER2||' Records Out Scuceesfully ');
DBMS_OUTPUT.PUT_LINE (COUNTER||' Records Inserted '|| COUNTER1||' Records Repaired Scuceesfully '|| COUNTER2||' Records Out Scuceesfully ');
END DATA_SYNC;
|
|
|
|
| Re: DBMS_OUTPUT Messages on Form [message #541735 is a reply to message #541714] |
Thu, 02 February 2012 01:32  |
 |
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Please, format your code and use [code] tags to preserve formatting. If you don't know how to do that, check this short tutorial (takes only a few seconds). Code you posted is ugly and difficult to follow.
Anyway: stored procedure is executed in the database and Forms doesn't have a way to display result of DBMS_OUTPUT.PUT_LINE command. Therefore, you might modify your procedure in order to include an OUT parameter whose value would be displayed in a form. Or, you could store procedure's result into a table and let form read it. I believe that the first option (an OUT parameter) is simpler.
Quote:how to handle exception handling when internet is down or mssql server not responsding
No idea. I wouldn't know which exception handles "internet down" condition or "MSSQL server not responding" (apart from WHEN OTHERS, but that's questionable as it would catch all the "others" exceptions). Someone might know it, though, and will assist.
[Updated on: Thu, 02 February 2012 01:32] Report message to a moderator |
|
|
|
| Re: DBMS_OUTPUT Messages on Form [message #541775 is a reply to message #541735] |
Thu, 02 February 2012 04:42 |
usmanelahi
Messages: 54
Registered: July 2007
Location: Karachi
|
Member |
|
|
Thank you for your reply, Can you give me a sample code of Out Parameter to call in form with error messages.
Actually when i run the process cursor1 is going to select data from oracle and insert data into mssql with immediately effect because remote database need commit.
During running process some time internet down or power failure of local or remote db in this case my last code of Update portion not work or some process not run completely where to identify who records are inserted and updated.
|
|
|
|
| Re: DBMS_OUTPUT Messages on Form [message #541776 is a reply to message #541775] |
Thu, 02 February 2012 04:46 |
|
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Here's an example of a procedure that uses the OUT parameter:
SQL> create or replace procedure prc_out
2 (par_deptno in number,
3 par_sum_sal out number
4 ) is
5 begin
6 select sum(sal)
7 into par_sum_sal
8 from emp
9 where deptno = par_deptno;
10 end;
11 /
Procedure created.
SQL> declare
2 l_sum_sal number;
3 begin
4 prc_out (10, l_sum_sal);
5 dbms_output.put_line(l_sum_sal);
6 end;
7 /
11150
PL/SQL procedure successfully completed.
SQL>
In a form, you'd (instead of DBMS_OUTPUT.PUT_LINE)or put that value into a form item or whatever you find appropriate.
|
|
|
|
Goto Forum:
Current Time: Mon Jul 15 17:36:40 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
