Home » SQL & PL/SQL » SQL & PL/SQL » Problems when trying to use SYS_GUID()
Problems when trying to use SYS_GUID() [message #18452] Thu, 31 January 2002 07:31 Go to next message
CATHYBEE
Messages: 20
Registered: January 2002
Junior Member
MY TABLE:
SQL> DESC TBL_CLAIMS;

Name Null? Type

HOSTKEY NOT NULL TIMESTAMP(4)
CLAIMID NOT NULL RAW(32)

The procedure, i am trying to use, when populating the CLAIMID column with SYS_GUID()

MY PROCEDURE:
CREATE OR REPLACE PROCEDURE uspi_Claims
(V_HKEY IN OUT tbl_Claims.HostKey%TYPE,
V_CLAIMID IN tbl_Claims.ClaimID%TYPE) AS

BEGIN
IF
V_CLAIMID is NULL THEN
SET V_CLAIMID := SYS_GUID();
END IF;
INSERT INTO tbl_Claims
(Hostkey)
VALUES
(V_HKEY);
COMMIT;
END;

ERROR I AM GETTING IS:

Warning: Procedure created with compilation errors.

SQL> SHO ERRORS
Errors for PROCEDURE USPI_CLAIMS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/1 PLS-00363: expression 'V_CLAIMID' cannot be used as an assignment
target

8/1 PL/SQL: Statement ignored

PLEASE HELP!

Thanks.
Re: Problems when trying to use SYS_GUID() [message #18458 is a reply to message #18452] Thu, 31 January 2002 10:26 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
this looks like a problem I experienced in 7.3.3 but which was OK in 7.3.4. Basically toy are trying to assign a value to something which is only an IN variable. You could fix it by making it IN OUT, but that would confuse the users of the proc, because that value it not intended to be passed out again. Assign V_CLAIMID to a local variable and then assign sys_guid to that.
You could also try simplifying the proc to avoid that logic using DEFAULT - something like this...

CREATE OR REPLACE PROCEDURE uspi_Claims
(V_HKEY IN OUT tbl_Claims.HostKey%TYPE,
V_CLAIMID IN tbl_Claims.ClaimID%TYPE default sys_guid()) AS...
Previous Topic: unix date to "oracle date"
Next Topic: Using Hints in View Definitions - Not woking
Goto Forum:
  


Current Time: Thu Jul 02 11:50:46 CDT 2020