OraFAQ Forum: SQL & PL/SQL » how to get the value of count(*) in plsql

Home » SQL & PL/SQL » SQL & PL/SQL » how to get the value of count(*) in plsql

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to get the value of count(*) in plsql [message #20128]

Tue, 30 April 2002 08:27

ksr
Messages: 112
Registered: January 2002

Senior Member

Hi,
I have a procedure where i want to check how many records are there.

procedure test as
TYPE dblnk_REF_CURSOR IS REF CURSOR;
l_dblink_CUR dblnk_REF_CURSOR;
sql_st1 varchar2(2000);
sql_st2 varchar2(2000);
dblink_ay varchar2(20);
i_count pls_integer;
v_col1 tab1.col%type;
begin
dblink_ay:='test.com';
sql_st1:='select col1 from tab1';
OPEN l_dblink_CUR FOR sql_st1;
LOOP
FETCH l_dblink_CUR INTO v_col1;
sql_st2:= 'select count(*) into ' || i_count || ' from tab2@' || dblink_ay || ' where col1 =' || v_col1 ;
----
Here how to check whether if the count(*) is > 0 or not.
If the count(*) is > 0, i want to do something else something..

How to check it..

Report message to a moderator

Re: how to get the value of count(*) in plsql [message #20130 is a reply to message #20128]

Tue, 30 April 2002 09:12

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Change your variable assignment to:

sql_st2 := 'select count(*) from tab2@' || dblink_ay || ' where col1 = :value';

And then use NDS (native dynamic SQL):

execute immediate sql_st2 into i_count using v_col1;

Do not hardcode the v_col1 variable into the string - instead use a bind variable and pass the value on the execution as in the code sample.

Report message to a moderator

Previous Topic:	how to assign the database link dynamically to a table
Next Topic:	plz help me in knowing this

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 04 04:42:44 CDT 2024