Calling a stored procedure with CLOB output

Question

I am trying to use the CLOB datatype as the output parameter in my stored procedure because its resultset exceeds the storage capacity of a var datatype. How do I execute the procedure? Below are the commands I had run to execute.

I tried assigning the resultset to the the CLOB variable using the INTO query as shown in the query.

var cl CLOB; EXECUTE procedure_name(:cl); print cl;

How do i declare the binding variable because if you look at the first command, I am first initializing cl as var I am not able to initialize it as CLOB as it is giving out an error.

This is a sample of my procedure. The actual query in the procedure is 700 lines long.

CREATE OR REPLACE PROCEDURE procedure_name (cl OUT CLOB)
IS
BEGIN OPEN cl FOR
SELECT * FROM .....
statement 1
.
.
.
.
.
statement n
INTO cl
FROM
statement 1
.
.
.
statement n
EXCEPTION 
 WHEN 
  OTHERS THEN 
   DECLARE 
      err_num NUMBER  := SQLCODE; 
      err_msg VARCHAR2(512) := SQLERRM; 
      error_id_pk NUMBER; 
      error_dt DATE; 
   BEGIN
      SELECT (REGEXP_REPLACE(CURRENT_TIMESTAMP, '[^0-9]+', ''))INTO error_id_pk FROM DUAL;
      SELECT SYSDATE INTO error_dt FROM DUAL;
      INSERT INTO ODS_CONTROL.ERROR_DETAILS(ERROR_ID, ERROR_CODE, ERROR_DATE, PROCEDURE_NAME, ERROR_MSG)
      VALUES ( error_id_pk,
               err_num,
               error_dt,
               'PRC_FLEXI_CARD',
                err_msg 
              ); 
     END; 
END;

Error message:

Error starting at line : 2 in command -
EXECUTE procedure_name( :clb )
Error report -
ORA-06550: line 1, column 7:
PLS-00905: object procedure_name is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Answer 1

[TL;DR] VAR is a keyword for declaring a variable and is not a data type; your actual error is due to using invalid syntax when you tried to define your procedure and it has not compiled.

---

VAR is short for VARIABLE and defines a PL/SQL bind variable.

This declaration has the syntax:

VAR[IABLE] [variable [type] ]

where type represents one of the following:

NUMBER
CHAR
CHAR (n [CHAR | BYTE])
NCHAR
NCHAR (n)
VARCHAR2 (n [CHAR | BYTE])
NVARCHAR2 (n)
BLOB
BFILE
CLOB
NCLOB
REFCURSOR
BINARY_FLOAT
BINARY_DOUBLE

So with:

var cl CLOB;

you are declaring a variable using the VAR keyword and the variable is named cl and has the type CLOB.

Also, your CREATE PROCEDURE statement has a syntax error as you cannot have the single quotes around the procedure name. For example:

CREATE PROCEDURE procedure_name (clb OUT CLOB)
IS
BEGIN
  clb := 'test';
END;
/

Then:

VAR cl CLOB;
EXECUTE procedure_name( :cl );
PRINT cl;

Outputs:

test

---

Updated:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE ERROR_DETAILS(
  ERROR_ID       NUMBER,
  ERROR_CODE     NUMBER,
  ERROR_DATE     DATE,
  PROCEDURE_NAME VARCHAR2(30),
  ERROR_MSG      VARCHAR2(512)
)
/

CREATE PROCEDURE procedure_name (cl OUT CLOB)
IS
BEGIN
  SELECT DUMMY
  INTO   cl
  FROM   dual
  WHERE  ROWNUM = 1;
EXCEPTION 
 WHEN 
  OTHERS THEN 
   DECLARE 
     err_num NUMBER  := SQLCODE; 
     err_msg VARCHAR2(512) := SQLERRM; 
   BEGIN
     INSERT INTO /* ODS_CONTROL. */ ERROR_DETAILS(
       ERROR_ID,
       ERROR_CODE,
       ERROR_DATE,
       PROCEDURE_NAME,
       ERROR_MSG
     ) VALUES (
       TO_NUMBER( TO_CHAR( CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISSFF9' ) ),
       err_num,
       SYSDATE,
       'PRC_FLEXI_CARD',
       err_msg 
     ); 
   END; 
END;
/

Query 1:

SELECT * FROM USER_ERRORS

Results:

No rows selected