CODEWITHSUNDEEP
stored-proceduresoracle11g
lpfx
lpfx

Reputation: 1516

Oracle stored procedure stuck

I'm having a problem with a stored procedure on oracle 11g.

I create this procedure and when I run it on sql developer it gets stuck. The execution never ends and nothing is printed out.

Procedure:

create or replace PROCEDURE "GET_RECORDS_SP" (CURSOR_ OUT sys_refcursor, PARAM_0 VARCHAR2,PARAM_1 VARCHAR2)
AS
myquery varchar2(255);
  BEGIN  
    DBMS_OUTPUT.enable();
    DBMS_OUTPUT.PUT_LINE(PARAM_0);
    DBMS_OUTPUT.PUT_LINE('-- 1 --');
    CASE
      WHEN PARAM_0 = 'A' THEN 
        DBMS_OUTPUT.PUT_LINE('-- 2 --');
        myquery := 'SELECT * FROM table
               WHERE char_field = ''N'' and date_field is not null';
      WHEN PARAM_0 = 'B' THEN 
        DBMS_OUTPUT.PUT_LINE('-- 3 --');
        myquery:= 'SELECT * FROM table
              WHERE char_field = ''N'' and 
              date_field is not null and
              char_field2 = '''||PARAM_1||'''';
      WHEN PARAM_0 = 'C' THEN 
        DBMS_OUTPUT.PUT_LINE('-- 4 --');
        myquery:= 'SELECT * FROM table 
              WHERE char_field = ''N'' and
              date_field is not null 
              and char_field3 = ''S''';
    END CASE;

  DBMS_OUTPUT.PUT_LINE(myquery);
  OPEN CURSOR_ FOR myquery;
END;

If I comment the line OPEN CURSOR_ FOR myquery; it prints A, -- 1 --, -- 2 -- and the query SELECT * FROM table WHERE char_field = ''N'' and date_field is not null (passing A in PARAM_0).

If I try to run the query SELECT * FROM table WHERE char_field = ''N'' and date_field is not null alone, it executes and give me the results.

Can anyone tell me what is wrong in my procedure? Thanks!

Upvotes: 1

Views: 1988

Answers (1)

lpfx
lpfx

Reputation: 1516

I realized that I was querying more fields than I needed.

So I change the query from:

SELECT * FROM table WHERE char_field = ''N'' and date_field is not null';

To:

SELECT field1, field2, field3, field4 FROM table WHERE char_field = ''N'' and date_field is not null';

And now the procedure is not getting stuck any more. It is not so fast but I get the results.

Upvotes: 0

Related Questions