Hi
I have wriitten a stored procedure to update the Organisation_Name in a table based on the Full_Org_Nm of an another table.
Though the procodure got executed sucessfully but it is not updating the records.
I even tried executing the procedure by writing Execute <Procedure name>,the database got hanged.
Please find my procedure below.
Create or Replace
PROCEDURE UPDT_ISSUE_USR_ROLE
As
Cursor cur_user_role Is
Select a.org_id,a.user_id
from prts_user a,issue_user_role b
where a.user_id=b.user_id;
v_rows_processed Number:=0;
Begin
For rec in cur_user_role Loop
update Issue_user_role
set User_org_nm=(Select full_org_nm from VW_Org where org_id=rec.org_id)
Where Issue_User_Role.rowid in
(select issue_user_role.rowid
FROM issue_user_role,issue,issue_workflow,Issue_step_dtl_wrkflw
Where Issue_User_Role.Issue_Id=Issue.Issue_id
And Issue_User_Role.Issue_id=issue_workflow.issue_id
And Issue_User_Role.Workflow_compnt_id=Issue_Workflow.CURR_STEP_WORKFLOW_COMPNT_ID
And Issue_User_Role.Workflow_compnt_id=Issue_Step_Dtl_wrkflw.Workflow_compnt_id
And Issue_User_Role.Issue_id=Issue_Step_Dtl_wrkflw.Issue_Id
And Issue.Issue_id=Issue_workflow.Issue_Id
And Issue.Issue_id=Issue_Step_Dtl_Wrkflw.Issue_id
And Issue_workflow.Issue_id=Issue_Step_Dtl_Wrkflw.Issue_id
And Issue_Workflow.CURR_STEP_WORKFLOW_COMPNT_ID=Issue_Step_Dtl_wrkflw.Workflow_compnt_id
And issue_workflow.primry_workflow_flag='Y'
And issue_user_role.user_id = rec.user_id
And issue.issue_status_id in (1636,50738,275,50737,2090)
And issue_step_dtl_wrkflw.Issue_step_status_id in (61248,61249,61250));
v_rows_processed :=v_rows_processed + SQL%ROWCOUNT;
End Loop;
COMMIT;
dbms_output.enable;
dbms_output.put_line('There were '||v_rows_processed||' rows updated');
End;
/
Please let me know where i am wrong.
Thanks in advance.
Cheers