📄 plgcgml.spb
字号:
' IS BEGIN
FOR rec IN (SELECT * FROM ' ||
v_tab ||
' WHERE ' ||
NVL (whr, '1=1') ||
')
LOOP ' ||
v_array ||
'(NVL (' ||
v_array ||
'.LAST, 0)+1) := rec;
END LOOP;
END;';
/* Create the package body. */
DBMS_SQL.parse (dyncur1, stmt, DBMS_SQL.native);
/* Grant access to the package. */
/* 99.2.8 Always grant to PUBLIC */
--IF v_pkgsch != PLGadmin.genxowner
--THEN
stmt := 'GRANT EXECUTE ON ' ||
/* 99.2.8 Leave it to default v_pkgsch || '.' || */
v_pkg || ' TO PUBLIC';
DBMS_SQL.parse (dyncur1, stmt, DBMS_SQL.native);
--END IF;
DBMS_SQL.close_cursor (dyncur1);
/* Now define as an array to PLG_DOIR. */
defarray (v_pkg,
v_array,
NULL, /* 99.2.8 Leave it to default v_pkgsch*/
drvr
);
EXCEPTION
WHEN OTHERS
THEN
/* Display error and close cursors. */
DBMS_SQL.close_cursor (dyncur1);
PLGerr.raise ('Error ' || SQLCODE ||
' defining table array in package ' ||
/* 99.2.8 Leave it to default v_pkgsch || '.' || */
v_pkg ||
' for table/view ' ||
v_tab ||
' with statement ' ||
stmt
);
END;
PROCEDURE define_view_array (
viewname IN VARCHAR2,
viewstring IN VARCHAR2,
tabsch IN VARCHAR2 := NULL,
pkgsch IN VARCHAR2 := NULL,
drvr IN VARCHAR2 := NULL
)
IS
dyncur1 PLS_INTEGER := DBMS_SQL.open_cursor;
fdbk PLS_INTEGER;
stmt PLGadmin.maxvc2;
BEGIN
stmt :=
RTRIM ('CREATE OR REPLACE VIEW ' ||
PLGadmin.ifelse (tabsch IS NULL,
NULL,
tabsch || '.'
) ||
viewname ||
' AS ' ||
viewstring,
';'
);
/* Create the view. */
DBMS_SQL.parse (dyncur1, stmt, DBMS_SQL.native);
define_table_array (viewname,
NULL,
tabsch,
pkgsch,
drvr
);
DBMS_SQL.close_cursor (dyncur1);
EXCEPTION
WHEN OTHERS
THEN
/* Display error and close cursors. */
DBMS_SQL.close_cursor (dyncur1);
PLGerr.raise ('Error ' || SQLCODE ||
' defining view array package for query ' ||
stmt
);
END;
PROCEDURE file2strings (
dir IN VARCHAR2,
file IN VARCHAR2,
string1_inout IN OUT VARCHAR2,
string2_inout IN OUT VARCHAR2,
string3_inout IN OUT VARCHAR2,
string4_inout IN OUT VARCHAR2,
string5_inout IN OUT VARCHAR2
)
IS
fid UTL_FILE.file_type;
line VARCHAR2 (32767);
stringx VARCHAR2 (32767);
onstring PLS_INTEGER := 1;
BEGIN
PLGerr.assert (dir IS NOT NULL,
'File2Strings: you must provide a non-null directory.'
);
fid := UTL_FILE.fopen (dir, file, 'R');
LOOP
BEGIN
UTL_FILE.get_line (fid, line);
IF stringx IS NULL
THEN
stringx := line;
ELSE
stringx := stringx || CHR (10) || line;
END IF;
EXCEPTION
WHEN VALUE_ERROR
THEN
/* Move to next string. */
IF onstring = 1
THEN
string1_inout := stringx;
onstring := 2;
ELSIF onstring = 2
THEN
string2_inout := stringx;
onstring := 3;
ELSIF onstring = 3
THEN
string3_inout := stringx;
onstring := 4;
ELSIF onstring = 4
THEN
string4_inout := stringx;
onstring := 5;
ELSIF onstring = 5
THEN
string5_inout := stringx;
onstring := NULL;
ELSE
DBMS_OUTPUT.put_line ('Generated text is too long! ' ||
'Partial text has been returned.'
);
EXIT;
END IF;
stringx := line;
END;
END LOOP;
UTL_FILE.fclose (fid);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
IF onstring = 1
THEN
string1_inout := stringx;
ELSIF onstring = 2
THEN
string2_inout := stringx;
ELSIF onstring = 3
THEN
string3_inout := stringx;
ELSIF onstring = 4
THEN
string4_inout := stringx;
ELSIF onstring = 5
THEN
string5_inout := stringx;
END IF;
UTL_FILE.fclose (fid);
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error opening file ' || dir ||
' ' ||
file
);
UTL_FILE.fclose (fid);
END;
PROCEDURE initialize
IS
BEGIN
clear (PLGdoir.c_global);
/* Set flag to make sure all inserts are "foundations" for the driver. */
PLGdoir.drvfoundation;
/* Driver-wide settings. */
PLGdoir.setsrc (PLGdoir.c_global,
PLGdoir.c_objname,
PLGdoir.c_driver_wide,
doc => 'Name of object serving as source for generation'
);
PLGdoir.setsrc (PLGdoir.c_global,
PLGdoir.c_jobjname,
PLGdoir.c_driver_wide,
doc => 'Name of object (Java format) serving as source for generation'
);
PLGdoir.setsrc (PLGdoir.c_global,
PLGdoir.c_objabbrev,
PLGdoir.c_driver_wide,
doc => 'Abbreviation of object serving as source for generation'
);
PLGdoir.setsrc (PLGdoir.c_global,
PLGdoir.c_dbobjname,
PLGdoir.c_driver_wide,
doc => 'Actual name of object serving as source for generation'
);
PLGdoir.setsrc (PLGdoir.c_global,
PLGdoir.c_target,
PLGdoir.c_driver_wide,
doc => 'Name of target/memory area to receive text'
);
PLGdoir.setsrc (PLGdoir.c_global,
PLGdoir.c_schema,
PLGdoir.c_driver_wide,
doc => 'Schema that owns the object or synonym'
);
PLGdoir.setsrc (PLGdoir.c_global,
PLGdoir.c_dbschema,
PLGdoir.c_driver_wide,
doc => 'Actual schema that owns the object'
);
PLGdoir.setsrc (PLGdoir.c_global,
PLGdoir.c_target_schema,
PLGdoir.c_driver_wide,
doc => 'Schema that will own the generated object'
);
PLGdoir.setsrc (PLGdoir.c_global,
'arrayname',
PLGdoir.c_driver_wide,
doc => 'Name of array'
);
PLGdoir.setdynvarchar2 (PLGdoir.c_global,
'GENXOWNER',
'''' || USER || '''',
doc => 'Owner of GenX code'
);
PLGdoir.setdynvarchar2 (PLGdoir.c_global,
'RIGHTNOW',
'TO_CHAR (SYSDATE, ''Month DD, YYYY HH24:MI:SS'')',
doc => 'Date and time'
);
PLGdoir.setdynvarchar2 (PLGdoir.c_global,
'PLGVERSION',
'PLGadmin.version',
doc => 'Version of PL/Generator'
);
PLGdoir.setdynvarchar2 (PLGdoir.c_global,
'GENXVERSION',
'PLGadmin.version',
doc => 'Version of GenX'
);
PLGdoir.setdynvarchar2 (PLGdoir.c_global,
'ORAVERSION',
'PLGadmin.oraversion',
doc => 'Version of Oracle'
);
PLGdoir.setdynvarchar2 (PLGdoir.c_global,
'DEFDATEMASK',
'''DD-MON-YYYY''',
doc => 'Default date mask in use'
);
PLGdoir.setdynvarchar2 (PLGdoir.c_global,
'YEARNO',
'TO_CHAR (SYSDATE, ''YYYY'')',
doc => 'The four digit year number'
);
PLGdoir.setdynvarchar2 (PLGdoir.c_global,
'THISUSER',
'USER',
doc => 'Name of currently-connected user'
);
PLGdoir.setdynvarchar2 (PLGdoir.c_global,
'COMPANY',
doc => 'Name of company'
);
/* Loop state variables */
PLGdoir.setsrc (PLGdoir.c_global,
'CURRROW',
PLGdoir.c_iteration_specific,
doc => 'INTERNAL USE ONLY'
);
PLGdoir.setsrc (PLGdoir.c_global,
'NTHROW',
PLGdoir.c_iteration_specific,
doc => 'The Nth row being processed in the current or specified array.'
);
PLGdoir.setsrc (PLGdoir.c_global,
'STATETYPE',
PLGdoir.c_iteration_specific,
doc => 'INTERNAL USE ONLY'
);
PLGdoir.setsrc (PLGdoir.c_global,
'STATECOMMAND',
PLGdoir.c_iteration_specific,
doc => 'INTERNAL USE ONLY'
);
PLGdoir.setsrc (PLGdoir.c_global,
'STATEVAR',
PLGdoir.c_iteration_specific,
doc => 'INTERNAL USE ONLY'
);
PLGdoir.setsrc (PLGdoir.c_global,
'STATELABEL',
PLGdoir.c_iteration_specific,
doc => 'INTERNAL USE ONLY'
);
PLGdoir.setsrc (PLGdoir.c_global,
'STATENAME',
PLGdoir.c_iteration_specific,
doc => 'INTERNAL USE ONLY'
);
PLGdoir.setsrc (PLGdoir.c_global,
'BETWEENTEXT',
PLGdoir.c_iteration_specific,
doc => 'INTERNAL USE ONLY'
);
/* Meta object list */
PLGdoir.setdynvarchar2 (PLGdoir.c_global,
'OBJECTNAME',
'PLGgen.dbobject([\dbobject.currrow]).name',
doc => 'Name of object in DB objects list'
);
PLGdoir.setdynvarchar2 (PLGdoir.c_global,
'OBJECTTYPE',
'PLGgen.dbobject([\dbobject.currrow]).type',
doc => 'Type of object in DB objects list'
);
PLGdoir.setdynvarchar2 (PLGdoir.c_global,
'OWNER',
'PLGgen.dbobject([\dbobject.currrow]).owner',
doc => 'Owner of object in DB objects list'
);
/* Define globally available arrays */
PLGdoir.set_array (PLGdoir.c_global,
'day',
c_package,
'Days of the week'
);
PLGdoir.set_array_tag (PLGdoir.c_global,
'day',
'day',
1,
'Name of day'
);
PLGdoir.set_array_tag (PLGdoir.c_global,
'day',
'abbrev',
1,
'Abbreviation of name of day'
);
PLGdoir.set_array_tag (PLGdoir.c_global,
'day',
'number',
1,
'Number of day in the week'
);
PLGdoir.set_array (PLGdoir.c_global,
'month',
c_package,
'Months of the year'
);
PLGdoir.set_array_tag (PLGdoir.c_global,
'month',
'month',
1,
'Name of month'
);
PLGdoir.set_array_tag (PLGdoir.c_global,
'month',
'abbrev',
1,
'Abbreviation of name of month'
);
PLGdoir.set_array_tag (PLGdoir.c_global,
'month',
'number',
1,
'Number of month in the year'
);
PLGdoir.nodrvfoundation;
END;
PROCEDURE array_tag_initialize (drv IN VARCHAR2)
IS
v_drv PLG_doir.driver%TYPE := UPPER (drv);
v_row PLS_INTEGER;
CURSOR utag_cur (drv VARCHAR2)
IS
SELECT DISTINCT attrname tagname
FROM PLG_doir
WHERE driver = drv
AND owner = '*ALL'
AND objtype = 'ARRAYTAG'
AND objname NOT LIKE 'GA#%';
CURSOR array_cur (drv VARCHAR2, tag VARCHAR2)
IS
SELECT objname array_name, doc description
FROM PLG_doir
WHERE driver = drv
AND owner = '*ALL'
AND objtype = 'ARRAYTAG'
AND objname NOT LIKE 'GA#%'
AND attrname = tag;
BEGIN
array_tag.delete;
/* For each distinct tag name, build the CHR(10) delimited
string of descriptions for each usage */
FOR trec IN utag_cur (v_drv)
LOOP
v_row := NVL (array_tag.LAST, 0) + 1;
array_tag (v_row).tagname := trec.tagname;
FOR arec IN array_cur (v_drv, trec.tagname)
LOOP
IF array_cur%rowcount = 1
THEN
array_tag (v_row).first_array :=
arec.array_name;
END IF;
array_tag (v_row).array_list :=
array_tag (v_row).array_list || ',' ||
arec.array_name;
array_tag (v_row).desc_list :=
array_tag (v_row).desc_list || CHR (10) ||
'In ' ||
arec.array_name ||
': ' ||
arec.description;
END LOOP;
array_tag (v_row).array_list :=
LTRIM (array_tag (v_row).array_list, ',');
array_tag (v_row).desc_list :=
LTRIM (array_tag (v_row).desc_list, CHR (10));
END LOOP;
END;
/* This should be run in the initialization section so that
each user gets the data. */
PROCEDURE array_initialization
IS
v_num PLS_INTEGER;
v_date DATE;
BEGIN
/* Populate day array */
FOR indx IN 1 .. 7
LOOP
v_date := SYSDATE + indx - 1;
v_num := TO_NUMBER (TO_CHAR (v_date, 'D'));
day (v_num).day := TO_CHAR (v_date, 'DAY');
day (v_num).INITCAP := TO_CHAR (v_date, 'Day');
day (v_num).abbrev := TO_CHAR (v_date, 'DY');
day (v_num).number := v_num;
END LOOP;
/* Populate month array */
FOR indx IN 1 .. 12
LOOP
v_date := ADD_MONTHS (SYSDATE, indx - 1);
v_num := TO_NUMBER (TO_CHAR (v_date, 'MM'));
month (v_num).month := TO_CHAR (v_date, 'MONTH');
month (v_num).INITCAP := TO_CHAR (v_date, 'Month');
month (v_num).abbrev := TO_CHAR (v_date, 'MON');
month (v_num).number := v_num;
END LOOP;
END;
BEGIN
array_initialization;
END PLGcgml;
/
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -