Link to home
Start Free TrialLog in
Avatar of devguru001
devguru001Flag for South Africa

asked on

Dynamic SQL with Cursor package Error

Hi

I have created a dynamic sql package. I have a feeling that I am missing something which would be obvious to experts. I have created this code by trying to piece together my referencing of online examples. I am not experienced in PLSQL.

What I am trying to achieve:
Have centralized query to which I can append my specific "where clause criteria" passed as a paramater. The Where Clause will differ depending on user input.

The code:
CREATE OR REPLACE PACKAGE PWCDW.PACGETJOBREGIONACCESS
AS
   FUNCTION FUNCGETJOBREGIONACCESS (
      sqlWhere  IN   NVARCHAR2 := NULL
   )
      RETURN coldynamicsearchreturntype PIPELINED;
END;
/

Open in new window



CREATE OR REPLACE PACKAGE BODY PACGETJOBREGIONACCESS
AS
   FUNCTION FUNCGETJOBREGIONACCESS (sqlWhere IN NVARCHAR2 := NULL)
      RETURN coldynamicsearchreturntype
      PIPELINED
   IS
      TYPE r_cursor IS REF CURSOR;

      searchCurse   r_cursor;
      sqlStr        VARCHAR2 (8000)
         := '
                WITH main_table
              AS (  SELECT DISTINCT j.jobnumber,
                             j.name1,
                             j.jobname,
                             j.customernumber,
                             j.specification3name,
                             j.specification2name,
                             j.specification1name,
                             j.employeenumber10,
                             j.blockedfortimeregistrations,
                             j.blockedforamountregistration,
                             j.locationname
                     FROM pwc.employee e,
                          pwc.jobHeader j,
                          (SELECT l.locationName locationName, t.nameOfUser
                             FROM location l,
                                  (  SELECT ast.accessLevelName,
                                            an.subLevelName,
                                            uA.nameOfUser
                                       FROM accessLevelLine ast,
                                            pwc.userAccessLevel uA,
                                            (SELECT li.subLevelName, li.accessLevelName
                                               FROM pwc.accessLevelLine li) an
                                      WHERE     uA.userAccessLevelName =
                                                   ast.accessLevelName
                                            AND an.accessLevelName =
                                                   uA.userAccessLevelName
                                   GROUP BY ast.accessLevelName,
                                            an.sublevelName,
                                            uA.NameOfUser) t
                            WHERE    l.locationName LIKE t.accessLevelName || ''%''
                                  OR l.locationName LIKE t.subLevelName || ''%'') p
                    WHERE     
                          AND j.locationName = p.locationName
                          AND j.blockedfortimeregistrations = 0
                          AND j.closed = 0
                          ----Insert sqlwhere here------';


      col1          NVARCHAR2 (200);
      col2          NVARCHAR2 (200);
      col3          NVARCHAR2 (200);
      col4          NVARCHAR2 (200);
      col5          NVARCHAR2 (200);
      col6          NVARCHAR2 (200);
      col7          NVARCHAR2 (200);
      col8          NVARCHAR2 (200);
      col9          NVARCHAR2 (200);
      col10         NVARCHAR2 (200);
      numrows       NUMBER;
   BEGIN
      sqlStr := sqlStr || sqlWhere;
      sqlStr :=
            sqlStr
         || '    
                ORDER BY j.jobnumber),
                count_table AS --this table is necessary for paging functionality
                 (
                    SELECT COUNT (*) numRows 
                    FROM main_table
                 )
                SELECT  m.name1,
                             m.jobname,
                             m.customernumber,
                             m.specification3name,
                             m.specification2name,
                             m.specification1name,
                             m.employeenumber10,
                             m.blockedfortimeregistrations,
                             m.blockedforamountregistration,
                             m.locationname,
                             c.numrows
                FROM main_table m, count_table c';


      OPEN searchCurse FOR sqlStr;

      LOOP
         FETCH searchCurse
         INTO col1,
              col2,
              col3,
              col4,
              col5,
              col6,
              col7,
              col8,
              col9,
              col10,
              numrows;

         EXIT WHEN searchCurse%NOTFOUND;
         PIPE ROW (objdynamicsearchreturn (col1,
                                           col2,
                                           col3,
                                           col4,
                                           col5,
                                           col6,
                                           col7,
                                           col8,
                                           col9,
                                           col10,
                                           numrows));
      END LOOP;

      CLOSE searchCurse;

      RETURN;
   END FUNCGETJOBREGIONACCESS;
END PACGETJOBREGIONACCESS;
/

Open in new window


The call:
SELECT *
  FROM TABLE (
          pacgetjobregionaccess.FUNCGETJOBREGIONACCESS (
             ' and e.employeenumber = ''P178479''
                  AND j.locationName = p.locationName
                  AND j.blockedfortimeregistrations = 0
                  AND j.closed = 0
                  AND p.nameOfUser = ''dfolscher003'' '))

Open in new window


The Error:
[Error] Execution (4: 11): ORA-00936: missing expression
ORA-06512: at "PACGETJOBREGIONACCESS", line 89
ORA-06512: at line 1

Open in new window

SOLUTION
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Good that you got the lead from there but it is very important that your code has debug/log messages and also exception handling section to handle/trap any errors. This is something you can consider to enhance your code once you get through this specific issue.
Avatar of devguru001

ASKER

Yes, I am new to plsql so I do not yet know how to debug, I will try to google the methods on how to.
Thanks again.
no problem. Good and thanks :)
don't you need to append chr(13)||chr(10)
and after that your where clause ?

otherwise your where may be commented ...

sqlStr := sqlStr || chr(13)||chr(10) || sqlWhere;
Always check that your dynamic sql builds a statement that runs!! Run the statement separately to find bugs.. This would have saved me some time instead of me focusing on the cursor