Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I think <code>%ROWTYPE</code> is a dead-end here. As far as I know there is way to extract useful metadata about a PL/SQL variable.</p> <p>But things are different if you can use an abstract data type (ADT, or "object"). It is more powerful, and kind of similar to <code>%ROWTYPE</code>. But it is not quite as convenient, and will make your initial code a little more complicated. You will have to pre-define the objects, and use them in your SQL.</p> <p>For example, replace code like this:</p> <pre><code>declare v_test tbl%rowtype; begin select * into v_test from tbl; end; / </code></pre> <p>with this:</p> <pre><code>declare v_test2 tbl_type; begin select tbl_type(msrp, some_other_column) into v_test2 from tbl; end; / </code></pre> <p>If that is acceptable, you can use dynamic PL/SQL for your updates:</p> <pre><code>--Create table, ADT, and test data create table tbl(MSRP varchar2(100), some_other_column varchar2(100)); create or replace type tbl_type as object ( msrp varchar2(100), some_other_column varchar2(100) ); / insert into tbl values('1', '1'); --Convert object to ANYDATA, process with dynamic PL/SQL declare my_tbl tbl_type := tbl_type('2', '3'); procedure UpdateByColumn(p_anydata in anydata, colName in varchar2) is v_typename varchar2(30) := p_anydata.getTypeName; begin execute immediate ' declare v_anydata2 anydata := :anydata; v_object '||v_typename||'; v_dummy pls_integer; begin v_dummy := v_anydata2.getObject(v_object); update tbl set '||colName||' = v_object.'||colName||'; end; ' using p_anydata; end; begin updateByColumn(anyData.convertObject(my_tbl), 'MSRP'); end; / --Show the new data select * from tbl; MSRP SOME_OTHER_COLUMN ---- ----------------- 2 1 </code></pre> <p><strong>UPDATE</strong></p> <p><code>%ROWTYPE</code> only exists in PL/SQL, there is no way to pass or convert those values. But you can store the record as a package variable, and then pass the name and type of that variable to your function. The function can refer to the record using dynamic PL/SQL, and then can convert it to a value to be used by the SQL.</p> <p>(This does not address the issue of changing multiple columns at once, it is just a demo showing a way to dynamically use a <code>%ROWTYPE</code>.)</p> <pre><code>--Create table and test data create table tbl(MSRP varchar2(100), some_other_column varchar2(100)); insert into tbl values('1', '1'); commit; --Create another table, tbl2, that will be used to update tbl --(The tables in this example have similar columns, but that is not --actually necessary.) create table tbl2(MSRP varchar2(100), some_other_column varchar2(100)); insert into tbl2 values('2', '2'); commit; --New function works by passing in names of global variables and --their types, instead of actual values. create or replace procedure UpdateByColumn( p_package_and_variable_name in varchar2, p_rowtype in varchar2, colName in varchar2) is begin execute immediate ' declare v_rec '||p_rowtype||' := '||p_package_and_variable_name||'; begin update tbl set '||colName||' = v_rec.'||colName||'; end; '; end; / --A test package that calls the function to update tbl. create or replace package test_package is tbl2_rec tbl2%rowtype; procedure test_procedure; end; / create or replace package body test_package is procedure test_procedure is begin select * into tbl2_rec from tbl2; UpdateByColumn('test_package.tbl2_rec', 'tbl2%rowtype', 'MSRP'); end; end; / begin test_package.test_procedure; end; / </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POOracle PL/SQL Dynamic SQL not so dynamic?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJon Heller
    1. USJon Heller
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POOracle PL/SQL Dynamic SQL not so dynamic?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat would the common update routine's parameter list? Wouldn't you also need to "hard code" the specific type? e.g. Create or replace Procedure UpdateByColumn(rec tbl_type, colName varchar2) is... Thus each type would require a separate similar looking routine?
      singulars
      1. PO
      1. USdreamfly
    2. COYou're right. I updated the code to use ANYDATA, so only one procedure is needed. The new code is based on: http://www.oracle-developer.net/display.php?id=218#1c1f6
      singulars
      1. PO
      1. USJon Heller
    3. COThe 'using p_anydata' will also be a problem because the number of columns to update may vary. Thus binding is not possible in native dynamic sql. My final implementation consists of 2 arrays, one is an associative array of ANYDATA where I loaded all data from a rowtype. The other is a list of columns to update. The associative is indexed by column Names so I can refer to the actual data by column name. This way I can even do binding using DBMS_SQL. It's rather disappointing that there's still no way to refer to a rowtype data by literal like rowdata('colname') where rowdata is %rowtype
      singulars
      1. PO
      1. USdreamfly
 

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