oracle 如何打开存储过程并在 SQL*Plus 中对其进行编辑
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1065575/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I Open a Stored Procedure and Edit it in SQL*Plus
提问by Jeff
I need to make some changes to an old Oracle stored procedure on an obsolete box that is being kept around to run an old legacy process. The only tool I can connect to the db with is SQLPlus. How do I load a stored proc into memory for editing in SQLPlus?
我需要在一个过时的机器上对旧的 Oracle 存储过程进行一些更改，该机器被保留以运行旧的遗留进程。我可以连接到数据库的唯一工具是 SQL Plus。如何将存储的 proc 加载到内存中以在 SQLPlus 中进行编辑？
The tutorials I've found on-line don't exlain how that's done. :-(
我在网上找到的教程没有解释这是如何完成的。:-(
采纳答案by Jeff
The box is HPUX with no TNS listener running which pretty much wipes out the entire fleet of dev tools. However, the DBA was able to connect using an admin tool called OEM and make the necissary changes. Thanks all for your suggestions.
该盒子是 HPUX，没有运行 TNS 侦听器，这几乎消除了整个开发工具群。但是，DBA 能够使用名为 OEM 的管理工具进行连接并进行必要的更改。谢谢大家的建议。
回答by Ron Savage
It would be a lot easier to download the trial version of TOAD
下载TOAD的试用版会容易很多
scratch that previous suggestion, I just tried out the Oracle SQL Developer link suggested and it works peachy fine for editing procs.
从以前的建议开始，我刚刚尝试了建议的 Oracle SQL Developer 链接，它在编辑过程中工作得很好。
for SQLPlusyou pretty much need to display the contents of the stored proc, and spool it to a file (as suggested by DCookie :-)) :
对于SQLPlus，您几乎需要显示存储过程的内容，并将其假脱机到一个文件（如 DCookie 所建议的 :-)）：
sqlplus> spool myprocname.sql;
sqlplus> select text from all_source where name = 'MYPROCNAME' and type = 'PROCEDURE' order by line;
sqlplus> quit;
then edit the local SQL file in a decent editor.
然后在合适的编辑器中编辑本地 SQL 文件。
Then use SQLPlusto run the SQL file to re-build the proc for testing.
然后使用SQLPlus运行 SQL 文件重新构建 proc 进行测试。
>sqlplus username/password@tnsnamesentry @myproc.sql
>sqlplus username/password@tnsnamesentry @myproc.sql
In short, a massive pain in the keester. :-)
简而言之，基斯特的巨大痛苦。:-)
回答by alexey
You can use dbms_metadata.get_ddlfunction to retrieve code, it is much better then all_sources.
您可以使用dbms_metadata.get_ddl函数来检索代码，它比 all_sources 好得多。
spool /tmp/my_proc.txt
set long 100000
select dbms_metadata.get_ddl('PROCEDURE','NAME','OWNER') from dual;
spool off
回答by DCookie
Can you not download and install Oracle SQL Developer? It's free.
您不能下载并安装Oracle SQL Developer吗？免费。
For Unix, there's TOra, (open source)
对于 Unix，有TOra，（开源）
回答by akf
If you can connect to the db using SQL*Plus, you should be able to connect using a SQL IDE. That said, you cannot open a proc for edit in SQL*Plus, but you can compile one. You can copy your updated proc into the command prompt (line by line if necessary) and compile it.
如果您可以使用 SQL*Plus 连接到数据库，那么您应该能够使用 SQL IDE 进行连接。也就是说，您不能在 SQL*Plus 中打开一个 proc 进行编辑，但您可以编译一个。您可以将更新后的 proc 复制到命令提示符中（如有必要，可以逐行）并编译它。
If you need to access the current source in the manual fashion, you can query the USER_SOURCE table.
如果需要以手动方式访问当前源，可以查询 USER_SOURCE 表。
I strongly suggest, however, looking into connecting to the DB with an IDE.
但是，我强烈建议考虑使用 IDE 连接到数据库。
