How to return CLOB as OUT parameter in ORACLE stored procedure

Question

I want to return an XML output for my stored procedure in Oracle SQL Developer and I want to return this as CLOB datatype(Suggest me if there is any better approach than CLOB). I tried to google but couldn't find any helpful resource.

Thanks in advance.

Answer 1

Here's a simple example.

CREATE TABLE "XML_STUFF" 
   (    "COLUMN1" NUMBER(*,0) NOT NULL ENABLE, 
    "XML_STUFF" "SYS"."XMLTYPE" , 
     CONSTRAINT "XML_STUFF_PK" PRIMARY KEY ("COLUMN1")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
 XMLTYPE COLUMN "XML_STUFF" STORE AS SECUREFILE CLOB (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES 
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

-- insert some XML/records into your table

CREATE OR REPLACE PROCEDURE OUT_XML 
(
  PARAM1 IN INTEGER 
, PARAM2 OUT CLOB 
) AS 
 xml_bits xmltype;
BEGIN
  select xml_stuff into xml_bits 
  from demo.xml_stuff
  where column1 = param1;

  param2 := xml_bits.getclobval();
  dbms_output.put_line(param2); -- this is just here to make it easier to 'see'
END OUT_XML;

--run it!

DECLARE
  PARAM1 NUMBER;
  PARAM2 CLOB;
BEGIN
  PARAM1 := 1;

  DEMO.OUT_XML(
    PARAM1 => PARAM1,
    PARAM2 => PARAM2
  );
  /* Legacy output: 
DBMS_OUTPUT.PUT_LINE('PARAM2 = ' || PARAM2);
*/ 
  :PARAM2 := PARAM2;
--rollback; 
END;