Re: How to do this in procedures

From: Billy <vslabs_at_onwe.co.za>
Date: 1 Jul 2005 04:36:30 -0700
Message-ID: <1120217790.630583.168150@g14g2000cwa.googlegroups.com>

ramprakash.a_at_gmail.com wrote:
>
> while in procedures, I want some columns in a table seperated by "|"
> symbol in a variable
>
> take for example emp table
>
> i need ename,job,sal,deptno from emp table in a single string like
> 'SMITH|CLERK|800|20'
> here the table and column changes often. so i need to generalise this.
> this is not only for emp table.
>

I agree with Sybrand that this not how you should treat a database, never mind Oracle. This smacks all too much of using Cobol and flat files in the 80's. Nothing wrong with that - if you are using Cobol and flat files. But you're not.

Okay, with the required preaching and lead pipe waving done, here's how it can be done.

==
SQL> create or replace type TColumnNames is table of varchar2(30); 2 /

Type created.

SQL> create or replace type TColumnValues is table of varchar2(4000); 2 /

Type created.

SQL> create or replace type TLotsOfMeaninglessLines is table of varchar2(4000);
2 /

Type created.

SQL> create or replace function StuffItUp( ctable varchar2, columnsList TColumnNames )
2 return TLotsOfMeaninglessLines
3 pipelined is

  4          type    TRefCursor is REF CURSOR;
  5          sql$    varchar2(4000);
  6          c$      TRefCursor;
  7          col$    TColumnValues;
  8          line$   varchar2(4000);
  9  begin
 10          sql$ := 'SELECT TColumnValues( ';
 11          for i in 1..columnsList.Count-1
 12          loop
 13                  sql$ := sql$  || columnsList(i) || ',';
 14          end loop;
 15          sql$ := sql$  || columnsList( columnsList.Count ) || ')

FROM '||ctable;
16
17 PIPE ROW( sql$ ); -- for interest sake, show the SQL in the output
18

 19          open c$ for sql$;
 20          loop
 21                  fetch c$ into col$;
 22                  exit when c$%NOTFOUND;
 23
 24                  line$ := '';
 25                  for i in 1..col$.Count
 26                  loop
 27                          line$ := line$ || col$(i) || '|';
 28                  end loop;
 29
 30                  PIPE ROW( line$ );
 31          end loop;
 32
 33          close c$;
 34          return;

35 end;
36 /

Function created.

SQL> col LINE format a79 truncated
SQL> select

  2          column_value as LINE
  3  from       TABLE( StuffItUp( 'USER_OBJECTS',
TColumnNames('OBJECT_NAME','OBJECT_ID','OBJECT_TYPE') ))
  4  where      rownum < 11;

LINE

SQL>
SQL> alter session set nls_date_format='yyyymmdd hh24miss';

Session altered.

SQL>
SQL> select

  2          column_value as LINE
  3  from       TABLE( StuffItUp( 'USER_TAB_COLUMNS',
TColumnNames('TABLE_NAME','COLUMN_NAME','LAST_ANALYZED') ))
  4  where      rownum < 11;

LINE

SELECT TColumnValues( TABLE_NAME,COLUMN_NAME,LAST_ANALYZED) FROM USER_TAB_COLUM

ABC|NLIST||
BIG_TABLE|OBJECT_ID|20041029 092427|
BIG_TABLE|OBJECT_NAME|20041029 092427|
BIG_TABLE|OBJECT_TYPE|20041029 092427|
BIG_TABLE|CREATED|20041029 092427|
BIG_TABLE|DESCR|20041029 092427|
BILLING_PERIODS|ID||

BILLING_PERIODS|TARIFF_CODE||
BILLING_PERIODS|MONTH|| 10 rows selected.

SQL>
==

> and if i have the string similar to this how can i insert or update
> into the table

Whoa.. definately not. Not update. Ever. (at least not on any of my databases).

For insert, use EXTERNAL TABLES.

--
Billy

Received on Fri Jul 01 2005 - 06:36:30 CDT