2

So I have a need to be able to take a ROWTYPE argument and serialize it to a string. I know that there is a DBMS package to serialize to XML but I want to instead simply serialize it (if possible) to a name/value pair (e.g., colname1=colval1,colname2=colval2,...).

So I have created a very simple function where I'd basically pass in a row and this method would be able to parse that and serialize it. 

CREATE OR REPLACE FUNCTION "A1CLIENT01"."SERIALIZE_TP"(AMyTabRec MyTable%ROWTYPE) RETURN VARCHAR2
is
begin
  ... work some magic here...

  return theMagicString;
end;

What I thought would be easy is not possible (it seems), but I was thinking something like the following:

CURSOR MyTabCur IS
  SELECT COLUMN_NAME INTO lCurrColumn
  FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME = 'MyTable';

FOR a_mytab_rec IN MyTabCur 
LOOP
  theMagicString := theMagicString || lCurrColumn || '=' || AMyTabRec(lCurrColumn) || ',';
END LOOP;

But this of course does not work as I can't refefence the ROWTYPE by an numeric index, let alone by name. Any ideas how I could go about it in a different idea? Ideally I don't want to have to change this serialize method whenever I add a column to the table (i.e., should be "generic").

nathanchere
  • 8,008
  • 15
  • 65
  • 86
Dave LeJeune
  • 407
  • 5
  • 13
  • 2
    I'm *pretty* sure there is no way with a %ROWTYPE to do what you're asking. You have to specify all the attributes explicitly. But I won't answer that because I'm not sure. – Jeffrey Kemp Oct 15 '13 at 03:57
  • @JeffreyKemp `%rowtype` is a PL/SQL [record](http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/rowtype_attribute.htm) type and PL/SQL doesn't support [reflection](http://stackoverflow.com/q/37628/272735) (AFAIK). This is different for PL/SQL and SQL. So yes, all the attributes have to be specified explicitly. A very verbose but also a very expicit. – user272735 Oct 15 '13 at 05:24

1 Answers1

0

loop over the query on USER_TAB_COLUMNS and then try

execute immediate 'begin :1 := aMyTabRec.'||myTabCur.column_name||'; end;'
  using out theMagicString;
HAL 9000
  • 3,877
  • 1
  • 23
  • 29
  • Hi - I had seen something similar on AskTom and I should have posted, I guess my fear would be the expense of the execute immediate especially given the # of calls that would be made. What are your thoughts there? – Dave LeJeune Oct 15 '13 at 12:19
  • It's similar to "prepared statements", so you'll get only minimal overhead. Of course you should try to reduce calls to the minimum. One 'bigger' EXECUTE IMMEDIATE will be faster then several small ones. – HAL 9000 Oct 22 '13 at 05:46