ERROR: syntax error at or near "%" when using %type

[mourad_radi]

Hello,

I have used ora2pg tool to convert packages from oracle to postgresql.

when I run the below code, it gives an error:

CREATE TYPE bck_geco_pb_estr_saldo.rec_anamer AS (
cod_mercato s_mercati.cod_mercato%type,
des_mercato s_mercati.des_mercato%type

);

ERROR: syntax error at or near "%"
LINE 2: cod_mercato s_mercati.cod_mercato%type,

I'm using a GCP Paas postgresql  instance with the version bellow:

-----------------------------------------------------------------------------------------
PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit

it's a newest version, and I don't understand why it does not recognize "%TYPE". may be the problem is elsewhere.

Thanks in advance for your help.

Mourad.

[ms4446 · Accepted solution]

The %TYPE syntax is a convenient Oracle PL/SQL feature that allows variables to dynamically inherit the data type and attributes (like precision and scale) of a table column. PostgreSQL lacks a direct equivalent, leading to potential errors during migrations. Here is how to overcome this challenge in your Oracle to PostgreSQL migration using ORA2PG.

Troubleshooting Steps:

1. Examine ORA2PG Configuration: Thoroughly review the ORA2PG configuration files or settings. While the tool may not have a dedicated %TYPE translator, there might be options influencing how data types are mapped between Oracle and PostgreSQL.

2. Consult ORA2PG Documentation: Delve into the ORA2PG documentation, paying close attention to sections on data type conversion and the handling of Oracle's %TYPE attribute. You may find specific recommendations or best practices.

3. Manual Adjustments: Be prepared for manual refactoring of the generated PostgreSQL code. This involves:

   • Identify Oracle Data Types: Determine the precise data types of cod_mercato and des_mercato columns in the Oracle s_mercati table.
   • Adjust PostgreSQL CREATE TYPE: Modify the PostgreSQL CREATE TYPE statement, replacing %TYPE with the explicit data types discovered above. Ensure you carefully consider precision, scale, and length for optimal compatibility.

Additional Considerations:

• ORA2PG Customization: If feasible and you anticipate many similar migrations, consider extending ORA2PG with a custom pre-processor or plugin to automate %TYPE conversion.

• Complex Data Types: Be mindful that Oracle supports complex data types (e.g., objects, nested tables) that might not have straightforward PostgreSQL counterparts. These scenarios could require more involved refactoring or alternative representations.

• Schema Synchronization: Double-check that the s_mercati table exists in your PostgreSQL database and that its column definitions align with the Oracle source. Schema mismatches can cause further complications.

• Version Compatibility: Ensure the ORA2PG version you're using is fully compatible with both your Oracle database version and the target PostgreSQL 15.4 version.

• Thorough Testing: Implement a rigorous testing plan including:

  • Unit Testing: Verify code correctness and translation accuracy.
  • Integration Testing: Confirm proper interaction between migrated components.
  • Performance Testing: Identify performance bottlenecks and potential optimizations in the PostgreSQL environment.

Explanation of Changes:

• Introductory Paragraph: Added a clear definition of %TYPE, emphasizing its dynamic nature along with the lack of a direct PostgreSQL equivalent.
• Emphasized Potential Complexity: Included notes on complex data types and schema synchronization as issues to be aware of.
• Streamlined Language: Minor edits to improve sentence flow and readability.

[JadielMiles]

The problem you encountered is due to the fact that PostgreSQL does not support the %TYPE construct, which is common in Oracle PL/SQL. In PostgreSQL, you must explicitly specify the data type for each column.

To fix the error, you need to replace %TYPE with the appropriate data type in PostgreSQL. For example, if s_mercati.cod_mercato has a varchar data type, you can specify it explicitly in the type definition as shown below:

sql
Copy code
CREATE TYPE bck_geco_pb_estr_saldo.rec_anamer AS (
cod_mercato varchar, -- here you must specify the varchar data type or the corresponding
des_mercato varchar -- similar for des_mercato
);
Keep in mind that PostgreSQL data types may differ from Oracle, so you may need to adapt the data types to suit your PostgreSQL database.

Hope this helps you to solve the problem.

[mourad_radi]

Hello, Thank you for your response.

but in postgresql's documentation it says that it is supported :

(URL removed by Staff)

I don't understand 😞

any way do you know please what is the equivalent of %ROWTYPE in postgresql?