Home » SQL & PL/SQL » SQL & PL/SQL » How to populate the dynamic recordset while passing the tablename dynamically (Oracle9i)
How to populate the dynamic recordset while passing the tablename dynamically [message #420794] Wed, 02 September 2009 05:05 Go to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

Passing a table name and getting the data for table.
table name will have the dynamic value.
Getting error in the section of code [PRINT the data]
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Pls. suggest:
i) How to hold the dynamic table value with respect to tables values changes i.e.How to populate the dynamic recordset while passing the tablename as one does'nt know the table structure in order to fetch the value from ref cursor.

CREATE OR REPLACE PROCEDURE  TK_Ext_TabCol_1(p_tab IN  VARCHAR2)
IS

TYPE r_cursor IS REF CURSOR;
c_cur r_cursor;
v_cur r_cursor;

v_return_cur SYS_REFCURSOR;

v_col VARCHAR2(4000);
frows NUMBER:=0;
BEGIN
  
  -- Populating the col name
  FOR i IN (SELECT column_name  FROM dba_tab_columns WHERE table_name=p_tab ORDER BY column_id)
  LOOP
  v_col:=i.column_name||','||v_col;
  END LOOP;
  v_col:=SUBSTR(v_col,1,LENGTH(v_col)-1);  
 
-- Populating the   
  OPEN c_cur  FOR
  'SELECT '||v_col ||' FROM '||p_tab;

-- PRINT the data  
 LOOP
      FETCH c_cur INTO v_cur;
      EXIT WHEN c_cur%NOTFOUND;
     frows :=frows+1;
  END LOOP;
  CLOSE c_cur;
  dbms_output.put_line(frows);

END TK_Ext_TabCol_1;
Re: How to populate the dynamic recordset while passing the tablename dynamically [message #420805 is a reply to message #420794] Wed, 02 September 2009 06:09 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.
Where (which lines) does the error come from?

In addition, the following is wrong:
FETCH c_cur INTO v_cur;

You don't into a ref cursor, you fetch into a record (or a set of variables).

You can't do it this way, you have to use dbms_sql.
Search from "print_table" on AskTom for an example.

Regards
Michel
Previous Topic: condition
Next Topic: Import File
Goto Forum:
  


Current Time: Wed Dec 07 06:55:17 CST 2016

Total time taken to generate the page: 0.23029 seconds