CODEWITHSUNDEEP
oracle-databaseplsqlcursor
Kay
Kay

Reputation: 15

SQL Developer cursor returning one 1 row when it should be returning multiple rows

procedure new_cust (p_practiceid varchar2)is


  custid varchar2(255);
  l_error_stack varchar2(4000);
  l_error_code integer;

cursor c1 is select id
from customers
where joining_date = to_char(sysdate,'YYYY-MM');


begin

OPEN c1;
loop
FETCH c1  INTO custid;
EXIT WHEN c1%NOTFOUND;

begin 

execute immediate 'drop table new_clients';
execute immediate 'create table new_clients as  select * from client_names where custid= '''||custid||'''';

exception when others then
  l_error_stack := substr(dbms_utility.format_error_backtrace,1,2500)||chr(10)||substr(dbms_utility.format_error_stack,1,500) ;
  l_error_code := sqlcode;
  daily_check_log.logger(systimestamp, inPracticeId, 'error', 'new_cust', l_error_code, l_error_stack);
end;


END LOOP;
CLOSE c1;

end;

I am using a cursor to determine new clients that joined this month. There should be at least 10 new records but the cursor is returning just 1 row. Where am I going wrong?

Upvotes: 0

Views: 256

Answers (2)

Littlefoot
Littlefoot

Reputation: 142705

In my opinion, the whole concept is wrong. In Oracle, you don't create table dynamically, there are really, really rare situations which justify that. Your code is completely static, there's nothing that requires dynamic SQL.

Consider creating a table, once - and reuse it as needed. It might involve deleting rows from it.

CTAS with a false condition will create an empty table:

create table new_clients as select * from client_names where 1 = 2;

Insert data into it on a monthly basis (as that's what your code suggests):

insert into new_clients 
  select * 
  from client_names 
  where custid in (select id 
                   from customers 
                   where joining_date between trunc(sysdate, 'mm')
                                          and trunc(last_day(sysdate))
                  );

I suppose that JOINING_DATE isn't really YYYY-MM but a true date. If there's an index on it, it'll still work because of the BETWEEN clause which fetches the whole current month (though, it might need further adjustment, depending on data you have).

Also, it would be better if you specified column names involved in the INSERT statement (doesn't matter if there are lot of them - name them, all, one by one). select * works fine until something changes, and then it works not.

If necessary such an insert can be easily moved into a procedure. Speaking of which: what's the IN parameter used for? You declared it, but it's never used.

Upvotes: 0

San
San

Reputation: 4538

You don't have to create a cursor for this simple task, this code should be ok for your requirement 

procedure new_cust (p_practiceid varchar2) is

begin

  execute immediate 'drop table new_clients';
  execute immediate 'create table new_clients as  
                       select cn.* 
                         from client_names cn 
                         join customers c 
                           on c.id = cn.custid
                        where c.joining_date = to_char(sysdate,''YYYY-MM'')';
end;

And don't forget to put exception handling in it :)

Upvotes: 1

Related Questions