IBM Informix Error "-556 Cannot create, drop or modify an object that is external to the current Database"

Question

I have create a procedure. In that automatically a "New Table" will be created on the "First Day Of the Year" And "New Table" will be created during the first time execution (Any day in the year).

This Procedure is executed fine in "Data Studio" (DB Client Software).

But, when executing through OAT (Open Admin Tool), I am getting an error saying "-556 Cannot create, drop or modify an object that is external to the current database".

Is there any way to resolve this? I want to schedule this procedure. We cannot execute manually every time.

Please find the procedure code below.

CREATE PROCEDURE neura_mams_dev:informix.inventory_data_historical_proc( )


/* Current Year Table Name and Sequence Name */

LET v_year = year(sysdate);
LET v_table_name = 'inventory_batch_historical_'||''||v_year||''||'_tbl';

-------------------------------------------------------------------------------------------------
/* Checking Today's Date and First Day Of Year */

SELECT 
today,
MDY(1, 1, YEAR(today))
INTO
v_today,v_first_day_of_year
FROM 
systables 
WHERE 
tabid = 1;


/* Creates New Table and Sequence If today is the first day of the year */

IF (v_today = v_first_day_of_year) THEN

LET v_create_table_stmt = 'CREATE TABLE neura_mams_dev:informix.'|| v_table_name || ' (id int8,material_document_id varchar(15),quantity int8,exp_date date,issue_quantity int8,location_id int8,store_id int8,generated_time datetime year to fraction)';

EXECUTE IMMEDIATE v_create_table_stmt;


END IF;

END PROCEDURE;

I have written a Procedure to create a table dynamically. It is working fine in Informix Data Studio, but the procedure is not executing through OAT (Open Admin Tool).

Answer 1

The error message is fairly clear:

-556 Cannot create, drop or modify an object that is external to the current database.

• Your procedure is called neura_mams_dev:informix.inventory_data_historical_proc.
• The table you are trying to create (for 2024) is called neura_mams_dev:informix.inventory_batch_historical_2024_tbl.

In both cases, the neura_mams_dev: prefix locates the object (procedure, table) in a database called neura_mams_dev.

This will work, but only when the current database is neura_mams_dev. If the current database is something else (e.g. sysmaster), the CREATE PROCEDURE statement will fail with the message you show. If you executed the CREATE PROCEDURE statement successfully because the current database was neura_mams_dev, but you then executed the procedure from another database:

EXECUTE PROCEDURE neura_mams_dev:inventory_data_historical_proc();

you'd also get the same error.

I infer from the error message that when this is executed via OAT, the current database is not neura_mams_dev.

I didn't even realize that you can use the database name prefix when creating a procedure. Even though it is allowed, I'd recommend against using the notation. Likewise, I'm not convinced that specifying the database in the table name is a good idea — I would not do it in a CREATE TABLE statement. The notation is sometimes necessary: for example, you need it when you do a cross-database join in a DML statement such as a SELECT statement.

---

Other notes:

LET v_table_name = 'inventory_batch_historical_'||''||v_year||''||'_tbl';

It is not clear what benefit there is to concatenating two empty strings in the table name.

LET v_table_name = 'inventory_batch_historical_' || v_year || '_tbl';

As noted in a comment, do NOT use INT8; use BIGINT instead. Similarly, do not use SERIAL8; use BIGSERIAL instead. For convoluted historical reasons, INT8 and SERIAL8 occupy 10 bytes on disk whereas BIGINT and BIGSERIAL were introduced to fix this problem; they use just 8 bytes.

You should be able to use the DB-Cron job scheduler to execute this procedure on 1st January each year.

The code provided does not define the variables it uses.

It is hard to test this code — you have to be working on 1st January and if you run out of time, you have to wait a year. You should probably rethink the design of the procedure to make it more readily testable. For example, the operational code could be in a new routine that is given the year number as an argument and would create the table. You might keep the existing procedure signature but arrange for it to call the new routine to create the table. (For testing, I jimmied the procedure by adding LET v_today = v_first_day_of_year; after the SELECT statement.)

Consider using CREATE OR REPLACE PROCEDURE or CREATE PROCEDURE IF NOT EXISTS (and you might use DROP PROCEDURE IF EXISTS for cleaning up). Likewise, consider using CREATE TABLE IF NOT EXISTS in the dynamic SQL.

Note that the SELECT statement is not really necessary; you can call the functions directly, as in this example stored procedure:

CREATE FUNCTION using_date() RETURNING DATE AS r_today, DATE AS r_jan1;

    DEFINE v_today DATE;
    DEFINE v_jan1 DATE;

    LET v_today = TODAY;
    LET v_jan1 = MDY(1, 1, YEAR(TODAY));

    RETURN v_today, v_jan1;

END FUNCTION;

When executed on 2024-01-12, it returned the values 2024-01-12 and 2024-01-01. I prefer TODAY to SYSDATE; it is the traditional Informix name.