CODEWITHSUNDEEP
sqlplus
T.Juan
T.Juan

Reputation: 23

Is there any way to get the syntax of an SQL statement when using SQLPlus?

I have an exam using SQLPlus and sometimes I don't remember the exact syntax of SQL statements, so I was wondering if there is any way to get some nice inline help from inside SQLPlus.

For instance, say I forgot how to use INSERT INTO, and I want some reminder like this:

INSERT INTO table-name (column-names) 
VALUES (values)

Is this possible?

I tried HELP command but none of that seems to suits my needs. I Googled it with no success.

Upvotes: 0

Views: 110

Answers (1)

robertus
robertus

Reputation: 356

No. SQL is a standardized language (at least ANSI SQL) and SQLPlus "just" uses that syntax, so it's not covered by internal help. Internal help lists only SQLPlus specific commands (ex. SET, CONNECT, SPOOL).

It is possible to workaround that in some way, but very limited. You can call dbms_metadata.get_ddl function for some existing object. Some of those DDLs could have statements you are intrested in. For example - you'd like to see select statement - then you could call dbms_metadata.get_ddl for some existing view:

select dbms_metadata.get_ddl('VIEW', 'USER_TABLES', 'SYS') 
  from dual;

Be aware - it works only for Oracle 11G and lower, in the newest one SYS objects are not accessible in that way (I'm not sure about Oracle 12.1).

The more interesting are tiggers, procedures, functions, and packages. You cannot use dbms_metadata to get DDLs of packages owned by SYS, but maybe you can connect to some sample schemas like HR (Human Resources), AD (Academic), SH (Sales History).

In HR schema there is stored procedure ADD_JOB_HISTORY, which has inside insert statement, so it looks like that:

select dbms_metadata.get_ddl('PROCEDURE', 'ADD_JOB_HISTORY') 
  from dual;

CREATE OR REPLACE EDITIONABLE PROCEDURE "HR"."ADD_JOB_HISTORY" 
  (  p_emp_id          job_history.employee_id%type   
   , p_start_date      job_history.start_date%type   
   , p_end_date        job_history.end_date%type   
   , p_job_id          job_history.job_id%type   
   , p_department_id   job_history.department_id%type    
   )   
IS   
BEGIN   
  INSERT INTO job_history (employee_id, start_date, end_date,    
                           job_id, department_id)   
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);   
END add_job_history;

There are better ways and better tools to achieve your goal - see below.

Are you allowed to use SQL Developer instead of SQLPlus? SQL Developer has nice feature to drag-and-drop table icon into worksheet, then you will be nicely prompted to choose what kind of example statement you are looking for (SELECT, INSERT, UPDATE etc.) - after choosing one you will get sample statement.

But the best way is just open in browser Database SQL Language Reference: https://docs.oracle.com/database/121/SQLRF/toc.htm

Upvotes: 1

Related Questions