CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsqloracle10g
Girish R Acharya
Girish R Acharya

Reputation: 140

how to assign variable dynamically

    QL> desc newemp
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    EMPNO                                     NOT NULL NUMBER(4)
    ENAME                                              VARCHAR2(10)
    JOB                                                VARCHAR2(9)
    MGR                                                NUMBER(4)
    HIREDATE                                           DATE
    SAL                                                NUMBER(7,2)
    COMM                                               NUMBER(7,2)
    DEPTNO                                    NOT NULL NUMBER(2)

declare
    empsfile utl_file.file_type;
    cursor empscur is
    select * from newemp;
    begin
    empsfile := utl_file.fopen('DIPRJDIR','EMPS.TXT','W');
    for rec in empscur
    loop
    --utl_file.put_line(empsfile,rec.EMPNO||rec.ENAME||rec.JOB||rec.MGR||rec.HIREDATE||rec.SAL||rec.COMM||rec.DEPTNO);
    utl_file.put_line(empsfile,lpad(rec.EMPNO,4,'0')||Rpad(rec.ENAME,10,' ')||Rpad(rec.JOB,9,' ')||Rpad(rec.MGR,4,'0')||Rpad(rec.HIREDATE,10,' ')||lpad(rec.SAL,9,'0')||Lpad(rec.COMM,9,'0')||Lpad(rec.DEPTNO,2,'0'));

    end loop;
    UTL_FILE.FCLOSE(empsfile );
    EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE( 'ERROR -->' ||  SQLERRM);
    END;
    /

I had written a program executing porperly but now my boss is telling pading should be done according to the datatype value for example empno number (4) than padding should be done by 4.But in the above mention code i had manually written the value. Now my boss is telling me i should dynamically assign value while padding for example (rec.SAL,9,'0') in this 9 values should be taken directly from table.So the if boss has alter the table than also above code should work and we dont have to write the code again.

pleqse help me

Upvotes: 0

Views: 185

Answers (1)

tbone
tbone

Reputation: 15473

Hmm, empno/deptno, I remember those. Not quite sure what you (or your boss/teacher) is looking for, but I think she's talking about:

select column_name || ','
from all_tab_columns
where table_name = 'SOME_TABLE'
and owner = 'SOME_OWNER'
order by column_id;

You can then copy/paste into your code, which is helpful for a table with 100 columns for example. You can of course modify for your specific needs, maybe (untested):

select
  case
    when data_type = 'NUMBER' then
        'lpad(rec.' || column_name || ',4,''0'') ||'
    when data_type = 'VARCHAR2' then
        'rpad(rec.' || column_name || ',10,'' '') ||'
    when data_type = 'DATE' then
        'rpad(to_char(rec.' || column_name || ',''MM/DD/YYYY''),10,'' '') ||'
    else
        'rpad(rec.' || column_name || ',10,'' '') ||'
  end val
from all_tab_columns
where table_name = 'SOME_TABLE'
and owner = 'SOME_OWNER'
order by column_id;

Upvotes: 2

Related Questions