Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1239
  • Last Modified:

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

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
marmar02
Asked:
marmar02
2 Solutions
 
bmeuwisCommented:
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
 
konektorCommented:
u have only parse command, but no execute command there. use DBMS_SQL.execute command after DBMS_SQL.parse
0
 
Tom KnowltonWeb developerCommented:
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
 
YensidModCommented:
Forced Accept

Yensidmod
EE Moderator
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now