OraFAQ Forum: SQL & PL/SQL » utl

Home » SQL & PL/SQL » SQL & PL/SQL » utl_file help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

utl_file help [message #257020]

Tue, 07 August 2007 07:14

gautamvv
Messages: 254
Registered: June 2005

Senior Member

I have two tables


CREATE TABLE parent ( 
  SETTING_ID        NUMBER (16)   NOT NULL, 
  FUNCT_ID          NUMBER (16),
  
  DESCR             VARCHAR2 (128),
     
  SETTING_FUNCT_ID  NUMBER (16), 
  
     
  SIBLING_INDEX     NUMBER (8)
   );
  

CREATE TABLE child ( 
  SETTING_ID        NUMBER (16)   NOT NULL, 
    CODE              VARCHAR2 (50));

I was given a file which has many insert into parent....insert into child...statements,
and I prepared a script, which basically has anonymous blocks, the purpose of script
is to populate these tables..

one such block looks like this:


----
BEGIN
    l_SIBLING_IND:=l_CUSTOMERTYPE_ID*300;
    l_SIBLING_IND:=l_SIBLING_IND+1;
    l_SETTING_ID_LK:=l_ECFN_ID||l_ENTITY_TYPE_ID||l_SIBLING_IND; 
    
    BEGIN
        
        INSERT INTO parent ( SETTING_ID, FUNCT_ID,  DESCR,
        SETTING_FUNCT_ID,  SIBLING_INDEX ) VALUES ( 
        l_SETTING_ID_LK, 200, 
        , 'AlternateLabel', 200,  l_SIBLING_IND); 

        dbms_output.put_line('Added Setting for parent: '||l_SETTING_ID_LK);
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            DBMS_OUTPUT.PUT_LINE('Existing Setting in parent: '||l_SETTING_ID_LK);
            UPDATE parent SET
            FUNCT_ID=200,
            DESCR='AlternateLabel',
            SETTING_FUNCT_ID=200,
            SIBLING_INDEX=l_SIBLING_IND
            WHERE SETTING_ID=l_SETTING_ID_LK;
            DBMS_OUTPUT.PUT_LINE('Updated Setting in parent : '||l_SETTING_ID_LK);
        WHEN OTHERS THEN NULL;
    END;

    BEGIN
       
        
        INSERT INTO child ( SETTING_ID, 
         CODE ) VALUES ( 
        l_SETTING_ID_LK,  NULL); 

        dbms_output.put_line('Added Setting for child: '||l_SETTING_ID_LK);
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            DBMS_OUTPUT.PUT_LINE('Existing Setting in child: '||l_SETTING_ID_LK);
            UPDATE child SET
                     
            CODE=NULL
           WHERE SETTING_ID=l_SETTING_ID_LK;
           DBMS_OUTPUT.PUT_LINE('Updated Setting in child : '||l_SETTING_ID_LK);
        WHEN OTHERS THEN NULL;
    END;

END;
----

so obviously if there are 100 insert into statements, i have to code this, copy and paste the
same above block, and change the data properly, for 100 times !

so the next time something like this comes up, i dont want to keep on copy / paste

i want to write a script, and the output of that script is the above block(s)..

meaning, data is already there in the tables, when i run the script, it should

query those two tables, and generate those many blocks for insert and update.

Now, i think i can use utl_file, i am fairly new and i have never used it, i have come up

with a skeleton piece of code about how i can use utl_file and achieve what i want to do

will anyone please tell me if this approach is right:


----

DECLARE
    cursor c1 is select * from parent;
    cursor c2(sid number) is select * from child where SETTING_ID=sid;
    

BEGIN -- OUTER BLOCK

    for r1 in c1 loop
        dbms_output.put_line('BEGIN')  -- print BEGIN, the begininng of block

            l_SIBLING_IND:=l_CUSTOMERTYPE_ID*300;
            l_SIBLING_IND:=l_SIBLING_IND+1;
            l_SETTING_ID_LK:=l_ECFN_ID||l_ENTITY_TYPE_ID||l_SIBLING_IND; -- these 3 lines should be printed

                dbms_output.put_line('BEGIN')  -- print BEGIN, the begininng of block /or use UTL_FILE ? 
                
                        print - insert into parent(SETTING_ID,FUNCT_ID....) values(r1.SETTING_ID,r1.FUNCT_ID....);
			dbms_output.put_line('Added Setting for parent: '||l_SETTING_ID_LK);								
                        dbms_output.put_line('EXCEPTION') 
                            dbms_output.put_line(' WHEN DUP_VAL_ON_INDEX THEN') 
                                update parent set funct_id=,descr=,SETTING_FUNCT_ID=,SIBLING_INDEX=
                                where setting_id=l_SETTING_ID_LK;

                 dbms_output.put_line('END')   -- use utl otherwise?
                 

                    for r2 in c2(r1.SETTING_ID) loop
                        BEGIN
                            print - insert into child(SETTING_ID,CODE) values(l_SETTING_ID_LK,  NULL); 
                        dbms_output.put_line('EXCEPTION') 
                            dbms_output.put_line(' WHEN DUP_VAL_ON_INDEX THEN') 
                                update child set  CODE=NULL
                                WHERE SETTING_ID=l_SETTING_ID_LK; 

                                DBMS_OUTPUT.PUT_LINE('Updated Setting in child : '||l_SETTING_ID_LK);
                                WHEN OTHERS THEN NULL;
                         END;
                         END LOOP; -- INNER LOOP

       dbms_output.put_line('END') -- PRINT THIS END

        END LOOP; -- OUTER LOOP

 END;-- OUTER BLOCK

----

the declare, and begin and end commented as 'outer block'
has to be my code, remaining ones need to be printed

in the above, the dbms_output.put_line, and print are the ones which should appear in the output..

somehow i need to spool it and do it''

can anyone tell me if this is right? and how to go about it?

or any other approach?

i have gone through utl_file documentation, should i use utl_file_dir parameter in init.ora approach

or create directory approach ?

Report message to a moderator