First of all you should create your spec then body;
To create your table:
CREATE TABLE APPS.XXPC_ATALAY_USERS
(
IDENTIFIER VARCHAR2(50 BYTE),
ALIAS VARCHAR2(50 BYTE),
TITLE VARCHAR2(1000 BYTE),
TYPE VARCHAR2(50 BYTE),
REGISTERTIME DATE
);
To create your type:
create or replace type xxpc_atalay_user_t as object (
identifier varchar2(50),
alias varchar2(50),
title varchar2(1000),
TYPE varchar2(150),
registertime date
);
spec:
/* Formatted on 6/3/2013 2:10:22 AM (QP5 v5.163.1008.3004) */
CREATE OR REPLACE PACKAGE apps.xxpc_atalay_user_pkg –spec
AS
PROCEDURE set_user (p_user IN xxpc_atalay_user_t,
p_debug IN VARCHAR2 DEFAULT ‘N’,
x_retcode OUT VARCHAR2,
x_retmsg OUT VARCHAR2);
END xxpc_atalay_user_pkg;
body:
/* Formatted on 6/3/2013 2:11:29 AM (QP5 v5.163.1008.3004) */
CREATE OR REPLACE PACKAGE BODY apps.xxpc_atalay_user_pkg
AS
—
g_debug VARCHAR2 (10) := ‘Y’;
g_location VARCHAR2 (80) := ”;
—
PROCEDURE LOG (p_subject IN VARCHAR2)
IS
BEGIN
IF (g_debug = ‘Y’)
THEN
DBMS_OUTPUT.put_line (‘[Log (‘ || g_location || ‘)]:’ || p_subject);
END IF;
END LOG;
PROCEDURE set_user (p_user IN xxpc_atalay_user_t,
p_debug IN VARCHAR2 DEFAULT ‘N’,
x_retcode OUT VARCHAR2,
x_retmsg OUT VARCHAR2)
IS
l_ctr NUMBER := 0;
BEGIN
g_debug := p_debug;
—
g_location := ‘set_user.01:check exist’;
—
SELECT COUNT (*)
INTO l_ctr
FROM XXPC_ATALAY_USERS X
WHERE 1 = 1 AND x.identifier = p_user.identifier;
IF l_ctr = 0
THEN
—
g_location := ‘set_user.02:insert’;
INSERT INTO xxpc_atalay_users (identifier,
alias,
title,
TYPE,
registertime)
VALUES (p_user.IDENTIFIER,
p_user.alias,
p_user.title,
p_user.TYPE,
p_user.registertime);
LOG (‘inserted 1 row’);
ELSE
—
g_location := ‘set_user.03: update’;
UPDATE xxpc_atalay_users x
SET alias = p_user.alias,
title = p_user.title,
TYPE = p_user.TYPE,
registertime = p_user.registertime
WHERE x.identifier = p_user.identifier;
LOG (‘updated’ || SQL%ROWCOUNT || ‘row (s)’);
END IF;
–commit;
—
x_retcode := ‘S’;
x_retmsg := ”;
EXCEPTION
WHEN OTHERS
THEN
—
x_retcode := ‘E’;
x_retmsg := ‘ERROR (‘ || g_location || ‘):’ || SQLERRM;
LOG (x_retcode || ‘-‘ || x_retmsg);
–Rollback
END set_user;
END xxpc_atalay_user_pkg;
Code 