How would I store PL/SQL scripts in a table? 1

[mattmaycsg]

I'm on Oracle 8.1.7 and was wondering if anyone had any suggestions on how I could I store PL/SQL scripts in a table and be able to retrieve them while keeping the orginial formatting of the script i.e. spacing and hard returns? Thanks in advance.
-Matt

 

[karluk]

I would imitate the structure of the DBA_SOURCE catalog view. If this method is good enough for Oracle, it should be good enough for the rest of us. DBA_SOURCE contains source code for database objects, one line per row. To retrieve the source code of a stored procedure, for example, do a query like

SELECT TEXT FROM DBA_SOURCE
WHERE OWNER='OWNER_NAME'
AND NAME='PROCEDURE_NAME'
ORDER BY LINE;

 

[MikeJones]

As an alternative, you could explore using CLOB's to hold the data, I'm not sure what would happen with The Hard Carridge returns but they should be OK from what I've read.

Or, if you only want to read the scripts from the DB and rarely change them you could look at B files, this would allow you just to dump the scripts on the server and patch them into Oracle. I'vre never used these though so you'd have to look into it more.

Just some more ideas....

Mike.