My Oracle Experience Part 1

I want to share to you my exploration in ORACLE database:

  1. ROWTYPE variable
  2. ALIAS in ORACLE >> SELECT COLUMN_NAME “COLUMN ALIAS” FROM TABLE
  3. LOOP clause for CURSOR
  4. SUBSTR clause (LEFT : Positive Value, RIGHT : Negative Value)

Below my ORACLE script:

SET SERVEROUTPUT ON;
 
DECLARE
    V_FORMCODE VARCHAR(100);
    V_FORMTYPE VARCHAR(1);
    V_TABLENAME VARCHAR(100);
    V_SOURCEFLAGFIELD VARCHAR(100);
    V_SOURCEFLAGVALUE VARCHAR(100);
    V_FORMMANAGEMENT TBLM_FORMMANAGEMENT%ROWTYPE;
    V_CURSOR SYS_REFCURSOR;
    V_QUERY VARCHAR(400);
BEGIN
    V_FORMCODE := 'F0001';
 
    SELECT
        TABLESOURCE,
        SOURCEFLAGFIELD,
        SOURCEFLAGVALUE
    INTO
        V_TABLENAME,
        V_SOURCEFLAGFIELD,
        V_SOURCEFLAGVALUE
    FROM TBLM_FORM
    WHERE
        FORMCODE = V_FORMCODE;
 
    OPEN V_CURSOR FOR
    SELECT *
    FROM TBLM_FORMMANAGEMENT
    WHERE 
        FORMCODE = V_FORMCODE
        AND SOURCEFIELD IS NOT NULL
    ORDER BY SEQ;
 
    LOOP
        FETCH V_CURSOR INTO V_FORMMANAGEMENT;
        EXIT WHEN V_CURSOR%NOTFOUND;
        V_QUERY := V_QUERY || ',' || V_FORMMANAGEMENT.SOURCEFIELD || ' AS "' || V_FORMMANAGEMENT.REPORTITEM || '"';
    END LOOP;
    CLOSE V_CURSOR;
 
    V_QUERY := 'SELECT ' || SUBSTR(V_QUERY, -LENGTH(V_QUERY) + 1);
    V_QUERY := V_QUERY || ' FROM ' || V_TABLENAME;
    V_QUERY := V_QUERY || ' WHERE ' || V_SOURCEFLAGFIELD || ' = ''' || V_SOURCEFLAGVALUE || '''';
    DBMS_OUTPUT.PUT_LINE(V_QUERY);
 
    OPEN V_CURSOR FOR V_QUERY;
 
END;

CMIIW :).

Leave a Reply

Your email address will not be published. Required fields are marked *

Afiseaza emoticoanele Locco.Ro