Home » SQL & PL/SQL » SQL & PL/SQL » execute immediate
execute immediate [message #186204] Mon, 07 August 2006 01:38 Go to next message
crmoffat
Messages: 33
Registered: November 2005
Location: Australia
Member
hi all

am having problems with a function using execute immediate. All I am trying to do is do something like

for x in (select statement)
loop
etc...

where the select statement is dynamic. however when I try to put it after an execute immediate I get errors (mostly the old 'encountered 'IMMEDIATE' expecting one of the following .(...)

can somebody help me out with the correct syntax / approach to do this...can't seem to find any useful examples on google to assist...

CREATE OR REPLACE
FUNCTION XXOSS_SET_ASSIGNMENT_OBJ(P_ORG_ID IN NUMBER, P_LEVEL_NO IN VARCHAR2) RETURN XXOSS_HR_ASSIGNMENTS
IS
V_XXOSS_HR_ASSIGNMENTS XXOSS_HR_ASSIGNMENTS := XXOSS_HR_ASSIGNMENTS();
BEGIN
for x in (EXECUTE IMMEDIATE 'SELECT 
ASG.ASSIGNMENT_ID
FROM
PER_ASSIGNMENTS_F ASG, 
HR_POSITIONS_F POS,
XXOSS_HR_AGENCY_ORGUNITS ORG
WHERE ASG.POSITION_ID = POS.POSITION_ID
AND POS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ASG.PRIMARY_FLAG = ''Y''
AND ASG.ASSIGNMENT_TYPE = ''E''
AND TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN POS.EFFECTIVE_START_DATE AND POS.EFFECTIVE_END_DATE
AND ' || P_LEVEL_NO || ' = ' || P_ORG_ID || '')
LOOP
V_XXOSS_HR_ASSIGNMENTS.EXTEND;
V_XXOSS_HR_ASSIGNMENTS(V_XXOSS_HR_ASSIGNMENTS.COUNT) := XXOSS_HR_ASSIGNMENT(X.ASSIGNMENT_ID);
END LOOP;
RETURN V_XXOSS_HR_ASSIGNMENTS;
END;
Re: execute immediate [message #186209 is a reply to message #186204] Mon, 07 August 2006 01:52 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Try using ref cursor...

Naveen
Re: execute immediate [message #186217 is a reply to message #186204] Mon, 07 August 2006 02:07 Go to previous messageGo to next message
crmoffat
Messages: 33
Registered: November 2005
Location: Australia
Member
thanks for the reply

i had tried a ref cursor without any success...could you provide a simple example of the syntax i could use...

cheers

cameron
Re: execute immediate [message #186225 is a reply to message #186204] Mon, 07 August 2006 02:28 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Something like this...


CREATE OR REPLACE
FUNCTION XXOSS_SET_ASSIGNMENT_OBJ(P_ORG_ID IN NUMBER, P_LEVEL_NO IN VARCHAR2) 
RETURN XXOSS_HR_ASSIGNMENTS
IS
type c1 is ref cursor;
c c1;
v_query  long;
    V_XXOSS_HR_ASSIGNMENTS XXOSS_HR_ASSIGNMENTS := XXOSS_HR_ASSIGNMENTS();
BEGIN
v_query := ' SELECT ASG.ASSIGNMENT_ID FROM PER_ASSIGNMENTS_F ASG, HR_POSITIONS_F POS, ';
 			v_query :=v_query||' XXOSS_HR_AGENCY_ORGUNITS ORG  ';
 			v_query :=v_query||' WHERE ASG.POSITION_ID = POS.POSITION_ID  ';
 			v_query :=v_query||' AND POS.ORGANIZATION_ID = ORG.ORGANIZATION_ID  ';
 			v_query :=v_query||' AND ASG.PRIMARY_FLAG = ''Y''  ';
 			v_query :=v_query||' AND ASG.ASSIGNMENT_TYPE = ''E''  ';
 			v_query :=v_query||' AND TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE  ';
 			v_query :=v_query||' AND TRUNC(SYSDATE) BETWEEN POS.EFFECTIVE_START_DATE AND POS.EFFECTIVE_END_DATE  ';
 			v_query :=v_query||' AND ' || P_LEVEL_NO || ' = ' || P_ORG_ID ;
open c for v_query;
LOOP
	<include ur code here>
END LOOP;
close c ;
RETURN V_XXOSS_HR_ASSIGNMENTS;
END;



The code is not tested...
Is XXOSS_HR_ASSIGNMENTS is an user defined object?


Naveen
Re: execute immediate [message #186233 is a reply to message #186204] Mon, 07 August 2006 02:39 Go to previous messageGo to next message
crmoffat
Messages: 33
Registered: November 2005
Location: Australia
Member
XXOSS_HR_ASSIGNMENTS is indeed a user defined object.

will have a bit of a play around with your code right now and see how it goes...will let you know how it goes...

thanks

cameron
Re: execute immediate [message #186239 is a reply to message #186204] Mon, 07 August 2006 02:47 Go to previous messageGo to next message
crmoffat
Messages: 33
Registered: November 2005
Location: Australia
Member
well no errors...that is more success than I had!

inside the loop...end loop I wish to reference each item in the cursor

such as

LOOP
V_XXOSS_HR_ASSIGNMENTS.EXTEND;
V_XXOSS_HR_ASSIGNMENTS(V_XXOSS_HR_ASSIGNMENTS.COUNT) := XXOSS_HR_ASSIGNMENT(c.assignment_id);
END LOOP;


however get error messages saying invalid reference to variable 'C'

can i use the cursor inside the loop such as this, and if so how do I actually reference it?

once again thanks, assistance is much appreciated
Re: execute immediate [message #186246 is a reply to message #186204] Mon, 07 August 2006 02:58 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Here is one way to get the cursor variable....

type rec1 is record
  (
       ASSIGNMENT_ID	   PER_ASSIGNMENTS_F%type
  );
rec rec1; 

begin
  LOOP
	fetch c1 into rec; 
        exit when c1%NOTFOUND;
        .......
  END LOOP;


Now use rec.assignment_id instead of c.assignment_id...

Naveen
Re: execute immediate [message #186256 is a reply to message #186204] Mon, 07 August 2006 03:29 Go to previous messageGo to next message
crmoffat
Messages: 33
Registered: November 2005
Location: Australia
Member
that did the trick...works well now.

thanks for your help...have learnt quite a bit from it (i had never used cursors / records before)

cheers

Cameron
Re: execute immediate [message #186261 is a reply to message #186204] Mon, 07 August 2006 03:35 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Cameron, It would be great if u could post ur code.
This will help others to know and also anyone can suggest if there is any better way to do...

Naveen

Re: execute immediate [message #186433 is a reply to message #186204] Mon, 07 August 2006 23:56 Go to previous message
crmoffat
Messages: 33
Registered: November 2005
Location: Australia
Member
Here is the final code I ended up using.

CREATE OR REPLACE
FUNCTION XXOSS_SET_ASSIGNMENT_OBJ(P_ORG_ID IN NUMBER, P_LEVEL_NO IN VARCHAR2) 
RETURN XXOSS_HR_ASSIGNMENTS
IS
type c1 is ref cursor;
c c1;
type rec1 is record
  (ASSIGNMENT_ID NUMBER);
rec rec1;
v_query  long;
V_XXOSS_HR_ASSIGNMENTS XXOSS_HR_ASSIGNMENTS := XXOSS_HR_ASSIGNMENTS();
v_assignment_id number;
BEGIN
v_query := ' SELECT ASG.ASSIGNMENT_ID FROM PER_ASSIGNMENTS_F ASG, HR_POSITIONS_F POS, ';
 			v_query :=v_query||' XXOSS_HR_AGENCY_ORGUNITS ORG  ';
 			v_query :=v_query||' WHERE ASG.POSITION_ID = POS.POSITION_ID  ';
 			v_query :=v_query||' AND POS.ORGANIZATION_ID = ORG.ORGANIZATION_ID  ';
 			v_query :=v_query||' AND ASG.PRIMARY_FLAG = ''Y''  ';
 			v_query :=v_query||' AND ASG.ASSIGNMENT_TYPE = ''E''  ';
 			v_query :=v_query||' AND TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE  ';
 			v_query :=v_query||' AND TRUNC(SYSDATE) BETWEEN POS.EFFECTIVE_START_DATE AND POS.EFFECTIVE_END_DATE  ';
 			v_query :=v_query||' AND ' || P_LEVEL_NO || ' = ' || P_ORG_ID ;
open c for v_query;
LOOP
fetch c into rec; 
exit when c%NOTFOUND;
V_XXOSS_HR_ASSIGNMENTS.EXTEND;
V_XXOSS_HR_ASSIGNMENTS(V_XXOSS_HR_ASSIGNMENTS.COUNT) := XXOSS_HR_ASSIGNMENT(rec.assignment_id);
END LOOP;
close c ;
RETURN V_XXOSS_HR_ASSIGNMENTS;
END;
Previous Topic: Cache memory
Next Topic: REP 1401
Goto Forum:
  


Current Time: Wed Dec 07 02:55:32 CST 2016

Total time taken to generate the page: 0.08865 seconds