?
Solved

Insert with select using dblink as a variable in dbms_sql.parse

Posted on 2003-02-25
4
Medium Priority
?
1,217 Views
Last Modified: 2011-09-20
Hi

Im trying to insert with a select with a dblink name as a variable in a dbms_sql parse is this possible as I use dbms_output.put_line to display the command and it seems ok .  If I copy the insert from the put_line and execute it in sqlplus it works fine but the parse command wont run.  Here is part of the code
begin
  P1 := 'insert into test_stats ';
  P2 := '(db_name,obowner,obname,obtype,obsize,tbspace,stat_date) ';
  P3 := '(select name, owner,segment_name,segment_type,ROUND(bytes/1024/1024,2), ';
  P4 := 'tablespace_name,sysdate';
  P5 := ' from sys.v_$database@';
  P6 := ', sys.dba_SEGMENTS@';
  p7 :=')';
  open c1;
  loop
    fetch c1 into var_dlink;
    exit when c1%notfound;
      var_cid := DBMS_SQL.OPEN_CURSOR;
     dbms_output.put_line(p1||p2||p3||p4||P5||var_dlink||P6||var_dlink||')');
      DBMS_SQL.PARSE(var_cid, 'p1||p2||p3||p4||P5||var_dlink||P6||var_dlink||p7', dbms_sql.v7);
      DBMS_SQL.CLOSE_CURSOR(var_cid);
  end loop;
  close c1;

Thanks for your help
0
Comment
Question by:marmar02
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 

Accepted Solution

by:
bmeuwis earned 152 total points
ID: 8017175
I think you need to put the string you want to put in the second variable to DBMS_SQL.PARSE in another variable first. The problem you run into, I think, is that the concatenation signs will not work between the single quotes, so DBMS_SQL only sees "p1||p2..." in stead of "insert into test ..." .

First define a variable as a varchar with a sufficient length to hold your statement, then put the whole statement in there and then refer to that variable in the DBMS_SQL.PARSE-line.

So for instance do something like this :

mysql  varchar2(4000);

and then just before calling dbms_sql :

mysql := p1 || p2 || p3 || ... ;

Hope this helps !

Bart.
0
 
LVL 9

Assisted Solution

by:konektor
konektor earned 148 total points
ID: 8017208
u have only parse command, but no execute command there. use DBMS_SQL.execute command after DBMS_SQL.parse
0
 
LVL 5

Expert Comment

by:Tom Knowlton
ID: 9460080
marmar02:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area to do the following:


Split points between bmeuwis http:#8017175 and konektor http:#8017208


Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

knowlton
EE Cleanup Volunteer
0
 

Expert Comment

by:YensidMod
ID: 9939120
Forced Accept

Yensidmod
EE Moderator
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question