11 Replies Latest reply: May 28, 2013 1:16 AM by Prakash T Soundappan RSS

    how to clear this type of run time error

    Prakash T Soundappan
      good eve,
      i am migrating sql server procedures to oracle procedures
      i am trying to execute char(39) function in plsql is the following way correct and if this way is correct i am getting
      run time error like
      create or replace 
      procedure Sel_subgroup(key in varchar2,gradename in varchar2)
      as
      gid res_grades.grade_id%type;
      gn res_grades.gradename_en%type;
      stid sub_topics.subtopic_id%type;
      stn sub_topics.subtopicname_en%type;
      cc res_grades.countrycode%type;
      estid exsub_topics.exsub_topics_id%type;
      estn exsub_topics.exsubtopicname_en%type;
      d_stmt varchar2(500);
      type ref_cursor is ref cursor;
      rc ref_cursor;
      begin
      d_stmt :='SELECT RG.Grade_ID,RG.Gradename_'||key||',ST.SubTopic_ID,ST.SubTopicname_'||key||', RG.CountryCode, 
      Est.ExSub_Topics_ID, Est.ExSubTopicname_'||key||' from 
      Res_Grades RG INNER JOIN Sub_Topics ST ON RG.Grade_Id= ST.Grade_ID 
      INNER JOIN ExSub_Topics Est ON ST.SubTopic_ID = Est.Sub_Topics_Id 
      Where Gradename_'||key||' = '''||gradename||'''';
      
      open rc for d_stmt;
      loop
      fetch rc into gid,gn,stid,stn,cc,estid,estn;
      if rc%found then
      dbms_output.put_line(gid||gn||stid||stn||cc||estid||estn);
      else 
      exit;
      end if;
      end loop;
      close rc;
      commit;
      end Sel_subgroup;
      the out screen result is:
      Connecting to the database rasool.
      Process exited.
      Disconnecting from the database rasool.
      can u please help me,


      thanking you,
      prakash
        • 1. Re: how to clear this type of run time error
          dba-india
          what is the ora-xxx error that you are getting? What is the database versions?
          • 2. Re: how to clear this type of run time error
            Prakash T Soundappan
            my database version is 11g XE and im using sql developer along with it.
            when im compiling i dont have any ORA errors.after running im not getting my dbms_ouput,put_line statement....
            my output looks like this
            Connecting to the database rasool.
            Process exited.
            Disconnecting from the database rasool.
            rasool is my user name

            Edited by: Prakash T Soundappan on May 27, 2013 5:03 PM

            Edited by: Prakash T Soundappan on May 27, 2013 5:04 PM
            • 3. Re: how to clear this type of run time error
              Billy~Verreynne
              Prakash T Soundappan wrote:

              when im compiling i dont have any ORA errors.after running im not getting my dbms_ouput,put_line statement....
              That is because DBMS_OUTPUT does not write to screen/display.

              DBMS_OUTPUT is a package on the server that contains a buffer (array). Writes using DBMS_OUTPUT, adds lines to this buffer.

              This buffer resides in expensive server memory. It is intended for very basic debugging and tracing. Server code writes to this buffer. After execution of that server code, the client reads and displays that server buffer. The latter part happens in SQL*Plus for example when the client setting SERVEROUTPUT is set to on.

              DBMS_OUTPUT is not a client display device. It is the wrong choice to use to display (or send) data to the client. The correct method in your case would be to return the ref cursor handle to the client. The client will then do the following (using SQL*Plus to illustrate):
              SQL> --// create host variables
              SQL> var c refcursor
              SQL> var key number
              SQL> var gradename varchar2(10)
              
              SQL> --// initialise input host variables
              SQL> exec :key := 123;
              SQL> exec :gradename := 'test';
              
              SQL> --// execute the PL/SQL code, passing host variables as bind variables
              SQL> begin MyProcedure( cursorHandle => :c, key => :key, gradeName => :gradeName ); end;
              
              
              SQL> --// process cursor output via ref cursor host variable
              SQL> print c
              • 4. Re: how to clear this type of run time error
                SamB
                It doesn't look like you are getting a runtime error. Is it possible your query is returning no rows?

                If you are sure your query should be returning rows, you probably need to check if DBMS_OUTPUT is enabled in SQL Developer (View menu I think).
                • 5. Re: how to clear this type of run time error
                  Karthick_Arp
                  when im compiling i dont have any ORA errors.after running im not getting my dbms_ouput,put_line statement....
                  Did enable serveroutput?
                  set serveroutput on
                  • 6. Re: how to clear this type of run time error
                    Frank Kulash
                    Hi, Prakash,
                    Prakash T Soundappan wrote:
                    good eve,
                    i am migrating sql server procedures to oracle procedures
                    i am trying to execute char(39) function in plsql
                    I don't see it. Point out exactly where you are using char(39).

                    In Oracle, CHAR is a datatype, but not a function. There is a function CHR, without an "A", that returns the character corresponding to a given ASCII code number:
                    CHR (39)
                    returns a 1-character string, consisting of a single-quote character.
                    is the following way correct and if this way is correct i am getting
                    run time error like
                    Please post the actual run-time error message.

                    Edited by: Frank Kulash on May 27, 2013 7:58 AM
                    Sorry, I thought I had spotted an error, but I made a mistake.
                    • 7. Re: how to clear this type of run time error
                      SamB
                      where where 123=key and 'FUBAR'=gradename?
                      If so, you want 3 single-quotes at the end of the string, not 4:
                      You sure? I think 4 is correct.
                      • 8. Re: how to clear this type of run time error
                        jeneesh
                        SELECT seems fine
                        SQL> ed
                        Wrote file afiedt.buf
                        
                          1  create or replace
                          2  procedure Sel_subgroup(key in varchar2,gradename in varchar2)
                          3  as
                          4   d_stmt varchar2(500);
                          5  begin
                          6  d_stmt :='SELECT RG.Grade_ID,RG.Gradename_'||key||',ST.SubTopic_ID,ST.SubTopicname_'||key||', RG.CountryCode,
                          7  Est.ExSub_Topics_ID, Est.ExSubTopicname_'||key||' from
                          8  Res_Grades RG INNER JOIN Sub_Topics ST ON RG.Grade_Id= ST.Grade_ID
                          9  INNER JOIN ExSub_Topics Est ON ST.SubTopic_ID = Est.Sub_Topics_Id
                         10  Where Gradename_'||key||' = '''||gradename||'''';
                         11  dbms_output.put_line(d_stmt);
                         12* end Sel_subgroup;
                        SQL> /
                        
                        Procedure created.
                        
                        SQL> set serverout on
                        SQL> exec Sel_subgroup('1','TEST');
                        SELECT RG.Grade_ID,RG.Gradename_1,ST.SubTopic_ID,ST.SubTopicname_1,
                        RG.CountryCode,
                        Est.ExSub_Topics_ID, Est.ExSubTopicname_1 from
                        Res_Grades RG
                        INNER JOIN Sub_Topics ST ON RG.Grade_Id= ST.Grade_ID
                        INNER JOIN ExSub_Topics
                        Est ON ST.SubTopic_ID = Est.Sub_Topics_Id
                        Where Gradename_1 = 'TEST'
                        
                        PL/SQL procedure successfully completed.
                        @OP: Use bind variables, that will make your life easier..
                        • 9. Re: how to clear this type of run time error
                          Prakash T Soundappan
                          to samb,
                          It doesn't look like you are getting a runtime error. Is it possible your query is returning no rows?
                          
                          If you are sure your query should be returning rows, you probably need to check if DBMS_OUTPUT is enabled in SQL Developer (View menu I think).
                          my query is returning a row
                          where where 123=key and 'FUBAR'=gradename?
                          If so, you want 3 single-quotes at the end of the string, not 4:
                          
                          You sure? I think 4 is correct.
                          i am sure 4 is correct i have tried as per jeenesh said

                          to jeenesh
                          SQL> ed
                          Wrote file afiedt.buf
                           
                            1  create or replace
                            2  procedure Sel_subgroup(key in varchar2,gradename in varchar2)
                            3  as
                            4   d_stmt varchar2(500);
                            5  begin
                            6  d_stmt :='SELECT RG.Grade_ID,RG.Gradename_'||key||',ST.SubTopic_ID,ST.SubTopicname_'||key||', RG.CountryCode,
                            7  Est.ExSub_Topics_ID, Est.ExSubTopicname_'||key||' from
                            8  Res_Grades RG INNER JOIN Sub_Topics ST ON RG.Grade_Id= ST.Grade_ID
                            9  INNER JOIN ExSub_Topics Est ON ST.SubTopic_ID = Est.Sub_Topics_Id
                           10  Where Gradename_'||key||' = '''||gradename||'''';
                           11  dbms_output.put_line(d_stmt);
                           12* end Sel_subgroup;
                          SQL> / 
                           
                          Procedure created.
                           
                          SQL> set serverout on
                          SQL> exec Sel_subgroup('1','TEST');
                          SELECT RG.Grade_ID,RG.Gradename_1,ST.SubTopic_ID,ST.SubTopicname_1,
                          RG.CountryCode,
                          Est.ExSub_Topics_ID, Est.ExSubTopicname_1 from
                          Res_Grades RG
                          INNER JOIN Sub_Topics ST ON RG.Grade_Id= ST.Grade_ID
                          INNER JOIN ExSub_Topics
                          Est ON ST.SubTopic_ID = Est.Sub_Topics_Id
                          Where Gradename_1 = 'TEST'
                           
                          PL/SQL procedure successfully completed.
                          i have also executed your code sir it is executing.in my coding,i found that the part of dbms_output.put_line in my ref cursor loop is not displaying me the print.


                          to karthick arp
                          Did enable serveroutput?
                          yes i have enabled it with buffer size 200000


                          to
                          Billy Verreynne     & Frank Kulash

                          as per you said sir i have done and got an error my code as follows
                          SQL> var rc refcursor
                          SQL> var key varchar2(2)
                          SQL> var gradename varchar2(30)
                          SQL> exec :key :='EN';
                          
                          PL/SQL procedure successfully completed.
                          
                          SQL> exec :gradename :='PRE-K';
                          
                          PL/SQL procedure successfully completed.
                          
                          SQL> begin sel_subgroup(key => :key, gradename => :gradename,rc => :rc); end;
                            2  /
                          
                          PL/SQL procedure successfully completed.
                          
                          SQL> print rc
                          ERROR:
                          ORA-24338: statement handle not executed
                          
                          
                          SP2-0625: Error printing variable "rc"
                          how to handle this error sir
                          • 10. Re: how to clear this type of run time error
                            jeneesh
                            Is the below query running fine for you? (Just the query)
                            SELECT RG.Grade_ID,RG.Gradename_1,ST.SubTopic_ID,ST.SubTopicname_1,
                            RG.CountryCode,
                            Est.ExSub_Topics_ID, Est.ExSubTopicname_1 from
                            Res_Grades RG
                            INNER JOIN Sub_Topics ST ON RG.Grade_Id= ST.Grade_ID
                            INNER JOIN ExSub_Topics
                            Est ON ST.SubTopic_ID = Est.Sub_Topics_Id
                            Where Gradename_en = 'PRE-K'
                            Can you paste your latest code?
                            • 11. Re: how to clear this type of run time error
                              Prakash T Soundappan
                              sorry jeenesh as i am a fresher i dont know it is case sensitive.i tried with 'Pre-K' instead of 'PRE-K' now my code is working.sorry for disturbing you guys
                              my code as follows....
                              create or replace 
                              procedure Sel_subgroup(key in varchar2,gradename in varchar2,rc out sys_refcursor)
                              as
                              gid res_grades.grade_id%type;
                              gn res_grades.gradename_en%type;
                              stid sub_topics.subtopic_id%type;
                              stn sub_topics.subtopicname_en%type;
                              cc res_grades.countrycode%type;
                              estid exsub_topics.exsub_topics_id%type;
                              estn exsub_topics.exsubtopicname_en%type;
                              d_stmt varchar2(500);
                              
                              begin
                              d_stmt :='SELECT RG.Grade_ID,RG.Gradename_'||key||',ST.SubTopic_ID,ST.SubTopicname_'||key||', RG.CountryCode, 
                              Est.ExSub_Topics_ID, Est.ExSubTopicname_'||key||' from 
                              Res_Grades RG INNER JOIN Sub_Topics ST ON RG.Grade_Id= ST.Grade_ID 
                              INNER JOIN ExSub_Topics Est ON ST.SubTopic_ID = Est.Sub_Topics_Id 
                              Where Gradename_'||key||' = '''||gradename||'''';
                               
                              open rc for d_stmt;
                              loop
                              fetch rc into gid,gn,stid,stn,cc,estid,estn;
                              dbms_output.put_line(gid||gn||stid||stn||cc||estid||estn); 
                              exit when rc%notfound;
                              end loop;
                              close rc;
                              commit;
                              end Sel_subgroup;
                              DECLARE
                                KEY VARCHAR2(200);
                                GRADENAME VARCHAR2(200);
                                RC sys_refcursor;
                              BEGIN
                                KEY := 'en';
                                GRADENAME := 'Pre-K';
                              
                                SEL_SUBGROUP(
                                  KEY => KEY,
                                  GRADENAME => GRADENAME,
                                  RC => RC
                                );
                                /* Legacy output: 
                              DBMS_OUTPUT.PUT_LINE('RC = ' || RC);
                              */ 
                                :RC := RC; --<-- Cursor
                              END;
                              thank you for all your replies guys

                              prakash