Reputation: 1516
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
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