1 2 Previous Next 19 Replies Latest reply: Jan 16, 2014 6:50 AM by hemu RSS

    pipe row

    hemu

      hi

      i have done following steps

      step 1

      CREATE OR REPLACE TYPE  address_type as object
      (
        ad1      varchar2(50),
        ad2      varchar2(50),
        ad3      varchar2(50),
        city      varchar2(20),
        pin      varchar2(10)
      );
      

       

      step 2

      CREATE OR REPLACE TYPE  H_address is table of address_type;
      

       

      step 3

       create or replace function address_details
       (
       brcode in NUMBER,
       prd in varchar2)
       return sys_refcursor
       is
       x_cursor   sys_refcursor;
       vquery varchar2(4000);
       begin
       vquery:='SELECT ADDR1,ADDR2,addr3,citycd,pincd from d010054 where lbrcode='||brcode||' and prdacctid='||prd ||' and addrtype=1';
       open x_cursor for
       vquery;
       return x_cursor;
       IF (x_cursor%NOTFOUND) THEN
             vquery:='select add1,add2,add3,citycd,pincode from d009011 where custno='||t_cust(brcode,prd);
          open x_cursor for vquery;
       END IF;
       return x_cursor;
       end address_details;
      

      function is created without any error

      step 4

      create or replace function address_pipe_function (i_cursor sys_refcursor)
         return h_address pipelined is
         vapxrec address_type:=address_type(null,null,null,null,null);
       begin
          loop
          fetch i_cursor into
          vapxrec.ad1,
          vapxrec.ad2,
          vapxrec.ad3,
          vapxrec.city,
          vapxrec.pin;
          exit when i_cursor%notfound;
          pipe row (vapxrec);
          end loop;
       close i_cursor;
       return;
       end;
      

      function created without error

      then i tried the following

      SQL> select * from

        2  table(address_pipe_function(address_details(2,'SB      000000000003055300000000')));

      table(address_pipe_function(address_details(2,'SB      000000000003055300000000')))

                                  *

      ERROR at line 2:

      ORA-00933: SQL command not properly ended

      ORA-06512: at "RECORD.ADDRESS_DETAILS", line 11

      i am working on 11g

      please guide

        • 1. Re: pipe row
          BluShadow

          hemu wrote:

           

          hi

          i have done following steps

          step 1

          CREATE OR REPLACE TYPE  address_type as object
          (
            ad1      varchar2(50),
            ad2      varchar2(50),
            ad3      varchar2(50),
            city      varchar2(20),
            pin      varchar2(10)
          );

           

          step 2

          CREATE OR REPLACE TYPE  H_address is table of address_type;

           

          step 3

           create or replace function address_details
           (
           brcode in NUMBER,
           prd in varchar2)
           return sys_refcursor
           is
           x_cursor   sys_refcursor;
           vquery varchar2(4000);
           begin
           vquery:='SELECT ADDR1,ADDR2,addr3,citycd,pincd from d010054 where lbrcode='||brcode||' and prdacctid='||prd ||' and addrtype=1';
           open x_cursor for
           vquery;
           return x_cursor;
           IF (x_cursor%NOTFOUND) THEN
                 vquery:='select add1,add2,add3,citycd,pincode from d009011 where custno='||t_cust(brcode,prd);
              open x_cursor for vquery;
           END IF;
           return x_cursor;
           end address_details;

          function is created without any error

          step 4

          create or replace function address_pipe_function (i_cursor sys_refcursor)
             return h_address pipelined is
             vapxrec address_type:=address_type(null,null,null,null,null);
           begin
              loop
              fetch i_cursor into
              vapxrec.ad1,
              vapxrec.ad2,
              vapxrec.ad3,
              vapxrec.city,
              vapxrec.pin;
              exit when i_cursor%notfound;
              pipe row (vapxrec);
              end loop;
           close i_cursor;
           return;
           end;

          function created without error

          then i tried the following

          SQL> select * from

            2  table(address_pipe_function(address_details(2,'SB      000000000003055300000000')));

          table(address_pipe_function(address_details(2,'SB      000000000003055300000000')))

                                      *

          ERROR at line 2:

          ORA-00933: SQL command not properly ended

          ORA-06512: at "RECORD.ADDRESS_DETAILS", line 11

          i am working on 11g

          please guide

           

          Your first function "address_details" won't work correctly.

          You open the cursor and return it, and then have more code after the return statement.  That code after the return statement will never be reached.

          You also need to bear in mind that just because you've opened a cursor doesn't mean you can test to see how many rows it will return.  An opened cursor always returns 0 for the rowcount immediately after opening.  rowcount reports the number of fetched rows, and at that point, no rows have been fetched.

           

          As for your error, that's another matter, but first you need to get your ref cursor creation fixed

          • 2. Re: pipe row
            Sudhakar_B

            Hemu,

            You are getting the error

            ORA-00933: SQL command not properly ended

            ORA-06512: at "RECORD.ADDRESS_DETAILS", line 11, At line 11

            which is the open cursor for the dynamic SQL shown below.

            1. vquery:='SELECT ADDR1,ADDR2,addr3,citycd,pincd from d010054 where lbrcode='||brcode||' and prdacctid='||prd ||' and addrtype=1';

             

            Note that you are calling (address_details(2,'SB      000000000003055300000000')) and passing 'SB      000000000003055300000000'as parameter to prd.

            prd variable is JUST SUBSTITUTED in the SQL

            You NEED additional single quotes lie shown below in RED. This will enclose 'SB      000000000003055300000000' in quotes when SQL is formed.

            1. vquery:='SELECT ADDR1,ADDR2,addr3,citycd,pincd from d010054 where lbrcode='||brcode||' and prdacctid='''||prd ||''' and addrtype=1';

             

            Hope this helps.

             

            Also Please correct the logic flow as described by BluShadow.

            • 3. Re: pipe row
              BluShadow

              Sudhakar_B wrote:

               

              Hemu,

              You are getting the error

              ORA-00933: SQL command not properly ended

              ORA-06512: at "RECORD.ADDRESS_DETAILS", line 11, At line 11

              which is the open cursor for the dynamic SQL shown below.

              1. vquery:='SELECT ADDR1,ADDR2,addr3,citycd,pincd from d010054 where lbrcode='||brcode||' and prdacctid='||prd ||' and addrtype=1';

               

              Note that you are calling (address_details(2,'SB      000000000003055300000000')) and passing 'SB      000000000003055300000000'as parameter to prd.

              prd variable is JUST SUBSTITUTED in the SQL

              You NEED additional single quotes lie shown below in RED. This will enclose 'SB      000000000003055300000000' in quotes when SQL is formed.

              1. vquery:='SELECT ADDR1,ADDR2,addr3,citycd,pincd from d010054 where lbrcode='||brcode||' and prdacctid='''||prd ||''' and addrtype=1';

               

              Hope this helps.

               

              Also Please correct the logic flow as described by BluShadow.

               

              No, he doesn't need extra quotes, he needs to correctly use bind variables...

               

              create or replace function address_details (brcode in NUMBER, 

                                                          prd in varchar2

                                                         ) return sys_refcursor is 

                x_cursor   sys_refcursor; 

                vquery varchar2(4000); 

              begin 

                vquery:='SELECT ADDR1,ADDR2,addr3,citycd,pincd from d010054 where lbrcode=:1 and prdacctid=:2 and addrtype=:3';

                open x_cursor for vquery using brcode, prd, 1; 

                return x_cursor;

              ..

              ..

               

              and then there's no need to worry about the datatypes as it's all handled by PL/SQL properly.

              • 4. Re: pipe row
                Sudhakar_B

                Absolutely!!!

                Need to use BIND VARIABLEs.

                 

                Thanks.

                • 5. Re: pipe row
                  hemu

                  sir

                  how would i know whether the rows are returned or not from a cursor?

                  • 6. Re: pipe row
                    rp0428
                    how would i know whether the rows are returned or not from a cursor?

                    You have to FETCH the cursor and see if you get a row.

                     

                    A CURSOR doesn't have any rows - it is just the means you use to FETCH any rows that do exist.

                    • 7. Re: pipe row
                      AnnPricks E

                      If you want to check whether that query returns any rows or not then you can simply get the count and based on that you can modified the query.. Try the below.

                      CREATE OR REPLACE FUNCTION address_details

                      (

                      brcode IN NUMBER,

                      prd IN varchar2)

                      RETURN sys_refcursor

                      IS

                      x_cursor   sys_refcursor;

                      v_cnt NUMBER(10);

                      vquery VARCHAR2(4000);

                      begin

                      SELECT COUNT(1) INTO v_cnt

                      FROM d010054

                      WHERE lbrcode=brcode

                      AND prdacctid=prd

                      AND addrtype=1;

                      IF v_cnt > 0 THEN

                        vquery:='SELECT ADDR1,ADDR2,addr3,citycd,pincd from d010054 where lbrcode='||brcode||' and prdacctid='''||prd ||''' and addrtype=1';

                      ELSE

                        vquery:='select add1,add2,add3,citycd,pincode from d009011 where custno='||t_cust(brcode,prd);

                      END IF;

                        OPEN x_cursor FOR vquery;

                        RETURN x_cursor;

                      END address_details;

                      • 8. Re: pipe row
                        kendenny

                        Why use dynamic SQL to open your ref cursor?

                        create or replace function address_details (brcode in NUMBER, 
                                                                    prd in varchar2
                                                                  ) return sys_refcursor is
                          x_cursor  sys_refcursor; 
                          vquery varchar2(4000); 
                        begin 
                          open x_cursor for
                            SELECT ADDR1,ADDR2,addr3,citycd,pincd from d010054
                            where lbrcode=brcode and prdacctid=prd and addrtype=1; 
                          return x_cursor;

                        • 9. Re: pipe row
                          rp0428
                          If you want to check whether that query returns any rows or not then you can simply get the count and based on that you can modified the query..

                          No - that is NOT correct!

                           

                          A query and a CURSOR are two different things. Two queries are two different things.,

                           

                          Just because the query returns some number of rows when you execute it does NOT mean that the CURSOR you open will fetch the same number of rows when you fetch the cursor.

                           

                          Other users could be adding/deleting/updating rows and issue a commit between the time your first query begins execution and the time the CURSOR is opened.

                          • 10. Re: pipe row
                            AnnPricks E

                            What i understand from his question is,

                              He has two different query and he wants to check whether first query returns any rows or not. Based on that he wants to open the cursor with second query..

                            Like,

                               If 'SELECT ADDR1,ADDR2,addr3,citycd,pincd from d010054 where lbrcode='||brcode||' and prdacctid='''||prd ||''' this returns rows then he wants to open the cursor with this query otherwise he wants to open the cursor with other query(select add1,add2,add3,citycd,pincode from d009011 where custno='||t_cust(brcode,prd)). That is what i suggest above solution.


                            If i understood wrongly correct me

                            • 11. Re: pipe row
                              rp0428
                              He has two different query and he wants to check whether first query returns any rows or not. Based on that he wants to open the cursor with second query..

                              Yes - and if the first query returns rows he wants to open a cursor on that first query.

                               

                              So? How does that impact any of what I said?

                              Just because the query returns some number of rows when you execute it does NOT mean that the CURSOR you open will fetch the same number of rows when you fetch the cursor.

                              No matter what that first query returns a CURSOR might still return a different number of rows.

                               

                              So OP could execute the first query, find that it returns no rows and open a cursor for the second query. If you now execute that first query again it may now return rows even though it didn't before.

                               

                              Same with the second query - there is no way to know if opening the CURSOR for the second query will result in any rows being available to be fetched.

                               

                              Opening a CURSOR simply 'freezes' the SCN at which the data will be made available. Only data that was committed prior to the opening of the cursor will be part of the result set.

                              • 12. Re: pipe row
                                hemu

                                thanks sir

                                • 13. Re: pipe row
                                  hemu

                                  you ar not wrong sir

                                  but rp has given a rightful insight

                                  fortunately i not working on production database but on a staging database

                                  however i should be devising my queries as if i am working on production.

                                  thanks

                                  • 14. Re: pipe row
                                    rp0428

                                    I probably should have mentioned that sometimes it can be efficient to determine if a query will return any rows especially for simple queries with no ORDER BY or aggregate functions or if indexes can be used to determine that at least one row exists.

                                     

                                    Also any test query can safely uses a limiting predicate such as 'WHERE ROWNUM = 1' or a FIRST_ROWS hint since OP didn't say they needed to know how many rows - just if even one would be returned.

                                    1 2 Previous Next