Oracle 12c DM_QGEN Demos

Oracle DM_QGEN Version 12.1.0.2

General Information

Purpose

Undocumented support for Data Mining

AUTHID

CURRENT_USER

Data Types

TYPE lstmt_rec_type IS RECORD (
     lstmt dbms_sql.varchar2A,
     lb    BINARY_INTEGER DEFAULT 1,
     ub    BINARY_INTEGER DEFAULT 0);

TYPE lstmt_rec_type_array IS TABLE OF lstmt_rec_type;

Dependencies

DBMS_ASSERT	DBMS_PREDICTIVE_ANALYTICS	DM_XFORM
DBMS_DATA_MINING	DBMS_SQL	ODM_ASSOCIATION_RULE_MODEL
DBMS_DATA_MINING_INTERNAL	DMP_SEC	ODM_MODEL_UTIL
DBMS_DATA_MINING_TRANSFORM	DMP_SYS	ODM_UTIL

Documented

First Available

Not known

Security Model

Owned by SYS with no privileges granted

Source

{ORACLE_HOME}/rdbms/admin/prvtdmmi.plb

Subprograms

CI_GEN	CLOB_LITERAL	LS_PREPEND
CI_GEN_SCHEMA	LS_APPEND	TO_LITERAL

CI_GEN

Undocumented

dm_qgen.ci_gen(p_name IN VARCHAR2) RETURN VARCHAR2;

SELECT dm_qgen.ci_gen('ALPHA')
FROM dual;

CI_GEN_SCHEMA

Undocumented

dm_qgen.ci_gen_schema(p_schema IN VARCHAR2) RETURN VARCHAR2;

SELECT dm_qgen.ci_gen_schema('ALPHA')
FROM dual;

CLOB_LITERAL

Enapsulates a string in single quotes and prepends the literal "to_clob(null)||"

dm_qgen.clob_literal(p_val IN VARCHAR2) RETURN VARCHAR2;

DECLARE
pVC VARCHAR2(4000) := 'this is a test string';
rVC VARCHAR2(4000);
BEGIN
rVC := dm_qgen.clob_literal(pVC);
dbms_output.put_line(rVC);
END;
/
to_clob(null)||'this is a test string'

PL/SQL procedure successfully completed.

LS_APPEND

Appends an element to the target values list array

dm_qgen.ls_append(
r_lstmt IN OUT NOCOPY dm_qgen.lstmt_rec_type,
p_txt IN VARCHAR2);

DECLARE
TYPE target_values_list IS TABLE OF VARCHAR2(32);
attr_names target_values_list := target_values_list();
v_tmp_lstmt dm_qgen.lstmt_rec_type;
vQSrc       VARCHAR2(30) := 'UW_TABLE';
BEGIN
v_tmp_lstmt.ub := 0; -- initialize
dm_qgen.ls_append(v_tmp_lstmt, 'CREATE TABLE ');
dm_qgen.ls_append(v_tmp_lstmt, vQSrc);
dm_qgen.ls_append(v_tmp_lstmt, ' AS ');
dm_qgen.ls_append(v_tmp_lstmt, '( SELECT ');

attr_names.extend(3);
attr_names(1) := 'owner';
attr_names(2) := 'table_name';
attr_names(3) := 'tablespace_name';

FOR i IN attr_names.FIRST..attr_names.LAST LOOP
    IF (i != attr_names.FIRST ) THEN
      dm_qgen.ls_append(v_tmp_lstmt,',');
   END IF;
    dm_qgen.ls_append(v_tmp_lstmt, attr_names(i));
END LOOP;
dm_qgen.ls_append(v_tmp_lstmt, ' FROM dba_tables ');
dm_qgen.ls_prepend(v_tmp_lstmt, 'execute immediate ');

dbms_output.put_line('1' || v_tmp_lstmt.lstmt(1));
dbms_output.put_line('2' || v_tmp_lstmt.lstmt(2));
dbms_output.put_line('3' || v_tmp_lstmt.lstmt(3));
dbms_output.put_line('4' || v_tmp_lstmt.lstmt(4));
dbms_output.put_line('5' || v_tmp_lstmt.lstmt(5));
dbms_output.put_line('6' || v_tmp_lstmt.lstmt(6));
dbms_output.put_line('7' || v_tmp_lstmt.lstmt(7));
dbms_output.put_line('8' || v_tmp_lstmt.lstmt(8));
dbms_output.put_line('9' || v_tmp_lstmt.lstmt(9));
dbms_output.put_line('10' || v_tmp_lstmt.lstmt(10));
dbms_output.put_line('11' || v_tmp_lstmt.lstmt(0));
END;
/

LS_PREPEND

Undocumented

dm_qgen.ls_prepend(
r_lstmt IN OUT dm_qgen.lstmt_rec_type,
p_txt IN VARCHAR2);

See LS_APPEND demo above

TO_LITERAL

Undocumented

Overload 1

dm_qgen.to_literal(
p_val IN NUMBER,
p_paren_flag IN BOOLEAN)
RETURN VARCHAR2;

DECLARE
bf BOOLEAN := FALSE;
bt BOOLEAN := TRUE;
retVal VARCHAR2(30);
BEGIN
retVal := dm_qgen.to_literal(-1, bf);
dbms_output.put_line(retVal);

retVal := dm_qgen.to_literal(-1, bt);
dbms_output.put_line(retVal);
END;
/

Overload 2

dm_qgen.to_literal(
p_val IN BINARY_DOUBLE,
p_paren_flag IN BOOLEAN)
RETURN VARCHAR2;

Overload 3

dm_qgen.to_literal(
p_val IN BINARY_FLOAT,
p_paren_flag IN BOOLEAN)
RETURN VARCHAR2;

Overload 4

dm_qgen.to_literal(p_val IN VARCHAR2) RETURN VARCHAR2;

DECLARE
retVal VARCHAR2(30);
BEGIN
retVal := dm_qgen.to_literal('This is essentially a NOOP');
dbms_output.put_line(retVal);
END;
/