1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Issue with varchar in sys_refcursor

Discussion in 'SQL PL/SQL' started by Girishr369, Dec 14, 2016.

  1. Girishr369

    Girishr369 Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    hyderabad
    Hi team,
    can you please help me out on this.

    The error what I am getting is (ORA-00904) highlighted at the end with RED color.
    Below is the process what followed.
    i think the issue with build query with QUOTES(').

    please suggest.


    I have the below table:

    SQL> desc partym;

    PARTYNO VARCHAR2(10)

    PARTYTYPE VARCHAR2(10)

    COUNTRY VARCHAR2(2)



    Data in the above table is :

    FT00000001 PRIVATE IN

    FT00000002 PRIVATE US



    I wrote the below SYS_REFCURSOR function


    SQL> create or replace function reffunction(

    2 pno in varchar2,

    3 ptype in varchar2)

    4 return sys_refcursor

    5 is

    6 refcur sys_refcursor;

    7 vqry varchar2(200);

    8 begin

    9 vqry :=

    10 'select * from partym where partyno = '||pno||' and partytype = '||ptype;


    11 open refcur for vqry;

    12 return refcur;

    13 end;

    14 /


    Function created

    In the below block when I tried to call the above function I am getting errors

    SQL> declare

    2 frrefcur sys_refcursor;

    3 prtrec partym%rowtype;

    4 begin

    5 frrefcur := REFFUNCTION('FT00000001','PRIVATE');

    6 FETCH FRREFCUR INTO PRTREC;

    7 if frrefcur%found then

    8 dbms_output.put_line('yes row found');

    9 else

    10 dbms_output.put_line('no row found');

    11 end if;

    12 WHILE FRREFCUR%FOUND LOOP

    13 DBMS_OUTPUT.PUT_LINE(PRTREC.COUNTRY);

    14 FETCH FRREFCUR INTO PRTREC;

    15 END LOOP;

    16 CLOSE FRREFCUR;

    17 END;

    18 /

    declare

    *

    ERROR at line 1:

    ORA-00904: "PRIVATE": invalid identifier

    ORA-06512: at "SYSTEM.REFFUNCTION", line 11

    ORA-06512: at line 5

     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,619
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    SQL> create or replace function reffunction(

    2 pno in varchar2,

    3 ptype in varchar2)

    4 return sys_refcursor

    5 is

    6 refcur sys_refcursor;

    7 vqry varchar2(200);

    8 begin

    9 vqry :=

    10 'select * from partym where partyno ='''||pno||''' and partytype = '''||ptype||'''';


    11 open refcur for vqry;

    12 return refcur;

    13 end;

    14 /

    The values are strings and need to be enclosed in ''.
     
  3. Girishr369

    Girishr369 Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    hyderabad
    Thank you David Fitzjarrell, now I executed with your suggestion successfully.