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 to know number of rows updated by UPDATE statement ?
SQL%ROWCOUNT : return the number of rows fetched/processed by the last DML executed. If the DML fails after fetching 1 row, due to any reason, SQL%ROWCOUNT will return only 1, the number of rows fetched/processed so far basically you can use : BEGIN UPDATE xx_test_tab SET test_col = 777777 WHRead more
SQL%ROWCOUNT : return the number of rows fetched/processed by the last DML executed. If the DML fails after fetching 1 row, due to any reason, SQL%ROWCOUNT will return only 1, the number of rows fetched/processed so far
basically you can use :
hope this help 🙂
See lessCan i use select statement inside if statement ? (if (select) > 0 then) ?
hello Beter, you can't use SQL Statement directly in a PL/SQL expression instead you can use variable to store your count then use your variable into PL/SQL expression like this : DECLARE v_count NUMBER;BEGIN SELECT count (*) INTO v_count FROM dual; IF v_count >= 1 THEN dbms_output.put_liRead more
hello Beter,
you can’t use SQL Statement directly in a PL/SQL expression instead you can use variable to store your count then use your variable into PL/SQL expression like this :
or as you mentioned if you want to use it inside delete statement like this :
hope this helpful 🙂
See lessHow to Insert data include symbol '&' into a table ?
it's very simple just using this : set define off insert into table values ('& yourvalues'); and then press 'F5' to execute the above commands. also from sqlplus you can use this : set define off
it’s very simple just using this :
set define off insert into table values ('& yourvalues');and then press ‘F5’ to execute the above commands.
See lessalso from sqlplus you can use this :
set define off
How can i convert CLOBS TO VARCHAR2 column ?
hello Beter, you can use dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte ); and here basic two example : first by using SQL but note the max length is 4000 characters: select dbms_lob.substr( clob_col, 4000, 1 ) from Tablename; second by using PL/SQL max length is 32000 charactersRead more
hello Beter,
you can use
and here basic two example :
first by using SQL but note the max length is 4000 characters:
second by using PL/SQL max length is 32000 characters :
DECLARE var1 LONG; BEGIN FOR i IN (SELECT clob_col FROM tablename) LOOP var1 := dbms_lob.substr (i.clob_col, 32000, 1); END LOOP; END;hope this may help.
See lessyou can refer to DBMS_LOB from oracle
Load image
firstly, try to read more about the webutil library.second, find below a basic code snippet to do the job: --this code to open uploading dialog box V_CLIENT_PATH := CLIENT_GET_FILE_NAME(DIRECTORY_NAME => NULL ,FILE_NAME => NULL ,FILE_FILTER => 'ALL FILES (*.*)|*.*' ,MESSAGE => 'Open a fiRead more
firstly, try to read more about the webutil library.
second, find below a basic code snippet to do the job:
--this code to open uploading dialog box V_CLIENT_PATH := CLIENT_GET_FILE_NAME(DIRECTORY_NAME => NULL ,FILE_NAME => NULL ,FILE_FILTER => 'ALL FILES (*.*)|*.*' ,MESSAGE => 'Open a file' ,DIALOG_TYPE => NULL ,SELECT_FILE => NULL); -- create directory on local machine at client server HOST ( 'cmd /c mkdir'||' "'|| 'C:tempapplication' || '"',NO_SCREEN); -- this code to show uploading progress to user V_SUCCESS := WEBUTIL_FILE_TRANSFER.CLIENT_TO_AS_WITH_PROGRESS (CLIENTFILE => V_CLIENT_PATH ,SERVERFILE => V_SERVER_PATH ,PROGRESSTITLE => 'Uploading file in progress' ,PROGRESSSUBTITLE => 'Please wait' ,ASYNCHRONOUS => FALSE ,CALLBACKTRIGGER => NULL);hope this may help 🙂
See lessHow to add ' "single quotes" for string using Java?
hello beter, basically if you want to add single code to any string you can use : /' "any string you want here" /' and if you want to use it inside sql statement hence you are building VO at run time (onthefly :)) so you can write : SELECT column1 FROM table WHERE column2 = + "'"+ Vstr+"'"; where VsRead more
hello beter,
basically if you want to add single code to any string you can use :
and if you want to use it inside sql statement hence you are building VO at run time (onthefly :)) so you can write :
where Vstr is your String variable in your example.
See lesshope this helpful 🙂
share today to gain tomorrow.
How to pass date parameter to oracle report builder ?
hello albert, your code is correct can you write what is the error exactly to help you. in addition you can check your user parameter in your report builder property specifically these properties mentioned in below image
hello albert,
See lessyour code is correct can you write what is the error exactly to help you.
in addition you can check your user parameter in your report builder property specifically these properties mentioned in below image
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 less