• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2479
  • Last Modified:

Get column names in Oracle of a dblink table

I have a procedure that should take a table name which can be a dblink table.
When the table name is a normal table we can easily withdraw the column
Names using the following cursor
SELECT
    COLUMN_NAME
    FROM ALL_TAB_COLUMNS
    WHERE TABLE_NAME = upper(p_table_name);
     
The procedure has the following signature:
Procedure Get_ColumnList( p_table_name IN VARCHAR, p_list out va_list)
p_list is VARRAY list

When it is a dblink  table (tablename@my_link) the cursor would have to be in a similar format :
SELECT
    COLUMN_NAME
    FROM ALL_TAB_COLUMNS@my_link
    WHERE TABLE_NAME = upper(tablename);

I have try to take the tablename@my_link which is passed through the parameter p_table_name, split it into tablename and @mylink and use a OPEN FOR cursor with a dynamic SQL having bind variablies but I am having problems to pass ALL_TAB_COLUMNS@my_link during run time.

The cursor is the format:
OPEN c_colNames FOR
        'SELECT COLUMN_NAME FROM '||
            ':1 WHERE TABLE_NAME =:2'
        USING   v_my_table_type, v_tabname;

How can I execute the query which at the moment of declaration doesn’t know what would be passed in the FROM clause
0
diteps06
Asked:
diteps06
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
lwadwellCommented:
I do not think you can dynamically bind table (or column) names .. only values.  To do dynamic SQL like that you would need to construct it as a string and use EXECUTE IMMEDIATE.

If you do not have many dblinks you could build the cursor using a UNION ALL (it is a bit messy) e.g.
OPEN c_colNames FOR
        SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS
        WHERE TABLE_NAME =:2 AND 'thisdb' = NVL(:1,'thisdb')
        UNION ALL
        SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS@my_link
        WHERE TABLE_NAME =:2 AND '@my_link' = :1
        USING   v_my_table_type, v_tabname;
0
 
diteps06Author Commented:
The point is you don't know at compilation time the value of @my_link.
It is been passed at runtime.

@my_link is passed through the parameter p_name_table whose  value is tablename@my_link.
So it has to be in a variable.
So you cann't use   ALL_TAB_COLUMNS||v_my_link or  ALL_TAB_COLUMNS@my_link  in the FROM clause.
0
 
lwadwellCommented:
My point by "If you do not have many dblinks" was that you could hard-code for a finite list of known and possible dblinks (this could mean many UNION ALL's).
If you want to be completely flexible and not hard-code ... use EXECUTE IMMEDIATE.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
lwadwellCommented:
Sorry ... a REF CURSOR is probably what you want.
eg
DECLARE
    TYPE colCurTyp IS REF CURSOR;
    col_cv          colCurTyp;
    v_name          all_tab_columns.column_name%TYPE;
    all_table_name  VARCHAR2(80) := 'all_tab_columns';
    table_name      VARCHAR2(40) := 'CUSTOMERS';
BEGIN
    DBMS_OUTPUT.PUT_LINE('start');
    OPEN col_cv FOR 'SELECT column_name FROM ' || all_table_name || ' WHERE table_name = ''' || table_name || '''';
    LOOP
        FETCH col_cv INTO v_name;
        EXIT WHEN col_cv%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name);
    END LOOP;
    CLOSE col_cv;
END;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
If I understand the question correctly, can you not just check for a possible dblink and tweak the input string?

See if this is what you want:

create or replace procedure myproc(in_table_name in varchar2)
is
	col_count number;
	v_table_name	varchar2(255);
	v_db_link		varchar2(255);
begin
	if instr(in_table_name,'@') > 0 then
		v_db_link := regexp_replace(in_table_name,'(.*)(@.*)','\2');
	 	v_table_name := upper(regexp_replace(in_table_name,'([^@]*)(@.*)','\1'));
	else
	 	v_table_name := upper(in_table_name);
	end if;

	execute immediate 'select count(*) from user_tab_columns' || v_db_link || ' where table_name = :mytable'
	 into col_count using v_table_name;
	dbms_output.put_line(in_table_name || ' has ' || to_char(col_count) || ' columns.');
end;
/

Open in new window

0
 
diteps06Author Commented:
>>  slightwv
The solution is interesting .
However the query below has the result:  ‘tablename.’ whereas  I prefer  ‘tablename’

 select  upper(regexp_replace('tablename.@mylink','([^@]*)(@.*)','\1'))
from dual
0
 
diteps06Author Commented:
>> lwadwell
The solution is interesting .
I had a similar idea but there was something wrong with the concatenation.
Thanks!
0
 
slightwv (䄆 Netminder) Commented:
>>However the query below has the result:  ‘tablename.’ whereas  I prefer  ‘tablename’

Why would someone pass in an invalid table name?

You do know that can be a valid table name if you use double quotes:  create table "tablename."(col1 char(1));

What 'scrubbing' of the incoming data do you want?
0
 
diteps06Author Commented:
>>  slightwv
You are right. I have realised the query gives the expected result.
Thanks!!!
0
 
johnsoneSenior Oracle DBACommented:
What about DBMS_SQL?  It will take a query and list out the column names for you.  No need for parsing of anything to figure out links.

This is a quick example to show how it can be done.

declare
   p_table_name varchar2(4000);
   cur number;
   cur_exec number;
   col_cnt integer;
   col_tab DBMS_SQL.DESC_TAB;
begin
   p_table_name := 'dba_tables';
   cur := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(cur, 'select * from ' || p_table_name, DBMS_SQL.NATIVE);
   cur_exec := DBMS_SQL.EXECUTE(cur);
   DBMS_SQL.DESCRIBE_COLUMNS(cur, col_cnt, col_tab);
   for i in col_tab.first .. col_tab.last loop
     dbms_output.put_line ('column = >' || col_tab(i).col_name || '<');
   end loop;
   DBMS_SQL.CLOSE_CURSOR(cur);
end;
/

Open in new window


Let the SQL engine do all the work for you.

This can get you around the issue of needing permissions on dictionary tables as well.  As long as you have permission to select from the table, this will give you the column names.

Also, since this method only goes through the parsing phase (notice there is no FETCH), no query is actually executed to get the information.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now