You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Oracle Query Problem

2 replies [Last post]
ashok_star2004's picture
User offline. Last seen 30 weeks 1 day ago. Offline
Joined: 02/13/2009
Posts: 45

Hi All,
I was write a query to get all the ePerformance records' row count using only PSRECDEFN table.Here's my query and is ging me error as 'table or view doesnot exist'.

DECLARE
CURSOR c2 IS SELECT recname FROM psrecdefn WHERE objectownerid = 'HEP' AND RECTYPE NOT IN (1,2,3,4,5);
v_c1 c2%rowtype;
row_cnt NUMBER;

BEGIN
OPEN c2;
LOOP
FETCH c2
INTO v_c1;
EXIT
WHEN c2 % NOTFOUND;
EXECUTE IMMEDIATE 'SELECT COUNT(*) from PS_' ||v_c1.RECNAME|| 'INTO'|| row_cnt;
DBMS_OUTPUT.PUT_LINE('rows in table PS_' || v_c1.RECNAME || '=' || row_cnt);
--DBMS_OUTPUT.PUT_LINE('rows in table PS_' || v_c1.RECNAME);
END LOOP;
CLOSE c2;
END;

Please help me up to solve the query.Help appreciated if any one provide me the correct query

Guest's picture
Guest (not verified)
Re: Oracle Query Problem

Can you try the surson having table as PSRECDEFN in upper cas as well as column names in upper case

oddsnends's picture
User offline. Last seen 11 years 1 week ago. Offline
Joined: 03/28/2008
Posts: 16
Re: Oracle Query Problem

I'm not very famaliar with this code but could it be you need a space in the concatenation see second line of each line of code below.

EXECUTE IMMEDIATE 'SELECT COUNT(*) from PS_' ||v_c1.RECNAME|| 'INTO'|| row_cnt;
EXECUTE IMMEDIATE 'SELECT COUNT(*) from PS_' ||v_c1.RECNAME|| ' INTO '|| row_cnt;

DBMS_OUTPUT.PUT_LINE('rows in table PS_' || v_c1.RECNAME || '=' || row_cnt);
DBMS_OUTPUT.PUT_LINE('rows in table PS_' || v_c1.RECNAME || ' = ' || row_cnt);