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

Re: [SQLQueriesNoCode] Re: select into throwing more rows fetching issue

Expand Messages
  • 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 1 of 4 , Jul 1, 2012
      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 2 of 4 , Jul 1, 2012
        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.