| Pass partition name as parameter from procedure to the cusrsor in pl/sql [message #376948] |
Fri, 19 December 2008 04:19  |
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 #376962 is a reply to message #376950] |
Fri, 19 December 2008 04:40  |
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 #377024 is a reply to message #376948] |
Fri, 19 December 2008 08:36 |
flyboy
Messages: 1903
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>;
By the way, all this cursor loop may be done in a single UPDATE statement.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
