9 Replies Latest reply: Aug 21, 2013 9:17 AM by Tom Petrus RSS

    Once more: missing right parenthesis

    NDG123

      Hi guys,

       

      I've been looking on the forum for another thread which could give me the answer I'm looking for, but I haven't found one.

       

      I'm new to APEX and I have to build a crosstab. For this, I based my development on Really working Crosstab / Pivot Report example.

      My package is completely the same as it is on that page and I have adapted the query for the report(=Classic, not interactive) based on PL/SQL so it fits my needs, the result is:

      DECLARE

      l_return_value VARCHAR2(32767);

      BEGIN

       

      l_return_value := PK_PIVOT.PivotSQL('SELECT * FROM "Ploegrapport - view"', 'SOD_OMSCHR_LANG, OOD_OMSCHR_LANG, ACT_OMSCHR_LANG, TAAK_OMSCHR_LANG, ORG_NIV4, ORG_NIV5','NK_WERKNEMER', 'MIN', 'J_N', nvl(:P2_PAGENUMBER,1) );

       

      RETURN l_return_value;

      END;

       

      When I try to run this, I get the error ORA-00907: missing right parenthesis.

       

      When I click 'debug', it changes in:

      error logging debug ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-00907: missing right parenthesis

      failed to parse SQL query:

      ORA-00907: missing right parenthesis

       

      When I then click' view debug' I get the following:

      ...Execute Statement: declare function x return varchar2 is begin DECLARE l_return_value VARCHAR2(32767); BEGIN l_return_value := PK_PIVOT.PivotSQL('SELECT * FROM "Ploegrapport - view"', 'SOD_OMSCHR_LANG, OOD_OMSCHR_LANG, ACT_OMSCHR_LANG, TAAK_OMSCHR_LANG, ORG_NIV4, ORG_NIV5','NK_WERKNEMER', 'MIN', 'J_N', nvl(:P2_PAGENUMBER,1) ); RETURN l_return_value; END; return null; end; begin wwv_flow.g_computation_result_vc := x; end;

       

      ......Result = SELECT SOD_OMSCHR_LANG, OOD_OMSCHR_LANG, ACT_OMSCHR_LANG, TAAK_OMSCHR_LANG, ORG_NIV4, ORG_NIV5,MIN(DECODE(NK_WERKNEMER,'XXXXX',J_N,null)) as "XXXXX" ,MIN(DECODE(NK_WERKNEMER,'XXXXX',J_N,null)) as "XXXXX" ,MIN(DECODE(NK_WERKNEMER,'XXXXX',J_N,null)) as "XXXXX" ,MIN(DECODE(NK_WERKNEMER,XXXXX',J_N,null)) as "XXXXX" ,MIN(DECODE(NK_WERKNEMER,'XXXXX',J_N,null)) as "XXXXX" ,MIN(DECODE(NK_WERKNEMER,'XXXXX',J_N,null)) as "XXXXX" ,MIN(DECODE(NK_WERKNEMER,'XXXXX',J_N,null)) as "XXXXX" ,MIN(DECODE(NK_WERKNEMER,'XXXXX',J_N,null)) as "XXXXX" ,MIN(DECODE(NK_WERKNEMER,'XXXXX',J_N,null)) as "XXXXX" ,MIN(DECODE(NK_WERKNEMER,'XXXXX',J_N,null)) as "XXXXX" ,MIN(DECODE(NK_WERKNEMER,'XXXXX',J_N,null)) as "XXXXX" ,MIN(DECODE(NK_WERKNEMER,'XXXXX',J_N,null)) as "XXXXX" ,MIN(DECODE(NK_WERKNE~

       

       

      (Don't mind the XXXXX, I have put this there manually because this is sensitive information, the field is actually correct, and represents names of people.) The query works when I cut off the last piece, which is broken and when I add a group by, so the query it builds seems to be correct

      As you can see the query in the debug is actually broken after 999 characters, is this simply due to the fact that de debug won't show more than 999 characters, or is this actually my problem, that the query is broken before the full statement gets finished.

      If it's the first option: then where could my problem be? If it's the second option: I've checked the report query code and the package code and I can't find a reason why this query would be alowed to exist out of only 999 characters.

       

      I am currently working with the test environment Oracle supplied us, because we are testing if we can use APEX at our company.

      The version is Application Express 4.2.2.00.11 and it's running on Oracle 11g.

       

      If you need any more information, just ask and I will post it asap.

       

      When posting answers, please keep in mind that I am a beginner at this and have trouble understanding complex answers .

      Thanks for your help!

       

      NDG

        • 1. Re: Once more: missing right parenthesis
          Tom Petrus

          Your return value is a varchar2 and you're probably hitting the limit of what it can hold. You'll probably have to store your query in a CLOB and then write that to the buffer (however, i believe you may need to write it broken down in pieces of 32k bytes). If you're concerned about the size then try to apply a where clause to your source query and limit it to like 10 records (where rownum < 10) and see if that runs.

          • 2. Re: Once more: missing right parenthesis
            NDG123

            Hi Tom,

             

            Thank you for your fast reply.

             

            You were right, the problem was that the query was too long, I added the where rownum<10 and I got a result that looks like what I'm after.

            I'll try and figure out how CLOB works and how I can cut it in pieces.

             

            I'm not gonna close this discussion just yet, I will close it when my problem is completely resolved.

             

            Thanks again!

             

            Kind regards,

            NDG

            • 3. Re: Once more: missing right parenthesis
              Tom Petrus

              You can probably use the technique as posted in this thread, ignoring the fact he is dealing with a json-formatted string: Re: Append varchar2 data to a CLOB

              • 4. Re: Once more: missing right parenthesis
                NDG123

                Hi Tom,

                 

                Thank you for the link.

                Just so that I don't start searching somewhere I shouldn't be searching, should I integrate the code on that page in my report PL/SQL query?

                • 5. Re: Once more: missing right parenthesis
                  Tom Petrus

                  Oh goshdarnit. I was so obsessing over the plsql code that I thought this was a plsql region, while I can perfectly read right there that it is a classic report. Oh well - I'm not sure if it'll work but worth a try. Leave out the cutting clob in pieces part for now as that is what you'd use in a plsql region to emit the html yourself.

                  You have plsql block where you get the return value from the pk_pivot package. You'll first need to change the type of l_Return_value to clob. See if that helps first. If it doesn't then take a look in the pk_pivot package and check the datatype for the return variable it uses. If it's varchar then try changing it to clob.

                   

                  Sorry for the confusion, I was probably too eager.

                  • 6. Re: Once more: missing right parenthesis
                    NDG123

                    Hi,

                     

                    No problem, can't blame you for being too eager. =)

                     

                    I am using a classic report because I can't insert PL/SQL in an interactive report (this is correct, right? ^^).

                    EDIT: I just found out you can add a PL/SQL region to an interactive report (yeah, that's how new I am to this ...). Would you advice this over a classic report where you can put PL/SQL directly in the report PL/SQL query?

                     

                    So I ignored your earlier post and did the following:

                    - changed the type of l_Return_value to clob in the report region query

                    => same problem

                     

                    - changed (in both package body and in package specification)

                    Function PivotSQL (...)

                    return varchar2; --returns query text for crosstab

                     

                    to

                     

                    Function PivotSQL (...)

                    return CLOB; --returns query text for crosstab

                     

                    and l_query varchar2(32767); to l_query clob; (in package body)

                    => same problem


                    When the types do not match (package body = clob, package spec = varchar) I get the following error:
                    Logging exception in final_exception_handler: Sqlerrm: ORA-20987: APEX - Error during rendering of region "Report 1". - ORA-04063: package body &quot;VAZG_OP.PK_PIVOT&quot; has errors ORA-06508: PL&#x2F;SQL: could not find program unit being called: &quot;VAZG_OP.PK_PIVOT&quot; Backtrace: ORA-06512: at "APEX_040200.WWV_FLOW_ERROR", line 630 ORA-06512: at "APEX_040200.WWV_FLOW_ERROR", line 911 ORA-06512: at "APEX_040200.WWV_FLOW_DISP_PAGE_PLUGS", line 3241 ORA-06512: at "APEX_040200.WWV_FLOW_PAGE", line 1788 ORA-06512: at "APEX_040200.WWV_FLOW_PAGE", line 1958 ORA-06512: at "APEX_040200.WWV_FLOW", line 6441

                     

                    So, it's back to splitting a CLOB-type in 32K pieces?

                     

                    Message was edited by: NDG123

                    • 7. Re: Once more: missing right parenthesis
                      Tom Petrus

                      Well, you should actually make sure that your package is valid Both the spec and body. Certainly when you are changing the signature in the spec, you will also have to update the signature in the body. When you defined return type clob in the spec it should also be the return type in the body.

                      And as for plsql in classic or interactive: if neither will accept or can handle a clob then look no further though. I'm still amazed that you actually have such a huge sql statement generated but I have not used the pivot package before. Splitting the clob in pieces will not help you put this in a classic or interactive report, as the split is meant to work with htp.p calls which emit the code to the http buffer and thus is meant to manually output a table structure for example. You might be able to use piped rows (google for pipe rows or pipelined functions) but this would also mean you'd have to execute the sql statement and handle it. Just offering some options here of course - you'll need to play a bit and find out I'm afraid, unless someone else and more knowledgeable shows up!

                      • 8. Re: Once more: missing right parenthesis
                        NDG123

                        Hi,

                         

                        I discovered what the problem was.

                        In my data there was an employee with a '-character in his name and this is what made the query return an error. I tried again, this time ignoring all names with a '-character, and the query ran succesfully, now a column is made dynamically for every employee, just like I wanted.

                         

                        Thank you for your efforts Tom!

                        • 9. Re: Once more: missing right parenthesis
                          Tom Petrus

                          What a terrible waste of effort Glad it's only this!