This discussion is archived
10 Replies Latest reply: Nov 14, 2013 5:42 AM by marcusafs RSS

Question about utils.convert_to_varchar2 function

user8908352 Newbie
Currently Being Moderated

Dear all,

I'm doing about with SQL Developer, and I meet a problem about utils.convert_to_varchar2, it is detail the below.

SELECT REPLACE(REPLACE(UTILS.CONVERT_TO_VARCHAR2(SUM(CAST(Field AS NUMBER)),30), '2', '1'), '3', '1').

But the SQL Developer is notice that the UTILS.CONVERT_TO_VARCHAR2 is not identifier.

So I have a question about it.

how to do I can execute SQL statement above?

How to I have a utils package or what software I have to install?

  • 1. Re: Question about utils.convert_to_varchar2 function
    Marwim Expert
    Currently Being Moderated

    Hello,

     

    there is no package utils in Oracle. Is has to be some third party code. Do you use it anywhere else?

     

    Regards

    Marcus

  • 2. Re: Question about utils.convert_to_varchar2 function
    marcusafs Journeyer
    Currently Being Moderated

    Check public and private synonyms to determine who owns UTILS.  Then grant execute to the package.

  • 3. Re: Question about utils.convert_to_varchar2 function
    user8908352 Newbie
    Currently Being Moderated

    Thank Marwim!

    I searched it on internet, but I don't know who third party of it. and I use it in oracle 10g.

    do you have any other recomments?

  • 4. Re: Question about utils.convert_to_varchar2 function
    rp0428 Guru
    Currently Being Moderated
    I searched it on internet, but I don't know who third party of it. and I use it in oracle 10g.

     

    Well you need to find that 'third party' because it is NOT an Oracle package.

     

    What do you mean you 'use it'?

     

    Your query above isn't using it - it is raising an exception because it CANNOT use it.

     

    Post an example query that works that 'uses it' and the result it produces.

  • 5. Re: Question about utils.convert_to_varchar2 function
    user8908352 Newbie
    Currently Being Moderated

    This is my example:

    PROCEDURE spMenuItem

    (

      v_UserID IN VARCHAR2,

      cv_1 OUT SYS_REFCURSOR

    )

    AS

     

       v_VAL NVARCHAR2(30);

       v_Pos NUMBER(10,0);

       v_len NUMBER(10,0);

     

    BEGIN

     

       SELECT REPLACE(REPLACE(UTILS.CONVERT_TO_VARCHAR2(SUM(CAST(PrivilegeID AS NUMBER)),30), '2', '1'), '3', '1')

         INTO v_VAL

         FROM TBL_GroupPrivMst

         WHERE GroupCode IN ( SELECT GroupCode

                              FROM tbl_UserGrpMap

                                WHERE upper(UserName) = UPPER(v_UserID))

       ;

       v_Pos := 1 ;

       v_len := LENGTH(v_VAL) ;

       WHILE ( v_len != 0 )

       LOOP

          DECLARE

             v_Value CHAR(1);

     

          BEGIN

             v_Value := SUBSTR(v_VAL, v_Pos, 1) ;

             IF v_Value = 1 THEN

             BEGIN

                DBMS_OUTPUT.PUT_LINE(v_Value);

                INSERT INTO temp1

                  ( SELECT *

                    FROM TBL_MenuMaster

                      WHERE ID = v_Pos );

             END;

             END IF;

             v_Pos := v_Pos + 1 ;

             v_len := v_len - 1 ;

          END;

       END LOOP;

       --For first Node (Inserting The Parent Node)

       INSERT INTO temp1

         ( SELECT *

           FROM TBL_MenuMaster

             WHERE ID IN ( SELECT DISTINCT ParentID

                               FROM temp1

                                 WHERE ParentID NOT IN ( SELECT ID

                                                         FROM temp1  )

                    )

          );

       --For second Node (Inserting The Parent Node)

       INSERT INTO temp1

         ( SELECT *

           FROM TBL_MenuMaster

             WHERE ID IN ( SELECT DISTINCT ParentID

                               FROM temp1

                                 WHERE ParentID NOT IN ( SELECT ID

                                                         FROM temp1  )

                    )

          );

       --For third Node (Inserting The Parent Node)

       INSERT INTO temp1

         ( SELECT *

           FROM TBL_MenuMaster

             WHERE ID IN ( SELECT DISTINCT ParentID

                               FROM temp1

                                 WHERE ParentID NOT IN ( SELECT ID

                                                         FROM temp1  )

                    )

          );

       OPEN  cv_1 FOR

          SELECT *

            FROM temp1

            ORDER BY ID ASC ;

     

      COMMIT;

    END;

     

    It show permission for user: it return a string if in string have a number one, then user have a permission on menu, or in string have a number zero, then user havent go permission access a menu.

  • 6. Re: Question about utils.convert_to_varchar2 function
    user8908352 Newbie
    Currently Being Moderated

    I have already check public synonyms

    select *from DBA_SYNONYMS where synonym_name like '%UTILS%';

    it return YES, but execute sql above on other database, it return NO.

    why?

  • 7. Re: Question about utils.convert_to_varchar2 function
    rp0428 Guru
    Currently Being Moderated

    Well now you are just confusing things.

     

    Your original post said you executed that single query in sql developer and it failed.

     

    Now you post a procedure that includes that exact query.

     

    Which is it?

     

    You need to post a copy of a complete session that shows:

    1. What query you are executing

    2. How you are executing

    3. The EXACT results you are getting

     

    A procedure can has different issues (e.g. privilege problems) that merely executing a query. We need to know EXACTLY what you are doing and the exception you get.

     


  • 8. Re: Question about utils.convert_to_varchar2 function
    user8908352 Newbie
    Currently Being Moderated

    Yes,

    before I posted a single query, it is a part of inside procedure, when I excuted my store procedure or executed single query in SQL Developer then program notice error in UTILS.CONVERT_TO_VARCHAR2 is not declare identifier.

  • 9. Re: Question about utils.convert_to_varchar2 function
    rp0428 Guru
    Currently Being Moderated

    Then either that schema/package named 'UTILS' and procedure doesn't exist at all or doesn't exist for the user executing the query (i.e. no permissions).

     

    UTILS could be a schema or it could be a package in a SCHEMA. You need to check the data dictionary views to see if such a schema/package even exists in that database and if so what permissions have been granted on it.

  • 10. Re: Question about utils.convert_to_varchar2 function
    marcusafs Journeyer
    Currently Being Moderated

    Run the following queries and post the results.

    SELECT   users.username
    FROM     dba_users users
    WHERE    UPPER ( users.username ) LIKE 'UTILS%'
    ORDER BY users.username;
    SELECT   syn.*
    FROM     dba_synonyms syn
    WHERE    UPPER ( syn.synonym_name ) LIKE 'UTILS%'
    ORDER BY syn.synonym_name;
    SELECT   obj.owner,
             obj.object_name,
             obj.object_type
    FROM     dba_objects obj
    WHERE    UPPER ( obj.object_name ) LIKE 'UTILS%'
    ORDER BY obj.object_name 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points