Help inPL/SQL

[uparna]

Hi ,
I have a silly doubt in PLSQl....and am trying to settle the matter for the past 3 hours , but of no avail ! I just hope you guys can help me in this . its quite simple actually , but am too stressed out now to do R&D on this ! Well, here it is :

I need to do the following in a PLSQL : get the count of the number of records in a table and put them in a variable. Then if this value is greater than a preset value: say 2200 , then it should proceed to call another script (ex : @oracle_home\bin\test.sql)

Is this possible ? I have tried using cursors and all. just see the code and before laughing , be sure to post the solution !!!!

Thanks in advance .

set serveroutput on

-- *******. Automation Of Loader .******

DECLARE
CURSOR Check_Link_Employee IS
select count(*) from matc_employee_am ;

CURSOR Check_Link_Project IS
select count(*) from matc_proj_am ;


flag BOOLEAN;
emp_curr_rec emp_curr%ROWTYPE;
proj_curr_rec proj_curr%ROWTYPE;
empcounter NUMBER(5);
projcounter NUMBER(5);


BEGIN
open proj_curr;
open emp_curr ;

LOOP
fetch proj_curr into proj_curr_rec;
exit when proj_curr%NOTFOUND;
fetch emp_curr into emp_curr_rec;
exit when emp_curr%NOTFOUND;

projcounter := proj_curr_rec.count(*) ;
empcounter := emp_curr_rec.count(*) ;

IF (empcounter > 2200) then
dbms_output.put_line('Success ');
-- the script should come here

ELSE
dbms_output.put_line('Unable to process ');


END LOOP;

END;
/



Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .

 

[rcurva]

You can make use of HOST function:

IF (empcounter > 2200) then
dbms_output.put_line('Success ');
-- the script should come here

HOST(sqlplus user/pwd@db @oracle_home\bin\test.sql)

ELSE
dbms_output.put_line('Unable to process ');
END IF;


END LOOP;

 

[rcurva]

Or you could convert your plsql script into a stored procedure then you can call the procedure instead. This way, you will not be restricted with script file locations and the user/password will not be disclosed as well.

 

[uparna]

Thanks rcurva.
But my primary doubt remains unsolved . How can i get the count into the variable ? The above process (projcounter := proj_curr_rec.count(*) is obviously wrong. How do i do this ? Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .

 

[harris79]

If you want to get the value of count into a varable-

select count(*) into v_empcount from matc_employee_am;

or am I missing something?

Steve

 

[rcurva]

I found something in Metalink, If you are using an 8i database, you could use the bulk collect feature with a PL/SQL Table. Example :

DECLARE
TYPE NameTab IS TABLE OF emp.ename%TYPE;
names NameTab;
CURSOR c1 is select ename from emp;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO names;
dbms_output.put_line(c1%rowcount);
END;

This way, you can immediately get the rowcount without looping.

 

[uparna]

rcurva,harris79,
You both have been great helps !


Thanks a zillion !


Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .

 

[uparna]

Hey !
The HOST function doesnt seem to work inside a PLSQL . Even though i have pasted a working host statement on to the SQl . that is : I have a Host statemet that functions properly when i run on the sql>prompt , but even this doesnt work with the PLSQL. What seems to be the problem ?
Any ideas ?

Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .

 

[carp]

HOST is a SQL*PLUS command; it has no meaning within PL/SQL.
Harris79 has provided you with the answer to your problem. However, if you are trying to populate a bind variable, you will have to modify the code as follows:

select count(*) into :v_empcount from matc_employee_am;

 

[rcurva]

carp is correct, host can only work in sql*plus and not inside a plsql block. my mistake. looks like, you'll have to create a stored procedure instead.

 

[uparna]

Okay .

Thanks for the continued support ! Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .