Re: BIT_COMPLEMENT function giving errors..

Shishir wrote:
> hi friends,
>
> I am trying to migrate a t-sql code to pl/sql code using oracle
> migration workbench..
> i have modfied the code to include xml handling functions in pl/sql .
> On compiling i am getting the following errors. 'IsVisible' is a
> column in the LIBRARYTree and it is of type bit holiding only 0 0r
> 1..The errors are :
>
> Line # = 18 Column # = 4 Error Text = PL/SQL: SQL Statement ignored
> Line # = 19 Column # = 31 Error Text = PLS-00306: wrong number or
> types of arguments in call to 'BIT_COMPLEMENT'
> Line # = 19 Column # = 23 Error Text = PL/SQL: ORA-00904:
> "UTL_RAW"."BIT_COMPLEMENT": invalid identifier
>
> CREATE OR REPLACE PROCEDURE usp_LIBRARYHideUnhideTreeNodes
> (
> -- Add the parameters for the stored procedure here
> v_paramMyXml IN CLOB DEFAULT NULL
>
> )
> AS
> BEGIN
>
> --add the passed xml string to the table with the xmltype column
> INSERT INTO XMLTEST values
> (XMLTYPE(v_paramMyXml));
>
> UPDATE LIBRARYTree
> SET IsVisible = UTL_RAW.BIT_COMPLEMENT(IsVisible)
> WHERE NodeId IN ( select extractValue(data_xml,'HiddenNodes/Node/
> id' ) as nodefinder
> from XMLTEST);
>
> END;
>
> Any help in this regard will be appreciated??
>
> Thanks in advance.
>
> Shishir.

Oracle version number?
DDL for the librarytree table?

One possibility is that you had mixed-case object/column names in SQL Server and brought those into Oracle. If you've done so you will get to wrap every name in double quotes for the rest of your life or you will quickly fix the problem and make sure all names are all upper case all of the time.

Get the DDL as follows:
SELECT dbms_metadata.get_ddl('TABLE', 'LIBRARYTREE'); if that doesn't work you know what the problem is.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org