Home » SQL & PL/SQL » SQL & PL/SQL » Pass partition name as parameter from procedure to the cusrsor in pl/sql (oracle 9i)
Pass partition name as parameter from procedure to the cusrsor in pl/sql [message #376948] Fri, 19 December 2008 04:19 Go to next message
vboppa
Messages: 22
Registered: November 2008
Junior Member
Hi,

I have written a sp as following, I need to pass partition name as parameter to the cursor. Is it possible? If it's not possible is there any alternative way?

CREATE OR REPLACE PROCEDURE sp_purge_cgw_tibco_int(pname VARCHAR2,po_error_code OUT NUMBER)
AS
v_mdn cgw_tibco_int.mdn%TYPE;
e_recharge NUMBER;
e_code     VARCHAR2(20);
e_msg      VARCHAR2(100);
CURSOR c1 IS SELECT * FROM cgw_tibco_int1 partition(pname);

TYPE ARRAY IS TABLE OF c1%ROWTYPE;
data ARRAY;
e_exp		EXCEPTION;

 BEGIN
   OPEN C1;
   LOOP
      FETCH c1 BULK COLLECT INTO data LIMIT 10000;
        FOR i IN 1..data.COUNT LOOP
	      IF data(i).SERVICE_TYPE='DATA' THEN
             UPDATE mdn_map_tab1 
                SET tot_amt=nvl(tot_amt,0) - nvl(data(i).amount,0),
                    entry_date=SYSDATE
              WHERE mdn=data(i).MDN;
          END IF;
        END LOOP;
   EXIT WHEN c1%NOTFOUND;
   END LOOP;
   CLOSE C1;
 po_error_code:=1;
         EXCEPTION
         WHEN OTHERS THEN
		      po_error_code:=0;
              e_code:=substr(sqlcode,1,20);
              e_msg:=substr(sqlerrm,1,100);
              INSERT INTO ERROR VALUES(e_code,e_msg,NULL,SYSDATE);

END;
/

Re: Pass partition name as parameter from procedure to the cusrsor in pl/sql [message #376950 is a reply to message #376948] Fri, 19 December 2008 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Give the partition name (parameter) in cursor definition and at open time.
cursor c1(pname) is ...
open c1(pname);

Regards
Michel
Re: Pass partition name as parameter from procedure to the cusrsor in pl/sql [message #376951 is a reply to message #376948] Fri, 19 December 2008 04:24 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link on how to do partition pruning .

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#sthref2627

Also check this link how to get optimum performance when using cursor loops.

http://www.oracle.com/technology/tech/pl_sql/pdf/doing_sql_from_plsql.pdf

Especially the topic under Cursor Taxonomy and more precisely Page No : 28.

Regards

Raj
Re: Pass partition name as parameter from procedure to the cusrsor in pl/sql [message #376962 is a reply to message #376950] Fri, 19 December 2008 04:40 Go to previous messageGo to next message
vboppa
Messages: 22
Registered: November 2008
Junior Member
I have accordingly, still it's not working. And Error table populating with: ORA-02149: Specified partition does not exist.

Changed SP

CREATE OR REPLACE PROCEDURE sp_purge_cgw_tibco_int(part_name IN VARCHAR2,po_error_code OUT NUMBER)
AS
v_mdn cgw_tibco_int.mdn%TYPE;
e_recharge NUMBER;
e_code     VARCHAR2(20);
e_msg      VARCHAR2(100); 

CURSOR c1(pname VARCHAR2) IS SELECT * FROM cgw_tibco_int1 partition(pname);

TYPE ARRAY IS TABLE OF c1%ROWTYPE;
data ARRAY;
e_exp		EXCEPTION;

 BEGIN
   OPEN C1(part_name);
   LOOP
      FETCH c1 BULK COLLECT INTO data LIMIT 10000;
        FOR i IN 1..data.COUNT LOOP
	      IF data(i).SERVICE_TYPE='DATA' THEN
             UPDATE mdn_map_tab1 
                SET tot_amt=nvl(tot_amt,0) - nvl(data(i).amount,0),
                    entry_date=SYSDATE
              WHERE mdn=data(i).MDN;
          END IF;
        END LOOP;
   EXIT WHEN c1%NOTFOUND;
   END LOOP;
   CLOSE C1;
 po_error_code:=1;
         EXCEPTION
         WHEN OTHERS THEN
		      po_error_code:=0;
              e_code:=substr(sqlcode,1,20);
              e_msg:=substr(sqlerrm,1,100);
              INSERT INTO ERROR VALUES(e_code,e_msg,NULL,SYSDATE);

END;
/

Re: Pass partition name as parameter from procedure to the cusrsor in pl/sql [message #377004 is a reply to message #376962] Fri, 19 December 2008 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-02149: Specified partition does not exist
 *Cause:   Partition not found for the object.
 *Action:  Retry with correct partition name.


Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Pass partition name as parameter from procedure to the cusrsor in pl/sql [message #377024 is a reply to message #376948] Fri, 19 December 2008 08:36 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
SELECT * FROM cgw_tibco_int1 partition(pname);

In this case, PNAME does not refer to the variable PNAME, but PNAME is treated as name of the partition.

If you would like to pass the partition name in variable, you would have to build the SELECT statement dynamically.
Better option would be not to refer the partition directly and create the appropriate WHERE condition, something like
SELECT * FROM cgw_tibco_int1
WHERE <partitioned_column> = <its_value_for_pname>;
. Oracle is smart enough to use only the affected partition, as states the link posted by Raj.

By the way, all this cursor loop may be done in a single UPDATE statement.
Previous Topic: selecting by adding a value to comma separated record
Next Topic: Table and Procedure scritps from SQL (merged)
Goto Forum:
  


Current Time: Wed Dec 07 20:50:02 CST 2016

Total time taken to generate the page: 0.08047 seconds