Home » SQL & PL/SQL » SQL & PL/SQL » Modifications required in existing Procedure (Oracle 10g)
Modifications required in existing Procedure [message #397554] Sun, 12 April 2009 09:15 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hi this is my existing procedure.

CREATE OR REPLACE PROCEDURE usp_PatientidUpdate (OldPatient VARCHAR2, NewPatient VARCHAR2) AS
       temp   integer; 
       query_exec       varchar2(1000);
Begin
       Select count(*) into temp 
       From USER_TAB_columns 
       WHERE COLUMN_NAME='PATIENT_ID' and table_name not like '%$%' and 
               table_name not in (select VIEW_NAME from user_views) and table_name not in ('EMRPATIENTSMASTER','EMRPATIENTSPERSNLINFO','EMRPATIENTSPHARMAINFO','EMRPATIENTWORKMANSCOMP','EMRPATIENTINSURANCE','EMRPATIENTOTHERINFO');
       While (temp>0) Loop 
       Begin
	 select 'update '|| table_name ||' set Patient_ID='''||NewPatient||''' where Patient_ID= '''||OldPatient||'''' into query_exec 
	 From (Select Rownum as rnum, table_name from USER_TAB_columns WHERE COLUMN_NAME='PATIENT_ID' and table_name not like '%$%' and 
	    table_name not in (select VIEW_NAME from user_views) and table_name not in ('EMRPATIENTSMASTER','EMRPATIENTSPERSNLINFO','EMRPATIENTSPHARMAINFO','EMRPATIENTWORKMANSCOMP','EMRPATIENTINSURANCE','EMRPATIENTOTHERINFO')) M 
	Where rnum=temp;
        DBMS_OUTPUT.PUT_LINE(query_exec);
	execute immediate (query_exec);
	commit;
	temp:=temp-1;
        End;
End loop;
END;
/

Now i have to update the table EMRCorresondencehistory table based on the tables
a)EMRPatientsMaster
b)EMRPATIENTSPERSNLINFO

Table structure is like this
CREATE TABLE  EMRCorrespondenceHistory  (
             CORRES_HISTORY_ID		NUMBER(20, 0)	NOT NULL ,
             RECIPIENT_FULLNAME		VARCHAR2(255)	NULL ,
             RECIPIENT_FIRSTNAME	VARCHAR2(50)	NULL ,
             RECIPIENT_ADDRESS1		VARCHAR2(255)	NULL ,
             RECIPIENT_ADDRESS2		VARCHAR2(255)	NULL ,
             RECIPIENT_CITY		VARCHAR2(50)	NULL ,
             RECIPIENT_STATE		VARCHAR2(50)	NULL 
)

           
             


CREATE TABLE  EMRPatientsMaster (
	 PATIENT_ID			VARCHAR2(20)	NOT NULL ,
	 PATIENT_FIRSTNAME		VARCHAR2(50)	NOT NULL ,
	 PATIENT_LASTNAME		VARCHAR2(50)	NOT NULL ,
	 PATIENT_DOB			DATE		NOT NULL ,
	 PATIENT_HOMEPHONE		NUMBER(20, 0)	 NULL ,
	 PATIENT_GENDER			VARCHAR2(20)	NOT NULL ,
	)
/

CREATE TABLE  EMRPatientsPersnlInfo  (
	 DATE_OF_CREATION		DATE		NOT NULL ,
	 PATIENT_ID			VARCHAR2(20)	NOT NULL ,
	 PATIENT_MIDDLENAME		VARCHAR2(50)	NULL ,
	 PATIENT_SSN			NUMBER(20, 0)	NULL ,
	 SALUTATION_ID			NUMBER(20, 0)	NULL ,
	 PATIENT_ADDRESS1		VARCHAR2(500)   NULL ,
	 PATIENT_ADDRESS2		VARCHAR2(500)   NULL ,
	 
)  
/

CREATE TABLE  EMRStatesLkup  (
	 STATE_ID			NUMBER(20, 0)	NOT NULL ,
	 STATE_NAME			VARCHAR2(50)	NULL ,
	 STATE_CODE			VARCHAR2(50)	NULL
)   
/
Requirement is like this
RECIPIENT_FULLNAME- PATIENT_FIRSTNAME AND PATIENT_LASTNAME FROM EMRPATIENTSMASTER Table
RECIPIENT_FIRSTNAME-PATIENT_FIRSTNAME FROM EMRPATIENTSMASTER Table
RECIPIENT_ADDRESS1-PATIENT_ADDRESS1 FROM EMRPATIENTSPERSNLINFO Table
RECIPIENT_ADDRESS2-PATIENT_ADDRESS2 FROM EMRPATIENTSPERSNLINFO Table
RECIPIENT_CITY-PATIENT_CITY FROM EMRPATIENTSPERSNLINFO Table
RECIPIENT_STATE- STATE_CODE FROM EMRSTATESLKUP TABLE BASED ON STATE_ID FROM EMRPATIENTSPERSNLINFO Table
RECIPIENT_ZIP-PATIENT_ZIPCODE FROM EMRPATIENTSPERSNLINFO Table


The columns to be replaced are on left and these columns are IN
EMRCOrrespondenceHistory Table .
and the values are on right side and the tables from which it needs to be taken are
mentioned respectively

Can anyone help me out use update statement with in the procedure using the above requirement?


Re: Modifications required in existing Procedure [message #397556 is a reply to message #397554] Sun, 12 April 2009 09:54 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>execute immediate (query_exec);
> commit;
The 2 statements above run in separate sessions & do not interact.

The COMMIT has no impact on the results from EXECUTE IMMEDIATE.

Re: Modifications required in existing Procedure [message #397557 is a reply to message #397556] Sun, 12 April 2009 09:56 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hi blackswan,
help me out in the above scenario as i am unable to get the results
Re: Modifications required in existing Procedure [message #397573 is a reply to message #397554] Sun, 12 April 2009 17:34 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
> i am unable to get the results
That makes 2 (you & I) who are unable to get results.

It appears you did make a better than average attempt to follow posting guidelines.
I just may be suffering from senioritis since I am not clear what is the existing data or what is the desired results.

I do not understand why dynamic SQL is a requirements.
It appears from this perspective that the actual UPDATE statement(s) are fixed & well known.

I suggest you first try to hard code the UPDATE statements.
Yes, they may require some additional typing, but you need to do that only once.
By doing so, the results will scale much, much better than the dynamic SQL.
Re: Modifications required in existing Procedure [message #397590 is a reply to message #397573] Sun, 12 April 2009 23:39 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hi,
atleast give me an idea how to update the emrcorrepondencehistory based on the above mentined requiremrnt which that helps me a lot
even using a subquery also in stored procedure

[Updated on: Sun, 12 April 2009 23:44]

Report message to a moderator

Re: Modifications required in existing Procedure [message #397592 is a reply to message #397554] Sun, 12 April 2009 23:48 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
I am not clear what is the existing data or what is the desired results.
Re: Modifications required in existing Procedure [message #397595 is a reply to message #397592] Sun, 12 April 2009 23:54 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
all the tables consist of data in it, i have to update EMRCorrespondenceHistory table for some columns that values needs to be updated with other tables column values based on the patient id which is common in all the tables.
Re: Modifications required in existing Procedure [message #397597 is a reply to message #397595] Sun, 12 April 2009 23:58 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:

all the tables consist of data in it, i have to update EMRCorrespondenceHistory table for some columns that values needs to be updated with other tables column values based on the patient id which is common in all the tables.



but in your EMRCorrespondenceHistory table that you described above does not have patient id field at all

Table structure is like this
CREATE TABLE  EMRCorrespondenceHistory  (
             CORRES_HISTORY_ID		NUMBER(20, 0)	NOT NULL ,
             RECIPIENT_FULLNAME		VARCHAR2(255)	NULL ,
             RECIPIENT_FIRSTNAME	VARCHAR2(50)	NULL ,
             RECIPIENT_ADDRESS1		VARCHAR2(255)	NULL ,
             RECIPIENT_ADDRESS2		VARCHAR2(255)	NULL ,
             RECIPIENT_CITY		VARCHAR2(50)	NULL ,
             RECIPIENT_STATE		VARCHAR2(50)	NULL 
)

Re: Modifications required in existing Procedure [message #397598 is a reply to message #397597] Mon, 13 April 2009 00:00 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
sorry i have not included while formatting it is like this.
CREATE TABLE  EMRCorrespondenceHistory  (
             CORRES_HISTORY_ID		NUMBER(20, 0)	NOT NULL ,
             RECIPIENT_FULLNAME		VARCHAR2(255)	NULL ,
             RECIPIENT_FIRSTNAME	VARCHAR2(50)	NULL ,
             RECIPIENT_ADDRESS1		VARCHAR2(255)	NULL ,
             RECIPIENT_ADDRESS2		VARCHAR2(255)	NULL ,
             RECIPIENT_CITY		VARCHAR2(50)	NULL ,
             RECIPIENT_STATE		VARCHAR2(50)	NULL ,
             RECIPIENT_ZIP		VARCHAR2(50)	NULL ,
             CORREPONDENCE_CONTENT	CLOB Default    EMPTY_CLOB(),
             SENDER_NAME		VARCHAR2(255)	NULL ,
             PATIENT_ID			VARCHAR2(50)	NULL ,
             CREATED_DATE		DATE		NULL ,
             CONSULTANT_ID		VARCHAR2(50)	NULL ,
             SENT_STATUS		NUMBER(1,0)	NULL ,
             SENT_DATE			DATE		NULL ,
             CORRESPONDENCE_NAME	VARCHAR2(255)	NULL 
) 
/
Re: Modifications required in existing Procedure [message #397603 is a reply to message #397598] Mon, 13 April 2009 00:15 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I did not understand your procedure at all but based on your requirements
Quote:
RECIPIENT_FULLNAME- PATIENT_FIRSTNAME AND PATIENT_LASTNAME FROM EMRPATIENTSMASTER Table




I will suggest some thing like this


If Patient Id is a primary key or has an unique constraint in table EMRPatientsMaster then you can do like this



 update (select a.RECIPIENT_FULLNAME cond0,b.PATIENT_FIRSTNAME||' '|| PATIENT_LASTNAME t0 from
EMRCorrespondenceHistory a, EMRPatientsMaster b where a.PATIENT_ID = b.PATIENT_ID)
set cond0 = t0;


if you do not have primary key or unique constraint in EMRPatientsMaster table but you are sure that there are no duplicate patients id in this table then you can do update like this


merge into EMRCorrespondenceHistory a
using (select PATIENT_ID, PATIENT_FIRSTNAME||' '|| PATIENT_LASTNAME t0 from EMRPatientsMaster) b
on (a.patient_id = b.patient_id)
when matched then
update set a.RECIPIENT_FULLNAME = b.t0;


Accordingly you may have to write other update conditions from those other tables
Re: Modifications required in existing Procedure [message #397604 is a reply to message #397603] Mon, 13 April 2009 00:26 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
here patient_id in emrcorrespondencehistory is not a primary key and rest of the tables are having primary key as patient_id and one more thing is using merge we cannot include that in stored procedure know?
Re: Modifications required in existing Procedure [message #397605 is a reply to message #397554] Mon, 13 April 2009 00:32 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>here patient_id in emrcorrespondencehistory is not a primary key and rest of the tables are having primary key as patient_id and one more thing is using merge we cannot include that in stored procedure know?
And what is your point & why should others care you have a problem?
Re: Modifications required in existing Procedure [message #397609 is a reply to message #397604] Mon, 13 April 2009 00:33 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
In my reply I had clearly mentioned that patient_id should be primary key in EMRPatientsMaster table and not in emrcorrespondencehistory and therefore you can use the first update syntax.

Quote:
.....merge we cannot include that in stored procedure procedure know?


from where did you get that information?
Re: Modifications required in existing Procedure [message #397610 is a reply to message #397609] Mon, 13 April 2009 00:40 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
can you please let me know how to include that update statement which is a first one in a stored procedure.as i am very new to this
procedure section i know a very little about it.could you please give me the procedure too using that update statement.i can understand the query which you given and that helped me a lot too
Re: Modifications required in existing Procedure [message #397611 is a reply to message #397610] Mon, 13 April 2009 00:47 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Is it not better that you try this on your own?
If you get stuck somewhere then post it here somebody will definitely then help you.
Re: Modifications required in existing Procedure [message #397612 is a reply to message #397611] Mon, 13 April 2009 00:49 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
ok thankyou very much for your valuable suggestions.
Re: Modifications required in existing Procedure [message #397616 is a reply to message #397612] Mon, 13 April 2009 01:02 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member

update (select a.RECIPIENT_FULLNAME tk,b.PATIENT_FIRSTNAME||' '|| PATIENT_LASTNAME tu from
 EMRCorrespondenceHistory a, EMRPatientsMaster b where a.PATIENT_ID = b.PATIENT_ID)
 set tk = t0;

 update (select a.RECIPIENT_FIRSTNAME tk,b.PATIENT_FIRSTNAME tu from 
 EMRCorrespondenceHistory a, EMRPatientsMaster b where a.PATIENT_ID = b.PATIENT_ID)
 set tk = t0;

 update (select a.RECIPIENT_ADDRESS1 tk,c.PATIENT_ADDRESS1 tu from 
 EMRCorrespondenceHistory a, EMRPATIENTSPERSNLINFO c where a.PATIENT_ID = c.PATIENT_ID)
 set tk = t0;

 update (select a.RECIPIENT_ADDRESS2 tk,c.PATIENT_ADDRESS2 tu from 
 EMRCorrespondenceHistory a, EMRPATIENTSPERSNLINFO c where a.PATIENT_ID = c.PATIENT_ID)
 set tk = t0;

 update (select a.RECIPIENT_CITY tk,c.PATIENT_CITY tu from 
 EMRCorrespondenceHistory a, EMRPATIENTSPERSNLINFO c where a.PATIENT_ID = c.PATIENT_ID)
 set tk = t0;

 update (select a.RECIPIENT_STATE tk,c.STATE_CODE tu from 
 EMRCorrespondenceHistory a, EMRPATIENTSPERSNLINFO c where a.PATIENT_ID = c.PATIENT_ID)
 set tk = t0;

 update (select a.RECIPIENT_ZIP tk,c.PATIENT_ZIPCODE tu from 
 EMRCorrespondenceHistory a, EMRPATIENTSPERSNLINFO c where a.PATIENT_ID = c.PATIENT_ID)
 set tk = t0;


if this much update statments are there based on my conditions how can i write it as a single update stament inside a procedure?
Re: Modifications required in existing Procedure [message #397626 is a reply to message #397616] Mon, 13 April 2009 01:23 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I personally would prefer to have 2 update statement like this


update (select a.RECIPIENT_FULLNAME t0,a.RECIPIENT_FIRSTNAME t1,b.PATIENT_FIRSTNAME s0,b.PATIENT_FIRSTNAME||' '|| PATIENT_LASTNAME s1 from
 EMRCorrespondenceHistory a, EMRPatientsMaster b where a.PATIENT_ID = b.PATIENT_ID)
 set t0 = s0, t1=s1;


 update (select a.RECIPIENT_ADDRESS1 t0,a.RECIPIENT_ADDRESS2 t1, ..<<other required fields from EMRCorrespondenceHistory>>.. , c.PATIENT_ADDRESS1 s0, c.PATIENT_ADDRESS2 s1,..<<other required fields from EMRPATIENTSPERSNLINFO >>... from 
 EMRCorrespondenceHistory a, EMRPATIENTSPERSNLINFO c where a.PATIENT_ID = c.PATIENT_ID)
 set t0 = s0, t1=s1 ......;


If you want then you may test by combining both the queries to see if it is more performant and gives you correct result.


Re: Modifications required in existing Procedure [message #397633 is a reply to message #397626] Mon, 13 April 2009 01:48 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
CREATE OR REPLACE PROCEDURE usp_PatientidUpdate (OldPatient VARCHAR2, NewPatient VARCHAR2) AS
       temp        integer; 
       query_exec  varchar2(1000);
Begin
	Select count(*) into temp 
	From USER_TAB_columns 
	WHERE COLUMN_NAME='PATIENT_ID' and table_name not like '%$%' and 
               table_name not in (select VIEW_NAME from user_views) and table_name not in ('EMRPATIENTSMASTER','EMRPATIENTSPERSNLINFO','EMRPATIENTSPHARMAINFO','EMRPATIENTWORKMANSCOMP','EMRPATIENTINSURANCE','EMRPATIENTOTHERINFO');
	While (temp>0) Loop 
	Begin
		select 'update '|| table_name ||' set Patient_ID='''||NewPatient||''' where Patient_ID= '''||OldPatient||'''' into query_exec 
		From (Select Rownum as rnum, table_name from USER_TAB_columns WHERE COLUMN_NAME='PATIENT_ID' and table_name not like '%$%' and 
		    table_name not in (select VIEW_NAME from user_views) and table_name not in ('EMRPATIENTSMASTER','EMRPATIENTSPERSNLINFO','EMRPATIENTSPHARMAINFO','EMRPATIENTWORKMANSCOMP','EMRPATIENTINSURANCE','EMRPATIENTOTHERINFO')) M 
		Where rnum=temp;
		DBMS_OUTPUT.PUT_LINE(query_exec);
		execute immediate (query_exec);
		commit;
		temp:=temp-1;
	End;
	End loop;
        update  (select a.RECIPIENT_FULLNAME t0,a.RECIPIENT_FIRSTNAME t1,b.PATIENT_FIRSTNAME s0,b.PATIENT_FIRSTNAME||' '|| PATIENT_LASTNAME s1 from
        EMRCorrespondenceHistory a, EMRPatientsMaster b where a.PATIENT_ID = b.PATIENT_ID)
        set t0 = s0, t1=s1,Patient_ID='''||NewPatient||''' where Patient_ID= '''||OldPatient||'''';

        update (select a.RECIPIENT_ADDRESS1 t0,a.RECIPIENT_ADDRESS2 t1,a.RECIPIENT_CITY t2,a.RECIPIENT_STATE t3,a.RECIPIENT_ZIP t4,c.PATIENT_ADDRESS1 s0, c.PATIENT_ADDRESS2 s1,c.PATIENT_CITY s2,d.STATE_CODE s3,c.PATIENT_ZIPCODE s4 from 
        EMRCorrespondenceHistory a, EMRPATIENTSPERSNLINFO c where a.PATIENT_ID = c.PATIENT_ID)
        set t0 = s0, t1 = s1, t2 = s2, t3 = s3 , t4 = s4,Patient_ID='''||NewPatient||''' where Patient_ID= '''||OldPatient||'''';
	Commit;
END;
/

Bonker on a lot of attempts i did my procedure like this,
but in one of mu requirement it is like this
RECIPIENT_STATE- STATE_CODE FROM EMRSTATESLKUP TABLE BASED ON STATE_ID FROM EMRPATIENTSPERSNLINFO Table
how could i achieve this?

Re: Modifications required in existing Procedure [message #397637 is a reply to message #397633] Mon, 13 April 2009 02:12 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
ummmm..... I don't know where to start. Is this code actually sometimes in future will go into production.

If yes, then in IMHO I suggest that you get somebody from your company who understands a thing or two about Oracle database and PLSQL code and get him or her to mentor you on this.

Any way to just get you started

Why do you have still have this lines your code? I do not understand what it does.

	Select count(*) into temp 
	From USER_TAB_columns 
	WHERE COLUMN_NAME='PATIENT_ID' and table_name not like '%$%' and 
               table_name not in (select VIEW_NAME from user_views) and table_name not in ('EMRPATIENTSMASTER','EMRPATIENTSPERSNLINFO','EMRPATIENTSPHARMAINFO','EMRPATIENTWORKMANSCOMP','EMRPATIENTINSURANCE','EMRPATIENTOTHERINFO');
	While (temp>0) Loop 
	Begin
		select 'update '|| table_name ||' set Patient_ID='''||NewPatient||''' where Patient_ID= '''||OldPatient||'''' into query_exec 
		From (Select Rownum as rnum, table_name from USER_TAB_columns WHERE COLUMN_NAME='PATIENT_ID' and table_name not like '%$%' and 
		    table_name not in (select VIEW_NAME from user_views) and table_name not in ('EMRPATIENTSMASTER','EMRPATIENTSPERSNLINFO','EMRPATIENTSPHARMAINFO','EMRPATIENTWORKMANSCOMP','EMRPATIENTINSURANCE','EMRPATIENTOTHERINFO')) M 
		Where rnum=temp;
		DBMS_OUTPUT.PUT_LINE(query_exec);
		execute immediate (query_exec);
		commit;
		temp:=temp-1;
	End;
	End loop;



and then you have this code

 update  (select a.RECIPIENT_FULLNAME t0,a.RECIPIENT_FIRSTNAME t1,b.PATIENT_FIRSTNAME s0,b.PATIENT_FIRSTNAME||' '|| PATIENT_LASTNAME s1 from
        EMRCorrespondenceHistory a, EMRPatientsMaster b where a.PATIENT_ID = b.PATIENT_ID)
        set t0 = s0, t1=s1,Patient_ID='''||NewPatient||''' where Patient_ID= '''||OldPatient||'''';

        update (select a.RECIPIENT_ADDRESS1 t0,a.RECIPIENT_ADDRESS2 t1,a.RECIPIENT_CITY t2,a.RECIPIENT_STATE t3,a.RECIPIENT_ZIP t4,c.PATIENT_ADDRESS1 s0, c.PATIENT_ADDRESS2 s1,c.PATIENT_CITY s2,d.STATE_CODE s3,c.PATIENT_ZIPCODE s4 from 
        EMRCorrespondenceHistory a, EMRPATIENTSPERSNLINFO c where a.PATIENT_ID = c.PATIENT_ID)
        set t0 = s0, t1 = s1, t2 = s2, t3 = s3 , t4 = s4,Patient_ID='''||NewPatient||''' where Patient_ID= '''||OldPatient||'''';
	Commit;


why are you concatenating with a single quote you need to simply bind it like this

 set t0 = s0, t1 = s1, t2 = s2, t3 = s3 , t4 = s4,Patient_ID=NewPatient  where Patient_ID= OldPatient;
but what scares me most in this is that you are updating patient id itself just think what will happen to data integrity or quality.

So I really suggest that you get some one to explain about Database and then only try this. I am sorry if I sounded rude but I believe that trying to solve some critical PLSQL code issue over a forum without proper knowledge on plsql will cause
you more problems for you later.




[Updated on: Mon, 13 April 2009 02:23]

Report message to a moderator

Re: Modifications required in existing Procedure [message #397641 is a reply to message #397637] Mon, 13 April 2009 02:20 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i got it and here i no need to change the above code and i have to include it for further transaction.so mu requirement should gone through at the end.so what i need is some modifications in the second update statement as table differs from one more table.
Re: Modifications required in existing Procedure [message #397777 is a reply to message #397554] Mon, 13 April 2009 20:41 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>Now i have to update the table EMRCorresondencehistory table based on the tables

NOW?
When is "now"?
Under what conditions does DML against EMRCorresondencehistory table need to occur?

>update the table EMRCorresondencehistory table
UPDATE? Are you sure about this?
For some/many/most "history" tables INSERT is done of old value BEFORE the UPDATE occurs so you can capture previous values?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

Post DML for test data.

Post expected/desired results.
Re: Modifications required in existing Procedure [message #397853 is a reply to message #397556] Tue, 14 April 2009 04:04 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@BlackSwan
Quote:
>execute immediate (query_exec);
> commit;
The 2 statements above run in separate sessions & do not interact.

The COMMIT has no impact on the results from EXECUTE IMMEDIATE.

I'm going to have to call you on that I'm afraid - it's wrong, and there's no two ways about it.

Commands executed using EXECUTE IMMEDIATE do not execute in a different Session. They do not even execute as a different transaction in the current session.
They are quite definitely executed as part of the current session, and the commit quite definitely does commit the changes made by the preceding EXECUTE IMMEDIATE.

NDS statements do execute in a different execution context, and cannot see locally defined variables, cursors etc, but that's a totally different thing.
Previous Topic: PLS-00103 in Procedure using ref cursor
Next Topic: difference between explain plans
Goto Forum:
  


Current Time: Fri Dec 09 10:02:41 CST 2016

Total time taken to generate the page: 0.14074 seconds