Sign Up to our social questions and Answers to ask questions, answer people’s questions, and connect with other people.
Login to our social questions & Answers to ask questions, answer people’s questions & connect with other people.
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Please briefly explain why you feel this question should be reported.
Please briefly explain why you feel this answer should be reported.
Please briefly explain why you feel this user should be reported.
how can I know reserved words in oracle ?
just execute this query to list all oracle reserved words select *from v$reserved_wordswhere reserved = 'Y' hope this helpful :) share with others to stretch our experiences
just execute this query to list all oracle reserved words
select *from v$reserved_words
where reserved = 'Y'
hope this helpful 🙂
See lessshare with others to stretch our experiences
How to Send e-mail from PL/SQL ?
hi there,starting from oracle 8i we are eligible to send emails directly from PL/SQL by using either UTL_SMTP or UTL_TCP packages. However, oracle provides us valuable package called UTL_MAIL for sending emails in Oracle 10g.here is example of how to use UTL_MAIL to send emails from PL/SQL : BEGIN ERead more
hi there,
starting from oracle 8i we are eligible to send emails directly from PL/SQL by using either UTL_SMTP or UTL_TCP packages. However, oracle provides us valuable package called UTL_MAIL for sending emails in Oracle 10g.
here is example of how to use UTL_MAIL to send emails from PL/SQL :
BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1'''; UTL_MAIL.send(sender => 'me@address.com', recipients => 'you@address.com', subject => 'Test Mail', message => 'Hello World', mime_type => 'text; charset=us-ascii'); END;Note : for security purpose, UTL_MAIL is not enabled by default. so you should enable it by connecting to SYS user and executing the utlmail.sql and prvtmail.plb scripts in the $ORACLE_HOME/RDBMS/admin directory. In addition, you must configure an initialization parameter, SMTP_OUT_SERVER, to point to an outgoing SMTP server.
in addition, you must give EXECUTE permission to PUBLIC by run below script :
hope this helpful 🙂
See lessshare with others to stretch our experiences
How to read excel file from oracle pl/sql ?
first thing data must be saved as extension .CSV not .xls second we need to create directory and give (EXECUTE, READ, WRITE) privilege to appropriate user which will use this directory later into procedure we will create create directory syntax CREATE OR REPLACE DIRECTORY TEMP_DIR AS 'C:temp'; GRANTRead more
first thing data must be saved as extension .CSV not .xls
second we need to create directory and give (EXECUTE, READ, WRITE) privilege to appropriate user which will use this directory later into procedure we will create
create directory syntax
Note : “C:temp” this statement to create directory on windows server it will be little deference from Linux server.
third thing you need to create stage table which we will insert data into it by mapping excel column to be the same with columns name :
and now it’s time to read the data inside csv file and inserted to stage table by this procedure
create or replace procedure imp_proc_test is file_handle utl_file.file_type := UTL_FILE.FOPEN('TEMP_DIR','imptestdata.csv','R',5000); var1 test.TYPE_ID%type; v_string varchar2 (1000); begin loop begin UTL_FILE.get_line(file_handle,v_string); var1 := substr(v_string,1,instr(v_string,',',1,1)-1); insert into test (TYPE_ID) values (var1); exception when no_data_found then UTL_FILE.fclose (file_handle); exit; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(sqlcode||sqlerrm); end; end loop; commit; end;so now after created this procedure just call it by :
at the final there is too many ways to achieve this requirement but we pick easiest one for you so please if you have any addition just share with others here.
See lesswhat is FNDLOAD commands to download and upload forms personalization ?
FNDLOAD command for downloading personalization in form : FNDLOAD user/pass 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct PO_POXSCERQ_PERSONALIZ.ldt FND_FORM_CUSTOM_RULES function_name="PO_POXSCERQ" user : this your instance user name pass : this your instance password PO_POXSCERQ_PERSONALIZ.lRead more
FNDLOAD command for downloading personalization in form :
user : this your instance user name
pass : this your instance password
PO_POXSCERQ_PERSONALIZ.ldt : this generated file after download rename appropriate name
PO_POXSCERQ : this function name which you want to download all personalization from.
just after you run above command LDT file will generated in home directory just take this LDT file and copy it to another home instance then
FNDLOAD command for uploading personalization in form :
hope this helpful 🙂
See lessHow can i Update a table by data in another table ?
This called correlated update you can find examples below UPDATE TABLE(<SELECT STATEMENT>) <alias1> SET <column_1> = ( SELECT <column_1> FROM <table_2> <alias2> WHERE <alias2.table_name> = <alias1.table_name>); in you sample : UPDATE table1 t1 Read more
This called correlated update you can find examples below
in you sample :
alternative way you can do this
and now the final example is
See lessHow to combine “LIKE” and “IN” condition in sql ?
There is no combination of LIKE & IN in SQL, but you can use REGEXP_LIKE condition it's similar LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. Also this condition evaluates strings using characters as defined by iRead more
There is no combination of LIKE & IN in SQL, but you can use REGEXP_LIKE condition it’s similar LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. Also this condition evaluates strings using characters as defined by input character set.
Example 1 :
First Name
Walker, Mr. Kenneth (Ken)
Example 2 :
FIRST_NAME
Steven
Stephen
Example 3 :
LAST_NAME
De Haan
Greene
Bloom
Feeney
See lesshow can i use one cursor inside two or more procedure in oracle pl/sql?
The only way to achieve this is trying to declare that cursor in the package level then you can use it in any procedure or function in that package and the logic behind that is any cursor delcared in any function or procedure the scope for this cursor is only in that function below you can find sampRead more
The only way to achieve this is trying to declare that cursor in the package level then you can use it in any procedure or function in that package and the logic behind that is any cursor delcared in any function or procedure the scope for this cursor is only in that function below you can find sample example :
Note: keep in mind in that example below if you open declared cursor in procedure 1 and you didn’t close it if you trying to open the same cursor in the procedure 2 you will get an exception raising
See lessWhat is the difference between varchar and varchar2 data type?
Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage VARCHAR is reserved by Oracle to support distinction between NULL and empty string in future, as ANSI standard prescribes. VARCHAR2 does not distinguish between a NULL andRead more
Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage
See lessVARCHARis reserved byOracleto support distinction betweenNULLand empty string in future, asANSIstandard prescribes.VARCHAR2does not distinguish between aNULLand empty string, and never will.If you rely on empty string and
NULLbeing the same thing, you should useVARCHAR2How to check whether partitioning enabled or not in my database ?
we need to execute this select statement to check select * from v$option where parameter = 'Partitioning'; PARAMETER VALUE Partitioning TRUE
we need to execute this select statement to check
how to concatenate two string or two values in oracle sql ?
It is ||, for example: select 'Mr ' || emp_name from employees; or concatenate two column values by : select first_name || '-' || last_namefrom employees;
It is ||, for example:
or concatenate two column values by :
See less