Home » SQL & PL/SQL » SQL & PL/SQL » Had a Problem with type in function
Had a Problem with type in function [message #380752] Tue, 13 January 2009 04:04 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

I Had a problem in function with object and type I created.

below is the code.


create or replace FUNCTION esp_GetUserFeedback_v6
(
	v_Department_ID varchar2,
	v_Manager varchar2,
	v_UserName varchar2,
	v_Feedback_ID varchar2,
	v_Process_ID varchar2,
	v_Content_ID varchar2,
	v_Type varchar2,
	v_FCreated_1 varchar2,
	v_FCreated_2 varchar2,
	v_FRCreated_1 varchar2,
	v_FRCreated_2 varchar2,
	v_Response varchar2,
	v_Status_ID varchar2,
	v_RatingStart varchar2,
	v_RatingEnd varchar2,
	v_Category varchar2,
	v_Area_ID varchar2,
	v_Update varchar2,
	v_UpdateType varchar2,
	v_OrderCol varchar2,
	v_OrderType varchar2
)
RETURN SYS_REFCURSOR
AS
v_SQLQry varchar2(8000);
v_temp SYS_REFCURSOR;
cv_2  SYS_REFCURSOR;
v_splitvalue varchar2(1000);
v_Response1 VARCHAR2(2000);
BEGIN
v_SQLQry:= 'SELECT 	P.process_id,D.name AS department,C.content_id,
Case
			when F.Type = ''P'' then RTrim (Ltrim (P.name))
			else RTrim (Ltrim (C.title))
		End as Title
,F.feedback_id,F.created,F.rating,FC.title AS 

category,FS.status_id,FC.category_id,FS.status,D.department_id,U.username,F.type,U.manager,FA.area_id,	FA.area FROM 
		feedback_area_ec FA  INNER JOIN
		feedback_area_dept_ec FAD  
			ON FA.area_id = FAD.area_id RIGHT OUTER JOIN
		feedback_status_ec FS  INNER JOIN
		feedback_ec F  
			ON FS.status_id = F.status_id INNER JOIN
		feedback_category_ec FC  
			ON F.category_id = FC.category_id 
			ON FAD.feedback_area_id = F.feedback_area_id LEFT OUTER JOIN
		departments_ec D  
			ON F.dept_id = D.department_id LEFT OUTER JOIN
		feedback_response_ec FR  
			ON F.feedback_id = FR.feedback_id LEFT OUTER JOIN
		users_ec U  
			ON F.user_id = U.user_id LEFT OUTER JOIN
		processes_ec P  
			ON F.process_id = P.process_id LEFT OUTER JOIN
		content_ec C  
			ON F.content_id = C.content_id LEFT OUTER JOIN
		contenttracker_ec CT  
			ON F.contenttracker_id = CT.contenttracker_id
	WHERE 1=1';

IF v_Department_ID is not NULL AND v_Department_ID <> '0' THEN
v_SQLQry:= v_SQLQry || ' and D.Department_ID = ''' || v_Department_ID || '''';
END IF;

IF  v_Manager is not NULL THEN
v_SQLQry:= v_SQLQry || ' and Manager = ''' || v_Manager || '''';
END IF;

IF v_UserName is not NULL THEN
v_SQLQry:= v_SQLQry || ' and UserName = ''' || v_UserName || '''';
END IF;

IF v_Feedback_ID is not NULL THEN
v_SQLQry:= v_SQLQry || ' and F.feedback_id = ''' || v_Feedback_ID || '''';
END IF;

IF v_Process_ID is not NULL AND v_Process_ID <> '0' THEN
v_SQLQry:= v_SQLQry || ' and P.Process_ID = ''' || v_Process_ID || '''';
END IF;

IF v_Content_ID is not NULL AND  v_Content_ID <> '0' THEN
v_SQLQry:= v_SQLQry || ' and C.Content_ID = ''' || v_Content_ID || '''';
END IF;

IF v_Type <> 'both' THEN
v_SQLQry:= v_SQLQry || ' and Type = ''' || v_Type || '''';
END IF;

IF v_FCreated_1 is not NULL AND v_FCreated_2 is null THEN
v_SQLQry:= v_SQLQry || ' and F.Created > ''' || v_FCreated_1 || '' ||'''';
END IF;

IF v_FCreated_1 is null AND v_FCreated_2 is not NULL THEN
v_SQLQry:= v_SQLQry || ' and F.Created < ''' || v_FCreated_2 || '' ||'''';
END IF;

IF v_FCreated_1 is not NULL AND v_FCreated_2 is not NULL THEN
v_SQLQry:= v_SQLQry || ' and (F.Created >= ''' || v_FCreated_1 || '' || ''' and F.Created <= ''' || v_FCreated_2 || '' || 

''') ';
END IF;

IF v_FRCreated_1 is not NULL AND  v_FRCreated_2 is null THEN
v_SQLQry:= v_SQLQry || ' and FR.Created > ''' || v_FRCreated_1 || '''';
END IF;

IF v_FRCreated_1 is null AND  v_FRCreated_2 is not NULL THEN
v_SQLQry:= v_SQLQry || ' and FR.Created < ''' || v_FRCreated_2 || '''';
END IF;

IF v_FRCreated_1 is not NULL AND  v_FRCreated_2 is not NULL THEN
v_SQLQry:= v_SQLQry || ' and (FR.Created >= ''' || v_FRCreated_1 || ''' and FR.Created <= ''' || v_FRCreated_2 || ''') ';
END IF;

OPEN cv_2 for SELECT splitvalue FROM TABLE (CAST(SPLIT(v_Response,' ') as split_rec));
   LOOP
        FETCH cv_2 INTO v_splitvalue;
        EXIT WHEN cv_2%NOTFOUND;
        v_Response1:= v_Response1||' AND (FR.Response LIKE '''|| v_splitvalue||'%'') ';
  --" AND (FR.Response LIKE '%" & strItem & "%') "                       
    END LOOP;




IF v_Response is not NULL THEN
v_SQLQry:= v_SQLQry || v_Response1;
END IF;

IF v_Status_ID <> '0' THEN
v_SQLQry:= v_SQLQry || ' and FS.Status_ID = ''' || v_Status_ID || '''';
END IF;

IF v_RatingStart <> '0' AND v_RatingEnd = '0' THEN
v_SQLQry:= v_SQLQry || ' and F.Rating >= ''' || v_RatingStart || '''';
END IF;

IF v_RatingStart = '0' AND v_RatingEnd <> '0' THEN
v_SQLQry:= v_SQLQry || ' and F.Rating <= ''' || v_RatingEnd || '''';
END IF;

IF v_RatingStart <> '0' AND v_RatingEnd <> '0' THEN
v_SQLQry:= v_SQLQry || ' and (F.Rating >= ''' || v_RatingStart || ''' and F.Rating <= ''' || v_RatingEnd || ''') ';
END IF;

IF v_Category <> '0' THEN
v_SQLQry:= v_SQLQry || ' and FC.Category_ID = ''' || v_Category || '''';
END IF;

IF v_Area_ID <> '0' THEN
v_SQLQry:= v_SQLQry || ' and FA.Area_ID = ''' || v_Area_ID || '''';
END IF;

v_SQLQry:= v_SQLQry || ' GROUP BY P.process_id, P.name, D.name, C.content_id, F.feedback_id,  F.created, 
			         F.rating, FC.title, FS.status_id, FC.category_id, FS.status, D.department_id,
			         C.title, U.username, F.type, U.manager, FA.area_id, FA.area';

IF v_Update is not NULL THEN
Begin
	v_SQLQry:= v_SQLQry || ' HAVING (COUNT(FR.feedback_id) - 1) ';

	IF v_UpdateType = 'more' THEN
	v_SQLQry:= v_SQLQry || ' > ';
	END IF;

	IF v_UpdateType = 'less' THEN
	v_SQLQry:= v_SQLQry || ' < ';
	END IF;

	IF v_UpdateType = 'equal' THEN
	v_SQLQry:= v_SQLQry || ' = '	;
	END IF;

	v_SQLQry:= v_SQLQry || v_Update;
End;
END IF;

If v_OrderCol is not NULL THEN
Begin
	v_SQLQry:= v_SQLQry || ' Order by ' || v_OrderCol;
	IF  v_OrderType is not NULL THEN
	v_SQLQry:= v_SQLQry || ' ' || v_OrderType;
	END IF;
end;

ELSE
begin
	v_SQLQry:= v_SQLQry || ' ORDER BY F.Created ASC';
end;
END IF;
v_SQLQRy:= 'SELECT VW.process_id,VW.process,VW.department,VW.content_id,VW.feedback_id,
FE.PROGRESS,VW.created,VW.rating,VW.category,VW.status_id,VW.category_id,VW.status,
VW.department_id,VW.title,VW.username,
VW.type,VW.manager,VW.area_id,VW.area 
FROM ('||v_SQLQRY ||') VW,FEEDBACK_EC FE 
WHERE FE.FEEDBACK_ID=VW.FEEDBACK_ID';



--DBMS_OUTPUT.PUT_LINE(v_SQLQry);
OPEN v_temp FOR v_SQLQry;
RETURN v_temp;
END;
 
/


create or replace FUNCTION esp_GetCasesReport_v4
(
  v_UserName IN VARCHAR2 ,
  v_Manager IN VARCHAR2 ,
  v_Telephone IN VARCHAR2  ,
  v_CaseRef IN NVARCHAR2  ,
  v_Exit1 IN VARCHAR2 ,
  v_Exit1None IN VARCHAR2  ,
  v_Exit2 IN VARCHAR2 ,
  v_Exit2None IN VARCHAR2 ,
  v_Exit3 IN VARCHAR2 ,
  v_Exit3None IN VARCHAR2 ,
  v_SaveReason IN VARCHAR2 ,
  v_SaveReasonAuto IN VARCHAR2 ,
  v_CaseStatus IN VARCHAR2 ,
  v_Created_1 IN VARCHAR2 ,
  v_Created_2 IN VARCHAR2 ,
  v_CaseDuration IN NVARCHAR2  ,
  v_CaseDurationType IN NVARCHAR2 ,
  v_NoOfContacts IN NVARCHAR2 ,
  v_NoOfContactsType IN VARCHAR2 ,
  v_NoOfResponse IN NVARCHAR2 ,
  v_NoOfResponseType IN VARCHAR2,
  v_Department_ID IN NVARCHAR2 ,
  v_Auth_ID IN VARCHAR2 ,
  v_OrderCol IN VARCHAR2 ,
  v_OrderType IN VARCHAR2 
)RETURN SYS_REFCURSOR AS

   cv_1  SYS_REFCURSOR;
   v_SQLQry VARCHAR2(8000);
BEGIN

   v_SQLQry := '
SELECT ca.case_id, ca.custref, ca.created, ca.status, ca.savereason,
       COUNT (sr.case_id) srs, cd.caseduration, rpc.responses, 
Case 
		when	CA.status = ''Closed'' then nvl( EP1.label, ''[Not set]'')
		else	EP1.label  end EP1 ,
Case 
		when	CA.status = ''Closed'' then nvl( EP2.label, ''[Not set]'')
		else	EP2.label  end EP2 , 
Case 
		when	CA.status = ''Closed'' then nvl( EP1.label, ''[Not set]'')
                When    CA.STATUS=''OPEN'' then CA.savereason
		else	EP3.label  end EP3, 
ep1.exitpoint1_id, ep2.exitpoint2_id,
       ep3.exitpoint3_id
  FROM cases_ec ca INNER JOIN servicerequest_ec sr ON ca.case_id = sr.case_id
       INNER JOIN contacts_ec co ON sr.contact_id = co.contact_id
       LEFT OUTER JOIN users_ec u ON co.user_id = u.user_id
       LEFT OUTER JOIN vw_responsespercase_v4 rpc ON ca.case_id = rpc.case_id
       LEFT OUTER JOIN vw_caseduration_v4 cd ON ca.case_id = cd.case_id
       LEFT OUTER JOIN exitpoint3_ec ep3 ON ca.exitpoint3_id =
                                                             ep3.exitpoint3_id
       LEFT OUTER JOIN exitpoint2_ec ep2 ON ca.exitpoint2_id =
                                                             ep2.exitpoint2_id
       LEFT OUTER JOIN exitpoint1_ec ep1 ON ca.exitpoint1_id =
                                                             ep1.exitpoint1_id
 WHERE 1 = 1 ';

   IF v_UserName IS NOT NULL THEN
      v_SQLQry := v_SQLQry || ' and U.username = ''' || v_UserName || '''';

   END IF;

   IF v_Manager IS NOT NULL THEN
      v_SQLQry := v_SQLQry || ' and U.manager = ''' || v_Manager || '''';

   END IF;

   IF v_Telephone IS NOT NULL THEN
      v_SQLQry := v_SQLQry || ' and CA.custref like ''%' || v_Telephone || '%''';

   END IF;

   IF v_CaseRef IS NOT NULL THEN
      v_SQLQry := v_SQLQry || ' and CA.case_id like ''%' || v_CaseRef || '%''';

   END IF;

   IF v_Exit1 IS NOT NULL THEN
      v_SQLQry := v_SQLQry || ' and EP1.label = ''' || v_Exit1 || '''';

   END IF;

   IF v_Exit2 IS NOT NULL THEN
      v_SQLQry := v_SQLQry || ' and EP2.label = ''' || v_Exit2 || '''';

   END IF;

   IF v_Exit3 IS NOT NULL THEN
      v_SQLQry := v_SQLQry || ' and EP3.label  = ''' || v_Exit3 || '''';

   END IF;

   IF v_Exit1None = 'NULL' THEN
      v_SQLQry := v_SQLQry || ' and (EP1.label IS NULL)';

   END IF;

   IF v_Exit2None = 'NULL' THEN
      v_SQLQry := v_SQLQry || ' and (EP2.label IS NULL)';

   END IF;

   IF v_Exit3None = 'NULL' THEN
      v_SQLQry := v_SQLQry || ' and (EP3.label IS NULL)';

   END IF;

   IF v_SaveReason IS NOT NULL THEN
      v_SQLQry := v_SQLQry || ' and CA.savereason like ''%' || v_SaveReason || '%''';

   END IF;

   IF v_SaveReasonAuto IS NOT NULL THEN
      v_SQLQry := v_SQLQry || ' and CA.savereason = ''' || v_SaveReasonAuto || '''';

   END IF;

   IF v_CaseStatus <> 'all' THEN
      v_SQLQry := v_SQLQry || ' and CA.status in (''' || v_CaseStatus || ''')';

   END IF;

   IF v_Created_1 IS NOT NULL AND v_Created_2 IS NULL THEN
      v_SQLQry := v_SQLQry || ' and CA.created > ''' || v_Created_1 || '''';

   END IF;

   IF v_Created_1 IS NULL  AND v_Created_2 IS NOT NULL THEN
      v_SQLQry := v_SQLQry || ' and CA.created < ''' || v_Created_2 || '''';

   END IF;

   IF v_Created_1 IS NOT NULL AND v_Created_2 IS NOT NULL THEN
      v_SQLQry := v_SQLQry || ' and (CA.created >= ''' || v_Created_1 || ''' and CA.created <= ''' || v_Created_2 || ''') ';

   END IF;

   IF v_CaseDuration IS NOT NULL THEN
   BEGIN
      v_SQLQry := v_SQLQry || ' and CD.caseduration';

      IF v_CaseDurationType = 'more' THEN
         v_SQLQry := v_SQLQry || ' > ';

      END IF;

      IF v_CaseDurationType = 'less' THEN
         v_SQLQry := v_SQLQry || ' < ';

      END IF;

      IF v_CaseDurationType = 'equal' THEN
         v_SQLQry := v_SQLQry || ' = ';

      END IF;

      v_SQLQry := v_SQLQry || v_CaseDuration;

   END;
   END IF;

   IF v_NoOfResponse IS NOT NULL THEN
   BEGIN
      v_SQLQry := v_SQLQry || ' and RPC.responses';

      IF v_NoOfResponseType = 'more' THEN
         v_SQLQry := v_SQLQry || ' > ';

      END IF;

      IF v_NoOfResponseType = 'less' THEN
         v_SQLQry := v_SQLQry || ' < ';

      END IF;

      IF v_NoOfResponseType = 'equal' THEN
         v_SQLQry := v_SQLQry || ' = ';

      END IF;

      v_SQLQry := v_SQLQry || v_NoOfResponse;

   END;
   END IF;

   IF v_Department_ID IS NOT NULL THEN
      v_SQLQry := v_SQLQry || ' and CO.department_id = ''' || v_Department_ID || '''';

   END IF;

   IF v_Auth_ID IS NOT NULL THEN
      v_SQLQry := v_SQLQry || ' and CO.auth_id in (' || v_Auth_ID || ')';

   END IF;

   v_SQLQry := v_SQLQry || ' GROUP BY CA.custref, CA.created, CA.status, CA.savereason, EP1.exitpoint1_id, EP1.label, 

EP2.label, EP2.exitpoint2_id, EP3.exitpoint3_id, EP3.label, CA.case_id, CD.caseduration, RPC.responses';

   IF v_NoOfContacts IS NOT NULL THEN
   BEGIN
      v_SQLQry := v_SQLQry || ' HAVING COUNT(SR.case_id) ';

      IF v_NoOfContactsType = 'more' THEN
         v_SQLQry := v_SQLQry || ' > ';

      END IF;

      IF v_NoOfContactsType = 'less' THEN
         v_SQLQry := v_SQLQry || ' < ';

      END IF;

      IF v_NoOfContactsType = 'equal' THEN
         v_SQLQry := v_SQLQry || ' = ';

      END IF;

      v_SQLQry := v_SQLQry || v_NoOfContacts;

   END;
   END IF;

   IF v_OrderCol IS NOT NULL THEN
   BEGIN
      v_SQLQry := v_SQLQry || ' Order by ' || v_OrderCol;

      IF v_OrderType IS NOT NULL THEN
         v_SQLQry := v_SQLQry || '  ' || v_OrderType;

      END IF;

   END;
   END IF;
--DBMS_OUTPUT.PUT_LINE(v_sqlqry);

OPEN cv_1 FOR v_SQLQry;
RETURN cv_1;
  
END;


/



CREATE GLOBAL TEMPORARY TABLE tt_v_items
(
  PIpid NUMBER(10,0) ,
  PIqid NUMBER(10,0) ,
  PIpx NUMBER(10,0) ,
  PIpy NUMBER(10,0) ,
  PItext VARCHAR2(200) ,
  PItype VARCHAR2(50) ,
  PIfrom VARCHAR2(500) ,
  PIto VARCHAR2(500) ,
  PIas VARCHAR2(200) ,
  PIc NUMBER(10,0) ,
  PIe1 NUMBER(10,0) ,
  PIe2 NUMBER(10,0) ,
  PIe3 NUMBER(10,0) ,
  PIri VARCHAR2(40) ,
  PIlqid NUMBER(10,0) ,
  PIfoc NUMBER(1,0) ,
  PIui VARCHAR2(200) ,
  PIstls NUMBER(1,0) ,
  PIpans NUMBER(1,0) ,
  PIvagent NUMBER(1,0) ,
  PIretry NUMBER(10,0) ,
  PItag VARCHAR2(50) 
);


create or replace type tt_v_items_type_obj as object
  (
    PIpid NUMBER(10,0) ,
  PIqid NUMBER(10,0) ,
  PIpx NUMBER(10,0) ,
  PIpy NUMBER(10,0) ,
  PItext VARCHAR2(200) ,
  PItype VARCHAR2(50) ,
  PIfrom VARCHAR2(500) ,
  PIto VARCHAR2(500) ,
  PIas VARCHAR2(200) ,
  PIc NUMBER(10,0) ,
  PIe1 NUMBER(10,0) ,
  PIe2 NUMBER(10,0) ,
  PIe3 NUMBER(10,0) ,
  PIri VARCHAR2(40) ,
  PIlqid NUMBER(10,0) ,
  PIfoc NUMBER(1,0) ,
  PIui VARCHAR2(200) ,
  PIstls NUMBER(1,0) ,
  PIpans NUMBER(1,0) ,
  PIvagent NUMBER(1,0) ,
  PIretry NUMBER(10,0) ,
  PItag VARCHAR2(50) 
  );


create TYPE tt_v_items_type IS TABLE OF tt_v_items_type_obj ;

/*

Can we use this below statement here

create TYPE tt_v_items_type IS TABLE OF tt_v_items%Rowtype;

*/


create or replace 
FUNCTION fProcessItemFull
(
  v_id IN NUMBER
)
RETURN tt_v_items_type PIPELINED
AS
v_temp SYS_REFCURSOR;
v_temp_1 TT_V_ITEMS%ROWTYPE;

BEGIN
   -- First Items that DO have a library ID.
   INSERT INTO tt_v_items
     ( PIpid, PIqid, PIpx, PIpy, PItext, PItype, PIfrom, PIto, PIas, PIc, PIe1, PIe2, 
PIe3, PIri, PIlqid, PIfoc, PIui, PIstls, PIpans, PIvagent, PIretry, PItag )
     ( SELECT PQ.process_id,
              PQ.question_id,
              PQ.posx,
              PQ.posy,
              LQ.innertext,
              PQ.itemtype,
              PQ.linkfrom,
              PQ.linkto,
              PQ.associated,
              LQ.content_id,
              PQ.exitpoint1_id,
              PQ.exitpoint2_id,
              PQ.exitpoint3_id,
              LQ.resolveRef,
              PQ.libquestion_idfk,
              PQ.followoncall,
              PQ.userinput,
              LQ.isLocked,
              PQ.PreviousAnswer,
              PQ.VisibleToAgent,
              PQ.RetryAttempt,
              LQ.Tags
       FROM processesquestions_ec PQ
              JOIN libraryquestions_ec LQ
               ON PQ.libquestion_idfk = LQ.libquestion_id
          WHERE PQ.process_id = v_id );

   -- Now items that DO NOT hava a library ID.
   INSERT INTO tt_v_items
     ( PIpid, PIqid, PIpx, PIpy, PItext, PItype, PIfrom, PIto, PIas, PIc, PIe1, PIe2, 
PIe3, PIri, PIlqid, PIfoc, PIui, PIstls, PIpans, PIvagent, PIretry, PItag )
     ( SELECT process_id,
              question_id,
              posx,
              posy,
              innertext,
              itemtype,
              linkfrom,
              linkto,
              associated,
              content_id,
              exitpoint1_id,
              exitpoint2_id,
              exitpoint3_id,
              resolveidentifier,
              libquestion_idfk,
              followoncall,
              userinput,
              NULL stls,
              PreviousAnswer,
              VisibleToAgent,
              RetryAttempt,
              NULL tag
       FROM processesquestions_ec PQ
          WHERE PQ.process_id = v_id
                  AND PQ.libquestion_idfk IS NULL );

   OPEN v_temp FOR
      SELECT *
        FROM tt_v_items;

   LOOP
      FETCH v_temp INTO v_temp_1;
      EXIT WHEN v_temp%NOTFOUND;
      PIPE ROW ( v_temp_1 );
   END LOOP;

END;


Encountered with below errors

Error(80,7): PL/SQL: Statement ignored
Error(80,18): PLS-00382: expression is of wrong type





Any help really appreciated

Thanks in advance
Re: Had a Problem with type in function [message #380753 is a reply to message #380752] Tue, 13 January 2009 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The error is at line 80.

Regards
Michel
Re: Had a Problem with type in function [message #380755 is a reply to message #380752] Tue, 13 January 2009 04:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You have several statements in your code. When did the error occur?
Re: Had a Problem with type in function [message #380756 is a reply to message #380753] Tue, 13 January 2009 04:09 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member


Hi ,

Pls ignore my previous post.

Here below is the problem i having with function.



CREATE GLOBAL TEMPORARY TABLE tt_v_items
(
  PIpid NUMBER(10,0) ,
  PIqid NUMBER(10,0) ,
  PIpx NUMBER(10,0) ,
  PIpy NUMBER(10,0) ,
  PItext VARCHAR2(200) ,
  PItype VARCHAR2(50) ,
  PIfrom VARCHAR2(500) ,
  PIto VARCHAR2(500) ,
  PIas VARCHAR2(200) ,
  PIc NUMBER(10,0) ,
  PIe1 NUMBER(10,0) ,
  PIe2 NUMBER(10,0) ,
  PIe3 NUMBER(10,0) ,
  PIri VARCHAR2(40) ,
  PIlqid NUMBER(10,0) ,
  PIfoc NUMBER(1,0) ,
  PIui VARCHAR2(200) ,
  PIstls NUMBER(1,0) ,
  PIpans NUMBER(1,0) ,
  PIvagent NUMBER(1,0) ,
  PIretry NUMBER(10,0) ,
  PItag VARCHAR2(50) 
);


create or replace type tt_v_items_type_obj as object
  (
    PIpid NUMBER(10,0) ,
  PIqid NUMBER(10,0) ,
  PIpx NUMBER(10,0) ,
  PIpy NUMBER(10,0) ,
  PItext VARCHAR2(200) ,
  PItype VARCHAR2(50) ,
  PIfrom VARCHAR2(500) ,
  PIto VARCHAR2(500) ,
  PIas VARCHAR2(200) ,
  PIc NUMBER(10,0) ,
  PIe1 NUMBER(10,0) ,
  PIe2 NUMBER(10,0) ,
  PIe3 NUMBER(10,0) ,
  PIri VARCHAR2(40) ,
  PIlqid NUMBER(10,0) ,
  PIfoc NUMBER(1,0) ,
  PIui VARCHAR2(200) ,
  PIstls NUMBER(1,0) ,
  PIpans NUMBER(1,0) ,
  PIvagent NUMBER(1,0) ,
  PIretry NUMBER(10,0) ,
  PItag VARCHAR2(50) 
  );


create TYPE tt_v_items_type IS TABLE OF tt_v_items_type_obj ;

/*

Can we use this below statement here

create TYPE tt_v_items_type IS TABLE OF tt_v_items%Rowtype;

*/


create or replace 
FUNCTION fProcessItemFull
(
  v_id IN NUMBER
)
RETURN tt_v_items_type PIPELINED
AS
v_temp SYS_REFCURSOR;
v_temp_1 TT_V_ITEMS%ROWTYPE;

BEGIN
   -- First Items that DO have a library ID.
   INSERT INTO tt_v_items
     ( PIpid, PIqid, PIpx, PIpy, PItext, PItype, PIfrom, PIto, PIas, PIc, PIe1, PIe2, 
PIe3, PIri, PIlqid, PIfoc, PIui, PIstls, PIpans, PIvagent, PIretry, PItag )
     ( SELECT PQ.process_id,
              PQ.question_id,
              PQ.posx,
              PQ.posy,
              LQ.innertext,
              PQ.itemtype,
              PQ.linkfrom,
              PQ.linkto,
              PQ.associated,
              LQ.content_id,
              PQ.exitpoint1_id,
              PQ.exitpoint2_id,
              PQ.exitpoint3_id,
              LQ.resolveRef,
              PQ.libquestion_idfk,
              PQ.followoncall,
              PQ.userinput,
              LQ.isLocked,
              PQ.PreviousAnswer,
              PQ.VisibleToAgent,
              PQ.RetryAttempt,
              LQ.Tags
       FROM processesquestions_ec PQ
              JOIN libraryquestions_ec LQ
               ON PQ.libquestion_idfk = LQ.libquestion_id
          WHERE PQ.process_id = v_id );

   -- Now items that DO NOT hava a library ID.
   INSERT INTO tt_v_items
     ( PIpid, PIqid, PIpx, PIpy, PItext, PItype, PIfrom, PIto, PIas, PIc, PIe1, PIe2, 
PIe3, PIri, PIlqid, PIfoc, PIui, PIstls, PIpans, PIvagent, PIretry, PItag )
     ( SELECT process_id,
              question_id,
              posx,
              posy,
              innertext,
              itemtype,
              linkfrom,
              linkto,
              associated,
              content_id,
              exitpoint1_id,
              exitpoint2_id,
              exitpoint3_id,
              resolveidentifier,
              libquestion_idfk,
              followoncall,
              userinput,
              NULL stls,
              PreviousAnswer,
              VisibleToAgent,
              RetryAttempt,
              NULL tag
       FROM processesquestions_ec PQ
          WHERE PQ.process_id = v_id
                  AND PQ.libquestion_idfk IS NULL );

   OPEN v_temp FOR
      SELECT *
        FROM tt_v_items;

   LOOP
      FETCH v_temp INTO v_temp_1;
      EXIT WHEN v_temp%NOTFOUND;
      PIPE ROW ( v_temp_1 );
   END LOOP;

END;


Encountered with below errors

Error(80,7): PL/SQL: Statement ignored
Error(80,18): PLS-00382: expression is of wrong type








Thanks in advance
Re: Had a Problem with type in function [message #380757 is a reply to message #380756] Tue, 13 January 2009 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same answer.

Regards
Michel
Re: Had a Problem with type in function [message #380783 is a reply to message #380756] Tue, 13 January 2009 05:55 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
You're trying to send on output rows of tt_v_items%rowtype while your function declared to output tt_v_items_type.

But anyway why are you trying to insert the rows to output into a temporary table first?

Bye Alessandro
Re: Had a Problem with type in function [message #380906 is a reply to message #380783] Wed, 14 January 2009 00:18 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Pls find the below code and let me know where i went wrong.

create or replace type tt_v_items_type_obj as object
  (
    PIpid NUMBER(10,0) ,
  PIqid NUMBER(10,0) ,
  PIpx NUMBER(10,0) ,
  PIpy NUMBER(10,0) ,
  PItext VARCHAR2(200) ,
  PItype VARCHAR2(50) ,
  PIfrom VARCHAR2(500) ,
  PIto VARCHAR2(500) ,
  PIas VARCHAR2(200) ,
  PIc NUMBER(10,0) ,
  PIe1 NUMBER(10,0) ,
  PIe2 NUMBER(10,0) ,
  PIe3 NUMBER(10,0) ,
  PIri VARCHAR2(40) ,
  PIlqid NUMBER(10,0) ,
  PIfoc NUMBER(1,0) ,
  PIui VARCHAR2(200) ,
  PIstls NUMBER(1,0) ,
  PIpans NUMBER(1,0) ,
  PIvagent NUMBER(1,0) ,
  PIretry NUMBER(10,0) ,
  PItag VARCHAR2(50) 
  )



create or replace TYPE tt_v_items_type IS TABLE OF tt_v_items_type_obj


create or replace
FUNCTION temp
(
  v_id IN NUMBER
)
RETURN tt_v_items_type PIPELINED
AS
v_temp SYS_REFCURSOR;
v_temp_1 TT_V_ITEMS_TYPE_OBJ;


BEGIN
   OPEN v_temp FOR select * from (
       SELECT PQ.process_id,
              PQ.question_id,
              PQ.posx,
              PQ.posy,
              LQ.innertext,
              PQ.itemtype,
              PQ.linkfrom,
              PQ.linkto,
              PQ.associated,
              LQ.content_id,
              PQ.exitpoint1_id,
              PQ.exitpoint2_id,
              PQ.exitpoint3_id,
              LQ.resolveRef,
              PQ.libquestion_idfk,
              PQ.followoncall,
              PQ.userinput,
              LQ.isLocked,
              PQ.PreviousAnswer,
              PQ.VisibleToAgent,
              PQ.RetryAttempt,
              LQ.Tags
       FROM processesquestions_ec PQ
              JOIN libraryquestions_ec LQ
               ON PQ.libquestion_idfk = LQ.libquestion_id
          WHERE PQ.process_id = v_id 
union
SELECT process_id,
              question_id,
              posx,
              posy,
              innertext,
              itemtype,
              linkfrom,
              linkto,
              associated,
              content_id,
              exitpoint1_id,
              exitpoint2_id,
              exitpoint3_id,
              resolveidentifier,
              libquestion_idfk,
              followoncall,
              userinput,
              NULL stls,
              PreviousAnswer,
              VisibleToAgent,
              RetryAttempt,
              NULL tag
       FROM processesquestions_ec PQ
          WHERE PQ.process_id = v_id
                  AND PQ.libquestion_idfk IS NULL) ;

   LOOP
      FETCH v_temp INTO v_temp_1;
      EXIT WHEN v_temp%NOTFOUND;
      PIPE ROW ( v_temp_1 );
   END LOOP;

END;



SQL> select * from table (temp(2381)) ;
select * from table (temp(2381))
                     *
ERROR at line 1:
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
ORA-06512: at "MIGRATION.TEMP", line 67





Thanks in advance
Re: Had a Problem with type in function [message #380913 is a reply to message #380906] Wed, 14 January 2009 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
 *Cause: Number and/or types of columns in a query does not match declared
         return type of a result set  variable, or declared types of two Result
         Set variables do not match.
 *Action: Change the program statement or declaration. Verify what query the variable
          actually refers to during execution.

Regards
Michel
Re: Had a Problem with type in function [message #380915 is a reply to message #380913] Wed, 14 January 2009 00:55 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi Michel,

Could you pls let me how to change the pl/sql statement or delaration in code.

Thanks in advance
Re: Had a Problem with type in function [message #380916 is a reply to message #380915] Wed, 14 January 2009 01:02 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your Oracle version (4 decimals)?

Regards
Michel
Previous Topic: Errors while trying to create object Tables,kindly help.
Next Topic: DBMS job
Goto Forum:
  


Current Time: Fri Dec 09 17:22:47 CST 2016

Total time taken to generate the page: 0.26922 seconds