OraFAQ Forum: SQL & PL/SQL » Fetching table sql inside code

Home » SQL & PL/SQL » SQL & PL/SQL » Fetching table sql inside code (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)

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

Fetching table sql inside code [message #634002]

Mon, 02 March 2015 17:11

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Hi,

Suppose I have below procedure or packaged procedure.

CREATE TABLE test_manu
(
   emp_id     NUMBER,
   emp_name   VARCHAR2 (20)
);

INSERT INTO test_manu
     VALUES (1, 'Manu');

COMMIT;

CREATE OR REPLACE PROCEDURE proc_test_manu
IS
   v_test_manu   test_manu%ROWTYPE;
BEGIN
   SELECT * INTO v_test_manu.emp_name FROM DUAL;

   SELECT 1 INTO v_test_manu.emp_id FROM DUAL;

   SELECT emp_id,
          emp_name
     INTO v_test_manu
   FROM test_manu;

   SELECT 22 INTO v_test_manu.emp_id FROM DUAL;

   SELECT emp_id, emp_name INTO v_test_manu
   FROM test_manu;

   DBMS_OUTPUT.put_line (v_test_manu.emp_id || ' ' || v_test_manu.emp_name);

   SELECT emp_id, emp_name INTO v_test_manu FROM
   test_manu;

   SELECT emp_id, emp_name INTO v_test_manu FROM test_manu;
END;
/

The objective is, I have to heavily modified the things due to structure change in table (suppose I have to delete column emp_name from table).
Now if I am manually going through all the lines, it will take a lot of time, but if I have some way, to cut it down to some extent, it will help me a lot.

So I want to find out all those statements only, where this column is present along with the table name.
Logic is first find the table name from all_source, then find that sql statement having that table name (statement between leading and following ";")

If I am searching for table test_manu, output should be, along with line numbers:

   SELECT emp_id,
          emp_name
     INTO v_test_manu
   FROM test_manu;

   SELECT emp_id, emp_name INTO v_test_manu
   FROM test_manu;

   SELECT emp_id, emp_name INTO v_test_manu FROM
   test_manu;

   SELECT emp_id, emp_name INTO v_test_manu FROM test_manu;

Thanks,
Manu

[Updated on: Mon, 02 March 2015 17:15]

Report message to a moderator

Re: Fetching table sql inside code [message #634005 is a reply to message #634002]

Mon, 02 March 2015 17:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I have an easy way to obtain the desired details.
1) remove the column
2) run the complete QA test suite to learn when removed column now is used.

Report message to a moderator

Re: Fetching table sql inside code [message #634008 is a reply to message #634005]

Mon, 02 March 2015 19:37

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

That's a good idea, but after deletion, I have to add few columns and want to revise the statements having that table.

Report message to a moderator

Re: Fetching table sql inside code [message #634019 is a reply to message #634008]

Mon, 02 March 2015 23:50

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

If you want to know whether there are SQL statements which fetch from table test_manu, then after executing the procedure, you could look for the sql in v$sql. In test environment, you could flush the shared_pool before execution, and then you could query v$sql to see the SQL statement.

For example,

SQL> CREATE TABLE test_manu
  2  (
  3     emp_id     NUMBER,
  4     emp_name   VARCHAR2 (20)
  5  );

Table created.

SQL>
SQL> INSERT INTO test_manu
  2       VALUES (1, 'Manu');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> CREATE OR REPLACE PROCEDURE proc_test_manu
  2  IS
  3     v_test_manu   test_manu%ROWTYPE;
  4  BEGIN
  5     SELECT * INTO v_test_manu.emp_name FROM DUAL;
  6
  7     SELECT 1 INTO v_test_manu.emp_id FROM DUAL;
  8
  9     SELECT emp_id,
 10            emp_name
 11       INTO v_test_manu
 12     FROM test_manu;
 13
 14     SELECT 22 INTO v_test_manu.emp_id FROM DUAL;
 15
 16     SELECT emp_id, emp_name INTO v_test_manu
 17     FROM test_manu;
 18
 19     DBMS_OUTPUT.put_line (v_test_manu.emp_id || ' ' || v_test_manu.emp_name);
 20
 21     SELECT emp_id, emp_name INTO v_test_manu FROM
 22     test_manu;
 23
 24     SELECT emp_id, emp_name INTO v_test_manu FROM test_manu;
 25  END;
 26  /

Procedure created.

SQL>
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL>
SQL> SET serveroutput ON
SQL>
SQL> exec proc_test_manu;
1 Manu

PL/SQL procedure successfully completed.

SQL>
SQL> set linesize 100
SQL> SELECT SQL_FULLTEXT, EXECUTIONS FROM v$sql WHERE sql_fulltext LIKE '%TEST_MANU%';

SQL_FULLTEXT                                                                     EXECUTIONS
-------------------------------------------------------------------------------- ----------
SELECT EMP_ID, EMP_NAME FROM TEST_MANU                                                    4
SELECT SQL_FULLTEXT, EXECUTIONS FROM v$sql WHERE sql_fulltext LIKE '%TEST_MANU%'          1

SQL>

So, you know that the sql SELECT EMP_ID, EMP_NAME FROM TEST_MANU is executed in the given procedure 4 times. But I would prefer Blackswan's suggestion, as it seems to be a better regression test.

Report message to a moderator

Re: Fetching table sql inside code [message #634058 is a reply to message #634019]

Tue, 03 March 2015 11:42

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

And what if it's running through different packages, which I don't want to modify?

Well, thanks. I think, I will work on that on my own.

Manu

Report message to a moderator

Previous Topic:	Creating a salary report. Having trouble calculating previous salary.
Next Topic:	Adding a row based on a value

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 09:33:45 CDT 2024