It is simply that a tagged post which directly impacts the use and integration of openai api witing an existing code generator and process automailtion framework that is widely used at enterprise level has been flagged as not api.
This is an orchistration of agents … the scripts in these agents can be replaced by english language requests for the fucntiknaloty… and asked ro nw made generic… such that the metadata can be uaed to bulld enterpise levwl apicatolns…
This stucture by its nature writes out the execution acripy meaning iit can write out a trained genetic agent … or write out ‘as code’…
As openai api is used … the languages or scripts can be refeeshed from open ai
The application therefore of instruction in the script table.
Them execute and write agent
Means the develppment provess of creating and or ronning an application is both testable. Rules gased and secure…
It might be old… but its more than replacing lamgchain…
The given code example was wrotten by hub_api… and represents what is putput from the ai emabked hub api…
Its been rather a successful and unkque applicatikn … ans id suggest. …
It beats any implimenraton currently out there…
I cant seem to link to the prior post…
BI API
Towards conformed, controlled, condensed code
MBI API 1
Principles 1
Premise 1
Overview 2
Definitons MBI_PKG_API_DEFN_XXX 2
Execute code MBI_PKG_API_EXECUTE 3
Utilities 3
Utilities in packages 4
Utilities Extended 5
Visio for 2 processes 6
Principles
• Do it once
• Do it in one place
• Conform to standards
• Make it available
• Make it improvable
• Enable control
Premise
The Basic premise of the API is to enable people to make available code in a conformed and controlled manor, it is intended to :-
• Reduce duplication,
• Increase conformance to standards
• Increase our ability to improve and advance code in the future.
The code contained can be called :-
• by users on an adhoc basis
• by packages using defined parameters
• by packages working from cursors selecting from DBA tables
• by packages working from cursors selecting from metadata tables
• by packages working from cursors selecting from External tables.
They may consist of
• one statement
• groups of statements
• groups of statements, functions and procedures.
• groups of statements, functions and procedures gathering additional data.
Overview
The API consists of 3 basic layers
- Definitions
- Execution
- Utilities
Definitons MBI_PKG_API_DEFN_XXX
This is a group of packages that hold the DDL frameworks these can most easily be viewed as the instanciation of syntax of a statement.
For instance
Is converted to
‘CREATE <> INDEX <>.<<INDEX_NAME>> ON <>.<<TABLE_NAME>>(<>) NOLOGGING PARALLEL ( DEGREE Default INSTANCES Default ) TABLESPACE <<TABLE_SPACE>> COMPUTE STATISTICS’;
Scripts of this sort are easy to create after the first time it has been written simply by looking at any non syntax element and converting it to a parameter, in this case COMPUTE STATISTICS has been left un parameterised though at a later date this could also be parameterised, backwards compatability being ensured through the application of a default in the Utilities
MBI_PKG_API_CONTROL
The control API is simply a reader package, it can read a piece of text and replace named parameters with values received in a type.
It has 3 sections
MBI_FTN_API_CONTROL
This in the entrance procedure and is sent a required definition p_defn and the associated parameters and values. name_val_tab
It’s job is simply to call the next 2 elements in the appropriate order with the appropriate parameters.
MBI_FTN_API_READ
This package’s job is simply to get the required Script from the Defintions.
MBI_FTN_API_REPLACE
This package’s job is simply to replace the named parameters in the Script with the values passed in name_val_tab
For instance in the above example the script is altered to the following when provided with the following parameters
CREATE <> INDEX OWNER_MCT.MCT_MR03_1_IX ON OWNER_MCT.MCT_G_RESPONS_S03_INC(AWK_ID) NOLOGGING PARALLEL ( DEGREE Default INSTANCES Default ) TABLESPACE MCTX COMPUTE STATISTICS;
p_params (‘OWNER’) := ‘OWNER_MCT’;
p_params (‘TABLE_NAME’) := ‘MCT_G_RESPONS_S03_INC’;
p_params (‘ATTRIB’) := ‘AWK_ID’;
Type in this case has not been populated as it was not passed as a parameter.
Execute code MBI_PKG_API_EXECUTE
This package has 2 components
MBI_PRC_API_REQUEST
This package is the callable component of the package, it is able to call a definition and pass it the appropriate parameters and from that receive a completed script.
It will then pass that script to the second component
MBI_PRC_API_EXECUTE
This package is able to execute a given script, it is unable to be called except through request. This means that the executable scripts are restricted by the scripts made available via the DDL components to the calling user, it is also only able to execute scripts with the grants of the calling user.
It has 2 states, Debug and Execute identified by the
It has been coded to accept and execute multiple statements in the process order, however request does not currently posses this functionality, this is because I have not found a requirement for this as yet however if required a new request_multi can be appended.
Logging can also be applied at this stage when a specification is defined.
Utilities
Though the Defintions are individually callable they should be accompanied by a utility, this provides a layer for additional information to be gathered, multiple scripts to be called or other required functions to be enacted.
Essentially it is the subprogram relating the the function that you are trying to enact, for example within our standards, creating a primary key requires 2 functions,
the creation of a primary key index
the creation of the constraint
our standards also mean some information can be derived
The constraint name requires the alias of the table to be known
The Tablespace is defined by the Owner
so the util for this is as follows
– get the table alias
select alias into p_alias from
(SELECT REPLACE(REPLACE(comments,‘alias:’),’ ') alias FROM all_tab_comments where owner = to_char(p_params (‘OWNER’)) and table_name = to_char(p_params (‘TABLE_NAME’)));
– get the table space
p_params (‘TABLE_SPACE’) := replace(p_params (‘OWNER’),‘OWNER_’,‘’)||‘X’ ;
–create contraint name
p_params (‘CONSTRAINT_NAME’):= replace(p_params (‘OWNER’),‘OWNER_’)||‘_’||p_alias||‘_PK’;
p_params (‘TYPE’) := ‘UNIQUE’ ;
OWNER_MBI.MBI_PKG_API_EXECUTE.MBI_PRC_API_REQUEST ( ‘MBI_PKG_API_DEFN_DDL.MBI_FTN_API_DEFN_INDEX’,p_params,p_exec_debug) ;
OWNER_MBI.MBI_PKG_API_EXECUTE.MBI_PRC_API_REQUEST ( ‘MBI_PKG_API_DEFN_DDL.MBI_FTN_API_DEFN_PK_CONSTRAINT’,p_params,p_exec_debug) ;
Thus the only metadata required to tell the utility what to do is the table owner, the table name and the attributes that make up the primary key.
The final part to deal with is how to call these scripts
The above example is called as follows
declare
p_clob clob;
p_params OWNER_MBI.mbi_types.param_tt;
BEGIN
p_params (‘OWNER’) := ‘OWNER_MCT’;
p_params (‘TABLE_NAME’) := ‘MCT_G_RESPONS_S03_INC’;
p_params (‘CONTST_ATTRIB’) := ‘AWK_ID’;
OWNER_MBI.MBI_PKG_API_UTILS_DDL.MBI_PRC_API_CRT_PK ( p_params,‘D’) ;
end ;
Utilities in packages
This is useful in the short form in that it saves people looking up standards and ensures that creation follows standards, however it is more useful in conjunction with a cursor gathering the info required, for instance….
CURSOR cr_prim_key_ind_cons_dim
IS
SELECT
a.owner OWNER,
a.table_name TABLE_NAME ,
a.column_name ATTRIB
FROM all_tab_columns a
WHERE a.column_name like ‘%_ID’ AND a.table_name not like ‘%V’ AND a.table_name LIKE REPLACE(a.owner,'OWNER’)||‘_D%’ AND column_id = 1
and OWNER = p_owner;
This cursor gathers the information for dimension tables within the given schema (p_schema) – this is nice because the select can easily be viewed.
So in completeness the following API would create all PK’s required in the schema OWNER_DCT or if p_params is set to ‘D’ it will simply output the scipts.
declare
p_owner varchar2 (30) := ‘OWNER_DCT’;
p_params OWNER_MBI.mbi_types.param_tt;
CURSOR cr_prim_key_ind_cons_dim
IS
SELECT
a.owner OWNER,
a.table_name TABLE_NAME,
a.column_name ATTRIB
FROM all_tab_columns a
WHERE a.column_name like '%_ID' AND a.table_name not like '%_V' AND a.table_name LIKE REPLACE(a.owner,'OWNER_')||'_D%' AND column_id = 1
and OWNER = p_owner;
begin
for rec in cr_prim_key_ind_cons_dim
loop
p_params (‘OWNER’) := rec.OWNER;
p_params (‘TABLE_NAME’) := rec.TABLE_NAME;
p_params (‘ATTRIB’) := rec.ATTRIB;
OWNER_MBI.MBI_PKG_API_UTILS_DDL.MBI_PRC_API_CRT_PK ( p_params,‘E’) ;
end loop;
end ;
Utilities Extended
The final note is that utilities can be created calling one or more of these routines to carry out tasks that require groups of defined functions, examples of these might be deduplicating a table, merging data, exporting a view or in the following case automatically indexing and constraining a defined star schema correctly to enable star query.
The following call calls a package that will index all dimensions, parent dimensions, facts. It will then dynamically build the FK relationships between the fact and its closest parent dimensions.
Currently the output of the FK’s will be null and some of the index names will look a little odd, this is because they are reliant on their creation in an earlier step… but the case still holds.
begin
owner_mbi.MBI_PKG_API_UTILS_DDL.mbi_prc_api_crt_tech_indexes(‘OWNER_DCT’,‘D’);
end ;
Creating a new definition and utility.
It is essential that the packages can be extended without to much additional effort, this section of the document discusses how this can be done with the case study of an external table.
The start point
The start point is a script that you feel is repeatable and useful, I will use the case of a data export via datapump using an external table.
- This is useful for exporting data oracle to oracle
- It is repeatable as it can be made genereric for any table or view.
Generic Parts (ie when developing just copy) will be shown in bold.
Edited components in italics
Script
The starting script is as follows.
CREATE TABLE OWNER_PCT.MCT_G_RESPONS_S03_INC
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dct_file_system
LOCATION ( ‘MCT_G_RESPONS_S03_INC_20091208150936.dmp’ )
)
AS
SELECT AWK_ID,PRODUCT,AWK_STATUS,CREATIE_DATUM,STATUS_DATUM,ACTIECODE,RESPONSDATUM,ZP,INSTAPMAAND,POSTCODE,HUISNR,HUISNR_TVG,LOOPTIJD,TE_LAAT_ACTIECODE
FROM OWNER_MCT.MCT_G_RESPONS_S03_INC
Adding a Definition
If there is an existing DFN package then you can add the definition to that one, or you can request a new package. In this case I am using a set of definitions specifically related to external data. OWNER_MBI.MBI_PKG_API_DEFN_EXT
I need to add a function to hold the definition
So the specification has the following line added
FUNCTION MBI_FTN_API_DEFN_CR_EXT_DP_LD return clob;
If it is a new set I also create the body and then parameterize the sql I want to make generic and place in the function
FUNCTION MBI_FTN_API_DEFN_CR_EXT_DP
return clob
is
p_clob clob;
begin
p_clob := ‘CREATE TABLE <<LOAD_OWNER>>.<<TABLE_NAME>>
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY <<FILE_SYSTEM>>
LOCATION ( ‘’<<FILE_NAME>>’’ )
)
AS
SELECT <>
FROM <<SRC_OWNER>>.<<TABLE_NAME>>’
return p_clob;
end;
/
Adding a Utility
Basic
I now want to create a utilities package to accompany it
I add the following to the existing package
OWNER_MBI.MBI_PKG_API_UTILS_EXP
The following line is added to the specification
PROCEDURE MBI_PRC_API_CRT_EXT_TBL_DP (p_params in out mbi_types.param_tt, p_exec_debug varchar);
The most basic utility is as follows.
PROCEDURE MBI_PRC_API_CRT_EXT_TBL_DP (p_params in out mbi_types.param_tt, p_exec_debug varchar)
IS
OWNER_MBI.MBI_PKG_API_EXECUTE.MBI_PRC_API_REQUEST ( ‘MBI_PKG_API_DEFN_EXT.MBI_FTN_API_DEFN_CR_EXT_DP’,p_params,p_exec_debug) ;
end ;
Derived parameters
This assumes that all parameters are just passed in, however
In the body I know I must provided the parameters required of the definition
The list is as follows
<<PUT_OWNER>>
<<TABLE_NAME>>
<<FILE_SYSTEM>>
<<FILE_NAME>>
<>
<<SRC_OWNER>>
<<TABLE_NAME>>
Of this list some parameters can be derived from others that are passed
parameter <> can be derived from <<SRC_OWNER>>,<<TABLE_NAME>>
If it is assumed that in the case of a transformed set, that this transformation is enacted through a view (table_name is preserved as a parameter name as it remains named so in all_tab_columns).
select owner_mbi.MBI_STRING_AGG(column_name) a into p_params (‘ATTRIB’) from all_tab_columns where table_name = to_char(p_params (‘TABLE_NAME’)) and owner = to_char(p_params (‘SRC_OWNER’)) ;
Further <<FILE_NAME>> can be derived from <<TABLE_NAME>> and sysdate.
p_params (‘FILE_NAME’) := p_params (‘TABLE_NAME’) ||‘_’||to_char(sysdate,‘yyyymmddhh24miss’)||‘.dmp’;
so the body of the utility is as follows
PROCEDURE MBI_PRC_API_CRT_EXT_TBL_DP (p_params in out mbi_types.param_tt, p_exec_debug varchar)
IS
begin
p_params (‘FILE_NAME’) := p_params (‘TABLE_NAME’) ||‘_’||to_char(sysdate,‘yyyymmddhh24miss’)||‘.dmp’;
select owner_mbi.MBI_STRING_AGG(column_name) a into p_params (‘ATTRIB’) from all_tab_columns where table_name = to_char(p_params (‘TABLE_NAME’)) and owner = to_char(p_params (‘SRC_OWNER’)) ;
OWNER_MBI.MBI_PKG_API_EXECUTE.MBI_PRC_API_REQUEST ( ‘MBI_PKG_API_DEFN_EXT.MBI_FTN_API_DEFN_CR_EXT_DP’,p_params,p_exec_debug) ;
end;
Some error handling should also be added for the select into but this can be dealt with later.
The call to this new package is as follows (with test strings)
declare
p_clob clob;
p_params OWNER_MBI.mbi_types.param_tt;
BEGIN
p_params (‘SRC_OWNER’ ) := ‘OWNER_MCT’;
p_params (‘TABLE_NAME’ ) := ‘MCT_G_RESPONS_S03_INC’;
p_params (‘PUT_OWNER’ ) := ‘OWNER_PCT’;
p_params (‘FILE_SYSTEM’) := ‘dct_file_system’;
OWNER_MBI.MBI_PKG_API_UTILS_EXP.MBI_PRC_API_CRT_EXT_TBL_DP ( p_params,'D') ;
end ;
and the debug output is as follows
CREATE TABLE OWNER_PCT.MCT_G_RESPONS_S03_INC
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dct_file_system
LOCATION ( ‘MCT_G_RESPONS_S03_INC_20091208150936.dmp’ )
)
AS
SELECT AWK_ID,PRODUCT,AWK_STATUS,CREATIE_DATUM,STATUS_DATUM,ACTIECODE,RESPONSDATUM,ZP,INSTAPMAAND,POSTCODE,HUISNR,HUISNR_TVG,LOOPTIJD,TE_LAAT_ACTIECODE
FROM OWNER_MCT.MCT_G_RESPONS_S03_INC
Visio for 2 processes