Putting additional blanks before the will not change this behaviour. This lowers the usefulness of the “generate DB doc” functionality in SQL Developer a lot. SP2-0310: unable to open file "return.sql"Īn at the start of a line is always interpreted as a call to a script even if it is inside a multi line comment. SP2-0310: unable to open file : converted number SP2-0310: unable to open file : p1 : input string SP2-0310: unable to open file "usage.sql" Return to_number(p1 default '0' on conversion error) If conversion is not possible, 0 is : p1 : input : converted number But the intention was to only drop all triggers.Ĭreate or replace function convertNum(p1 in varchar2) return converts a string into a number. In this example the statement produced a drop command for each object in the schema. When SQL*plus finds a semicolon at the end of a line, it interprets this as the end of the command. SQL> SP2-0734: unknown command beginning "where obje…" - rest of line ignored.Ģ242 rows selected? Yes there were some triggers, but not that many. The examples do work in SQL Developer, but behave differently in SQL*Plus. Try to guess what happens before reading the result. SQL*Plus interprets comments slightly differently than SQL or PL/SQL. Although it is not a real comment, it is used in a similar way.
We can also use PROMPT to have a kind of echo inside a SQL*Plus script. Additionally we have a REM(ARK) command to add comments. The normal SQL and PLSQL comments do function in SQL*plus as well. Typical examples are SELECT statements, CREATE OR REPLACE PACKAGE commands and SET commands. In general we run SQL statements, PLSQL blocks and SQL*plus commands in a SQL*Plus script. The comment syntax is used to add some special meaning to the surrounding code. Select /*+warp_speed(5) */ from big_table Ī hint could be compared to an annotation in other languages. A + indicates that this comment is a hint. A hint tries to give the SQL optimizer additional instructions. In SQL a very special form of a comment is a hint. So line 5 and 6 are not commented, instead line 5 will raise a syntax error. The multi-line comment ends after the first time “*/” is encountered. * thisĪ slash “/” followed by an asterisks “*” starts a multi line comment. The double hyphen “–” starts the one line comment. The one line comment and the multi line comment. The basics comments in SQL and PLSQLĬomments in SQL and PLSQL come in two flavors. This post is to create awareness about such traps. Comments are simple ways to add information to objects/code without changing code.Ĭomments that work in SQL or PLSQL might behave differently in SQL*Plus. "You know the great thing about TV? If something important happens anywhere at all in the world, no matter what time of the day or night, you can always change the channel" ~ Jim IgnatowskiĭBA_COL_COMMENTS ALL_COL_COMMENTS USER_COL_COMMENTSĭBA_TAB_COMMENTS ALL_TAB_COMMENTS USER_TAB_COMMENTSĬOMMENT - Add a comment to a table or a column.I am a big fan of comments (see also CDD Manifesto a bit further down). So now if you want a list of the columns in the EMP table emp Names? Why bother when it's all available in the data dictionary.
Writing code and find yourself typing in a bunch of column
WHERE table_name='TABLE_NAME_TO_DESCRIBE' With the same name as a TABLE in the same schema.Īn alternative to the DESC command is selecting directly fromĬoncat(concat(concat(data_type,'('),data_length),')') "Type" Returned (for functions) the argument names, types, whether input orĭESC user.object_name will always identify a distinct database objectīecause a user's database objects must have unique names. Object (function or procedure) the name of the function or procedure, The description for functions and procedures contains the type of PL/SQL desc DBMS_UTILITY.GET_PARAMETER_VALUEĭESCRIBE DESCRIBE command allows you to describe objects recursively to the depth In Oracle 7 you could describe individual proceduresĮ.g. Note that because this is a SQL*Plus command you don't need to terminate it Describe an Oracle Table, View, Synonym, package or Function.