Posts

Showing posts from 2023

SQL*Plus Report Syntax and Code

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; =====...
Post a Comment
Read more

Important Commands for SQL*Plus Reports

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 se...
Post a Comment
Read more

SQL,PLSQL, TSQL and SQL*Plus

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 us...
Post a Comment
Read more

Structure of metadata and how to use metadata structure in Orale database

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 o...
Post a Comment
Read more

What is Metadata in Oracle and What is Data Dictionary | Oracle Shooter

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 h...
Post a Comment
Read more

importJournals method in SOAP web service with request and response PAYLOAD

Image
Import Journals ImportJournals method in SOAP web service with request and response PAYLOAD Service WSDL URL:  https://servername/fscmService/JournalImportService?WSDL Code: REQUEST PAYLOAD REQUEST PAYLOAD:- <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/generalLedger/journals/desktopEntry/journalImportService/types/" xmlns:jour="http://xmlns.oracle.com/apps/financials/generalLedger/journals/desktopEntry/journalImportService/" xmlns:jour1="http://xmlns.oracle.com/apps/flex/financials/generalLedger/journals/desktopEntry/journalLineGdf/"> <soapenv:Header/> <soapenv:Body> <typ:importJournals> <typ:interfaceRows> <!--Optional:--> <jour:BatchName>SOAP JV 90001</jour:BatchName> <!--Optional:--> <jour:BatchDescription>TESTING</jour:BatchDescr...
Post a Comment
Read more

submitInvoiceImport and submitInvoiceImportWithSource Operations in SOAP web service

Image
Submit Invoice Import submitInvoiceImport  and  submitInvoiceImportWithSource  Operations in SOAP web service Service WSDL URL:  https://servername/fscmService/InvoiceInterfaceService?WSDL Code for submitInvoiceImport PAYLOAD :-  <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/payables/invoices/quickInvoices/invoiceInterfaceService/types/"> <soapenv:Header/> <soapenv:Body> <typ:submitInvoiceImport> <typ:ledgerId>300000001520052</typ:ledgerId> <typ:orgId>300000001534054</typ:orgId> <typ:summarizeReport>No</typ:summarizeReport> </typ:submitInvoiceImport> </soapenv:Body> </soapenv:Envelope> Code for submitInvoiceImportWithSource PAYLOAD :-  <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http:/...
Post a Comment
Read more

createInvoiceInterface Operations in Payables Invoice in SOAP web service (createInvoiceInterface payload with Response)

Image
Create Invoice Interface createInvoiceInterface Operations in Payables Invoice in SOAP web service (createInvoiceInterface payload with Response) Service WSDL URL:  https://servername/fscmService/InvoiceInterfaceService?WSDL Code for REQUEST PAYLOAD :- “ STANDARD INVOICE “ <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/payables/invoices/quickInvoices/invoiceInterfaceService/types/" xmlns:inv="http://xmlns.oracle.com/apps/financials/payables/invoices/quickInvoices/invoiceInterfaceService/"> <soapenv:Header/> <soapenv:Body> <typ:createInvoiceInterface> <typ:invoiceInterfaceHeader> <inv:Source>ELECTRONIC INVOICE</inv:Source> <inv:OperatingUnit>Snapdeal Private Limited</inv:OperatingUnit> <inv:VendorName>Test Supplier App99</inv:VendorName> <in...
Post a Comment
Read more

findInvoiceInterface Operation in Payables Invoice in SOAP web service (FIND_INVOICE_INTERACE payload with Response)

Image
Find Invoice Interface findInvoiceInterface Operation in Payables Invoice in SOAP web service (FIND_INVOICE_INTERACE payload with Response)  Service WSDL URL:  https://servername/fscmService/InvoiceInterfaceService?WSDL Code for REQUEST Payload <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/payables/invoices/quickInvoices/invoiceInterfaceService/types/" xmlns:typ1="http://xmlns.oracle.com/adf/svc/types/"> <soapenv:Header/> <soapenv:Body> <typ:findInvoiceInterface> <typ:findCriteria> <typ1:fetchStart>0</typ1:fetchStart> <typ1:fetchSize>10</typ1:fetchSize> <!--Optional:--> <typ1:filter> <typ1:group> <typ1:item> <typ1:attribute>InvoiceTypeLookupCode</typ1:attribute> ...
Post a Comment
Read more