1
Answer

Help me to run oracle package (uniface) from .net ?

Baagii

Baagii

13y
2k
1
Hello, I have oracle packages like following. Could you please help me to execute this kind of packages from c#?

#################################################
CREATE OR REPLACE PACKAGE BODY myschema."my_table$U" IS

PROCEDURE "PACKAGE_VERSION"(

"MAJOR_VERSION" OUT
NUMBER,

"MINOR_VERSION" OUT
NUMBER )

IS
BEGIN

"PACKAGE_VERSION"."MAJOR_VERSION" := 2;
"PACKAGE_VERSION"."MINOR_VERSION" := 3;

END "PACKAGE_VERSION";

PROCEDURE "UNIFACE_IO"(

"UNIFACE_IO_REQUEST" IN
NUMBER,

"XFIELD1" IN OUT
"my_table"."FIELD1"%TYPE,

"XFIELD2" IN OUT
"my_table"."FIELD2"%TYPE,

"XFIELD3" IN OUT
"my_table"."FIELD3"%TYPE,

"XU_VERSION" IN OUT
"my_table"."U_VERSION"%TYPE,

"XFIELD4" IN OUT
"my_table"."FIELD4"%TYPE,

"XFIELD5" IN OUT
"my_table"."FIELD5"%TYPE,

"XFIELD6" IN OUT
"my_table"."FIELD6"%TYPE,

"XFIELD7" IN OUT
"my_table"."FIELD7"%TYPE,

"XFIELD8" IN OUT
"my_table"."FIELD8"%TYPE,

"XFIELD9" IN OUT
"my_table"."FIELD9"%TYPE,

"XFIELD10" IN OUT
"my_table"."FIELD10"%TYPE,

"XFIELD11" IN OUT
"my_table"."FIELD11"%TYPE,

"XROWID" IN OUT
ROWID,

"WFIELD1" IN
"my_table"."FIELD1"%TYPE,

"WFIELD2" IN
"my_table"."FIELD2"%TYPE,

"WFIELD3" IN
"my_table"."FIELD3"%TYPE,

"WU_VERSION" IN
"my_table"."U_VERSION"%TYPE,

"ONE_ROW_AFFECTED" OUT
NUMBER )

IS

"DUMMY_FIELD"
NUMBER;

"NUM_FALSE" CONSTANT NUMBER := 0;
"NUM_TRUE" CONSTANT NUMBER := 1;

"SELECT_ROW_BY_FIELD1" CONSTANT NUMBER := 0;
"SELECT_ROWIDROW_BY_FIELD1" CONSTANT NUMBER := 1;
"SELECT_ROW_BY_ROWID" CONSTANT NUMBER := 2;
"CHECK_EXISTENCE_OF_FIELD1" CONSTANT NUMBER := 3;
"LOCK_ROW_BY_FIELD1" CONSTANT NUMBER := 4;
"LOCK_ROW_BY_ROWID" CONSTANT NUMBER := 5;
"DELETE_ROW_BY_ROWID" CONSTANT NUMBER := 6;
"DELETE_ROW_BY_FIELD1" CONSTANT NUMBER := 7;
"DELETE_ROW_BY_PK_AND_UVERSION" CONSTANT NUMBER := 8;
"UPDATE_ROW_BY_FIELD1" CONSTANT NUMBER := 9;
"UPDATE_ROW_BY_PK_AND_UVERSION" CONSTANT NUMBER := 10;
"UPDATE_ROW_BY_ROWID" CONSTANT NUMBER := 11;
"UPDATE_ROW_BY_ROWID_UVERSION" CONSTANT NUMBER := 12;

BEGIN

"ONE_ROW_AFFECTED" := "NUM_TRUE";

IF "UNIFACE_IO"."UNIFACE_IO_REQUEST" =
"SELECT_ROW_BY_FIELD1" THEN

SELECT /*+ FIRST_ROWS */
"FIELD1",
"FIELD2",
"FIELD3",
"U_VERSION",
"FIELD4",
"FIELD5",
"FIELD6",
"FIELD7",
"FIELD8",
"FIELD9",
"FIELD10",
"FIELD11"
INTO
"UNIFACE_IO"."XFIELD1",
"UNIFACE_IO"."XFIELD2",
"UNIFACE_IO"."XFIELD3",
"UNIFACE_IO"."XU_VERSION",
"UNIFACE_IO"."XFIELD4",
"UNIFACE_IO"."XFIELD5",
"UNIFACE_IO"."XFIELD6",
"UNIFACE_IO"."XFIELD7",
"UNIFACE_IO"."XFIELD8",
"UNIFACE_IO"."XFIELD9",
"UNIFACE_IO"."XFIELD10",
"UNIFACE_IO"."XFIELD11"
FROM "my_table"
WHERE
"FIELD1" = "UNIFACE_IO"."WFIELD1" AND
"FIELD2" = "UNIFACE_IO"."WFIELD2" AND
"FIELD3" = "UNIFACE_IO"."WFIELD3";

ELSIF "UNIFACE_IO"."UNIFACE_IO_REQUEST" =
"SELECT_ROWIDROW_BY_FIELD1" THEN

SELECT /*+ FIRST_ROWS */
"FIELD1",
"FIELD2",
"FIELD3",
"U_VERSION",
"FIELD4",
"FIELD5",
"FIELD6",
"FIELD7",
"FIELD8",
"FIELD9",
"FIELD10",
"FIELD11",
ROWIDTOCHAR(ROWID)
INTO
"UNIFACE_IO"."XFIELD1",
"UNIFACE_IO"."XFIELD2",
"UNIFACE_IO"."XFIELD3",
"UNIFACE_IO"."XU_VERSION",
"UNIFACE_IO"."XFIELD4",
"UNIFACE_IO"."XFIELD5",
"UNIFACE_IO"."XFIELD6",
"UNIFACE_IO"."XFIELD7",
"UNIFACE_IO"."XFIELD8",
"UNIFACE_IO"."XFIELD9",
"UNIFACE_IO"."XFIELD10",
"UNIFACE_IO"."XFIELD11",
"UNIFACE_IO"."XROWID"
FROM "my_table"
WHERE
"FIELD1" = "UNIFACE_IO"."WFIELD1" AND
"FIELD2" = "UNIFACE_IO"."WFIELD2" AND
"FIELD3" = "UNIFACE_IO"."WFIELD3";

ELSIF "UNIFACE_IO"."UNIFACE_IO_REQUEST" =
"SELECT_ROW_BY_ROWID" THEN

SELECT /*+ FIRST_ROWS */
"FIELD1",
"FIELD2",
"FIELD3",
"U_VERSION",
"FIELD4",
"FIELD5",
"FIELD6",
"FIELD7",
"FIELD8",
"FIELD9",
"FIELD10",
"FIELD11"
INTO
"UNIFACE_IO"."XFIELD1",
"UNIFACE_IO"."XFIELD2",
"UNIFACE_IO"."XFIELD3",
"UNIFACE_IO"."XU_VERSION",
"UNIFACE_IO"."XFIELD4",
"UNIFACE_IO"."XFIELD5",
"UNIFACE_IO"."XFIELD6",
"UNIFACE_IO"."XFIELD7",
"UNIFACE_IO"."XFIELD8",
"UNIFACE_IO"."XFIELD9",
"UNIFACE_IO"."XFIELD10",
"UNIFACE_IO"."XFIELD11"
FROM "my_table"
WHERE
ROWID = CHARTOROWID("UNIFACE_IO"."XROWID");

ELSIF "UNIFACE_IO"."UNIFACE_IO_REQUEST" =
"CHECK_EXISTENCE_OF_FIELD1" THEN

SELECT /*+ FIRST_ROWS */ 0
INTO
"UNIFACE_IO"."DUMMY_FIELD"
FROM "my_table"
WHERE
"FIELD1" = "UNIFACE_IO"."WFIELD1" AND
"FIELD2" = "UNIFACE_IO"."WFIELD2" AND
"FIELD3" = "UNIFACE_IO"."WFIELD3";

ELSIF "UNIFACE_IO"."UNIFACE_IO_REQUEST" =
"LOCK_ROW_BY_FIELD1" THEN

SELECT
"FIELD1",
"FIELD2",
"FIELD3",
"U_VERSION",
"FIELD4",
"FIELD5",
"FIELD6",
"FIELD7",
"FIELD8",
"FIELD9",
"FIELD10",
"FIELD11",
ROWIDTOCHAR(ROWID)
INTO
"UNIFACE_IO"."XFIELD1",
"UNIFACE_IO"."XFIELD2",
"UNIFACE_IO"."XFIELD3",
"UNIFACE_IO"."XU_VERSION",
"UNIFACE_IO"."XFIELD4",
"UNIFACE_IO"."XFIELD5",
"UNIFACE_IO"."XFIELD6",
"UNIFACE_IO"."XFIELD7",
"UNIFACE_IO"."XFIELD8",
"UNIFACE_IO"."XFIELD9",
"UNIFACE_IO"."XFIELD10",
"UNIFACE_IO"."XFIELD11",
"UNIFACE_IO"."XROWID"
FROM "my_table"
WHERE
"FIELD1" = "UNIFACE_IO"."WFIELD1" AND
"FIELD2" = "UNIFACE_IO"."WFIELD2" AND
"FIELD3" = "UNIFACE_IO"."WFIELD3"
FOR UPDATE NOWAIT;

ELSIF "UNIFACE_IO"."UNIFACE_IO_REQUEST" =
"LOCK_ROW_BY_ROWID" THEN

SELECT
"FIELD1",
"FIELD2",
"FIELD3",
"U_VERSION",
"FIELD4",
"FIELD5",
"FIELD6",
"FIELD7",
"FIELD8",
"FIELD9",
"FIELD10",
"FIELD11"
INTO
"UNIFACE_IO"."XFIELD1",
"UNIFACE_IO"."XFIELD2",
"UNIFACE_IO"."XFIELD3",
"UNIFACE_IO"."XU_VERSION",
"UNIFACE_IO"."XFIELD4",
"UNIFACE_IO"."XFIELD5",
"UNIFACE_IO"."XFIELD6",
"UNIFACE_IO"."XFIELD7",
"UNIFACE_IO"."XFIELD8",
"UNIFACE_IO"."XFIELD9",
"UNIFACE_IO"."XFIELD10",
"UNIFACE_IO"."XFIELD11"
FROM "my_table"
WHERE
ROWID = CHARTOROWID("UNIFACE_IO"."XROWID")
FOR UPDATE NOWAIT;

ELSIF "UNIFACE_IO"."UNIFACE_IO_REQUEST" =
"DELETE_ROW_BY_ROWID" THEN

DELETE FROM "my_table"
WHERE
ROWID = CHARTOROWID("UNIFACE_IO"."XROWID");

IF SQL%ROWCOUNT 1 THEN
"ONE_ROW_AFFECTED" := "NUM_FALSE";
END IF;

ELSIF "UNIFACE_IO"."UNIFACE_IO_REQUEST" =
"DELETE_ROW_BY_FIELD1" THEN

DELETE FROM "my_table"
WHERE
"FIELD1" = "UNIFACE_IO"."WFIELD1" AND
"FIELD2" = "UNIFACE_IO"."WFIELD2" AND
"FIELD3" = "UNIFACE_IO"."WFIELD3";

IF SQL%ROWCOUNT 1 THEN
"ONE_ROW_AFFECTED" := "NUM_FALSE";
END IF;

ELSIF "UNIFACE_IO"."UNIFACE_IO_REQUEST" =
"DELETE_ROW_BY_PK_AND_UVERSION" THEN

DELETE FROM "my_table"
WHERE
"FIELD1" = "UNIFACE_IO"."WFIELD1" AND
"FIELD2" = "UNIFACE_IO"."WFIELD2" AND
"FIELD3" = "UNIFACE_IO"."WFIELD3" AND
"U_VERSION" = "UNIFACE_IO"."WU_VERSION";

IF SQL%ROWCOUNT 1 THEN
"ONE_ROW_AFFECTED" := "NUM_FALSE";
END IF;

ELSIF "UNIFACE_IO"."UNIFACE_IO_REQUEST" =
"UPDATE_ROW_BY_FIELD1" THEN

UPDATE "my_table" SET
"U_VERSION" = "UNIFACE_IO"."XU_VERSION",
"FIELD4" = "UNIFACE_IO"."XFIELD4",
"FIELD5" = "UNIFACE_IO"."XFIELD5",
"FIELD6" = "UNIFACE_IO"."XFIELD6",
"FIELD7" = "UNIFACE_IO"."XFIELD7",
"FIELD8" = "UNIFACE_IO"."XFIELD8",
"FIELD9" = "UNIFACE_IO"."XFIELD9",
"FIELD10" = "UNIFACE_IO"."XFIELD10",
"FIELD11" = "UNIFACE_IO"."XFIELD11"
WHERE
"FIELD1" = "UNIFACE_IO"."WFIELD1" AND
"FIELD2" = "UNIFACE_IO"."WFIELD2" AND
"FIELD3" = "UNIFACE_IO"."WFIELD3";

IF SQL%ROWCOUNT 1 THEN
"ONE_ROW_AFFECTED" := "NUM_FALSE";
END IF;

ELSIF "UNIFACE_IO"."UNIFACE_IO_REQUEST" =
"UPDATE_ROW_BY_PK_AND_UVERSION" THEN

UPDATE "my_table" SET
"U_VERSION" = "UNIFACE_IO"."XU_VERSION",
"FIELD4" = "UNIFACE_IO"."XFIELD4",
"FIELD5" = "UNIFACE_IO"."XFIELD5",
"FIELD6" = "UNIFACE_IO"."XFIELD6",
"FIELD7" = "UNIFACE_IO"."XFIELD7",
"FIELD8" = "UNIFACE_IO"."XFIELD8",
"FIELD9" = "UNIFACE_IO"."XFIELD9",
"FIELD10" = "UNIFACE_IO"."XFIELD10",
"FIELD11" = "UNIFACE_IO"."XFIELD11"
WHERE
"FIELD1" = "UNIFACE_IO"."WFIELD1" AND
"FIELD2" = "UNIFACE_IO"."WFIELD2" AND
"FIELD3" = "UNIFACE_IO"."WFIELD3" AND
"U_VERSION" = "UNIFACE_IO"."WU_VERSION";

IF SQL%ROWCOUNT 1 THEN
"ONE_ROW_AFFECTED" := "NUM_FALSE";
END IF;

ELSIF "UNIFACE_IO"."UNIFACE_IO_REQUEST" =
"UPDATE_ROW_BY_ROWID" THEN

UPDATE "my_table" SET
"U_VERSION" = "UNIFACE_IO"."XU_VERSION",
"FIELD4" = "UNIFACE_IO"."XFIELD4",
"FIELD5" = "UNIFACE_IO"."XFIELD5",
"FIELD6" = "UNIFACE_IO"."XFIELD6",
"FIELD7" = "UNIFACE_IO"."XFIELD7",
"FIELD8" = "UNIFACE_IO"."XFIELD8",
"FIELD9" = "UNIFACE_IO"."XFIELD9",
"FIELD10" = "UNIFACE_IO"."XFIELD10",
"FIELD11" = "UNIFACE_IO"."XFIELD11"
WHERE
ROWID = CHARTOROWID("UNIFACE_IO"."XROWID");

IF SQL%ROWCOUNT 1 THEN
"ONE_ROW_AFFECTED" := "NUM_FALSE";
END IF;

ELSIF "UNIFACE_IO"."UNIFACE_IO_REQUEST" =
"UPDATE_ROW_BY_ROWID_UVERSION" THEN

UPDATE "my_table" SET
"U_VERSION" = "UNIFACE_IO"."XU_VERSION",
"FIELD4" = "UNIFACE_IO"."XFIELD4",
"FIELD5" = "UNIFACE_IO"."XFIELD5",
"FIELD6" = "UNIFACE_IO"."XFIELD6",
"FIELD7" = "UNIFACE_IO"."XFIELD7",
"FIELD8" = "UNIFACE_IO"."XFIELD8",
"FIELD9" = "UNIFACE_IO"."XFIELD9",
"FIELD10" = "UNIFACE_IO"."XFIELD10",
"FIELD11" = "UNIFACE_IO"."XFIELD11"
WHERE
ROWID = CHARTOROWID("UNIFACE_IO"."XROWID") AND
"U_VERSION" = "UNIFACE_IO"."WU_VERSION";

IF SQL%ROWCOUNT 1 THEN
"ONE_ROW_AFFECTED" := "NUM_FALSE";
END IF;

ELSE

raise_application_error( -20000, 'Unhandled UNIFACE I/O request' );

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

"ONE_ROW_AFFECTED" := "NUM_FALSE";

END "UNIFACE_IO";
END "my_table$U";
/

#################################################
Answers (1)
Next Recommended Forum