Thanks for the question, Ned.
Asked: October 02, 2016 - 5:14 pm UTC
Last updated: October 02, 2016 - 11:39 pm UTC
Version: 11g
Viewed 1000+ times
You Asked
Using WORKING with Cursors:
Based on the example:
DECLARE
l_last_name employees.last_name%TYPE;
BEGIN
SELECT last_name
INTO l_last_name
FROM employees
WHERE employee_id = 138;
DBMS_OUTPUT.put_line (
l_last_name);
END;
I am trying to acquire the Table OWNER and TABLE_NAME based on having two specific TABLE_NAMES
My script looks like:
(Select OWNER || '.' || TABLE_NAME
FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME = 'COLUMN1')
INTERSECT
(Select OWNER || '.' || TABLE_NAME
FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME = 'COLUMN2');
RESULTS LIKE:
USER1.TABLE1
USER1.TABLE2
USER2.TABLE1
USER1.TABLE2
USER1.TABLE1
...
If possible, I would like to create a pointer counter for each OWNER.TABLE_NAME pair.
How do I extract the above results within the Using WORKING with Cursors Format.
Any assistance would be appreciated.
TIA
Ned Holyoak
and Connor said...
I'm not entirely sure what you are asking here...I'm assuming you want to loop through each row that comes back from your query ? If so, then you can do this:
SQL> set serverout on
SQL> begin
2 for i in
3 (
4 (Select OWNER || '.' || TABLE_NAME fqn
5 FROM ALL_TAB_COLUMNS
6 WHERE COLUMN_NAME = 'DEPTNO')
7 INTERSECT
8 (Select OWNER || '.' || TABLE_NAME
9 FROM ALL_TAB_COLUMNS
10 WHERE COLUMN_NAME = 'EMPNO')
11 )
12 loop
13 dbms_output.put_line(i.fqn);
14 end loop;
15 end;
16 /
APEX_040200.WWV_DEMO_EMP
SCOTT.EMP
PL/SQL procedure successfully completed.
SQL>