in reply to export table to text.

Please, please clean up your post. It's very hard to read. Your 'sample data' makes it worse.

This is a shot in the dark (since I can't understand you), but you might try something like this:

#!/usr/bin/perl -w
use strict;
use DBI;

# variables you need to fill in
my $database = '';
my $table    = '';
my $hostname = '';
my $port     = '';
my $user     = '';
my $password = '';

my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
my $dbh = DBI->connect($dsn, $user, $password)
    or die "DBI connect failed\n";
my $sth = $dbh->prepare("SELECT * FROM $table");
$sth->execute;

while (my $ref = $sth->fetchrow_arrayref()) {
    open(my $fh, '>>', $ref->[0].'txt')    or die "open: $!\n";
    print $fh $ref->[1];
    print $fh "\n(_KJ_)\n";
}
$dbh->disconnect;

[download]

If you're not using MySQL, you'll need to change $dsn to an appropriate data source string for the database you are using.

That would give you mail.txt, abcd.txt, etc. Your code blocks in each text file would be separated by the string (_KJ_). Can you guarantee no code block will ever include that 6-char string?

Disclaimer: code not tested. It could perhaps be made more efficient (namely hoisting the open()s using another SELECT) but I'll leave it as is for simplicity.

email: perl -e 'print reverse map { chr( ord($_)-1 ) } split //, "\x0bufo/hojsfufqAofc";'
'Under no circumstances should you program the way I say to because I say to; program the way you think expresses best what you're trying to accomplish in the program. And do so consistently and ruthlessly.' --Rob Pike

Replies are listed 'Best First'.
Re^2: export table to text.
by kirtivardhan (Initiate) on Oct 16, 2008 at 11:23 UTC

    Thanks all for helping me. ;)

    Unfortunately, I can't use DBI. Due to some constraints,
    I have to use the shell script to query the DB.

    sample output from script

    primary_only_automation_enable_KJ_create or replace procedure primary_only_a
    utomation_enable()
    begin
    -- Disable the automations that should not be running when in secondary mode
    --alter trigger group automatic_backup_system set enabled true;
    alter trigger group primary_only_triggers set e_KJ_
    profile_write_KJ_create or replace procedure profile_write(in now UTC, in s
    Char(16), in d Char(16), in j Char(16), in g Char(4), in an Char(32), in who Cha
    r(64), in val Char(32))
    begin
    write into profiler_report values( to_char(now)+' [status='+s+', journals='+j+',
    detai_KJ_
    EvalCountThresh_KJ_create or replace procedure EvalCountThresh (in Occurrenc
    eTime Integer) executable '/app/netcool/omnibus/utils/CountThreshold.pl' host 'd
    evcrb' user xxxx group xxx arguments OccurrenceTime_KJ_
    primary_only_automation_disable_KJ_create or replace procedure primary_only_
    automation_disable()
    begin
    -- Disable the automations that should not be running when in secondary mode.
    -- Requires tuning for local requirements
    alter trigger group audit_config set enabled false;
    alter trigger g_KJ_

    I want output as filenamed primary_only_automation_enable with content as

    create or replace procedure primary_only_automation_enable()
    begin
    -- Disable the automations that should not be running when in secondary mode
    --alter trigger group automatic_backup_system set enabled true;
    alter trigger group primary_only_triggers set e

    and so on

    Thanks again for help. btw Apologies for not formatting my earlier post and appreciate efforts from all to still help.

[reply]
      sample output from script primary_only_automation_enable_KJ_create or...

      Perhaps you want something like this?

      #!/usr/bin/perl -w
use strict;

my $raw_data;
open(my $in, '-|', 'sh my_shell_script.sh arg1 arg2')    or die "open(
+in): $!\n";
while (<$in>) {
    $raw_data .= $_;
}
my %pairs = split /_KJ_/, $raw_data;
for (keys %pairs) {
    open(my $out, '>>', $_)    or die "open(out): $!\n";
    print $out %pairs{$_};
}

      [download]

      Thanks for improving the formatting of your nodes. You could improve it further still by wrapping anything that's not English in code tags, like so:

      <c>$some = code($goes_here);</c>

      or:

      <c>sr.no. Name CodeBlock ...</c>

      Hint hint ;)

      email: perl -e 'print reverse map { chr( ord($_)-1 ) } split //, "\x0bufo/hojsfufqAofc";'
      'Under no circumstances should you program the way I say to because I say to; program the way you think expresses best what you're trying to accomplish in the program. And do so consistently and ruthlessly.' --Rob Pike
[reply]
[d/l]

        Hi,
        I did struggle quite a while to get this right but am again
        back to where i started from. I am still unable to get
        this work. Below is the code that I am using

        Any suggestions?

        #!/usr/bin/perl


# Initialize Server Specific Variables
$server = <STDIN>;
$user = <STDIN>;
$password = <STDIN>;
$omnihome = "/app/netcool/omnibus";

# Initialize Variables that define SQL Login Commands
$sqsh_string = "${omnihome}/bin/nco_sql.sqsh -server $server -user $us
+er -password $password";


#----------------------------------------------------------
# Script Body
#----------------------------------------------------------
#$running = `$test`;

# Initialize SQL Action Statements
$sql_select_Procedure = "select ProcedureName, SQLBlock from persist.p
+rocedures";

# Select ProcedureName and SQLBLock
&GetRawProcedureData;

my %pairs = split /_KJ_/, $raw_data;
for (keys %pairs) {
    open(my $out, '>>', $_)    or die "open(out): $!\n";
    print $out %pairs{$_};
}
#----------------------------------------------------------
# Subroutines
#----------------------------------------------------------
sub GetRawProcedureData
        {
        $raw_data = `$sqsh_string <<'EOF';
        $sql_select_Procedure
        go
        quit
        EOF`;
        }

        [download]

        The raw output from DB query.

         output from query script:
send_email_yvs_kj_
create or replace procedure send_email (in node character(1), in sever
+ity integer, in subject character(1), in email character(1), in summa
+ry character(1), in hostname character(1)) executable '$OMNIHOME/util
+s/nco_mail' host hostname user 0 group 0 arguments '\''+node+'\'', se
+verity,'\''+subject+'\'','\''+email+'\'','\''+summary+'\'';_yvs_kj_
jinsert_yvs_kj_
create or replace procedure jinsert( in serial int,  in uid int,  in t
+stamp utc,  in msg char(4080) )begin---- Procedure inserts a record i
+nto the alerts.journal table. Automations that -- require journal ent
+ries should execute this procedure.---- Usage:  --  call procedure ji
+nsert( old.Serial, %user.user_id, getdate, 'This is my journal entry'
+);--        insert into alerts.journal values (        journal_keyfie
+ld( to_int( serial ), to_int( uid ), tstamp ), -- KeyField           
+     serial,                         -- Serial                uid,   
+                         -- UID                tstamp,               
+          -- Chrono                split_multibyte(msg, 1, 255),   --
+ Text1                split_multibyte(msg, 2, 255),   -- Text2       
+         split_multibyte(msg, 3, 255),   -- Text3                spli
+t_multibyte(msg, 4, 255),   -- Text4                split_multibyte(m
+sg, 5, 255),   -- Text5                split_multibyte(msg, 6, 255), 
+  -- Text6                split_multibyte(msg, 7, 255),   -- Text7   
+             split_multibyte(msg, 8, 255),   -- Text8                
+ split_multibyte(msg, 9, 255),   -- Text9                 split_multi
+byte(msg, 10, 255),  -- Text10                split_multibyte(msg, 11
+, 255),  -- Text11                split_multibyte(msg, 12, 255),  -- 
+Text12                split_multibyte(msg, 13, 255),  -- Text13      
+          split_multibyte(msg, 14, 255),  -- Text14                sp
+lit_multibyte(msg, 15, 255),  -- Text15                split_multibyt
+e(msg, 16, 255)   -- Text16    );end_yvs_kj_
convert_severity_yvs_kj_
create or replace procedure convert_severity 
(IN omnibus_severity INTEGER, OUT tec_severity INTEGER) 
BEGIN
    if (omnibus_severity = 5)
    then
        set tec_severity = 50
    elseif (omnibus_severity = 4)
    then
        set tec_severity = 40    
    elseif (omnibus_severity = 3)
    then
        set tec_severity = 40    
    elseif (omnibus_severity = 2)
    then
        set tec_severity = 30    
    elseif (omnibus_severity = 1)
    then
        set tec_severity = 10    
    else
        set tec_severity = 10    
    end if;
END_yvs_kj_

        [download]
[reply]
[d/l]
[select]

        Hi,
        I did struggle quite a while to get this right but am again
        back to where i started from. I am still unable to get
        this work. I am warned of printing on closed file handle. I am not sure how to resolve it.
        Below is the code that I am using

        Any suggestions?

        #!/usr/bin/perl


# Initialize Server Specific Variables
$server = <STDIN>;
$user = <STDIN>;
$password = <STDIN>;
$omnihome = "/app/netcool/omnibus";

# Initialize Variables that define SQL Login Commands
$sqsh_string = "${omnihome}/bin/nco_sql.sqsh -server $server -user $us
+er -password $password";


#----------------------------------------------------------
# Script Body
#----------------------------------------------------------
#$running = `$test`;

# Initialize SQL Action Statements
$sql_select_Procedure = "select ProcedureName, SQLBlock from persist.p
+rocedures";

# Select ProcedureName and SQLBLock
&GetRawProcedureData;

%val = split(/_yvs_kj_/, $raw_data);

while (($name, $code) = each (%val))
{
        open (FILE, $name) or die ("jhand");
        print FILE $code;
        print "$name\n";
        print "$code\n";
}
close FILE;
#----------------------------------------------------------
# Subroutines
#----------------------------------------------------------
sub GetRawProcedureData
        {
        $raw_data = `$sqsh_string <<'EOF';
        $sql_select_Procedure
        go
        quit
        EOF`;
        }

        [download]

        This is the output of my sql query

         output from query script:
send_email_yvs_kj_
create or replace procedure send_email (in node character(1), in sever
+ity integer, in subject character(1), in email character(1), in summa
+ry character(1), in hostname character(1)) executable '$OMNIHOME/util
+s/nco_mail' host hostname user 0 group 0 arguments '\''+node+'\'', se
+verity,'\''+subject+'\'','\''+email+'\'','\''+summary+'\'';_yvs_kj_
jinsert_yvs_kj_
create or replace procedure jinsert( in serial int,  in uid int,  in t
+stamp utc,  in msg char(4080) )begin---- Procedure inserts a record i
+nto the alerts.journal table. Automations that -- require journal ent
+ries should execute this procedure.---- Usage:  --  call procedure ji
+nsert( old.Serial, %user.user_id, getdate, 'This is my journal entry'
+);--        insert into alerts.journal values (        journal_keyfie
+ld( to_int( serial ), to_int( uid ), tstamp ), -- KeyField           
+     serial,                         -- Serial                uid,   
+                         -- UID                tstamp,               
+          -- Chrono                split_multibyte(msg, 1, 255),   --
+ Text1                split_multibyte(msg, 2, 255),   -- Text2       
+         split_multibyte(msg, 3, 255),   -- Text3                spli
+t_multibyte(msg, 4, 255),   -- Text4                split_multibyte(m
+sg, 5, 255),   -- Text5                split_multibyte(msg, 6, 255), 
+  -- Text6                split_multibyte(msg, 7, 255),   -- Text7   
+             split_multibyte(msg, 8, 255),   -- Text8                
+ split_multibyte(msg, 9, 255),   -- Text9                 split_multi
+byte(msg, 10, 255),  -- Text10                split_multibyte(msg, 11
+, 255),  -- Text11                split_multibyte(msg, 12, 255),  -- 
+Text12                split_multibyte(msg, 13, 255),  -- Text13      
+          split_multibyte(msg, 14, 255),  -- Text14                sp
+lit_multibyte(msg, 15, 255),  -- Text15                split_multibyt
+e(msg, 16, 255)   -- Text16    );end_yvs_kj_
convert_severity_yvs_kj_
create or replace procedure convert_severity 
(IN omnibus_severity INTEGER, OUT tec_severity INTEGER) 
BEGIN
    if (omnibus_severity = 5)
    then
        set tec_severity = 50
    elseif (omnibus_severity = 4)
    then
        set tec_severity = 40    
    elseif (omnibus_severity = 3)
    then
        set tec_severity = 40    
    elseif (omnibus_severity = 2)
    then
        set tec_severity = 30    
    elseif (omnibus_severity = 1)
    then
        set tec_severity = 10    
    else
        set tec_severity = 10    
    end if;
END_yvs_kj_

        [download]
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom