Oracle Shooter

  -- FIRST Session  =================================== XXSD_TEST_FUN(PAR_1) XXSD_TEST_FUN(PAR_1,PAR_2) XXSD_TEST_FUN(PAR_1,PAR_1_2,PAR_3) --======================================= can we create DB Object with same name  ?  how to use whenever it is required ? --======================================= ---======  TABLE --========== select * from XXMK_TEST create table XXMK_TEST ( EMPID NUMBER, EMPNAME VARCHAR2(100) ) create table XXSD.XXMK_TEST ( EMPID NUMBER, EMPNAME VARCHAR2(100) ) create table XXMK_TEST ( EMPID NUMBER ) create table XXSD.XXMK_TEST ( EMPID NUMBER ) select * from all_objects  where object_name = 'XXMK_TEST'  edit  XXMK_TEST select * from XXMK_TEST edit  XXSD.XXMK_TEST select * from XXSD.XXMK_TEST ---======  VIEW--========== create or replace view  XXMK_TEST as  select * from  APPS.XXMK_TEST create or replace view  XXSD.XXMK_TEST as  select * from  APPS.XXMK_TEST create or replace view  XXMK_TEST_V as  select * from  APPS.XXMK_TEST --drop view XXSD.XXMK_TEST sel

SQL*Plus Report Syntax and Code ======================================================== Example-1 SET PAGESIZE 30; SET LINESIZE 256; SET PAUSE ON SET PAUSE ‘Please Enter Any key to Continue..’; TTITLE CENTER ‘Employee detail Report’; BTITLE ‘End Of the Report’; COLUMN JOB FORMAT A3 TRUNC; SELECT EMPNO, ENAME, JOB, HIREDATE FROM SCOTT.EMP; TTITLE OFF; BTITLE OFF; CLEAR COLUMN; SET PAGESIZE 24; SET LINESIZE 80; ========================================================= Example-2 SET PAGESIZE 30; SET LINESIZE 256; SET PAUSE ON SET PAUSE ‘Please Enter Any key to Continue..’; TTITLE CENTER ‘Employee detail Report’ SKIP 1 – RIGHT ‘Page_No’ FORMAT 99 SQL.PNO SKIP 2 ; BTITLE ‘End Of the Report’; COLUMN invoice_date FORMAT A6 TRUNC; COLUMN invoice_num HEADING Invoice_number; COLUMN invoice_amount FORMAT $999999.99; select invoice_num, invoice_date, invoice_amount, amount_paid FROM ap_invoices_all where rownum < 25; TTITLE OFF; BTITLE OFF; CLEAR COLUMN; SET PAGESIZE 24; SET LINESIZE 80; =====

Important Commands for SQL*Plus Reports ========================================================= Some Command We have to understand before developing the report by using the command line utility in Oracle database  ========================================================= SET PAGESIZE : – Sets maximum number of line per page and the default value is 24.  SET LINESIZE :-  Sets maximum number of characters allowed per line and the default value is 80.  SET PAUSE ON/OFF:-  Sets system to pause after each page. TTITLE :-  Sets top title for each page of reports.  BTITLE:-  Sets bottom title for each page of reports.  HEADING :-  The HEADING setting controls whether column headings print when you SELECT or PRINT data. The default value for this setting is ON, which allows column headings to print. COLUMN  Command Details:- It is used fo formating of an column in report. CLEAR COLUMN :-  it clear the column command instruction as set previous. BREAK  ON/OFF  :- it tells SQL*Plus where to pu

SQL,PLSQL, TSQL and SQL*Plus SQL :-  Structured Query Language (SQL) is the set of statements with which all programs and users access data in an Oracle Database.    SQL is used for adding, retrieving, or updating data stored in a database. It is used across many different types of databases PL-SQL :-  PL/SQL is Oracle’s procedural extension to industry-standard SQL. PL/SQL naturally, efficiently, and safely extends SQL for developers and it is used by Oracle  SQL Example :-  SELECT * FROM users ORDER BY age LIMIT 10; T-SQL :-  T-SQL, which stands for Transact-SQL and is sometimes referred to as TSQL, is an extension of the SQL language           used primarily within Microsoft SQL Server. This means that it provides all the functionality of SQL but with some added extras. T-SQL Example :-  SELECT TOP 10 (*) FROM users ORDER BY age; SQL*Plus :-  it used by oracle database to provide command line utility and by using this SQL Plus we can do some different task.     

Structure of metadata and how to use metadata structure in Oracle database —-How to Use the Data Dictionary (Metadata) —- The views of the data dictionary serve as a reference for all database users.  Access the data dictionary views with SQL statements.  Some views are accessible to all Oracle users, and others are intended for database administrators only. The data dictionary consists of sets of views. In many cases, a set consists of three views  containing similar information and distinguished from each other by their prefixes: Prefix Scope USER :- User’s view (what is in the user’s schema) ALL  :- Expanded user’s view (what the user can access) DBA  :- Database administrator’s view (what is in all users’ schemas) 1. Views with the Prefix USER ——— The views most likely to be of interest to typical database users are those with the prefix USER. These views: Refer to the user’s own private environment in the database,  including information about schema objects created by the user, 

Metadata What is Metadata in Oracle and What is Data Dictionary | Oracle Shooter –Definition —  The Oracle data dictionary is one of the most important components of the Oracle DBMS.  It contains all information about the structures and objects of the database  such as. • tables, • columns, • users, • data files etc.  The data stored in the data dictionary are also often called metadata. –Introduction to the Data Dictionary One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables that provides information about the database. A data dictionary contains: The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on) How much space has been allocated for, and is currently used by, the schema objects Default values for columns Integrity constraint information The names of Oracle users Privileges and roles each user has been granted Aud