Home » SQL & PL/SQL » SQL & PL/SQL » fetch data based on Column (Oracle 11g)
fetch data based on Column [message #627854] Mon, 17 November 2014 08:57 Go to next message
thelearner
Messages: 133
Registered: April 2013
Location: INDIA
Senior Member
I have to display data using dynamic column name
create table XX_TEST(name varchar2(20));
insert into XX_TEST('DEPTNO');
commit;

I written this code
DECLARE
   CURSOR lc_name
   IS
      SELECT NAME
        FROM xx_test;
   str1       VARCHAR2 (1000) := 'Name';
   sal        NUMBER (10);
   ln_sal     NUMBER (10);
BEGIN
   FOR cur_name IN lc_name
   LOOP
      str1 :=
            'SELECT e.sal -- into ln_sal   
       FROM emp_d e, dept d
      WHERE   d.'
         || cur_name.NAME
         || '=e.deptno and empno=7566';

      EXECUTE IMMEDIATE str1
                   INTO ln_sal;

      DBMS_OUTPUT.put_line ('sal' || ln_sal);
   END LOOP;
END;

am gettig below output
sal 2975

but my requirement is I have to get all values without empno in execute statement like
str1 :=
            'SELECT e.sal -- into ln_sal   
       FROM emp_d e, dept d
      WHERE   d.'
         || cur_name.NAME
         || '=e.deptno';


my desired output
SAL 800
SAL 1600
SAL 1250
SAL 2975
SAL 1250
SAL 2850
SAL 2450
if there is any mistake please tell me.
How to achieve this..
Re: fetch data based on Column [message #627855 is a reply to message #627854] Mon, 17 November 2014 09:03 Go to previous messageGo to next message
thelearner
Messages: 133
Registered: April 2013
Location: INDIA
Senior Member
I tried in this way,

/* Formatted on 2014/11/17 20:31 (Formatter Plus v4.8.8) */
DECLARE
   CURSOR lc_name
   IS
      SELECT NAME
        FROM xx_test;

   TYPE emptabtyp IS TABLE OF emp.sal%TYPE
      INDEX BY BINARY_INTEGER;

   dept_tab   emptabtyp;
   str1       VARCHAR2 (1000) := 'Name';
   sal        NUMBER (10);
   ln_sal     NUMBER (10);
BEGIN
   FOR cur_name IN lc_name
   LOOP
      --select sal into ln_sal from emp_d where job=cur_name.name;
      str1 :=
            'SELECT e.sal  bulk collect into dept_tab   
        FROM emp_d e, dept d
       WHERE   d.'
         || cur_name.NAME
         || '=e.deptno';

      EXECUTE IMMEDIATE str1;

--      DBMS_OUTPUT.put_line ('sal' || ln_sal);
      FOR i IN dept_tab.FIRST .. dept_tab.LAST
      LOOP
         DBMS_OUTPUT.put_line (dept_tab (i));
      END LOOP;
   END LOOP;
END;

but I am getting below error
ORA-03001: unimplemented feature
ORA-06512: at line 26

Re: fetch data based on Column [message #627858 is a reply to message #627855] Mon, 17 November 2014 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
interesting homework problem.

How do you know for which table column in XX_TEST applies?

Objects should not be named using RESERVED_WORDS

SQL> select keyword from v$reserved_words where keyword like 'NA%' order by 1;

KEYWORD
------------------------------
NAME
NAMED
NAMESPACE
NAN
NANVL
NATIONAL
NATIVE
NATIVE_FULL_OUTER_JOIN
NATURAL
NAV

10 rows selected.

Re: fetch data based on Column [message #627861 is a reply to message #627855] Mon, 17 November 2014 09:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

BULK COLLECT should be as an option of EXECUTE IMMEDIATE not inside the string to execute.
This string is executed at SQL level when "bulk collect into" is meaningless (as "line 26" is meaningless for us) and is just a syntax error.

2 good books for you (but only if you read them):
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

[Updated on: Mon, 17 November 2014 09:43]

Report message to a moderator

Re: fetch data based on Column [message #627862 is a reply to message #627861] Mon, 17 November 2014 10:08 Go to previous messageGo to next message
thelearner
Messages: 133
Registered: April 2013
Location: INDIA
Senior Member
Got the Output... Thank you Cadot, Blackswan,
DECLARE
   CURSOR lc_name
   IS
      SELECT dname
        FROM xx_test;

   TYPE emptabtyp IS TABLE OF emp.sal%TYPE
      INDEX BY BINARY_INTEGER;

   dept_tab   emptabtyp;
   str1       VARCHAR2 (1000) := 'Name';
   sal        NUMBER (10);
   ln_sal     NUMBER (10);
BEGIN
   FOR cur_name IN lc_name
   LOOP
      --select sal into ln_sal from emp_d where job=cur_name.name;
      EXECUTE IMMEDIATE    'SELECT e.sal    
        FROM emp_d e, dept d
       WHERE   d.'
                        || cur_name.dname
                        || '=e.deptno'
      BULK COLLECT INTO dept_tab;

      FOR i IN dept_tab.FIRST .. dept_tab.LAST
      LOOP
         DBMS_OUTPUT.put_line (dept_tab (i));
      END LOOP;
   END LOOP;
END;
Re: fetch data based on Column [message #627863 is a reply to message #627862] Mon, 17 November 2014 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> || '=e.deptno'

Strange problem which contains hard coded column name as part of the "solution"; which only succeeds for one specific table value in XX_TEST
Re: fetch data based on Column [message #627864 is a reply to message #627862] Mon, 17 November 2014 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thank for the feedback and code.
However, this is just an exercise as you should never have such things (name of the column/table... in another table) in your application.

Re: fetch data based on Column [message #627868 is a reply to message #627854] Mon, 17 November 2014 11:00 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
You could use XML:

with t as (
           select  dbms_xmlgen.getxmltype(
                                          'select  sal
                                             from  emp e,dept d
                                             where e.' || name || ' = d.' || name
                                         ) x
             from  xx_test
          )
select  'SAL ' || sal 
  FROM  t,
        xmltable(
                 '/ROWSET/ROW'
                 passing x
                 columns
                   sal number path 'SAL'
                )
/

'SAL'||SAL
-------------------
SAL 800
SAL 1600
SAL 1250
SAL 2975
SAL 1250
SAL 2850
SAL 2450
SAL 3000
SAL 5000
SAL 1500
SAL 1100

'SAL'||SAL
-------------------
SAL 950
SAL 3000
SAL 1300

14 rows selected.

SQL> 


SY.
Previous Topic: how to find record with all 0 values records
Next Topic: compile package - select V$MVREFRESH without user's rights
Goto Forum:
  


Current Time: Fri Mar 29 00:35:04 CDT 2024