10 Replies Latest reply on Nov 14, 2013 1:42 PM by marcusafs

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

                      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
                          rp0428

                          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