-
Guys
i have a function like thiscreate or replace FUNCTION insBRAND_DATA(
p_BRAND_DATA_ID OUT BRAND_DATA.BRAND_DATA_ID%TYPE,
p_CONTENT_TYPE IN BRAND_DATA.CONTENT_TYPE%TYPE,
p_CONTENT_SZ IN BRAND_DATA.CONTENT_SZ%TYPE,
p_DATA_TYPE IN BRAND_DATA_TYPE.DATA_TYPE%TYPE,
p_DESCRIPTION IN BRAND_DATA.DESCRIPTION%TYPE,
p_AUDIT_BRAND_ID IN BRAND_INFO.BRAND_ID%TYPE,
p_AUDIT_USER_ID IN USERS.USER_ID%TYPE
)RETURN INTEGER IS
BEGIN
INSERT INTO BRAND_DATA
( DATA_TYPE,
BRAND_ID,
BRAND_DATA_ID,
CONTENT_SZ,
CONTENT_TYPE,
DESCRIPTION,
LAST_MODIFIED
) VALUES (
p_DATA_TYPE,
p_AUDIT_BRAND_ID,
SEQ_BRAND_DATA_ID.NEXTVAL,
p_CONTENT_SZ,
p_CONTENT_TYPE,
p_DESCRIPTION,
SYSDATE
);
SELECT BRAND_DATA_ID into p_brand_data_id FROM BRAND_DATA
WHERE BRAND_DATA_ID = SEQ_BRAND_DATA_ID.CURRVAL;
RETURN 0;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
--record did not insert, key already exists
RETURN -2;
WHEN OTHERS THEN
--record did not insert, unknown error
RETURN -1;
END insBRAND_DATA;
and when i execute this i get discoonectted to db by error 03113.all other functions works fine,but only this..could u pls let me know if there is any problem in this fun.
thx
sat
-
Did you function compile without errors ?
In the following insert there should not be a semi-colon at the end --
INSERT INTO BRAND_DATA
( DATA_TYPE,
BRAND_ID,
BRAND_DATA_ID,
CONTENT_SZ,
CONTENT_TYPE,
DESCRIPTION,
LAST_MODIFIED
) VALUES (
p_DATA_TYPE,
p_AUDIT_BRAND_ID,
SEQ_BRAND_DATA_ID.NEXTVAL,
p_CONTENT_SZ,
p_CONTENT_TYPE,
p_DESCRIPTION,
SYSDATE
); (REMOVE THIS SEMI-COLON...................)
Also, you may want to run this SQL from SQL/Plus to see that the table and Sequence don't have any problems.
- Rajeev
-
yes suri,
the function returned without errors.
but when i execute this i get disconnecteted.
sat
-
Could you do the following --
Run the following query at the sql/plus prompt --
select * from dual;
just to see that the connection works fine.
Then try the following sql --
INSERT INTO BRAND_DATA
( DATA_TYPE,
BRAND_ID,
BRAND_DATA_ID,
CONTENT_SZ,
CONTENT_TYPE,
DESCRIPTION,
LAST_MODIFIED
) VALUES (
p_DATA_TYPE,
p_AUDIT_BRAND_ID,
SEQ_BRAND_DATA_ID.NEXTVAL,
p_CONTENT_SZ,
p_CONTENT_TYPE,
p_DESCRIPTION,
SYSDATE
)
SELECT BRAND_DATA_ID into p_brand_data_id FROM BRAND_DATA
WHERE BRAND_DATA_ID = SEQ_BRAND_DATA_ID.CURRVAL;
Let me know what happens ?
- Rajeev
-
yes suri i tried the same.it returned with error like
expected ';' and found select--sql stsmt not properly ended.
in the above fun..if i take out the select part last few lines the function gets executed,but i want to return the value..how?its very urgent folks.
thanks anyhow
sat
-
sorry about the semi-colon suggestion; I didn't read the sql statement carefully...
Try to replace the following statement --
SELECT BRAND_DATA_ID into p_brand_data_id FROM BRAND_DATA
WHERE BRAND_DATA_ID = SEQ_BRAND_DATA_ID.CURRVAL;
with the following --
-- declare a Number variable seq_val
SELECT SEQ_BRAND_DATA_ID.CURRVAL into seq_val FROM DUAL;
SELECT BRAND_DATA_ID into p_brand_data_id FROM BRAND_DATA WHERE BRAND_DATA_ID = seq_val;
I hope you tested the following on sql prompt --
SELECT SEQ_BRAND_DATA_ID.CURRVAL into seq_val FROM DUAL;
-- to make sure your sequence is valid.
- Rajeev
-
hi rsuri,
it worked with this soln.thx man.
i declared a value from a seq as u suggested and it worked fine.
thx a lot
sat
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|