OraFAQ Forum: SQL & PL/SQL » Bind Variables in DBMS_SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Bind Variables in DBMS_SQL - PL/SQL GURU NEEDED

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

Bind Variables in DBMS_SQL - PL/SQL GURU NEEDED [message #38423]

Wed, 17 April 2002 06:59

Tony
Messages: 190
Registered: June 2001

Senior Member

I need a PL/SQL guru to tell me why this procedure is not working..All I want to do is to select the indexes that have more than 3 leaf levels and rebuild them using dynamic SQL. Thank you for your time in advance..
CREATE OR REPLACE PROCEDURE INDEX_REBUILD
IS
first_cursor integer;
second_cursor integer;
index_name_var VARCHAR2(100);
ret integer;

begin
first_cursor:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(first_cursor,'select index_name from user_indexes where blevel>=3',DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(first_cursor, 1, index_name_var, 100);
ret:=DBMS_SQL.EXECUTE(first_cursor);

second_cursor:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(second_cursor,'alter index :index_name_bind rebuild online',DBMS_SQL.native);

LOOP
IF DBMS_SQL.FETCH_ROWS(first_cursor)>0 THEN

DBMS_SQL.COLUMN_VALUE(first_cursor, 1, index_name_var);
DBMS_SQL.BIND_VARIABLE(second_cursor, ':index_name_bind', index_name_var);
ret:=DBMS_SQL.EXECUTE(second_cursor);
ELSE
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(first_cursor);
DBMS_SQL.CLOSE_CURSOR(second_cursor);

END;

Report message to a moderator

Re: Bind Variables in DBMS_SQL - PL/SQL GURU NEEDED [message #38424 is a reply to message #38423]

Wed, 17 April 2002 08:56

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

hi!
are u expecting some like this?
---------------------------------------
SQL> get in
1 create or replace procedure index_rebuild
2 AUTHID CURRENT_USER
3 is
4 cursor c1 is select index_name from user_indexes where blevel>=3;
5 str varchar2(200);
6 begin
7 for mag in c1 loop
8 exit when c1%notfound;
9 str:='alter index '||mag.index_name || ' rebuild online';
10 EXECUTE IMMEDIATE str;
11 end loop;
12* end;
SQL> /

Procedure created.

SQL> exec index_rebuild

PL/SQL procedure successfully completed.

SQL>
---------------------------------------------------
i have simplified the procedure.
and, u NEED to add AUTHID CURRENT_USER else u will get error saying insufficient privs.
note: this method is good only for oracle 8i.
for older versions, u got to use another method.
hope this will help u;

Report message to a moderator

Re: Bind Variables in DBMS_SQL - PL/SQL GURU NEEDED [message #38430 is a reply to message #38423]

Wed, 17 April 2002 10:27

Tony
Messages: 190
Registered: June 2001

Senior Member

Thanks a lot John and Mahesh... I guess I need some PL/SQL practice... Thanks again..

Report message to a moderator

Previous Topic:	URGENT PL/SQL FUNCTIONS IN SELECT STATEMENTS
Next Topic:	Another command in place of substr

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 21:23:24 CDT 2024