Home » SQL & PL/SQL » SQL & PL/SQL » problem with sys_refcursor (Oracle 10g)
problem with sys_refcursor [message #356382] Thu, 30 October 2008 10:07 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member


Hi,

I encountered with below errors when i tried to compile below function.

Error(24,3): PL/SQL: Statement ignored.
Error(24,18): PLS-00382: expression is of wrong type


create or replace
function esp_SuggestExitPoint_v3_fun
(
v_intTier IN VARCHAR2 ,
v_exitpoint IN VARCHAR2 DEFAULT NULL

)RETURN SYS_REFCURSOR AS
cv_1 SYS_REFCURSOR;
v_SQLQry NVARCHAR2(4000);

BEGIN

v_SQLQry := 'SELECT exitpoint' || v_intTier || '_id, label FROM exitpoint' || v_intTier || '_ec WHERE 1=1 ';

IF ( v_exitpoint <> ''
OR v_exitpoint <> NULL ) THEN
BEGIN
v_SQLQry := v_SQLQry || ' ' || v_exitpoint;

END;
END IF;
-- DBMS_OUTPUT.PUT_LINE(v_SQLQry);

OPEN cv_1 FOR v_SQLQry;
Return cv_1;

END;



Any help really appreciated
Re: problem with sys_refcursor [message #356388 is a reply to message #356382] Thu, 30 October 2008 10:38 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,
do you have any reason to use NVARCHAR2 type for the dynamic query? It seems, that it is not allowed, as following demonstration shows:
SQL> declare
  2    l_cmd nvarchar2(1000);
  3    l_cur sys_refcursor;
  4  begin
  5    l_cmd := 'select 1 from dual';
  6    open l_cur for l_cmd;
  7  end;
  8  /
  open l_cur for l_cmd;
                 *
ERROR at line 6:
ORA-06550: line 6, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored


SQL> declare
  2    l_cmd varchar2(1000);
  3    l_cur sys_refcursor;
  4  begin
  5    l_cmd := 'select 1 from dual';
  6    open l_cur for l_cmd;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> 
Re: problem with sys_refcursor [message #356390 is a reply to message #356388] Thu, 30 October 2008 10:41 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member


Thanks a lot for your reply. If I want to use NVARCHAR2 is there anyway where we can do the same.

Thanks & Regards
Re: problem with sys_refcursor [message #356405 is a reply to message #356390] Thu, 30 October 2008 11:22 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/mv/msg/124867/347949/102589/#msg_347949

Regards
Michel
Previous Topic: Check Constraint using Trigger
Next Topic: Is this possible?
Goto Forum:
  


Current Time: Wed Dec 07 18:26:45 CST 2016

Total time taken to generate the page: 0.08045 seconds