Home » SQL & PL/SQL » SQL & PL/SQL » While printing refcursor " ORA-01858 " is coming. (Oracle 10g)
While printing refcursor " ORA-01858 " is coming. [message #351857] Fri, 03 October 2008 08:37 Go to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

Hi All,

Please see the code as follows

I wrote below package and is very simple code which got successfully created:
SQL> CREATE OR REPLACE PACKAGE T1_USAGE AS
  2  
  3  TYPE refcursor IS REF CURSOR;
  4   
  5  PROCEDURE GET_USAGE_DETAIL(v_firmguid IN VARCHAR2,
  6      v_startdate IN DATE, 
  7      v_enddate IN DATE,    
  8      v_content IN VARCHAR2,
  9      o_usage_detail OUT refcursor);
 10  END;
 11  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY T1_USAGE AS
  2  
  3  PROCEDURE GET_USAGE_DETAIL( v_firmguid IN VARCHAR2, 
  4      v_startdate IN DATE, 
  5      v_enddate IN DATE,    
  6      v_content IN VARCHAR2,
  7      o_usage_detail OUT refcursor)
  8      AS
  9  v_error VARCHAR2(200); 
 10  v_sql   VARCHAR2(4000);
 11  BEGIN
 12    
 13    IF v_startdate > v_enddate THEN
 14      v_error := 'FAILURE: Start Date is greater than End Date.';
 15     ELSE  
 16     
 17      IF v_content = 'ALL' THEN
 18    OPEN o_usage_detail FOR
 19       SELECT user_id AS "User ID",
 20              content_type AS "Content Type",
 21              report_type AS "Report Type",
 22              TO_DATE(TO_CHAR(event_datetime,'MM/DD/YYYY'),'MM/DD/YYYY') AS "Event Date",
 23              TO_DATE(TO_CHAR(event_datetime,'HH:MI:SS'),'HH:MI:SS') AS "Event Time",
 24              NVL(Content_Date,'n/a') AS "Content Date",
 25              NVL(Company,'n/a') AS "Company Name",
 26              NVL(Contributor,'n/a') AS "Contributor",
 27              PCode1 AS "PCode1",
 28              PCode2 AS "PCode2",
 29              PCode3 AS "PCode3",
 30              PCode4 AS "PCode4",
 31              PCode5 AS "PCode5",
 32              PCode6 AS "PCode6",
 33              PCode7 AS "PCode7",
 34              PCode8 AS "PCode8",
 35              PCode9 AS "PCode9",
 36              PCode10 AS "PCode10"
 37       FROM T1_USAGE_DETAIL h
 38      WHERE firm_guid = v_firmguid
 39        AND event_datetime between v_startdate and v_enddate
 40      ORDER BY event_datetime;
 41    ELSE
 42     v_sql := 'SELECT user_id AS "User ID",'||
 43              'content_type AS "Content Type",'||
 44              'report_type AS "Report Type",'||
 45              'TO_CHAR(event_datetime,''MM/DD/YYYY'') AS "Event Date",'||
 46              'TO_CHAR(event_datetime,''HH:MI:SS'') AS "Event Time",'||
 47              'NVL(Content_Date,''n/a'') AS "Content Date",'||
 48              'NVL(Company,''n/a'') AS "Company Name",'||
 49              'NVL(Contributor,''n/a'') AS "Contributor",'||
 50              'PCode1 AS "PCode1",'||
 51              'PCode2 AS "PCode2",'||
 52              'PCode3 AS "PCode3",'||
 53              'PCode4 AS "PCode4",'||
 54              'PCode5 AS "PCode5",'||
 55              'PCode6 AS "PCode6",'||
 56              'PCode7 AS "PCode7",'||
 57              'PCode8 AS "PCode8",'||
 58              'PCode9 AS "PCode9",'||
 59              'PCode10 AS "PCode10" '||
 60       'FROM T1_USAGE_DETAIL h '||
 61      ' WHERE firm_guid = v_firmguid'||
 62      '  AND event_datetime between '||v_startdate||' and '|| v_enddate ||
 63      '  AND content_type IN ('''||v_content||''')'||
 64      ' ORDER BY event_datetime';
 65      
 66      OPEN o_usage_detail FOR v_sql
 67      USING v_startdate,v_enddate,v_content;
 68    END IF;
 69   END IF;
 70  EXCEPTION WHEN OTHERS THEN 
 71           log_oracle_error (SQLCODE, SQLERRM, 'Error raised in: '|| $$plsql_unit||','||v_sql, 
 72             SYSDATE, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
 73  END; 
 74  
 75  END;
 76  /

Package body created.

But when I tried to run it through SQL Plus I am getting ORA: 01858 error.
SQL> variable r refcursor;
SQL>  
SQL>  
SQL>  DECLARE       
  2         p_firm_guid        VARCHAR2(50);
  3         p_stdate        DATE;
  4         p_eddate        DATE;
  5         v_content VARCHAR(20);
  6   r             sys_refcursor;
  7         
  8     BEGIN
  9         p_firm_guid    :=     '{2C5E30D1-68B9-47CE-8CB3-B27D1C4D1C4F}';
 10           p_stdate    :=    TO_DATE('1/1/2008 12:00:00 AM','MM/DD/YYYY hh:mi:ss AM') ;
 11         p_eddate    :=     TO_DATE('6/25/2008 12:00:00 PM','MM/DD/YYYY hh:mi:ss AM');         
 12      v_content := 'ALL';
 13     T1_USAGE.GET_USAGE_DETAIL (p_firm_guid,p_stdate,p_eddate,v_content,   :r);  
 14     
 15   END;
 16   /

PL/SQL procedure successfully completed.

SQL>  
SQL>  print r
ERROR:
ORA-01858: a non-numeric character was found where a numeric was expected

no rows selected

I checked the log table also and no error is getting logged in it. I have taken care of date formats in the package it self. Even if I am missing something please let me know.

Please let me know if you need any more information.

Thanks in advance.

[Updated on: Fri, 03 October 2008 08:55] by Moderator

Report message to a moderator

Re: While printing refcursor " ORA-01858 " is coming. [message #351860 is a reply to message #351857] Fri, 03 October 2008 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Wed, 11 June 2008 17:30
...
But before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel

Also post your Oracle version with 4 decimals.

Re: While printing refcursor " ORA-01858 " is coming. [message #351862 is a reply to message #351860] Fri, 03 October 2008 08:48 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

I will keep that in mid for the next time.

[Updated on: Fri, 03 October 2008 09:02]

Report message to a moderator

Re: While printing refcursor " ORA-01858 " is coming. [message #351866 is a reply to message #351862] Fri, 03 October 2008 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, I modified your post, take care for your next ones.

What is the type of Content_Date, Company, Contributor?

Given the field names, TO_DATE(TO_CHAR(event_datetime,'MM/DD/YYYY'),'MM/DD/YYYY') and TO_DATE(TO_CHAR(event_datetime,'HH:MI:SS'),'HH:MI:SS') seems to be wrong.

Regards
Michel
Re: While printing refcursor " ORA-01858 " is coming. [message #351868 is a reply to message #351866] Fri, 03 October 2008 09:07 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

The giving you the description of table:
SQL> desc t1_usage_detail;
TRANSACTIONID NUMBER
FIRM_GUID VARCHAR2(40)
USER_GROUP_GUID VARCHAR2(40)
USER_GUID VARCHAR2(40)
ACCOUNT VARCHAR2(128)
USER_ID VARCHAR2(128)
CONTENT_TYPE VARCHAR2(20)
REPORT_TYPE VARCHAR2(20)
CONTENT_DATE DATE
EVENT_DATETIME DATE
COMPANY VARCHAR2(20)
CONTRIBUTOR VARCHAR2(20)
PCODE1 VARCHAR2(200)
PCODE2 VARCHAR2(200)
PCODE3 VARCHAR2(200)
PCODE4 VARCHAR2(200)
PCODE5 VARCHAR2(200)
PCODE6 VARCHAR2(200)
PCODE7 VARCHAR2(200)
PCODE8 VARCHAR2(200)
PCODE9 VARCHAR2(200)
PCODE10 VARCHAR2(200)
RECEIVED_TIME DATE

What do u think is wrong with field names?
Re: While printing refcursor " ORA-01858 " is coming. [message #351869 is a reply to message #351868] Fri, 03 October 2008 09:16 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

And I can retreive the data from database in below query.

SELECT TO_CHAR(EVENT_DATETIME,'DD/MM/YYYY') AS "Event Date",
       TO_CHAR(EVENT_DATETIME,'hh:mi:ss') AS "Event Time"
FROM   T1_USAGE_DETAIL
WHERE  ROWNUM < 4
/

Event Date Event Ti
---------- --------
04/01/2008 02:44:25
04/01/2008 03:03:52
04/01/2008 12:36:00


But not from SQL Plus. It seems there is some problem in printing it from the refcursor.

[Updated on: Fri, 03 October 2008 09:22]

Report message to a moderator

Re: While printing refcursor " ORA-01858 " is coming. [message #351877 is a reply to message #351857] Fri, 03 October 2008 09:54 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
There is problem in the dynamic cursor (the second one).
Instead trying to fix it, use this method for passing an array into IN part, e.g. http://www.orafaq.com/forum/m/246168/96705/?srch=AskTom+variable+IN#msg_246168 (also have a look into the referenced AskTom page).

By the way, ALL is quite a strange name for content type.
Of course it would be much easier, if V_CONTENT parameter would be a collection and not VARCHAR2.
Re: While printing refcursor " ORA-01858 " is coming. [message #351879 is a reply to message #351868] Fri, 03 October 2008 10:09 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
amorphous4u wrote on Fri, 03 October 2008 16:07
The giving you the description of table:
SQL> desc t1_usage_detail;
 TRANSACTIONID                                      NUMBER
 FIRM_GUID                                          VARCHAR2(40)
 USER_GROUP_GUID                                    VARCHAR2(40)
 USER_GUID                                          VARCHAR2(40)
 ACCOUNT                                           VARCHAR2(128)
 USER_ID                                           VARCHAR2(128)
 CONTENT_TYPE                                       VARCHAR2(20)
 REPORT_TYPE                                        VARCHAR2(20)
 CONTENT_DATE                                       DATE
 EVENT_DATETIME                                     DATE
 COMPANY                                            VARCHAR2(20)
 CONTRIBUTOR                                        VARCHAR2(20)
 PCODE1                                            VARCHAR2(200)
 PCODE2                                            VARCHAR2(200)
 PCODE3                                            VARCHAR2(200)
 PCODE4                                            VARCHAR2(200)
 PCODE5                                            VARCHAR2(200)
 PCODE6                                            VARCHAR2(200)
 PCODE7                                            VARCHAR2(200)
 PCODE8                                            VARCHAR2(200)
 PCODE9                                            VARCHAR2(200)
 PCODE10                                           VARCHAR2(200)
 RECEIVED_TIME                                      DATE

What do u think is wrong with field names?

You still don't format your post, see what I made with your original one and with this one.

Clue:
SQL> select nvl(sysdate,'n/a') from dual;
select nvl(sysdate,'n/a') from dual
                   *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel

Previous Topic: migrating data from different tables into a single table
Next Topic: sequence.nextval and sequence.currval
Goto Forum:
  


Current Time: Tue Dec 06 00:15:28 CST 2016

Total time taken to generate the page: 0.12236 seconds