Writing literal text across more than one line in PL/SQL

Hi,

I'm writing some unit tests with utPLSQL (http://utplsql.sourceforge.net/) that do query comparisons.

I'd like to write and define them so that I can test the SQL in SQL*Plus without having to then re-write them (adding the necessary quotes and concatenation symbols) in the PL/SQL package.

At the moment I need to write something like.

e.g.

        SQLQuery         VARCHAR2(4000);
        SQLNullQuery     VARCHAR2(4000) := 'SELECT ''TABLE'' FROM DUAL

        SQLQuery := 'SELECT object_type'||


                    'FROM dba_objects'||

                    'WHERE OWNER = ''DNICHOLS''';


        testDescription := 'Blah blah';

        utAssert.eqquery(testDescription
                        ,SQLQuery
                        ,SQLNullQuery);

Any body have any ideas?

Only one I've thought of is saving the SQL in a CLOB in a database table and then load them up. But it would be nice to keep the code in the PL/SQL package. I suppose it could also be possible to write a UtAsset method that took a cursor rather than literal SQL.

Ideally I would like PL/SQL to have a qq operator like perl which makes writing tidy, easily readable SQL very easy.

e.g.

    my ($sql_get_pop) = qq{SELECT pr.rule_description

                           FROM population_rule cpr
                           WHERE pr.record_name = ?
                           AND pr.field_name = 'RECORD_TYPE'};

Thanks,
Daniel. Received on Wed May 26 2004 - 15:26:03 CDT