Home » SQL & PL/SQL » SQL & PL/SQL » CREATE OR REPLACE FUNCTION (Oracle 10g, UNIX (AIX))
CREATE OR REPLACE FUNCTION [message #358236] Mon, 10 November 2008 04:48 Go to next message
mounir
Messages: 10
Registered: August 2008
Location: Paris
Junior Member
Could someone please tell me what's wrong with this code (that's my first piece of code using PL/SQL) ?

SET SERVEROUTPUT ON;



CREATE OR REPLACE type headerObject as object 
(eventid number(3), trcStmpId varchar2(25), 
srcStmpId varchar2(25), aggrStmpId varchar2(25), 
audRuleCode varchar2(12));
/

CREATE OR REPLACE type hResultTab as table of headerObj ;
/

CREATE OR REPLACE function myfunction(currentTrcStmpId in VARCHAR2)
    return hResultTab PIPELINED
  IS
    Line headerObject := headerObject(NULL, NULL, NULL, NULL, NULL);
    
BEGIN
  for i in 1 .. 5
  loop
      Line.eventid := i;
      Line.trcStmpId := currentTrcStmpId;
      Line.srcStmpId := 'test_2';
      Line.aggrStmpId := 'test_3';
      Line.audRuleCode := 'test_4';
      
      PIPE ROW(Line);
  end loop; 
  
  return;
  
END;
/
SHOW ERRORS;




I get the following error message:




Type cree.


Type cree.


Avertissement : Fonction creee avec erreurs de compilation.

Erreurs pour FUNCTION MYFUNCTION :

LINE/COL ERROR
-------- -----------------------------------------------------------------
15/7     PL/SQL: Statement ignored
15/16    PLS-00382: expression du mauvais type





[Updated on: Mon, 10 November 2008 08:08] by Moderator

Report message to a moderator

Re: CREATE OR REPLACE FUNCTION [message #358237 is a reply to message #358236] Mon, 10 November 2008 04:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your table is defined as table of headerObj
Is that a typo?
Re: CREATE OR REPLACE FUNCTION [message #358239 is a reply to message #358237] Mon, 10 November 2008 05:06 Go to previous messageGo to next message
mounir
Messages: 10
Registered: August 2008
Location: Paris
Junior Member
Thanks very much, that was the problem. I had really a lot of errors that I corrected and I could not find this one Embarassed
Re: CREATE OR REPLACE FUNCTION [message #358283 is a reply to message #358239] Mon, 10 November 2008 09:05 Go to previous messageGo to next message
mounir
Messages: 10
Registered: August 2008
Location: Paris
Junior Member

One last question about this topic: Can someone tell me how to call this function once it has been created. I wanna call a select on the table created so that I can display all the rows (i.e select *)
Re: CREATE OR REPLACE FUNCTION [message #358285 is a reply to message #358283] Mon, 10 November 2008 09:13 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://otn.oracle.com/pls/db102/drilldown?remark=quick_search&word=pipelined+function&tab_id=

Regards
Michel
Previous Topic: nested views with hints
Next Topic: to_number in view
Goto Forum:
  


Current Time: Sat Dec 03 10:03:26 CST 2016

Total time taken to generate the page: 0.12603 seconds