Re: CONNECT STRING

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 04 Sep 1998 18:08:29 GMT
Message-ID: <36212c72.191716313@192.86.155.100>

A copy of this was sent to beverett_at_remove_this.usa.net (Brian Everett) (if that email address didn't require changing) On Fri, 04 Sep 1998 16:58:02 GMT, you wrote:

>
> Greetings All!
>
> I'm attempting in PL/SQL to dynamically
> assign a connect string based on information
> about which server should be written to from
> a table of information. It seems that
> parsing the information to a string variable and
> using that in a select statement won't do the trick.
>
> i.e. select * from table || '@' || connect_string || ';'
>
> Even building the string before the select statement seems to fail.
>
> Anyone have a suggestion. It seems we HAVE to do it code.
>
> Thanks for any and all kind replies.
>
>
>Good Luck! Brian_RestonVA
>
>
>
>

To do something like this, you can use DBMS_SQL (dynamic sql). Here is a quick exmaple that takes any query and prints it out in a CSV format:

create or replace procedure csv( p_query     in varchar2,
                                 p_separator in varchar2 default ',' )
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';

begin

    dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

    for i in 1 .. 255 loop

        begin
            dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;

    end loop;

    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );

    l_status := dbms_sql.execute(l_theCursor);

    loop

        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            dbms_output.put( l_separator || l_columnValue );
            l_separator := p_separator;
        end loop;
        dbms_output.new_line;

    end loop;
    dbms_sql.close_cursor(l_theCursor); end csv;
/  

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 Fri Sep 04 1998 - 13:08:29 CDT