Sample Scripts to Convert the Rows into One Column with seperator

 SELECT header_id, RTRIM(EXTRACT(EXML,'//ordereditem/text()'),',') ORDERED_ITEM
   FROM (SELECT E.header_id,
          xmlelement("HDR",
              XMLAGG(xmlelement("ordereditem",E.ordered_item||',') ORDER BY ordered_item))EXML
   FROM oe_order_lines_all E
   where header_id= 278563
   GROUP BY E.header_id);
***************************************************************************
SELECT header_id, LTRIM( SYS_CONNECT_BY_PATH( ORDERED_ITEM, ',' ), ',' ) iTEM_STRING
      FROM (SELECT LINE_ID, HEADER_ID, ORDERED_ITEM, ROW_NUMBER( ) OVER (PARTITION BY HEADER_ID ORDER BY ROWNUM) RN
              FROM OE_ORDER_LINES_aLL
             WHERE header_id = 278563)
     WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY HEADER_ID = PRIOR HEADER_ID
       AND RN = PRIOR RN + 1
START WITH rn = 1
  ORDER BY HEADER_ID;
***************************************************************************
SELECT   header_id
       , RTRIM(XMLAGG(XMLELEMENT(e
                               , ordered_item || ','
                                )).EXTRACT('//text()')
             , ','
              ) ordered_item
    FROM oe_order_lines_All
    where header_id = 278563
GROUP BY header_Id
***************************************************************************
SELECT     header_id
         , SUBSTR(SYS_CONNECT_BY_PATH(ordered_item
                                    , ','
                                     )
                , 2
                 ) name_list
      FROM (SELECT ordered_item
                 , header_id
                 , COUNT(*) OVER(PARTITION BY header_id) cnt
                 , ROW_NUMBER() OVER(PARTITION BY header_id ORDER BY ordered_item) seq
              FROM oe_order_lines_all
             WHERE header_id = 278563)
     WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq
       AND PRIOR header_id = header_id;

Storing Images in Oracle tables

Images and Oracle BLOB data are easy to add to a Oracle table. There are two ways to load BLOBs and CLOBs into the database. The first method uses PL/SQL and the DBMS_LOB package and the BFILE datatype to transfer external LOB files into the database internal LOB structures. The second uses the Oracle Call Interface (OCI) to perform the same function. Let's look at the first method.

To load external LOB data into internal LOB storage using PL/SQL alone you must first use a table containing BFILE locators for the files to allow Oracle to access them.

create table xx_images_tbl (

  file_id number,

  file_desc varchar2(30),

  file_loc bfile,

  file_type varchar2(15))

 /

Listing 3: Example Use of BFILE datatype in a Table

Notice in the above code that no LOB storage clause is specified. This is because all that is stored in the database is a locator value for the BFILE consisting of an internal DIRECTORY specification and a file name. The BFILE locators are loaded into the table using the BFILENAME function and a standard insert statement.

An example of this process is shown here in Listing 4.

INSERT INTO xx_images_tbl VALUES(1,'Test_file',bfilename('XX_DIR_NAME','TEST.JPG'),'JPEG');

commit;

Listing 4: Example Set of INSERT Commands to Load BFILE Locators Manually

TIP:

Using a host command to perform a single column directory listing into a file (for example on NT: dir /B >file.lis), then using the UTL_FILE package to read the contents of the created file into the DBMS_SQL package to build the INSERT commands on the fly, an entire directory of LOB datafiles can be loaded at one time into a BFILE table and then on into the internal LOB storage table. For an example see Listing 5.

CREATE OR REPLACE PROCEDURE xx_insert_images_proc(
   bfile_dir                  IN             VARCHAR2
 , bfile_lis                  IN             VARCHAR2
 , bfile_int_dir                             VARCHAR2
)
AS
   cur                                               INTEGER;
   bfile_int                                         VARCHAR2(100);
   sql_com                                           VARCHAR2(2000);
   file_proc                                         INTEGER;
   file_hand                                         UTL_FILE.file_type;
   file_buff                                         VARCHAR2(1022);
   file_type                                         VARCHAR2(4);
BEGIN
   bfile_int                                      := UPPER(bfile_int_dir);
   file_hand                                      := UTL_FILE.fopen(bfile_dir
                                                                  , bfile_lis
                                                                  , 'R'
                                                                   );

   LOOP
      BEGIN
         UTL_FILE.get_line(file_hand
                         , file_buff
                          );
         cur                                            := DBMS_SQL.open_cursor;
         file_type                                      := SUBSTR(file_buff
                                                                , INSTR(file_buff
                                                                      , '.'
                                                                       ) + 1
                                                                , 3
                                                                 );
         file_type                                      := UPPER(file_type);

         IF file_type = 'GIF'
         THEN
            file_type                                      := 'GIF';
         ELSIF file_type = 'JPG'
         THEN
            file_type                                      := 'JPEG';
         END IF;

         sql_com                                        :=
               'INSERT INTO xx_images_tbl '
            || CHR(10)
            || 'VALUES (xx_images_tbl_seq.NEXTVAL,'
            || CHR(39)
            || CHR(39)
            || ', bfilename('
            || CHR(39)
            || bfile_int
            || CHR(39)
            || ','
            || CHR(39)
            || file_buff
            || CHR(39)
            || ') ,'
            || CHR(39)
            || file_type
            || CHR(39)
            || ')';
         DBMS_OUTPUT.put_line(sql_com);
         DBMS_SQL.parse(cur
                      , sql_com
                      , DBMS_SQL.v7
                       );
         file_proc                                      := DBMS_SQL.EXECUTE(cur);
         DBMS_SQL.close_cursor(cur);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;
   END LOOP;

   UTL_FILE.fclose(file_hand);
END;
/

Listing 5: Example Procedure for Loading BFILE Locators based on an External File List

Once the BFILE locators are set in the BFILE table we can use the DBMS_LOB package to read the external LOB (BFILE) into an internal LOB (BLOB, CLOB or NCLOB). This is shown in Listing

 6. The SELECT from the TEMP_BLOB table initializes the internal LOB values so they can be used, otherwise an error will be returned.

CREATE OR REPLACE PROCEDURE load_lob
AS
   ID                                                NUMBER;
   image1                                            BLOB;
   LOCATOR                                           BFILE;
   bfile_len                                         NUMBER;
   bf_desc                                           VARCHAR2(30);
   bf_name                                           VARCHAR2(30);
   bf_dir                                            VARCHAR2(30);
   bf_typ                                            VARCHAR2(4);
   ctr                                               INTEGER;

   CURSOR get_id
   IS
      SELECT file_id
           , file_desc
           , file_type
        FROM xx_images_tbl;
BEGIN
   OPEN get_id;

   LOOP
      FETCH get_id
       INTO ID
          , bf_desc
          , bf_typ;

      EXIT WHEN get_id%NOTFOUND;
      DBMS_OUTPUT.put_line('ID: ' || TO_CHAR(ID));

      SELECT file_loc
        INTO LOCATOR
        FROM xx_images_tbl
       WHERE file_id = ID;

      DBMS_LOB.filegetname(LOCATOR
                         , bf_dir
                         , bf_name
                          );
      DBMS_OUTPUT.put_line('Dir: ' || bf_dir);
      DBMS_LOB.fileopen(LOCATOR
                      , DBMS_LOB.file_readonly
                       );
      bfile_len                                      := DBMS_LOB.getlength(LOCATOR);
      DBMS_OUTPUT.put_line('ID: ' || TO_CHAR(ID) || ' length: ' || TO_CHAR(bfile_len));

      SELECT temp_blob
        INTO image1
        FROM temp_blob;

      bfile_len                                      := DBMS_LOB.getlength(LOCATOR);
      DBMS_LOB.loadfromfile(image1
                          , LOCATOR
                          , bfile_len
                          , 1
                          , 1
                           );

      INSERT INTO xx_images_tbl
           VALUES (ID
                 , bf_desc
                 , image1
                 , bf_typ
                  );

      DBMS_OUTPUT.put_line(bf_desc || ' Length: ' || TO_CHAR(bfile_len) || ' Name: ' || bf_name || ' Dir: ' || bf_dir || ' ' || bf_typ);
      DBMS_LOB.fileclose(LOCATOR);
   END LOOP;
END;
/

/

Listing 6: Example Procedure to Load BFILE values into Internal LOBs

By enforcing a naming standard on the external LOB files the loading procedure in Listing 6.6 could be modified to place BLOB types into BLOBs, CLOB types into CLOBs and NCLOB types into NCLOBs based on the file type values that are parsed from the file extensions in Listing 5. Between the procedure in Listing 5 and the one shown in Listing 6 the example tables in Listings 1 and 3 are populated with LOB values located in a specific directory.

Query to get the Line Number for Covered Product in Oracle Service Contracts

SELECT lines.line_number || '.' || prd_lines.line_number line_number,to_char(prd_lines.id)
  FROM okc_k_headers_b hdr
     , okc_k_lines_b lines
     , okc_k_lines_b prd_lines
     , okc_k_items oki
WHERE 1 = 1
   AND lines.dnz_chr_id = hdr.ID
  AND lines.ID = oki.cle_id
   AND hdr.contract_number = :p_contract_number
   AND hdr.contract_number_modifier = :p_modifier
   AND lines.lse_id = 1
   AND prd_lines.dnz_chr_id = hdr.ID
   AND lines.ID = prd_lines.cle_id
   AND prd_lines.lse_id = 9;

Query to get the Concatenated Line Number on the Sales Order

SELECT   oh.org_id
       , order_number
       ,    ol.line_number
         || '.'
         || ol.shipment_number
         || '.'
         || ol.option_number
         || '.'
         || ol.component_number
         || '.'
         || ol.service_number line_number
    FROM oe_order_lines_all ol
       , oe_order_headers_all oh
   WHERE oh.header_id = ol.header_id
     AND oh.order_number = '&Order_number'
ORDER BY ol.line_number
       , ol.shipment_number
       , ol.option_number
       , ol.component_number
       , ol.service_number

How to Load Images Into BLOB Columns

1. Create a table as mentioned below.

CREATE  TABLE xx_load_images_into_blobs
 ( ID    NUMBER,
   blob_col BLOB
 );

2. Create a logical directory in the database to the physical file system:

 create or replace directory GET_FILES as '/usr/tmp';

3. Create a procedure to load the blobs from the file system using the logical directory.  The gif "xyz.gif" must exist in /usr/tmp directory. 

declare
   f_lob                                             BFILE;
   b_lob                                             BLOB;
BEGIN
   INSERT INTO xx_load_images_into_blobs        
VALUES (1
              , EMPTY_BLOB ()
               )
        RETURN blob_col
          INTO b_lob;

   f_lob                                                    := BFILENAME ('GET_FILES', 'xyz.gif');
   DBMS_LOB.fileopen (f_lob, DBMS_LOB.file_readonly);
   DBMS_LOB.loadfromfile (b_lob, f_lob, DBMS_LOB.getlength (f_lob));
   DBMS_LOB.fileclose (f_lob);
   COMMIT;
END;
/

4. After executing the above block run the Query mentioned below and check the result.

SELECT * FROM xx_load_images_into_blobs;

Script to Compile all invalid Objects in the Data base.

DECLARE
   l_schema                                          VARCHAR2 (240) := '&Schema_name';
   l_include_java                                    INTEGER := 0;
   l_max_passes                                      INTEGER := 10;
   l_debug_flag                                      INTEGER := 0;
   -- Above four variables can be used as parameters if defining a procedure for the Anonymous block
   l_alter_string                                    VARCHAR2 (4000);
   c_cursor                                          INTEGER;
   l_ret_code                                        INTEGER;
   l_object_count                                    INTEGER;
   l_pass                                            INTEGER;
   l_message                                         VARCHAR2 (8000);
   l_status                                          NUMBER;

   CURSOR c_objects_dtls (
      c_schema                   IN             VARCHAR2
   )
   IS
      SELECT   owner
             , object_type
             , object_name
             , DECODE (object_type, 'TYPE', 1, 'OPERATOR', 2, 'PACKAGE', 3, 4) obj_type
          FROM all_objects
         WHERE (status = 'INVALID')
           AND (   c_schema IS NULL
                OR owner = UPPER (c_schema))
           AND object_type IN
                  ('DIMENSION', 'FUNCTION', 'INDEXTYPE', 'JAVA CLASS', 'JAVA SOURCE', 'MATERIALIZED VIEW', 'OPERATOR'
                 , 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TRIGGER', 'TYPE', 'TYPE BODY', 'VIEW')
      ORDER BY obj_type;
BEGIN
   c_cursor                                                 := SYS.DBMS_SQL.open_cursor;
   l_pass                                                   := 1;

   LOOP
      l_object_count                                           := 0;

      FOR c_objects_dtls_rec IN c_objects_dtls (l_schema)
      LOOP
         IF (c_objects_dtls_rec.object_type = 'PACKAGE')
         THEN
            l_alter_string                                           :=
               'ALTER PACKAGE "' || c_objects_dtls_rec.owner || '"."' || c_objects_dtls_rec.object_name
               || '" COMPILE PACKAGE';
         ELSIF (c_objects_dtls_rec.object_type = 'PACKAGE BODY')
         THEN
            l_alter_string                                           :=
                'ALTER PACKAGE "' || c_objects_dtls_rec.owner || '"."' || c_objects_dtls_rec.object_name || '" COMPILE BODY';
         ELSIF (c_objects_dtls_rec.object_type = 'TYPE')
         THEN
            l_alter_string                                           :=
                  'ALTER TYPE "'
               || c_objects_dtls_rec.owner
               || '"."'
               || c_objects_dtls_rec.object_name
               || '" COMPILE SPECIFICATION';
         ELSIF (c_objects_dtls_rec.object_type = 'TYPE BODY')
         THEN
            l_alter_string                                           :=
                   'ALTER TYPE "' || c_objects_dtls_rec.owner || '"."' || c_objects_dtls_rec.object_name || '" COMPILE BODY';
         ELSE
            l_alter_string                                           :=
                  'ALTER '
               || c_objects_dtls_rec.object_type
               || ' "'
               || c_objects_dtls_rec.owner
               || '"."'
               || c_objects_dtls_rec.object_name
               || '" COMPILE';
         END IF;

         IF (    l_include_java <> 1
             AND c_objects_dtls_rec.object_type LIKE '%JAVA%')
         THEN
            l_alter_string                                           := NULL;
         END IF;

         IF (l_alter_string IS NOT NULL)
         THEN
            l_object_count                                           := l_object_count + 1;
            l_message                                                :=
                  'CompileAllObjects pass '
               || TO_CHAR (l_pass, '99999')
               || ' processing object '
               || TO_CHAR (l_object_count, '99999')
               || ' ['
               || l_alter_string
               || ']';

            BEGIN
               DBMS_SQL.parse (c_cursor, l_alter_string, DBMS_SQL.native);
               l_ret_code                                               := DBMS_SQL.EXECUTE (c_cursor);
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_message                                                :=
                                                                  l_message || ' => ERROR # ' || SQLCODE || ' - ' || SQLERRM;
                  NULL;
            END;

            IF (l_debug_flag <> 0)
            THEN
               DBMS_PIPE.pack_message (LENGTH (l_message));
               DBMS_PIPE.pack_message (l_message);
               l_status                                                 :=
                                                      DBMS_PIPE.send_message ('plsql_debug', DBMS_PIPE.maxwait, 1024 * 1024);
            END IF;
         END IF;
      END LOOP;

      EXIT WHEN l_object_count = 0
            OR l_pass >= l_max_passes;
      l_pass                                                   := l_pass + 1;
   END LOOP;

   DBMS_SQL.close_cursor (c_cursor);

   IF l_debug_flag <> 0
   THEN
      l_message                                                :=
            '*** The Invalid Object Compilation is Completed'
         || TO_CHAR (l_object_count)
         || ' objects in '
         || l_pass
         || ' passes ***';
      DBMS_PIPE.pack_message (LENGTH (l_message));
      DBMS_PIPE.pack_message (l_message);
      l_status                                                 :=
                                                       DBMS_PIPE.send_message ('plsql_debug', DBMS_PIPE.maxwait, 1024 * 1024);
   END IF;
END;
/