Home » SQL & PL/SQL » SQL & PL/SQL » Build Dynamic Table Name to use in Cursor Definition
Build Dynamic Table Name to use in Cursor Definition [message #250934] Wed, 11 July 2007 21:55 Go to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Hi Guys -

Has anyone successfully built a dynamic table name to select FROM in a cursor definition? My program will accept a parameter from an outside source. What I would like to do is reference the parameter value as part of the table name definition in my cursor:

PROCEDURE map_req_preparers (p_year  IN  VARCHAR2)
AS

cursor c_pos_to_export IS
SELECT
 segment1	           PO_NUMBER,
 po_header_id		   PO_HEADER_ID
FROM
 XXRI.XXRI_PO_FY||p_year||_PO_HEADERS
;

c_all_pos		c_pos_to_export%ROWTYPE;

l_po_num                VARCHAR2(30);

BEGIN
 
  FOR c_all_pos IN c_pos_to_export LOOP
      
      l_po_num := c_all_pos.PO_NUMBER;
      dbms_output.put_line(l_po_num);
  
  END LOOP;

END map_req_preparers;
/


I've also tried using a dynamic cursor such like:


PROCEDURE map_req_preparers (p_year  IN  VARCHAR2)
AS

TYPE c_pos_to_export IS REF CURSOR;

c_all_pos	        c_pos_to_export;

r_all_pos		c_pos_to_export%ROWTYPE;

l_po_num                VARCHAR2(30);

BEGIN
 
         OPEN c_all_pos FOR
	 SELECT
 	  segment1	           PO_NUMBER,
 	  po_header_id		   PO_HEADER_ID
	 FROM
 	  XXRI.XXRI_PO_FY||p_year||_PO_HEADERS
	 ;

	LOOP
	 	
            FETCH c_all_pos INTO r_all_pos;  

            EXIT WHEN c_all_pos%NOTFOUND;
		
	    l_po_num := r_all_pos.PO_NUMBER;

            dbms_output.put_line(l_po_num);

        END LOOP;         

END map_req_preparers;
/


Neither of these approaches will compile.

Any ideas?

Thanks in advance! Cool

edit - erroneous code

[Updated on: Wed, 11 July 2007 21:56]

Report message to a moderator

Re: Build Dynamic Table Name to use in Cursor Definition [message #250936 is a reply to message #250934] Wed, 11 July 2007 21:59 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Search this forum for "EXECUTE IMMEDIATE"
Re: Build Dynamic Table Name to use in Cursor Definition [message #250937 is a reply to message #250936] Wed, 11 July 2007 22:09 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
OK, thanks. I think I got it. I'm going to test something in the morning, but for now it's bed time.

If it's successful, I will post the solution.

Thanks,
Steve
Re: Build Dynamic Table Name to use in Cursor Definition [message #251512 is a reply to message #250934] Fri, 13 July 2007 22:47 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Well, Execute Immediate wasn't exactly what I was looking for. However, I was able to draw up the following anonymous procedure to accomplish my goal. Obviously, this procedure has no real purpose, but the logic and solution should be evident:

-- Example of Dynamic Table building cursor execution 

   BEGIN
    DECLARE
	
      TYPE c_ref_cursor IS REF CURSOR;
      ref_cursor_rec  c_ref_cursor;
	  
	  po_header_id    NUMBER;
	  po_number       VARCHAR2(30);
	  g_fiscal_yr  	  VARCHAR2(2) := '07';
	  
   BEGIN
      OPEN ref_cursor_rec FOR
	  'SELECT po_header_id, segment1 FROM schema_name.PO_EXPORT_PROJECT_FY'||g_fiscal_yr||'_PO_HEADERS';
      LOOP
         FETCH ref_cursor_rec 
         INTO po_header_id, po_number;
         EXIT WHEN ref_cursor_rec%NOTFOUND;
         dbms_output.put_line('ID ==> '||po_header_id ||CHR(10)||'NUMBER ==> '||po_number);
      END LOOP;
      CLOSE ref_cursor_rec;
   END;
END;


Hope that might help someone else looking for an idea. Cool

-Steve
Re: Build Dynamic Table Name to use in Cursor Definition [message #251519 is a reply to message #251512] Sat, 14 July 2007 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the way we programmed in version 7.
Now have a look at bulk operations (keywords: "bulk collect" and "forall").

Regards
Michel
Re: Build Dynamic Table Name to use in Cursor Definition [message #251616 is a reply to message #250934] Sun, 15 July 2007 09:27 Go to previous message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Thanks Michael. I would really prefer to use the BULK COLLECT method rather than the this method, however my company refuses to upgrade its development tools. We are still using Dev Suite 6i. Our database is 9i, however if I wanted to port any of this logic to Dev Suite, it would require mass changes. This way, I can keep it portable for my own selfish purposes. Cool

Thanks again guys,
Steve
Previous Topic: how to pass a current transaction values in statement level triggers?
Next Topic: ORA-02050 error: transaction ID rolled back,some remote dbs may be in-doubt
Goto Forum:
  


Current Time: Sat Dec 03 03:44:00 CST 2016

Total time taken to generate the page: 0.06125 seconds