Home » SQL & PL/SQL » SQL & PL/SQL » Size of Varchar2
Size of Varchar2 [message #212381] Fri, 05 January 2007 01:19 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have created a procedure and passing some parameter values.
I am passing varchar2 values through the parameters MPC_CLOSE_STATUS and MPC_OPEN_STATUS. When I am sending a value of 100 characters, it runs but when I have to pass a varchar2 value of around 1000, this is not working.
Please advice as how can I pass a value of varchar more than 1000 with the parameters in the below procedure.


CREATE OR REPLACE PROCEDURE Pc_Opp_List_For_Reopen_Proc (
	oppid_array IN VARCHAR2,
	MPC_CLOSE_STATUS IN VARCHAR2,
	MPC_OPEN_STATUS IN VARCHAR2,
	cur_oppid OUT Types.cursor_type
	)
	AS
	var_oppid VARCHAR2(50);
	i NUMBER := 1;
	pos NUMBER:=1;
	cnt_opp_id NUMBER:=0;
	cnt_opp_id1 NUMBER:=0;
	BEGIN
		WHILE pos!=0
		LOOP
			SELECT INSTR(oppid_array,',',i) INTO pos FROM dual;
			SELECT SUBSTR(oppid_array,i,pos-i) INTO var_oppid FROM dual;
			i:=pos+1;
			IF pos!=0 THEN
			   INSERT INTO OPPORTUNITY_PARKING_TEMP_TABLE (opportunity_id) VALUES (var_oppid);
			END IF;
			--DBMS_OUTPUT.put_line('var_oppid'||var_oppid);
		END LOOP;

		DECLARE
		CURSOR cur_temp_oppid IS SELECT opportunity_id FROM OPPORTUNITY_PARKING_TEMP_TABLE;			
		BEGIN
			FOR oppid_rec IN cur_temp_oppid
			LOOP
				SELECT COUNT(*) INTO cnt_opp_id FROM
				PC_OPPORTUNITY_DETAILS WHERE CLIENT_ID = (SELECT
				CLIENT_ID FROM PC_OPPORTUNITY_DETAILS WHERE
				OPPORTUNITY_ID = oppid_rec.opportunity_id AND LEAD_STATUS = MPC_CLOSE_STATUS ) AND
				LEAD_STATUS = MPC_OPEN_STATUS;
				--DBMS_OUTPUT.put_line('cnt_opp_id'||cnt_opp_id);
				IF cnt_opp_id = 0 THEN
				   SELECT COUNT(*) INTO cnt_opp_id1 FROM
				   PC_OPPORTUNITY_DETAILS WHERE CLIENT_ID = (SELECT
				   CLIENT_ID FROM PC_OPPORTUNITY_DETAILS WHERE
				   OPPORTUNITY_ID = oppid_rec.opportunity_id) AND LEAD_STATUS = MPC_OPEN_STATUS;
				   --DBMS_OUTPUT.put_line('cnt_opp_id1'||cnt_opp_id1);
					IF cnt_opp_id1 != 0 THEN
						INSERT INTO OPPORTUNITY_TEMP_TABLE (opportunity_id) VALUES (oppid_rec.opportunity_id);
						--DBMS_OUTPUT.put_line('oppid_rec.opportunity_id'||oppid_rec.opportunity_id);
					END IF;
				END IF;	
			END LOOP;
		END;
		OPEN cur_oppid FOR SELECT opportunity_id FROM OPPORTUNITY_TEMP_TABLE;
	END;

Thanks,
Mona
Re: Size of Varchar2 [message #212383 is a reply to message #212381] Fri, 05 January 2007 01:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Usual question I'm afraid - What do you mean by 'Not working'?

Does it error? Does it give the wrong result? Does it do nothing? Give us a clue.
Re: Size of Varchar2 [message #212385 is a reply to message #212381] Fri, 05 January 2007 01:44 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SELECT INSTR(oppid_array,',',i) INTO pos FROM dual;

Don't do that.
pos := instr(oppid_array,',',i);

is how this should be done.
Furthermore, if OPPORTUNITY_PARKING_TEMP_TABLE is a global temporary table, you could better use a pl/sql table for that.
Finally, if you would declare cursor cur_temp_oppid in your initial declaration part, you would have no need for the nested block. This would enhance readability of your procedure.

Now on to your question: you state that "when I have to pass a varchar2 value of around 1000, this is not working"
Please explain the 'is not working'. Does it raise an error? If yes, which error exactly? What exactly does happen?
Previous Topic: Member Functions !?! A Definition Please !!
Next Topic: primary no or not in loop
Goto Forum:
  


Current Time: Fri Dec 09 19:19:15 CST 2016

Total time taken to generate the page: 0.14129 seconds