Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

oracle temp table does not exist

by SpaceCowboy (Acolyte)
on Nov 29, 2021 at 20:49 UTC ( #11139245=perlquestion: print w/replies, xml ) Need Help??

SpaceCowboy has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

Perl newbie here.

I am looking to write some queries/stored procedures to return table, insert them into temp table and export that to flat file. While running, the perl program continues to throw "table or view does not exist" in runtime. I also am unable to see the temp table in my database.

Please can you help me understand what is wrong here...

# load all the libraries use DBI; use Config::IniFiles; use warnings; use strict; my $dbconfig = Config::IniFiles->new(-file => "C:/Users/Me/Documents/d +ev/perl/project1/dbcredentials.ini"); my $dbname = $dbconfig->val('myoracle','dbname'); my $driver = $dbconfig->val('myoracle','driver'); my $host = $dbconfig->val('myoracle','host'); my $sid = $dbconfig->val('myoracle','sid'); my $username = $dbconfig->val('myoracle','username'); my $password = $dbconfig->val('myoracle','password'); my $port = $dbconfig->val('myoracle','port'); my $dsn = "dbi:$driver:host=$host;sid=$sid;port=$port"; my $dbh = DBI->connect($dsn,$username,$password) or die $DBI::errstr; $dbh->do("CREATE GLOBAL TEMPORARY TABLE HR.Tempest( ID INT, NAME VARCHAR2(25), TITLE VARCHAR2(50), DEPT VARCHAR2(100) ) ON COMMMIT PRESERVE ROWS"); $dbh->commit(); my $sth = $dbh->prepare(" select e.employee_id, e.first_name, j.job_title,d. department_name + from HR.employees e, j, HR.departments d where e.job_id = j.job_id and e.department_id = d.department_id "); $sth->execute or die "cant execute SQL statement"; my @row; while (@row = $sth->fetchrow_array()){ my $id = @row[0]; my $name = @row[1]; my $title = @row[2]; my $dept = @row[3]; my $insert1 = $dbh->do(" INSERT INTO HR.Tempest ( ID, NAME, TITLE, DEPT ) VALUES ( $id, '$name', '$title', '$dept' ) "); ###$insert1->execute() or die "{ERROR} - ". $insert1->errstr; ###print"@row\n\n"; } my $resultset = $dbh->prepare("Select * from Tempest"); while(@row = $resultset->fetchrow_array()){ print"@row\n\n"; } $dbh->disconnect() or warn "error disconnecting"; exit;

Replies are listed 'Best First'.
Re: oracle temp table does not exist
by roboticus (Chancellor) on Dec 01, 2021 at 16:37 UTC


    I don't have a database server installed handy, so I can't really help directly with your question. But with the example code you listed, I just wanted to offer a couple suggestions:

    You might consider using placeholders in your prepared statements, as that is well-tested and can help you avoid injection attacks. Something like this:

    # Use ? as placeholders for the values my $sth2 = $dbh->prepare(<<EOSQL); INSERT INTO HR.Tempest ( ID, NAME, TITLE, DEPT ) VALUES ( ?, ?, ?, ? ) EOSQL while ( @row = $sth->fetchrow_array() ) { my ($id, $name, $title, $dept) = @row; # Now when executing, just pass the values you want to the execute # statement, and DBI will pass the properly-quoted values to the # DBD backend in the order specified (i.e., $id will be used for # the first ?, $name for the second, etc.) $sth2->execute( $id, $name, $title, $dept ); }

    Next, you ought to leverage the database and let it do more of the work. The database is optimized for large sets of data. The cost of serializing the result of the select and shipping it to your computer, then having your computer deserialize the data handle it and then turn it into a sequence of SQL statements, serializing each one and shipping it back to the database server (who would have to deserialize each of the statements and then process them and perform the inserts) costs *vastly* more time, CPU and network bandwidth than simply telling the database server to insert the rows into your database. Something like this:

    $dbh->do(" CREATE GLOBAL TEMPORARY TABLE HR.Tempest( ID INT, NAME VARCHAR2(25), TITLE VARCHAR2(50), DEPT VARCHAR2(100) ) ON COMMMIT PRESERVE ROWS "); $dbh->commit(); # Tell the database to select the rows of interest and populate the # temporary table with them $dbh->do(" INSERT INTO HR.Tempest SELECT e.employee_id, e.first_name, j.job_title, d.department_name + FROM HR.employees e, j, HR.departments d WHERE e.job_id = j.job_id AND e.department_id = d.department_id "); $dbh->commit();

    Finally, when working with SQL, I suggest you structure your statements slightly differently. Using the longer join syntax lets you tell the database how to connect the tables together, freeing the WHERE clause to specify which records to select. That helps future programmers more easily see what the statement is doing:

    INSERT INTO HR.Tempest INSERT INTO HR.Tempest SELECT e.employee_id, e.first_name, j.job_title, d.department_name + FROM HR.employees e JOIN j ON j.job_id = e.job_id JOIN HR.departments d ON d.department_id = e.department_id WHERE <criteria to select a subset of records>

    The more complex the statement, the easier it is to express the structure and make the SQL easier to comprehend. Here's a purely artificial example with multiple joins against tables in a single statement:

    ---- We want to find employees making over $150K/yr in marketing or sa +les. ---- However, the department_id of marketing and sales teams are split + into ---- dozens of regions, so we'll have to use the boss' department to ---- narrow the list of employees. That's fine, though, as we need th +e ---- bosses information, too. SELECT boss.employee_id as boss_id, boss.first_name as boss_ +name, boss_j.job_title as boss_title, boss_d.department_name as boss_ +dept, emp.employee_id as emp_id, emp.first_name as emp_n +ame, emp_j.job_title as emp_title, emp_d.department_name as emp_d +ept, emp.salary as emp_salary ---- Standard table hookup for the boss FROM HR.employees boss JOIN boss_j ON boss_j.job_id = boss.job_id JOIN HR.departments boss_d ON boss_d.department_id = boss_e.department +_id ---- Standard table hookup for the employee -- tie the employee to their boss JOIN HR.employees emp ON emp.boss_id = boss.employee_id JOIN emp_j ON emp_j.job_id = emp.job_id JOIN HR.departments emp_d ON emp_d.department_id = emp_e.department_id ---- Now select the employees we're looking for. WHERE boss.department_name IN ('MARKETING', 'SALES') AND emp.salary > 150000;

    Yes, it's an overly-contrived example, but it easily lets you separate "standard" table joining from the important selection criteria. When you stuff it all in a WHERE clause, it's difficult to sort it all out in your head. There's *no* difference at all to the SQL server, but it's a world of difference to the people who have to figure out what the statement says with a minimum of effort.


    When your only tool is a hammer, all problems look like your thumb.

Re: oracle temp table does not exist
by Marshall (Canon) on Nov 30, 2021 at 20:19 UTC
    I am not an expert on the DBI, but I do occasinally use it in conjunction with SQLite and less frequently MySQL. I don't know any Oracle specifics. However I have a few suggustions that are generally applicable.

    First relegate error handling to the DBI instead of using "or die" messages within your code.
    I would also turn AutoCommit on, if it is not on by default (it is in SQLite, but I don't know about Oracle).
    An anon hash can be specified in the connect, like this:

    my $dbh = DBI->connect($dsn,$username,$password,{RaiseError=>1, AutoCo +mmit=>1}) or die $DBI::errstr;
    You will not need $dbh->commit(); after a "do" - the commit will happen automatically.

    When you want an explict transaction, override the auto commit by starting a transaction explictly with $dbh->begin_work; After the work is done, then $dbh->commit; This way it is very clear within your code exactly what work is within that particular transaction.

    Use placeholders in your insert instead of continually "preparing" the statement again and again within a "do". The DB can wind up doing quite a bit of work in order to "prepare". Note that "prepares" are specific to a particular table meaning that the table name cannot be a "placeholder variable" within a prepared statement.

    my $insert1 = $dbh->prepare(" INSERT INTO HR.Tempest ( ID, NAME, TITLE, DEPT ) VALUES (?,?,?,?) "); for (...whatever...) { $insert1->execute($id,$name,$title,$dept); }

    When you say "program continues to throw "table or view does not exist" in runtime", that sounds like something is wrong with your temporary table.

    I would start by massively dumbing this thing down. Make a very simple temp table with perhaps just a single column. Put a few rows in it. Then see if you can read and print that table out. If that doesn't work and you can't make it work, then you have a simple and direct question to pose to the Monks.

    If your temp table is pretty big, then making its creation a single transaction will speed things up a lot. A main limit in DB's is the number of transactions per second. A single transaction can have millions of operations. Don't worry about this just to get your code working. After it is working, then slap a begin_work before and commit after the creation work.

    Update: Well now that I look more closely:

    my $resultset = $dbh->prepare("Select * from Tempest"); while(@row = $resultset->fetchrow_array()){ print"@row\n\n"; }
    Should be:
    my $resultset = $dbh->prepare("Select * from Hr.Tempest"); $resultset->execute(); while(@row = $resultset->fetchrow_array()){ print"@row\n\n"; }
    As it happened, a new SQL project appeared on my desk. I did follow my own advice about transactions. I thought I'd emphasize this with my following actual code. As shown below, each year's of data goes into a separate table. Each year takes about 7-8 seconds for something around 1M inserts. So for 3 years of data, program runs in less than 30 sec which is just fine. I commented out the transaction stuff with the intention of being able to quote a benchmark time difference. But the code ran all night without even processing the first directory! However I can say the difference between good transaction mgmt and none is seconds vs literally days!
    foreach my $year (sort keys %yr2dir) { create_table($dbh,$year); $dbh->begin_work; populate_table($dbh,$year,$yr2dir{$year}); $dbh->commit; }
    I chose to give the transaction a high visibility. I could have put these statements into the populate_table sub. Perhaps some Monks would do it that way? I don't know. A single transaction could be run for all 3 tables. But that would result in no gain. In my testing with large table creation, the "sweet spot" is about 250K inserts. Making a transaction bigger than that has no significant impact on execution time. For something like this I don't need any transaction rollback code. If something is wrong with one of the ~10,000 input files, I'll just fix that file and run the whole program again.

    If your turn auto commit off, you wind up having to put in explicit commit statements for relatively quick operations like create_table. My preference is leave auto commit on (and I do think that is usually the default) and explicitly override that for "important operations".

    This is off topic, but folks who work with large tables may find this interesting. With SQLite, I have found that it is faster when importing data to create an entire table, to create the table first without indices and then create the indices (if any) later. SQLite "likes" a lot of memory for index creation. You cannot vary the memory footprint of a Perl process. But you can and should vary the memory footprint of SQlite when appropriate. I have tested this and it works. Giving SQLite say an extra 500 MB of memory during index creation will speed things up a lot. When that is over, you can give that memory back to the O/S.

Re: oracle temp table does not exist
by erix (Prior) on Nov 30, 2021 at 05:04 UTC

    What oracle version?

    Does the same SQL work when given directly to the server (via SQLplus, for example)?

    Catch perl errors, DB errors; what do they say? (your code checks some but not all)

    (after the connect line, perhaps insert:

    $dbh->{RaiseError} = 1;
    to have db errors thrown in your face immediately)
Re: oracle temp table does not exist
by Anonymous Monk on Nov 30, 2021 at 20:13 UTC
    Seems that it might be that the select statement needs the table to be fully qualified. Select * from HR.Tempest
Re: oracle temp table does not exist
by Anonymous Monk on Dec 03, 2021 at 05:28 UTC

    I also am unable to see the temp table in my database.

    Well, you're not checking the CREATE table statement....


Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11139245]
Approved by Marshall
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (1)
As of 2022-05-19 03:31 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (71 votes). Check out past polls.