CODEWITHSUNDEEP
sqloracle-databaseplsql
user9259206
user9259206

Reputation:

sys_refcursor without column name

I am working on a function to print values from a table.

create or replace FUNCTION UserDetails(p_startDate IN VARCHAR, p_endDate in VARCHAR) RETURN sys_refcursor
AS
    v_cursor    sys_refcursor;
BEGIN

    IF to_date(p_endDate, 'dd-mm-yyyy') - to_date(p_startDate, 'dd-mm-yyyy') > 90 THEN
        RAISE invalid_number;
    END IF;
 
  OPEN v_cursor FOR  SELECT  UPPER(name) NAME, MAX(Updated_date) UPDATED_DATE
    FROM s_user_data
   WHERE Updated_date  between to_Date(p_startDate,'DD-MON-YYYY') and to_Date(p_endDate,'DD-MON-YYYY')
   ORDER BY Updated_date  DESC;

 RETURN v_cursor;

 EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (TO_CHAR (SYSDATE, 'HH24:MI:SS') || ' Error: ' || SQLCODE || ' ' || SQLERRM);
      RAISE;

END;

After running the function: select UserDetails('08-MAY-2021','09-MAY-2021') from dual;

I am getting below output: {<NAME=XYZ,UPDATED_DATE=08-MAY-21 02.58.30.714149000 PM>,<NAME=ABC,UPDATED_DATE=08-MAY-21 02.57.45.664223000 PM>,<NAME=MNOP,UPDATED_DATE=07-MAY-21 07.37.14.197251000 PM>,}

I have to achieve it like below: {<XYZ,08-MAY-21 02.58.30.714149000 PM>,<ABC,08-MAY-21 02.57.45.664223000 PM>,<MNOP,07-MAY-21 07.37.14.197251000 PM>,}

Is there any way to get the output without column name. Please advise.

Upvotes: 0

Views: 151

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

Your function is not consistent. Why do you declare input parameters p_startDate and p_endDate as VARCHAR instead of DATE?

In your code you have

to_date(p_endDate, 'dd-mm-yyyy') - to_date(p_startDate, 'dd-mm-yyyy')

and further down

to_Date(p_startDate,'DD-MON-YYYY') and to_Date(p_endDate,'DD-MON-YYYY')

The input values cannot have format dd-mm-yyyy and DD-MON-YYYY at the same time.

Regarding your actual question: It's really not clear what you like to get. How do you call the function?

If your function returns a RefCursor then you must call the function (which does not mean select ... from dual) and process the cursor. Another option could be DBMS_SQL.RETURN_RESULT or pipeline function. But as long as your requirements are not clear, it will be difficult to help you.

Upvotes: 0

Vahram Danielyan
Vahram Danielyan

Reputation: 309

If you want to execute from sqlplus, put:

SET HEAD OFF

if you are executing from sql developer or some IDE, Just before of anonym block put the same statement of: SET HEAD OFF, is equivalent to SET HEADING OFF.

In colcusion the artice is here: https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm

And the general rule is:

SET HEAD[ING] OFF

Upvotes: 1

Related Questions