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!

Pl/sql: ora-00932: inconsistent datatypes: expected number got curser

Discussion in 'SQL PL/SQL' started by Vik@s, Dec 16, 2016.

  1. Vik@s

    Vik@s Active Member

    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    India
    create the function to work?
    create or replace function xx_v_test (p_no in varchar2,ptype in varchar)return sys_refcursor
    is
    refcur sys_refcursor;
    vqry varchar2(200);
    begin
    vqry:='select * from po_headers_all where segment1='''||p_no||''' and TYPE_LOOKUP_CODE='''||ptype||'''';
    open refcur for vqry;
    return refcur;
    end;

    I want to run the function in toad they give a error like

    declare
    vqry varchar2(200);
    begin
    select xx_v_test('1','STANDARD') into vqry
    from dual;
    dbms_output.put_line(vqry);
    end ;
    Error at line 1
    ORA-06550: line 4, column 8:
    PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got CURSER
    ORA-06550: line 4, column 1:
    PL/SQL: SQL Statement ignored
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot return a refcursor into a varchar2 variable, period. You need to use the refcursor:

    Code (SQL):
    CREATE OR REPLACE FUNCTION xx_v_test (p_no IN varchar2,ptype IN VARCHAR)RETURN sys_refcursor
    IS
    refcur sys_refcursor;
    vqry varchar2(200);
    BEGIN
    vqry:='select * from po_headers_all where segment1='''||p_no||''' and TYPE_LOOKUP_CODE='''||ptype||'''';
    OPEN refcur FOR vqry;
    RETURN refcur;
    END;
    /

    SELECT xx_v_test('1','STANDARD') FROM dual;

     
    There is no need for another PL/SQL block you simply select from the refcursor returned from the function.