调试oracle,调试oracle过程

我有一个oracle存储过程,输入是

púfeatureClassName='未来包裹'

púu DrawnGeometry=MDSYS.SDOúu GEOMETRY(2003,2400000,空,

MDSYS.SDO_ELEM_INFO_数组(11003,1,27,2003,1),

MDSYS.SDO_坐标阵列(8439212.4589933194220066070556640625,

4540074.11913800988462104797363281250、8439201.79989653080701828002968750、4540058.37438372985383186340332025、8439315.8149596098810434143066406250、4540058.374466760084033012390136718750、8439319.98681152984493133544921875、4540067.636267419904704437255859375、8439323.2313705994041919708259531250,4540081.5284504797096370637067970214843750、8439328.3286184798926115030607421875、4540097.73737366031855347723388671875、8439330.6458750404417514801025390625、4540129.69104134012013673823486328125、8439332.95936319045722484586230468750、4540149.4864721801133729859843750、8439227.9849018367234201503906250,0098462104797363281250,252.6796298995614051818847656250,4540086.66042790003120899004394531250)

程序:

CREATE OR REPLACE

PROCEDURE get_DrawnGeometries (

p_processId IN INTEGER

, p_featureClassName IN VARCHAR2

, p_DrawnGeometry IN SDO_GEOMETRY

, p_prm_val OUT VARCHAR2

)

IS

PRAGMA AUTONOMOUS_TRANSACTION;

CURSOR featClassAttributes_cur (featureClassName IN VARCHAR2) IS

SELECT

gac.id

, lower(gac.column_name) AS column_name

, gac.view_u_pk

, gac.data_type

, gas.naziv

, gat.table_name

FROM geo_atrib_columns gac

JOIN geo_atrib_tables gat ON gat.table_name = gac.table_name

JOIN geo_app_slojevi gas ON gas.naziv = gat.layer_name

WHERE gas.naziv = featureClassName

AND gat.aktivan = 1

AND gac.aktivan = 1

AND gac.view_iu_write = 1

ORDER BY gac.view_order;

vc_sql CLOB;

vc_sql2 CLOB;

vc_sql3 CLOB;

v_columns VARCHAR2(1000);

v_tableName VARCHAR2(1000);

v_whereClause VARCHAR2(1000);

v_whereClause2 VARCHAR2(1000);

v_g_col_name VARCHAR2(30);

v_tolerance VARCHAR2(20);

v_separator VARCHAR2(16) := ' || ''' || glob_var.v_strSeparator || ''' || ';

p_id INTEGER;

v_mssg VARCHAR2(500) := NULL;

BEGIN

vc_sql := 'SELECT #columns# FROM #table_name# WHERE #where_clause#';

vc_sql2 := 'SELECT #columns# FROM #table_name# WHERE #where_clause2#';

vc_sql3 := vc_sql;

FOR rt_att IN featClassAttributes_cur(p_featureClassName) LOOP

IF rt_att.data_type = 'N' THEN

v_columns := v_columns || v_separator || ' nvl(gt_util.num2CharPointSep(' || rt_att.column_name|| '), ''NULL'')';

ELSE

v_columns := v_columns || v_separator || ' nvl(to_char(' || rt_att.column_name|| '), ''NULL'')';

END IF;

IF v_tableName IS NULL THEN

v_tableName := rt_att.table_name;

END IF;

--dbms_output.put_line(v_tableName);

END LOOP;

v_columns := ltrim(v_columns, v_separator);

v_g_col_name := gt_util.get_layer_g_col(v_tableName);

v_tolerance := gt_util.num2CharPointSep(gt_util.get_layer_tollerance(v_tableName, v_g_col_name));

--v_whereClause := 'ins_id = {0} AND SDO_GEOM.RELATE(:geom, ''EQUAL+INSIDE+CONTAINS+COVEREDBY+COVERS+OVERLAPBDYINTERSECT'', ' || v_g_col_name || ', ' || v_tolerance || ') = ''EQUAL+INSIDE+CONTAINS+COVEREDBY+COVERS+OVERLAPBDYINTERSECT''';

v_whereClause := 'ins_id = {0} AND SDO_GEOM.RELATE(:geom, ''EQUAL'', ' || v_g_col_name || ', ' || v_tolerance || ') = ''EQUAL''';

v_whereClause2 := 'ins_id = {0} AND gt_util.geom_is_equal(:geom, ' || v_g_col_name || ', ' || v_tolerance || ') = ''TRUE''';

v_whereClause := replace(v_whereClause, '{0}', to_char(p_processId));

v_whereClause2 := replace(v_whereClause2, '{0}', to_char(p_processId));

vc_sql := replace(vc_sql, '#columns#', v_columns);

vc_sql := replace(vc_sql, '#table_name#', v_tableName);

vc_sql := replace(vc_sql, '#where_clause#', v_whereClause);

vc_sql2 := replace(vc_sql2, '#columns#', v_columns);

vc_sql2 := replace(vc_sql2, '#table_name#', v_tableName);

vc_sql2 := replace(vc_sql2, '#where_clause2#', v_whereClause2);

dbms_output.put_line(vc_sql);

dbms_output.put_line(vc_sql2);

BEGIN

EXECUTE IMMEDIATE vc_sql INTO p_prm_val USING p_DrawnGeometry;

EXCEPTION WHEN NO_DATA_FOUND THEN

EXECUTE IMMEDIATE vc_sql2 INTO p_prm_val USING p_DrawnGeometry;

WHEN TOO_MANY_ROWS THEN

vc_sql3 := replace(vc_sql3, '#columns#', 'id');

vc_sql3 := replace(vc_sql3, '#table_name#', v_tableName);

vc_sql3 := replace(vc_sql3, '#where_clause#', v_whereClause);

vc_sql3 := 'SELECT * FROM (' || vc_sql3 || ' ORDER BY id DESC) WHERE ROWNUM = 1';

EXECUTE IMMEDIATE vc_sql3 INTO p_id USING p_DrawnGeometry;

EXECUTE IMMEDIATE 'DELETE FROM ' || v_tableName || ' WHERE id = ' || p_id;

COMMIT;

v_mssg := 'Identical geometry already exists. Drawn geometry is deleted. If you want to delete new geometry please delete the old one first';

RAISE error_util.ep_UsrExc;

--dbms_output.put_line(vc_sql2);

END;

EXCEPTION WHEN error_util.ep_UsrExc THEN

ERROR_UTIL.log_and_raise(SQLCODE, SQLERRM, v_mssg, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

RAISE;

END get_DrawnGeometries;

如何检查结果并调试它的输出?

当我可以调用它时返回了错误

Procedure execution failed ORA-06550: line 1, column 233: PLS-00382:

expression is of wrong type ORA-06550: line 1, column 213: PL/SQL:

Statement ignored