Home » SQL & PL/SQL » SQL & PL/SQL » [PACKAGE PL/SQL] - Creation d'un curseur...
icon5.gif  [PACKAGE PL/SQL] - Creation d'un curseur... [message #251789] Mon, 16 July 2007 12:07 Go to next message
jacquesh
Messages: 6
Registered: November 2006
Junior Member
Hello,

I've a big select statement using XMLELEMENT, ... and return more than one row. I want use it in a package but I can't create cursor in package ; my syntax is invalid (i've folowing the basic rule describe in Oracle docs... but all examples are based on Function or Procedure not in package...

can you give me a little sample with a cursor (return XMLTYPE) created in a package.

My Oracle Version is Oracle 10gR2

Thanks for your help...
Re: [PACKAGE PL/SQL] - Creation d'un curseur... [message #251792 is a reply to message #251789] Mon, 16 July 2007 12:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why don't you show us what goes wrong in your code?
Strip it to the bare essentials and copy-paste a sqlplus session in which you try to compile it, including the error you get.
That will be much easier for both sides to comment and help you further.
Re: [PACKAGE PL/SQL] - Creating cursor... [message #251892 is a reply to message #251789] Tue, 17 July 2007 01:56 Go to previous messageGo to next message
jacquesh
Messages: 6
Registered: November 2006
Junior Member
create or replace
PACKAGE PKG_RIGHTMNGT AS
...
aclDefault XMLTYPE ;  
TYPE xmlname_curtype IS REF CURSOR RETURN TFIELDS%ROWTYPE ;
...
END PKG_RIGHTMNGT;


create or replace
PACKAGE BODY PKG_RIGHTMNGT AS  
xmlname_cur xmlname_curtype ;
END PKG_RIGHTMNGT;


Quote:
Error(23,17): PLS-00103: Symbole "XMLNAME_CURTYPE" rencontré à la place d'un des symboles suivants : := . ( @ % ; Symbole ":=" a été substitué à "XMLNAME_CURTYPE" pour continuer.


the cursor must be fetch this select statement
SELECT 
	XMLElement("FIELDS",
		XMLElement("XMLNAME", 
			XMLAttributes(extractValue(ACL_DEF,'/FIELDS/XMLNAME/@ID') AS "ID"),
			XMLElement("PROFILE", 
				XMLElement("AU",extract(ACL_DEF,'/FIELDS/XMLNAME/PROFILE/AU/text()')))))
FROM 
	Tfields;


I've tried with a cursor (returning XMLTYPE) and cursor REF... same result ...

thanks...
Re: [PACKAGE PL/SQL] - Creating cursor... [message #251899 is a reply to message #251892] Tue, 17 July 2007 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't get the same error, I get this:
SQL> create or replace PACKAGE P AS
  2  aclDefault XMLTYPE ;  
  3  TYPE xmlname_curtype IS REF CURSOR RETURN T%ROWTYPE ;
  4  END;
  5  /

Package created.

SQL> create or replace PACKAGE BODY P AS  
  2  xmlname_cur xmlname_curtype ;
  3  END;
  4  /

Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY P:
LINE/COL
---------------------------------------------------------------------
ERROR
---------------------------------------------------------------------
2/13
PL/SQL: Item ignored
2/13
PLS-00994: Cursor Variables cannot be declared as part of a package

Regards
Michel
Re: [PACKAGE PL/SQL] - Creating cursor... [message #251945 is a reply to message #251899] Tue, 17 July 2007 04:00 Go to previous message
jacquesh
Messages: 6
Registered: November 2006
Junior Member
I've juste modified my PL/SQL BODY code and compile it...

  -- Renvoi les ACLs par default d'un profile
  function acl_default(profile_in IN VARCHAR2) return XMLTYPE [B]IS[/B]
    tmpdef XMLTYPE ;
    xpathstr VARCHAR2(1000) := '/FIELDS/XMLNAME/PROFILE/AU/text()' ;
    CURSOR xmlname_cur IS 
      SELECT 
        XMLElement("XMLNAME", 
        XMLAttributes(extractValue(ACL_DEF,'/FIELDS/XMLNAME/@ID') AS "ID"),
          XMLElement("AU", 
            XMLElement("AU",extract(ACL_DEF,xpathstr)))) AS "ACLDEFROW"
      FROM
	Tfields;
  BEGIN  
    OPEN xmlname_cur ;
    LOOP
      FETCH xmlname_cur into aclDefault ;
      IF xmlname_cur%FOUND THEN
        Select INSERTXMLBEFORE(tmpdef,'/',aclDefault) into tmpdef from dual ;
      ELSE
        RETURN tmpdef ;
      END IF ;
    END LOOP ;
    


I've no result ??
when I execute selecte statement from SQLPLUS i've 86 rows..

how can I append xmldata (i've already try with APPENDXML() same result...)

and if I try to display tmpdef content... with dbms_output.put_line(tmpdef.getStringVal i've this error

ERROR:
ORA-30625: la repartition de methode sur l'argument NULL SELF n'est pas
autorisee
ORA-06512: a "D47.PKG_RIGHTMNGT", ligne 35

Previous Topic: QUOTA: Oops
Next Topic: Regarding error : Subscript beyond count
Goto Forum:
  


Current Time: Sun Dec 04 16:36:56 CST 2016

Total time taken to generate the page: 0.10260 seconds