This forum is currently locked. You can't register or post questions at this time. (read more)

how to retrive information from dynamic query

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

kytemaniac
Senior Member
Posts: 234
Joined: Tue May 19, 2009 12:59 am

how to retrive information from dynamic query

Postby kytemaniac » Tue Mar 26, 2013 9:34 am

Hi

below is my code


Code: Select all


set serveroutput on

DECLARE
cursor c_oe_table_cursor is select table_name from user_tables where table_name like 'AC%';
v_dynamic_sql VARCHAR2(2000);
v_table_name user_tables.table_name%TYPE;
ecode NUMBER;
emesg VARCHAR2(200);
TYPE IndexCurTyp is REF CURSOR;
v_oe_index_cursor IndexCurTyp;
v_index_record user_indexes%ROWTYPE;
v_index_name user_indexes.index_name%TYPE;
v_dynamic_index_sql VARCHAR2(2000);
BEGIN
         FOR v_oe_table_rs in c_oe_table_cursor LOOP
            v_table_name :=   v_oe_table_rs.table_name;
            v_dynamic_sql :='truncate table ' || v_table_name;
            BEGIN
                  dbms_output.put_line('truncating table ' || v_dynamic_sql);
            EXCEPTION
            WHEN OTHERS THEN
            ecode := SQLCODE;
           emesg := SQLERRM;
           dbms_output.put_line(TO_CHAR(ecode) || '-' || emesg);
            END;
            
            v_dynamic_sql :='alter table ' || v_table_name || ' MOVE TABLESPACE XCEL_ADM_NOLOGGING';
            
            BEGIN
                  dbms_output.put_line('moving tables'' tablespace ' || v_dynamic_sql);
            EXCEPTION
            WHEN OTHERS THEN
            ecode := SQLCODE;
           emesg := SQLERRM;
           dbms_output.put_line(TO_CHAR(ecode) || '-' || emesg);
            END;
            
            v_dynamic_sql :='select index_name from ' || v_table_name;
            
            OPEN  v_oe_index_cursor for v_dynamic_sql; ->error kicks in start
            LOOP
               FETCH v_oe_index_cursor INTO v_index_record;
               v_index_name:=v_index_record.index_name;
               v_dynamic_index_sql :='alter index ' || v_index_name || ' rebuild tablespace XCEL_ADM_NOLOGGING';            
               
               EXIT WHEN v_oe_index_cursor%NOTFOUND;
            END LOOP;
             ->error kicks in end
         END LOOP;
END;
/



the line of code tag from ->error kicks in start and ->error kicks in end

have error. so do anyone know how to retrieve dynamic information from an dynamic sql?

thanks a lot!

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: how to retrive information from dynamic query

Postby Tim... » Tue Mar 26, 2013 3:44 pm

Hi.

There are a couple of things here.

1) Your code says,

Code: Select all

v_dynamic_sql :='select index_name from ' || v_table_name;


Are you sure you don't mean this?

Code: Select all

v_dynamic_sql :='select index_name from user_indexes where table_name = ''' || v_table_name || '''';


2) Your code is not using bind variables. That's a really bad idea. You should probably consider something like the following.

Code: Select all

v_dynamic_sql :='select index_name from user_indexes where table_name = :b1';
OPEN  v_oe_index_cursor for v_dynamic_sql using v_table_name;


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests