Question about utils.convert_to_varchar2 function

user8908352

    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

        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

          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

            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
              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

                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

                  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

                    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

                      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

                        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

                          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