Loading ...
Sorry, an error occurred while loading the content.

select into throwing more rows fetching issue

Expand Messages
  • ksekha
    Hi all, thanks for accepting me in the group.. my query is below, for x in (select table_name from all_tab_cols where owner = xxx ) loop select column_name
    Message 1 of 4 , Jun 28, 2012
    • 0 Attachment
      Hi all,

      thanks for accepting me in the group.. my query is below,

      for x in (select table_name from all_tab_cols where owner = 'xxx')
      loop

      select column_name from all_tab_cols where table_name = x.table_name

      end loop;

      Issue: When I run this query in TOAD i got message that "query is expecting INTO in second query above..

      so I did like this,

      vcolumn varchar(32000)

      for x in (select table_name from all_tab_cols where owner = 'xxx')
      loop

      select column_name INTO vcolumn from all_tab_cols where table_name = x.table_name

      end loop;

      now I get the error that.. ORA-01422: exact fetch returns more than requested number of rows

      COULD YOU PLEASE HELP.. thanks much
    • chetan
      ... ===================================== The error means your statement select column_name from all_tab_cols where table_name = x.table_name is fetching
      Message 2 of 4 , Jul 1, 2012
      • 0 Attachment
        --- In SQLQueriesNoCode@yahoogroups.com, "ksekha" <kharkasi@...> wrote:
        >
        > Hi all,
        >
        > thanks for accepting me in the group.. my query is below,
        >
        > for x in (select table_name from all_tab_cols where owner = 'xxx')
        > loop
        >
        > select column_name from all_tab_cols where table_name = x.table_name
        >
        > end loop;
        >
        > Issue: When I run this query in TOAD i got message that "query is expecting INTO in second query above..
        >
        > so I did like this,
        >
        > vcolumn varchar(32000)
        >
        > for x in (select table_name from all_tab_cols where owner = 'xxx')
        > loop
        >
        > select column_name INTO vcolumn from all_tab_cols where table_name = x.table_name
        >
        > end loop;
        >
        > now I get the error that.. ORA-01422: exact fetch returns more than requested number of rows
        >
        > COULD YOU PLEASE HELP.. thanks much
        >


        =====================================

        The error means your statement "select column_name from all_tab_cols where table_name = x.table_name" is fetching multiple column_names pertaining to table_name.

        You need to create a cursor to store this and fetch using another loop.

        Regards n Best Wishes
        Chetan
      • kharkasi@yahoo.com
        Thanks much Chetna , could you the code how to create cursor Sent from my iPhone ... [Non-text portions of this message have been removed]
        Message 3 of 4 , Jul 1, 2012
        • 0 Attachment
          Thanks much Chetna , could you the code how to create cursor

          Sent from my iPhone

          On Jul 1, 2012, at 12:04 PM, "chetan" <caj_y@...> wrote:

          >
          >
          > --- In SQLQueriesNoCode@yahoogroups.com, "ksekha" <kharkasi@...> wrote:
          > >
          > > Hi all,
          > >
          > > thanks for accepting me in the group.. my query is below,
          > >
          > > for x in (select table_name from all_tab_cols where owner = 'xxx')
          > > loop
          > >
          > > select column_name from all_tab_cols where table_name = x.table_name
          > >
          > > end loop;
          > >
          > > Issue: When I run this query in TOAD i got message that "query is expecting INTO in second query above..
          > >
          > > so I did like this,
          > >
          > > vcolumn varchar(32000)
          > >
          > > for x in (select table_name from all_tab_cols where owner = 'xxx')
          > > loop
          > >
          > > select column_name INTO vcolumn from all_tab_cols where table_name = x.table_name
          > >
          > > end loop;
          > >
          > > now I get the error that.. ORA-01422: exact fetch returns more than requested number of rows
          > >
          > > COULD YOU PLEASE HELP.. thanks much
          > >
          >
          > =====================================
          >
          > The error means your statement "select column_name from all_tab_cols where table_name = x.table_name" is fetching multiple column_names pertaining to table_name.
          >
          > You need to create a cursor to store this and fetch using another loop.
          >
          > Regards n Best Wishes
          > Chetan
          >
          >
          >
          >
          >
          >
          >

          [Non-text portions of this message have been removed]
        • John Warner
          Might want to repost this to an Oracle list instead of an SQL list since your issue is specific to PL-SQL. John Warner ... expecting ... where ... to
          Message 4 of 4 , Jul 1, 2012
          • 0 Attachment
            Might want to repost this to an Oracle list instead of an SQL list since
            your issue is specific to PL-SQL.

            John Warner


            > -----Original Message-----
            > From: SQLQueriesNoCode@yahoogroups.com
            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of chetan
            > Sent: Sunday, July 01, 2012 1:05 PM
            > To: SQLQueriesNoCode@yahoogroups.com
            > Subject: [SQLQueriesNoCode] Re: select into throwing more rows fetching
            > issue
            >
            >
            >
            > --- In SQLQueriesNoCode@yahoogroups.com, "ksekha" <kharkasi@...> wrote:
            > >
            > > Hi all,
            > >
            > > thanks for accepting me in the group.. my query is below,
            > >
            > > for x in (select table_name from all_tab_cols where owner = 'xxx')
            > > loop
            > >
            > > select column_name from all_tab_cols where table_name = x.table_name
            > >
            > > end loop;
            > >
            > > Issue: When I run this query in TOAD i got message that "query is
            expecting
            > INTO in second query above..
            > >
            > > so I did like this,
            > >
            > > vcolumn varchar(32000)
            > >
            > > for x in (select table_name from all_tab_cols where owner = 'xxx')
            > > loop
            > >
            > > select column_name INTO vcolumn from all_tab_cols where table_name =
            > x.table_name
            > >
            > > end loop;
            > >
            > > now I get the error that.. ORA-01422: exact fetch returns more than
            > requested number of rows
            > >
            > > COULD YOU PLEASE HELP.. thanks much
            > >
            >
            >
            > =====================================
            >
            > The error means your statement "select column_name from all_tab_cols
            where
            > table_name = x.table_name" is fetching multiple column_names pertaining
            to
            > table_name.
            >
            > You need to create a cursor to store this and fetch using another loop.
            >
            > Regards n Best Wishes
            > Chetan
            >
            >
            >
            >
            > ------------------------------------
            >
            > Yahoo! Groups Links
            >
            >
            >
          Your message has been successfully submitted and would be delivered to recipients shortly.