SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Important : Problem in stored procedure in Oracle

    I did not get query result in my stored procedure as mention below:

    create or replaceenter code here
    procedure system.TEST3_PROC1(a_build_nm in varchar2,
    a_build_no in out varchar2,
    a_locality in out varchar2)IS
    v_temp VARCHAR2(2000);

    Begin

    v_temp:='SELECT BUILD_NM, BUILD_NO, LOCALITY
    INTO a_build_nm, a_build_no, a_locality
    FROM DELHI_BUILD
    where upper(BUILD_NM) LIKE "%BUILD_NM%"';

    dbms_output.put_line(a_build_no);
    dbms_output.put_line(a_build_nm);
    dbms_output.put_line(a_locality);

    end;
    ======================================================
    PL/SQL Block:
    DECLARE
    A_BUILD_NM VARCHAR2(200);
    A_BUILD_NO VARCHAR2(200);
    A_LOCALITY VARCHAR2(200);
    BEGIN
    A_BUILD_NM := 'PRAC';
    A_BUILD_NO := NULL;
    A_LOCALITY := 'AJMERI GATE';

    TEST3_PROC1(
    A_BUILD_NM => A_BUILD_NM,
    A_BUILD_NO => A_BUILD_NO,
    A_LOCALITY => A_LOCALITY
    );
    DBMS_OUTPUT.PUT_LINE('A_BUILD_NO = ' || A_BUILD_NO);
    DBMS_OUTPUT.PUT_LINE('A_LOCALITY = ' || A_LOCALITY);
    DBMS_OUTPUT.PUT_LINE('A_BUILD_NM = ' || A_BUILD_NM);
    END;
    =======================================================
    OUTPUT Result :
    Connecting to the database abc.
    PRAC
    AJMERI GATE
    A_BUILD_NO =
    A_LOCALITY = AJMERI GATE
    A_BUILD_NM = PRAC
    Process exited.
    Disconnecting from the database abc.

    ====================================================================================

    If anybody tell why I am not getting any query result, because i did not found any error while running this procedure.

    Pl help seeking for early response.

  2. #2
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Run this query separately:

    Code:
    SELECT BUILD_NM, BUILD_NO, LOCALITY
    FROM DELHI_BUILD
    where upper(BUILD_NM) LIKE "%BUILD_NM%"
    Is it working? Do you get the result you're expecting?


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •