Urgent help

[portal]

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

[rsuri]

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

[portal]

yes suri,
the function returned without errors.
but when i execute this i get disconnecteted.

[rsuri]

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

[portal]

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

[rsuri]

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

[portal]

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