Registering the Oracle Concurrent Program from Back-end

SRY IT Solutions is specialized in Online training, End-to-End Project Support and Placement Assistance services. We are having ample of experience in SAP , Oracle , Other Online Training's.

For more Details on Oracle please contact or visit us at:

IND: +91- 9948030675, 

USA: +1- 563-823-8515

Email: info@sryitsolutions.com, 

                                            Web: http://www.sryitsolutions.com/

Here is an Oracle Concurrent Program Script :  

BEGIN

   FND_PROGRAM.register (

        'Test Concurrent Program Registration from Back-End'  -- program

                        ,'XXDO Custom'                                 -- application

       ,'Y'             -- enabled

       ,'XXINV_TEST_REG'                                        -- short_name

       ,'Test Concurrent Program Registration from Back-End'-- description

       ,'XXINV_TEST_REG'                          -- executable_short_name

       ,'XXDO Custom'                                   -- executable_application

       ,''             -- execution_options

       ,''             -- priority

       ,'Y'          -- save_output

       ,'Y'                                                                        -        -- print

       ,''                                                             -                      -- cols

       ,''                                                             -                      -- rows

       ,''                                                                    -               -- style

       ,'N'             -- style_required

       ,''               -- printer

       ,''                -- request_type

       ,''                -- request_type_application

       ,'Y'             -- use_in_srs

       ,'N'             -- allow_disabled_values

       ,'N'             -- run_alone

       ,'TEXT'                                                                        -- output_type

       ,'N'              -- enable_trace

       ,'Y'              -- restart

       ,'Y'              -- nls_compliant

       ,''                -- icon_name

       ,'US'

   );

   COMMIT;

END;

Read More

Users Authentication using more than one Authentication Provider

Some days backup I was completely stuck on question “How to provide access of Administration Tool and presentation to user which are present in default authentication provider and external authentication provider as well”.

After some work around, yes with the help of toolbox forum and by the help of experts I got the solution. So I am just putting the points in sequence here:

1)  Log in to Enterprise Manager ex: http://Machine_Name or IP: port/em

2) Expand Weblogic Domain

3) Right click on bifoundation_doamin and then select security-> Security Provider Configuration

4) Select configure button in identity store provider

5)  Click on Add

6)  Add virtual in Property Name and true in value 

7)  Restart the whole BI tier 


On the successful start of the services you can start login to presentation and admin tool by the user which are available in any authentication provider.

For more details Contact or visit us at:

IND: +91- 9948030675, 

USA: +1- 563-823-8515

Email: info@sryitsolutions.com, 

                                             Web: http://www.sryitsolutions.com/

Read More

How to Deploy the RPD using Shell Script ??

In OBIEE 11g the deployment procedure of RPD is handling by the Enterprise manager, but while doing the deployment of RPD using Enterprise Manager( to see how to deploy RPD using EM click here) I feel it is more time consuming as some time enterprise manager takes time to load the graphics.

To over come from such situation the OBIEE provides WLST( Web logic Scripting tool ), so I thought I can  pulled the hand completed, by launching the WLST and then running the RPD deployment script. Then question arises can we call the WLST and the command related to WLST in shell script and I crested the following script

echo "Enter user : "

read user

echo "Enter password : "

read password

echo "Enter host (ex. adminAddress:adminPort) : "

read host

echo "Enter RPD Path and Name (ex. /~/rpd_name.rpd) : "

read rpd_path

echo "Enter RPD Password : "

read rpd_password

/export/home/oracle/

Middlewarehome/OracleBI/wlserver_10.3/common/bin/wlst.sh

connect(user,password,host)

domainCustom()

cd('oracle.biee.admin')

# Lock

print 'Obtaining lock...'

cd('oracle.biee.admin:type=BIDomain,group=Service')

objs = jarray.array([], java.lang.Object)

strs = jarray.array([], java.lang.String)

try:

invoke('lock', objs, strs)

except:

print 'Already locked'

# Lock Complete

cd('..')

# Upload

print 'Uploading repository ...'

cd('oracle.biee.admin:type=BIDomain.BIInstance.ServerConfiguration,biInstance=coreapplication,group=Service')

params = jarray.array([rpd_path,rpd_password],java.lang.Object)

sign = jarray.array(['java.lang.String', 'java.lang.String'],java.lang.String)

invoke( 'uploadRepository', params, sign)

# Upload Complete

cd('..')

# Commit

print 'Applying changes...'

cd('oracle.biee.admin:type=BIDomain,group=Service')

objs = jarray.array([], java.lang.Object)

strs = jarray.array([], java.lang.String)

try:

invoke('commit', objs, strs)

except:

print 'Not Locked'

#Commit Complete

exit()

/export/home/oracle/Middlewarehome/OracleBI/instances/instance1/bin/opmnctl stopall

echo OPMNCTL Starting.......

/export/home/oracle/Middlewarehome/OracleBI/instances/instance1/bin/opmnctl startall

/export/home/oracle/Middlewarehome/OracleBI/instances/instance1/bin/opmnctl status

 I ran this script in Unix shell but rather than completing the deployment the control stopped at the "WLST:offline>" it means that user needs to provide the parameter and there is no use of opmnctl command.

 But I want to pull out hands so it is necessary to deploy  the RPD and restart the services from shell script only, so I created two file once shell script and another is .PY as WLST accept only the  jython.

Deploy.sh

echo "Enter the path (from current directory) of .py file: "

read pp

[weblogic_Home]/common/bin/wlst.sh $pp

[oracle_Home]/instances/instance1/bin/opmnctl stopall

echo OPMNCTL Starting.......

[Oracle_Home]/instances/instance1/bin/opmnctl startall

[Oracle_Home]/instances/instance1/bin/opmnctl status

RPD_Deployment.py

user = raw_input("Enter user : ")

password = raw_input("Enter password : ")

host = raw_input("Enter host(ex. adminAddress:adminPort) : ")

rpd_path = raw_input("Enter RPD Path and Name (ex. C:/~/rpd_name.rpd) ")

rpd_password = raw_input("Enter RPD Password : ")

connect(user,password,host)

domainCustom()

cd('oracle.biee.admin')

# Lock

print 'Obtaining lock...'

cd('oracle.biee.admin:type=

BIDomain,group=Service')

objs = jarray.array([], java.lang.Object)

strs = jarray.array([], java.lang.String)

try:

invoke('lock', objs, strs)

except:

print 'Already locked'

# Lock Complete

cd('..')

# Upload

print 'Uploading repository...'

cd('oracle.biee.admin:type=BIDomain.BIInstance.ServerConfiguration,biInstance=coreapplication,group=Service')

params = jarray.array([rpd_path,rpd_password],java.lang.Object)

sign = jarray.array(['java.lang.String', 'java.lang.String'],java.lang.String)

invoke( 'uploadRepository', params, sign)

# Upload Complete

cd('..')

# Commit

print 'Applying changes...'

cd('oracle.biee.admin:type=BIDomain,group=Service')

objs = jarray.array([], java.lang.Object)

strs = jarray.array([], java.lang.String)

try:

invoke('commit', objs, strs)

except:

print 'Not Locked'

#Commit Complete

exit()

Now... We call the WLST launch command and all the function required to deploy the RPD through the shell script itself in Deploy.sh and provide the path of RPD_Deployment.py.

Note: To avoid the parameter pass on the shell we can also createparameter and there values in to the separate the script and use the values of them in RPD_Deployment.py

Contact us for more details and Session Schedules at :

  IND: +91- 9948030675, 

USA: +1- 563-823-8515

Email: info@sryitsolutions.com, 

Web: http://www.sryitsolutions.com/oracle-obiee-online-training-in-hyderabad/

Read More

Oracle SQL/PLSQL Interview Questions with Answers

SRY IT Solutions Online IT Training programs aim to equip IT personnel with the needful skills and knowledge required for better project performance. SRY IT Provides Oracle PLSQL Online Training by 10+ years of Industry Expert.

Contact us for more details and Session Schedules at :

  IND: +91- 9948030675, 

USA: +1- 563-823-8515

Email: info@sryitsolutions.com, 

Web: http://www.sryitsolutions.com/oracle-plsql-online-training-in-hyderabad/

Here is an important interview questions for Oracle PL/SQL

1) What are joins and Types of join?
Ans. We need retrieve data from two or more tables to make our result complete. We need to perform a join.
INNER JOIN
This join returns rows when there is at least one match in both the tables.
OUTER JOIN
There are three different Outer Join methods.
LEFT OUTER JOIN
This join returns all the rows from the left table with the matching rows from the right table. If there are no field matching in the right table then it returns NULL values
RIGHT OUTER JOIN
Right outer join returns all the rows from the right table with the matching rows from the left table. If there are no field matching in the left table then it returns NULL values
FULL OUTER JOIN
Full outer join merge left outer join and right outer join.  this returns row from either table when the conditions are met and returns null value when there is no match
CROSS JOIN
Cross join is  does not necessary any condition to join. The output result contains records that are multiplication of record  from both the tables.

2) What is DIFFERENCE BETWEEN LEFT, RIGHT OUTER JOIN?
Ans:If there r any values in one table that do not have corresponding values in the other,in an equi join that row will not be selected.Such rows can be forcefully selected by using outer join symbol(+) on either of the sides(left or right)  based on the requirement.

3) WHAT ARE SET OPERATORS?
Ans: UNION, INTERSECT or MINUS is called SET OPERATORS.

4) What are different datatypes supported by sql in oracle?
Ans: Char (size), Nchar (size), Varchar2 (size), Nvarchar2 (size) data types for character values, Number (precision, scale), Number, Number (n), Float, Float (binary precision) data types for numerical values, Date data type for date values, Long, Raw (size),  Long Raw,  Clob, Blob, Nclob, Bfile for large objects.

5) What is difference between long and lob datatypes?
Ans:LOB
1) The maximum size is 4GB. 2) LOBs (except NCLOB) can be attributes of an object type. 3) LOBs support random access to data. 4) Multiple LOB columns per table or LOB attributes in an object type.
LONG
1) The maximum size is 2GB.  2) LONGs cannot.    3) LONGs support only sequential access. 4) Only one LONG column was allowed in a table

6) How much memory is allocated for date datatype? What is default date format in oracle?
Ans: For Date data type oracle allocates 7 bytes Memory.   Default Date Format is: DD-MON-YY.

7) What is range for each datatype of sql?
Ans: Datatype Range Char  Varchar2  Number    Float     LONG, RAW, LONGRAW  Large Objects (LOB’s) 2000 bytes  4000 bytes  Precision
1 to 38 Scale -84 to 127  Precision 38 decimals Or 122 binary precision   2 GB  4GB

8) What is a constraint? What are its various levels?
Ans: Constraint: Constraints are representators of the column to enforce data entity and consistency.There r two levels
1)Column-level constraints 2)Table-level constraints.

9) List out all the constraints supported by oracle
Primary Key , Foreign Key or Referential Integrity, Not Null, Unique, Check.

10) Difference between DELETE & TRUNCATE statement
Ans. Delete is a DML command. Truncate is a DDL command.
In Delete statement we can use where clause But we can’t use where clause in truncate statement.
Delete activates trigger. Truncate does not activate trigger.
We can rollback delete command. We can not rollback truncate command. Delete does not reset identity of table. Truncate resets identity of table.

11) Difference between Primary key and Unique Key
Ans. Primary key and Unique key enforce uniqueness of the column on which they are defined. But by default, the primary key creates a clustered index on the column, where as unique key creates a non-clustered index by default. Another major difference is that primary key does not allow NULL value, but unique key allows one NULL value only.

12) What are  oracle number, character, date, conversion, other
functions ?
Ans.
Oracle Number Functions – 
Round (m, [n]),
Trunc (m, [n]),
Power (m, n),
Sqrt,
Abs (m),
Ceil (m),
Floor (m),
Mod (m, n)
  
Oracle Character Functions- 
Chr (x)
Concert (string1, string2)
Lower (string)
Upper (string)
Substr (string, from_str, to_str)
ASCII (string)
Length (string)
Initcap (string). 
  
Oracle Date Functions-
sysdate
Months between (d1, d2)
To_char (d, format)
Last day (d)
Next_day (d, day).
Oracle Conversion Functions-
To_char
To_date
To_number

13) What is syntax of PL/SQL BLOCK ?
Ans. DECLARE
 <declarations>
 BEGIN
    <Exececutable Statements>
 EXCEPTION
    <Exception Handler(s)>
 END;

14) What are different types of oracle PL/SQL BLOCKS?
Ans:
Oracle PL/SQL DECLARE BLOCK – In DECLARE BLOCK all the declarations of the variable used in the program is made. If no variables are used this block will become optional.
Oracle PL/SQL BEGIN BLOCK -  In BEGIN BLOCK all the executable statements are placed. This block is Mandatory.
Oracle EXCEPTION BLOCK – In EXCEPTION BLOCK all the exceptions are handled. this block is optional.

15) what is a Oracle PL/SQL cursor? and how to create cursor syntax?
Ans: Cursor is Private SQL area in PL/SQL.
     Declare the Cursor,
     Open the Cursor,
     Fetch values from SQL into the local Variables,
     Close the Cursor.
16) Type of cursors are supported by oracle pl/sql?
Ans.  There are two types of cursors namely Implicit Cursor, Explicit Cursor.

17) What is a cursor for loop?
Ans: Cursor For Loop is shortcut process for Explicit Cursors because the Cursor is Open, Rows are fetched once for each iteration and the cursor is closed automatically when all the rows have been processed.

18) What are cursor attributes?
Ans:  %Found, %NotFound,   %IsOpen, %RowCount are the cursor attributes.

19) Use of cursor with “for update of” clause?
Ans: This Clause stop accessing of other users on the particular columns used by the cursor until the COMMIT is issued.

20) How Exception is different from error?
Ans: Whenever an error occurs Exception raises. Error is a bug whereas the Exception is a warning or error condition.

    

Read More

How to send E-mail from Oracle database

sqlplus / as sysdba
@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb
Grant execute on UTL_MAIL to public;
ALTER SYSTEM SET smtp_out_server = 'mailhost' scope=both;
For Example:
BEGIN
UTL_MAIL.send(sender => 'shahid.ahmed@al-sadhan.com',
recipients => 'you@address.com',
subject => 'Test Mail',
message => 'Everythings is OK',
mime_type => 'text; charset=us-ascii');
END;
/
Note: You must have an outgoing SMTP server IP to configure sending mail from the database. 

Oracle allows you to send mail using procedure may be just message or message from tables.
CREATE TABLE EMP
(
ENO NUMBER(4),
ENAME VARCHAR2(50),
BASIC NUMBER(10,2));

CREATE OR REPLACE PROCEDURE SENDMAIL(TOO IN VARCHAR, FROMM IN VARCHAR, SUBJECT IN VARCHAR, BODY IN VARCHAR) AS
SMTP_HOST VARCHAR2(50) :='212.12.164.2';
PORT varchar2(2) :='25';
CONN UTL_SMTP.CONNECTION;

BEGIN
CONN:=UTL_SMTP.OPEN_CONNECTION('212.12.164.2', '25');
UTL_SMTP.HELO(CONN,'212.12.164.2');
UTL_SMTP.MAIL(CONN,'shahid.ahmedt@al-sadhan.com');
UTL_SMTP.RCPT(CONN,'xyz_abc@al-sadhan.com');
UTL_SMTP.DATA(CONN,'Test');
UTL_SMTP.QUIT(CONN);
END;
/

CREATE OR REPLACE TRIGGER EMP_TRIGGER AFTER INSERT ON EMPFOR EACH ROW

BEGIN
sendmail('shahid.ahmedt@al-sadhan.com','xyz.abc@al-sadhan.com','Test',:new.ename);
END;

Desc emp;
insert into emp values (4, 'ahmed', 400);

The Above procedure will send a mail from oracle. When you call this procedure we need to pass To/From Address , Subject and Body of the message. For Example
To_address:shahid.ahmedt@al-sadhan.com
From_address: xyz.abc@al-sadhan.com
Subject and Body of the Message: Test



To Test this Procedure we created a temp table Emp with fields eno number(4), ename varchar2(50), basic number(10,2). Our aim is whenever some one insert record into emp table it needs to send an email with the Ename he entered. So we created Trigger after insert record into Emp table Call mail sending procedure with Employee name.

Posted SRY IT Solutions

Contact us for more details on oracle database administration,

  IND: +91- 9948030675,

USA: +1- 563-823-8515

Email: info@sryitsolutions.com, 

                   Web: http://www.sryitsolutions.com/oracle-dba-online-training-in-hyderabad/

Read More

Using 'RETURN_RESULT' WITH Clause in procedure/Function in Oracle 12c

Oracle 12c allows a procedure to return a list of rows by using RETURN_RESULT procedure of DBMS_SQL package. Rather than defining explicit reference cursor out parameters, the RETURN_RESULT procedure in the DBMS_SQL package allows you to pass them out implicitly. The following procedure returns list of rows from SALARY table as return value from a procedure.

CREATE OR REPLACE PROCEDURE Get_salary

AS

Salary_cursor SYS_REFCURSOR;

BEGIN

   OPEN salary_cursor FOR

   SELECT * from pay_payment_master;

   DBMS_SQL.RETURN_RESULT(salary_cursor);

END;

You can call the procedure and find the list of rows from the procedure as follows:

EXECUTE Get_salary

You can call the procedure and find the list of rows from the procedure as follows:

EXECUTE Get_salary

Functions in the WITH Clause:

The declaration section of the WITH clause can be used to define PL/SQL functions, as shown below.

WITH

  FUNCTION  expert(EMPLOYMENT_DATE date) RETURN NUMBER IS

  BEGIN

    RETURN  floor ((SYSDATE - EMPLOYMENT_DATE) / 365);

  END;

SELECT LATIN_NAME, expert(EMPLOYMENT_DATE) FROM pay_employee_personal_info;

From a name resolutions perspective, functions defined in the PL/SQL declaration section of the WITH clause take precedence over objects with the same name defined at the schema level OFFSET and FETCH clauses

Other Miscellaneous Features:

– The maximum size of the VARCHAR2, NVARCHAR2, and RAW data types has been increases from 4,000 to 32,767 bytes.

– Prior to Oracle 12c R1, undo generated by the temporary tables used to be stored in undo tablepace. However in 12c, the temporary undo records can now be stored in a temporary table instead of undo tablespace.

 Posted SRY IT Solutions

Contact us for more details and Session Schedules at :

  IND: +91- 9948030675, 

USA: +1- 563-823-8515

Email: info@sryitsolutions.com, 

Web: http://www.sryitsolutions.com/

Read More