Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Location
    McLean, VA -- USA
    Posts
    10

    Unanswered: PL/SQL variable concatenation ???

    Hi all, I am kind of new at PL/SQL. I hope someone knows the answer to this.

    I am trying to run an SQL statement that creates a CURSOR. This SQL statement will change the database link (I am linking to different DBs to get information from all) every time the LOOP comes arround.

    cursor new_stats_cursor is select * from sys.view_stats@vrec_newconnection.db_link;

    The "vrec_newconnection.db_link" is a variable that contains the DB Link name. The error that I get is: "06/12/2003 10:33 AM Error: ORA-04054: database link VREC_NEWCONNECTION.DB_LINK does not exist
    "
    do I have to declare the variable in a different way?
    do I have to use a different sintax, other thatn "table_name@variable_containing_db_link"?

    If you have any coments they will be welcome.

    Thank you,

    Paul Velit

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here's an example of dynamic SQL... You fill in the variables and
    "execute immediate" ... This code disconnects users from the database

    declare
    c_sid number;
    c_serial number;
    sql_stmt varchar2(300);
    v_jobs number;

    cursor get_users is
    select sid,serial#
    from v$session
    where username is not null
    and username not in ('SYS','SYSTEM')
    and username not like ('ORA%');
    begin

    open get_users;
    fetch get_users into c_sid,c_serial;
    loop
    exit when get_users%notfound;

    sql_stmt := 'alter system disconnect session '||''''||c_sid||','||c_serial||''''||' immediate';
    EXECUTE IMMEDIATE sql_stmt;
    fetch get_users into c_sid,c_serial;
    end loop;
    close get_users;
    end;
    /



    Gregg

  3. #3
    Join Date
    Jun 2003
    Location
    McLean, VA -- USA
    Posts
    10

    Red face

    Thank you Gregg for your prompt response. However, I still can't make it work.

    Here is a part of my code see if you can figure it out. The errors comes when trying to read the second cursor (new_stats_cursor). The DB link is strored in the variable "vrec_newconnection" (rowtype). Looks like Oracle can't read the variable.

    Is it a syntax problem?


    PROCEDURE TEST_STATS IS

    cursor new_connection_cursor is select * from db_names;
    vrec_newconnection new_connection_cursor%rowtype;

    BEGIN

    open new_connection_cursor;
    loop
    fetch new_connection_cursor into vrec_newconnection;
    exit when new_connection_cursor%notfound;

    declare
    cursor new_stats_cursor is select * from sys.view_stats@vrec_newconnection.db_link;
    vrec_newstats new_stats_cursor%rowtype;
    v_instance varchar2(15) := vrec_newconnection.db_name;
    BEGIN
    open new_stats_cursor;
    loop
    fetch new_stats_cursor into vrec_newstats;
    exit when new_stats_cursor%notfound;
    .
    .
    .
    .
    .

  4. #4
    Join Date
    Jun 2003
    Location
    McLean, VA -- USA
    Posts
    10

    Red face

    Thank you Gregg for your prompt response. However, I still can't make it work.

    Here is a part of my code see if you can figure it out. The errors comes when trying to read the second cursor (new_stats_cursor). The DB link is strored in the variable "vrec_newconnection" (rowtype). Looks like Oracle can't read the variable.

    Is it a syntax problem?


    PROCEDURE TEST_STATS IS

    cursor new_connection_cursor is select * from db_names;
    vrec_newconnection new_connection_cursor%rowtype;

    BEGIN

    open new_connection_cursor;
    loop
    fetch new_connection_cursor into vrec_newconnection;
    exit when new_connection_cursor%notfound;

    declare
    cursor new_stats_cursor is select * from sys.view_stats@vrec_newconnection.db_link;
    vrec_newstats new_stats_cursor%rowtype;
    v_instance varchar2(15) := vrec_newconnection.db_name;
    BEGIN
    open new_stats_cursor;
    loop
    fetch new_stats_cursor into vrec_newstats;
    exit when new_stats_cursor%notfound;
    .
    .
    .
    .
    .

  5. #5
    Join Date
    Jun 2003
    Location
    McLean, VA -- USA
    Posts
    10

    Red face

    Thank you Gregg for your prompt response. However, I still can't make it work.

    Here is a part of my code see if you can figure it out. The errors comes when trying to read the second cursor (new_stats_cursor). The DB link is strored in the variable "vrec_newconnection" (rowtype). Looks like Oracle can't read the variable.

    Is it a syntax problem?


    PROCEDURE TEST_STATS IS

    cursor new_connection_cursor is select * from db_names;
    vrec_newconnection new_connection_cursor%rowtype;

    BEGIN

    open new_connection_cursor;
    loop
    fetch new_connection_cursor into vrec_newconnection;
    exit when new_connection_cursor%notfound;

    declare
    cursor new_stats_cursor is select * from sys.view_stats@vrec_newconnection.db_link;
    vrec_newstats new_stats_cursor%rowtype;
    v_instance varchar2(15) := vrec_newconnection.db_name;
    BEGIN
    open new_stats_cursor;
    loop
    fetch new_stats_cursor into vrec_newstats;
    exit when new_stats_cursor%notfound;
    .
    .
    .
    .
    .

  6. #6
    Join Date
    Jun 2003
    Location
    McLean, VA -- USA
    Posts
    10
    OOPs!! Unintentional 3 times posted the same thing.

  7. #7
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: PL/SQL variable concatenation ???

    The syntax error from your previous post was the use of DECLARE in a named procedure. Declare can only be used in anonymous blocks. Here is what I think you are trying to do. This is somewhat complicated but should be able to do the trick. I haven't tested it though.

    This procedure uses the DBMS_SQL package to execute dynamic sql statements.

    Create Or Replace Procedure dynamic_cursors
    Is

    Cursor c_instances is
    Select db_link
    From DB_Names;

    lv_cursor_id PLS_INTEGER;
    lv_sql_text VARCHAR2(200);
    lv_row_count PLS_INTEGER := 0;

    lv_view_stat view_stats%rowtype;

    Begin

    For instance_name in c_instances Loop

    -- Open a cursor to be used
    lv_cursor_id := DBMS_SQL.OPEN_CURSOR;

    -- Set sql statement ONLY SELECT THOSE COLUMNS YOU NEED
    lv_sql_text := 'select COLUMN1,COLUMN2 '||
    'from sys.view_stats@'||instance_name.db_link;

    --Parse DBMS_SQL statement
    DBMS_SQL.PARSE(lv_cursor_id,lv_sql_text,DBMS_SQL.N ATIVE);

    -- Define column for each column in your select statement.
    DBMS_SQL.DEFINE_COLUMN(lv_cursor_id,1,lv_view_stat .COLUMN1);
    DBMS_SQL.DEFINE_COLUMN(lv_cursor_id,2,lv_view_stat .COLUMN2);

    lv_row_count := DBMS_SQL.EXECUTE(lv_cursor_id);

    LOOP

    EXIT WHEN DBMS_SQL.FETCH_ROWS(lv_cursor_id) = 0;

    -- Set columns equal to values out of cursor
    DBMS_SQL.COLUMN_VALUE(lv_cursor_id,1,lv_view_stat. COLUMN1);
    DBMS_SQL.COLUMN_VALUE(lv_cursor_id,2,lv_view_stat. COLUMN2);

    DBMS_OUTPUT.PUT_LINE('View Stats '||COLUMN1||' '||COLUMN2);

    END LOOP;

    -- Close DBMS cursor
    DBMS_SQL.CLOSE_CURSOR(lv_cursor_id);

    End Loop;

    Exception
    When others then
    DBMS_OUTPUT.PUT_LINE(sqlerrm);
    End dynamic_cursors;

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I would try something like this ... you may have to play a little with
    the concatenation....

    Gregg

    create or replace PROCEDURE TEST_STATS as
    v_instance varchar2(15) ;
    vrec_newstats new_stats_cursor%rowtype;
    vrec_newconnection new_connection_cursor%rowtype;

    cursor new_connection_cursor is select * from db_names;

    cursor new_stats_cursor is select * from sys.view_stats@v_instance;

    BEGIN

    open new_connection_cursor;
    loop
    fetch new_connection_cursor into vrec_newconnection;
    exit when new_connection_cursor%notfound;
    v_instance := vrec_newconnection||'db_name';
    open new_stats_cursor;
    loop
    fetch new_stats_cursor into vrec_newstats;
    exit when new_stats_cursor%notfound;
    .......
    end loop;
    end loop;

  9. #9
    Join Date
    Jun 2003
    Location
    McLean, VA -- USA
    Posts
    10
    Thank you guys for your help, I will try it.

    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •