Re: Newbie PL/SQL question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/08/31
Message-ID: <35ed0751.29162323@192.86.155.100>#1/1

A copy of this was sent to Brian Richardson <RichaBK_at_kscgws00.ksc.nasa.gov> (if that email address didn't require changing) On Mon, 31 Aug 1998 13:56:51 -0400, you wrote:

>I am trying to put together a SELECT statement that will
>grab all the table names strarting with a certain string of characters;
>then put these names into a data structure for display to
>users on an HTML page. I've gotten as far as the following:
>
>SELECT table_name FROM user_tables WHERE table_name LIKE 'STRING%';
>
>When I execute this from SQL, I get the desired result; I can't seem to
>get
>the correct syntax for adding an INTO in order to re-route the results
>to an
>array that I can use to display the results to the user.
>
>I am using packages and Oracle 7.3.x
>
>Thanks in advance.
>
>B. Richardson

Use cursors. For example (assuming you are using the PL/SQL cartridge -- you don't mention the tool) the pl/sql code would look like:

...
 htp.uListOpen;
 for x in ( select table_name from user_tables where table_name like '%STRING' )  loop

     htp.listItem( x.table_name );
 end loop;
 htp.uListClose;
...

If you are writing PRO*C, it might look like:

  EXEC SQL DECLARE C1 cursor for
  select table_name from user_tables where table_name like '%STRING';

  EXEC SQL OPEN C1;
  for(;;)
  {

     EXEC SQL whenever NOTFOUND DO break;
     EXEC SQL FETCH C1 into :table_name;
     printf( "<li>%.*s\n", table_name.len, table_name.arr );

  }
  EXEC SQL CLOSE C1; In short tho, you've given insufficient information (like db version, OS, programming language, webserver being used, what tools you are using, how you are trying to use it and so on) to give a good answer....  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Mon Aug 31 1998 - 00:00:00 CDT