Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Bhavesh.

Asked: April 06, 2001 - 10:17 pm UTC

Last updated: July 16, 2013 - 2:25 pm UTC

Version: 8.1.7`

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I have a table having columns columns tablename, username and columnname. This table will contain the columns and tables from user schema, which the user is not privelaged to see. So before generating the output (html format) using Pl/SQL server pages, we have to determine which columns user can see and then have to display only those columns. I have written a procedure something like below.

-------------PL/SQL Procedure

create or replace procedure colselect is
cursor c1 is select * from (select column_name from user_tab_columns
where table_name ='EMP' and data_type in ('VARCHAR2', 'NUMBER') minus
select columnname from user_col_privs where userid='TEST' and
tablename='EMP');
x c1%rowtype;
sqlstmt varchar2(2000);
begin
open c1;
loop
fetch c1 into x;
exit when c1%notfound;
sqlstmt := sqlstmt ||x.column_name||',';
end loop;
close c1;
sqlstmt := 'select '||substr(sqlstmt, 1, length(sqlstmt)-1)||' from EMP';
dbms_output.put_line(sqlstmt);
end;
/


Now here if I take the sqlstmt in a ref cursor, I am not able to determine where to fetch this into, because the number of columns in the sqlstmt varies. How can I use this sqlstmt to create a ref cursor, fetch into variable and then use htp.p column name, which also is a variable.
Also please let me know if I can use this procedure as a generic one where by I pass the username and tablename as parameters and if there is a better way to achieve above functionality.
Thanks a lot in Advance,
Regards


Bhavesh

and Tom said...

Well, you won't be able to use a REF CURSOR for this, you'll have to use DBMS_SQL. dbms_sql will let you procedurally process the columns and has a describe routine to get the column headings as well.

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:235814350980 <code>

for an example. (you might look at owa_util.cellsprint as well).

It would be quite impossible for a REF CURSOR to do this as you would have to have a huge if then elsif block to handle every single possible permutation of fetches you could ever have. DBMS_SQL is the right approach in this case.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Passing tables names as argument

A reader, April 09, 2003 - 12:10 am UTC

Hi Tom,
I need to populate data warehouse (DW) tables from data staging (DS) tables. The data is already cleaned in DS. The DW table definitions is exactly same as in DS except that it contains an extra column to indicate that row is marked for Insert, Delete or Update in DW. There are plenty of tables. Is it possible to dynamically do through DB procedure like this?
1. DB procedure excepts a table name
2. The procedure dynamically get all the column names from DS table (parameter) and insert, update or delete into DM tables (same parameter, as tables names are same) without writing explicit column names for each table
Could you please show me with an example?

Thanks


Tom Kyte
April 09, 2003 - 8:45 am UTC 

sure, here is an example:

ops$tkyte@ORA817DEV> create table t
  2  as
  3  select username, user_id, created
  4    from all_users;

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> alter table t add constraint t_pk primary key(user_id);

Table altered.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create table stage
  2  as
  3  select lower(username) username, user_id, created, 'U' dml_type
  4    from all_users
  5   where mod(user_id,2) = 1
  6  /

Table created.

ops$tkyte@ORA817DEV> insert into stage
  2  select 'X'||username, -(user_id-1), created, 'I' dml_type
  3    from all_users
  4  /

56 rows created.

ops$tkyte@ORA817DEV> insert into stage
  2  select username, user_id, created, 'D' dml_type
  3    from all_users
  4   where mod(user_id,2) = 0
  5  /

27 rows created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> alter table stage add constraint stage_pk primary key(user_id);

Table altered.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace procedure p ( p_str in varchar2 )
  2  is
  3     l_str   long := p_str;
  4  begin
  5     dbms_output.put_line( '---------------' );
  6     loop
  7        exit when l_str is null;
  8        dbms_output.put_line( substr( l_str, 1, 250 ) );
  9        l_str := substr( l_str, 251 );
 10     end loop;
 11     dbms_output.put_line( '---------------' );
 12  end;
 13  /

Procedure created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace procedure sync_em_up( p_tname in varchar2, p_stage in varchar2 )
  2  as
  3      l_cnames      dbms_sql.varchar2_table;
  4      l_cnames_pk   dbms_sql.number_table;
  5      l_cnames_csv  long;
  6      l_pknames_csv long;
  7      l_stmt        long;
  8  begin
  9      select user_tab_columns.column_name,
 10             decode(user_cons_columns.column_name,NULL,0,1)
 11        BULK COLLECT into l_cnames, l_cnames_pk
 12        from user_tab_columns,
 13             (select column_name
 14                from user_cons_columns
 15               where constraint_name =
 16                     ( select constraint_name
 17                         from user_constraints
 18                        where table_name = upper(p_tname)
 19                          and constraint_type = 'P' )
 20             ) user_cons_columns
 21       where user_tab_columns.table_name = upper(p_tname)
 22         and user_tab_columns.column_name = user_cons_columns.column_name(+)
 23       order by user_tab_columns.column_id;
 24
 25      for i in 1 .. l_cnames.count
 26      loop
 27          l_cnames_csv := l_cnames_csv || l_cnames(i) || ', ';
 28          if ( l_cnames_pk(i) = 1 )
 29          then
 30              l_pknames_csv := l_pknames_csv || l_cnames(i) || ', ';
 31          end if;
 32      end loop;
 33      l_cnames_csv := rtrim(rtrim(l_cnames_csv),',');
 34      l_pknames_csv := rtrim(rtrim(l_pknames_csv),',');
 35
 36      l_stmt := 'delete from ' || p_tname ||
 37                ' where ( ' || l_pknames_csv ||
 38                ' ) in ( select ' || l_pknames_csv ||
 39                ' from ' || p_stage || ' where dml_type = ''D'' )';
 40
 41      p(l_stmt);
 42      execute immediate l_stmt;
 43
 44
 45      l_stmt := 'update ( select ';
 46      for i in 1 .. l_cnames.count
 47      loop
 48          l_stmt := l_stmt || ' a.' || l_cnames(i) || ' A' || i ||
 49                             ', b.' || l_cnames(i) || ' B' || i || ',';
 50      end loop;
 51      l_stmt := rtrim(l_stmt, ',' ) || ' from ' || p_tname ||
 52                ' a, ' || p_stage || ' b '  ||
 53                ' where b.dml_type = ''U'' ';
 54      for i in 1 .. l_cnames.count
 55      loop
 56          if ( l_cnames_pk(i) = 1 )
 57          then
 58              l_stmt := l_stmt || ' and a.' || l_cnames(i) ||
 59                                    ' = b.' || l_cnames(i);
 60          end if;
 61      end loop;
 62      l_stmt := l_stmt || ' ) set ';
 63      for i in 1 .. l_cnames.count
 64      loop
 65          if ( l_cnames_pk(i) = 0 )
 66          then
 67              l_stmt := l_stmt || ' A' || i || ' = ' ||
 68                                  ' B' || i || ',';
 69          end if;
 70      end loop;
 71      l_stmt := rtrim(l_stmt,',');
 72
 73      p(l_stmt);
 74      execute immediate l_stmt;
 75
 76      l_stmt := 'insert into ' || p_tname ||
 77                '( ' || l_cnames_csv || ' ) ' ||
 78                'select ' || l_cnames_csv ||
 79                ' from ' || p_stage ||
 80                ' where dml_type = ''I'' ';
 81
 82      p(l_stmt);
 83      execute immediate l_stmt;
 84  end;
 85  /

Procedure created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> exec sync_em_up( 't', 'stage' );
---------------
delete from t where ( USER_ID ) in ( select USER_ID from stage where dml_type = 'D' )
---------------
---------------
update ( select  a.USERNAME A1, b.USERNAME B1, a.USER_ID A2, b.USER_ID B2, a.CREATED A3, b.CREATED B3 from t a, stage b  where
b.dml_type = 'U'  and a.USER_ID = b.USER_ID ) set  A1 =  B1, A3 =  B3
---------------
---------------
insert into t( USERNAME, USER_ID, CREATED ) select USERNAME, USER_ID, CREATED from stage where dml_type = 'I'
---------------

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV>

Thanks a lot "GENIUS"

A reader, April 09, 2003 - 6:19 pm UTC


Privilege problem

A reader, April 09, 2003 - 7:07 pm UTC

Tom,
Once again thank for you invaluable help.
During update I get error
1 update ( select a.CLIENT_NUMBER A1, b.CLIENT_NUMBER B1,
2 a.WC_EMPLOYER_DET A2,
3 b.WC_EMPLOYER_DET B2,
4 a.CLIENT_TYPE A3, b.CLIENT_TYPE B3
5 from tb_nscr_wc_employer a, nscds.tb_nscr_wc_employer b
6 where b.ddl_flag = 'U'
7 and a.CLIENT_NUMBER = b.CLIENT_NUMBER
8 and a.WC_EMPLOYER_DET = b.WC_EMPLOYER_DET )
9* set A3 = B3
from tb_nscr_wc_employer a, nscds.tb_nscr_wc_employer b
*
ERROR at line 5:
ORA-01031: insufficient privileges

The user running this statement (procedure) is nscdw. I am not able to understand why this user needs update privilges for nscds table.
Thanks


Tom Kyte
April 09, 2003 - 9:11 pm UTC

they just do (it's in the update portion of the update stmt) -- you'll have to use the where exists and correlated subquery update approach instead if you cannot get update (slower in all likelyhood)


How do I handle the exception?

A reader, April 09, 2003 - 11:18 pm UTC

Hi Tom,
If I want to update the DS table column - processed timestamp, after loading the data in DW, I can do it by putting an update statement for the data staging table. But if execute immediate fails, can I handle the exception? At the same time, after execute immediate success, if final update (DS table) fails, I need to rollback execute immediate.
Can I do this?
Thanks


Tom Kyte
April 10, 2003 - 7:36 am UTC

I don't know -- can you handle the exception? I mean you can definitely CATCH it but -- what are you going to do about it? How would you handle it? what exception do you expect? unexpected exceptions should not be caught (eg: don't use when others) as you have no hope of handling them.

execute immediate is not any different transactionally then static sql. You can do the same thing you would do in any case.



A reader, April 10, 2003 - 7:57 am UTC

Very well explained answer.

Excellent !

A reader, April 13, 2003 - 5:13 am UTC

Tom,
The code is excellent. Could you please provide help, if one wants to validate data based upon database constraints (dynamically). The constraints to be checked are:
PK, FK and NOT NULL constraints
i.e., the procedure takes table name as an argument, check for above mentioned constraints, and if data does not meet the constraints, the records are rejected, and put into corresponding rejects table. Obviously, corresponding rejects table does not have any constraint, just an additional column “reason_rejected”.


Tom Kyte
April 13, 2003 - 8:13 pm UTC

you insert it and if rejected, insert it elsewhere?

Rejects Records

A reader, July 07, 2013 - 2:20 pm UTC

Tom,

Following on your code above. I would like to get your thoughts on the following: How about if I have the requirements where :
I have to compare 2 tables with the following:
this is a batch process running continuously checking
for arriving records let's say every 30min.

1. I would like to query the main table first
if the records is new then insert it.
2. if the records is found mark it as on "hold"(
(the reason is that it might not have whole
record)
3. re-try during next run...

I am thinking that I should use a "merge" mechanishm
here....your thoughts?

thanks,

Mark
Tom Kyte
July 16, 2013 - 2:25 pm UTC

i don't understand your processing.

i don't get #2
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library