Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Stripped of all the irrelevancies, I think your question is simple.</p> <p>This update statement runs in SQL:</p> <pre><code>update xy set id = id + (SELECT min(id) FROM xy); </code></pre> <p>And this anonymous block also runs:</p> <pre><code>begin update xy set id = id + 100; end; </code></pre> <p>But combining the two doesn't work:</p> <pre><code>begin update xy set id = id + (SELECT min(id) FROM xy); end; </code></pre> <p>Probably you have run into a limitation of older Oracle. Prior to 9i, the SQL engine and the PL/SQL SQL engine were always out of sync. So latest features supported in SQL often weren't supported in PL/SQL. It seems like you have one of those. </p> <p>Since 9i Oracle have striven to keep the two engines in sync, so it is much rarer to find things which work in SQL but not in PL/SQL. </p> <p>Given the nature of your task, upgrading your version of Oracle is out. So all I can suggest is that you have two procedures, one which supports the sub query syntax (by avoiding the need for such subqueries. Something like this:</p> <pre><code>CREATE OR REPLACE FUNCTION xy_sqfn (main_query VARCHAR2 , sub_query VARCHAR2 ) RETURN NUMBER IS n pls_integer; BEGIN execute immediate sub_query into n; EXECUTE IMMEDIATE 'BEGIN '||main_query||'; END;' using n; RETURN 5; END; </code></pre> <p>call it like this</p> <pre><code>result := xy_sqfn ('update xy set id = id + :1' , 'SELECT min(id) FROM xy'); </code></pre> <p>Now this approach won't work for correlated sub-queries. So it you have any of them, you'll need to do something different again.</p> <hr> <p>Incidentally, using the AUTONOMOUS TRANSACTION pragma to fudge executing DML in a SELECT statement is quite horrible. Why not just run the functions in PL/SQL? Or use procedures? I suppose you'll say it doesn't matter because you're just writing some shonky code to support a data migration. Which is fair enough, but for the benefit of future seekers: <em>don't do this! It's very bad practice!</em></p>
    singulars
    1. This table or related slice is empty.
    1. POOracle 8i dynamic SQL error on subselects in pl/sql blocks
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USAPC
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you, the reason why I need select and autonomous transactions is that the data from DML statements should come to the python application that compares the result against similar ones from the PostgreSQL database. Given all the hurdles with the approach of wrapping DML statements in functions and the problem with 8i inconsistency between SQL and pl/sql engines I'm moving to anonymous pl/sql blocks per your previous answer, using host variables to collect results. Wish me luck :-)
      singulars
      1. PO
      1. USalexk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload