PL/SQL
This Category lists all questions related to PL/SQL programming language
Share
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.
This Category lists all questions related to PL/SQL programming language
HTTP request failed ORA-29270: too many open HTTP requests
This error is thrown because there is a limit of 5 open HTTP connections per session in the Oracle database server. Usually, the application intends to open one connection at a time. However, this error might occur due to the application not closing the connection properly after being finished. So,Read more
This error is thrown because there is a limit of 5 open HTTP connections per session in the Oracle database server.
Usually, the application intends to open one connection at a time. However, this error might occur due to the application not closing the connection properly after being finished.
So, it’s recommended to review your code and to make sure you are ending the response after you have finished as well as in the exception part also, ending the request like the below code.
Catch too many requests exception to handle it by closing the request and response:
Ending the request at the end of your program
See lessWhat is the difference between searched case and simple case in oracle pl/sql?
1 difference I can c is, when using case <expression> when ….(i.e simple case), u'll do an exact comparison (like a=b). e.g case <expression> when value1 then... when value2 then...etc (i.e ur checking if expression = value1 or expression = value2 & so on) for case when <expreRead more
1 difference I can c is, when using case <expression> when ….(i.e simple case), u’ll do an exact comparison (like a=b). e.g case <expression> when value1 then… when value2 then…etc (i.e ur checking if expression = value1 or expression = value2 & so on)
for case when <expression> then .. (i.e searched case), u’ll do at least 2 comparisons i.e >= x and <=y case when variable >= x & <=y then.. when variable between a and b then…
there may b other differences.. eager 2 know
See lessHow to convert text with comma separated to array in pl/sql ?
Use REGEXP_SUBSTR to Extract desired info REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] ) And CONNECT BY to loop over the string searching for the "," and use it as a delimiter. SELECT REGEXP_SUBSTR ('text1, text2, text3', '[^,]+',Read more
Use REGEXP_SUBSTR to Extract desired info
REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] )
And CONNECT BY to loop over the string searching for the “,” and use it as a delimiter.
SELECT REGEXP_SUBSTR (‘text1, text2, text3’,
See less‘[^,]+’,
1,
LEVEL),LEVEL
FROM DUAL
CONNECT BY REGEXP_SUBSTR (‘text1, text2, text3’,
‘[^,]+’,
1,
LEVEL)
IS NOT NULL;
Which symbol is used for concatenation? in plsql
its very simple you can use || symbol to concatenate two string or using concat function and here you can find full explanation with examples about how to use oracle || - oracle concatenation in knowledge base section. have a nice day.
its very simple you can use || symbol to concatenate two string or using concat function and here you can find full explanation with examples about how to use oracle || – oracle concatenation in knowledge base section.
have a nice day.
See lessWhat is the difference between binary_integer and pls_integer in pl/sql?
Hi, binary_integer and pls_integer both are same. Both are PL/SQL datatypes with range -2,147,648,467 to 2,147,648,467. Compared to integer and binary_integer pls_integer very fast in excution. Because pls_intger operates on machine arithmetic and binary_integer operes on library arithmetic. pls_intRead more
Hi,
binary_integerandpls_integerboth are same. Both are PL/SQL datatypes with range -2,147,648,467 to 2,147,648,467.Compared to
integerandbinary_integerpls_integervery fast in excution. Becausepls_intgeroperates on machine arithmetic andbinary_integeroperes on library arithmetic.pls_integercomes from oracle10g.
See lessbinary_integerallows indexing integer for assocative arrays prior to oracle9i.How to handle a unique constraint exceptions in PL/SQL code?
hello, you can use exception : [code] EXCEPTION WHEN DUP_VAL_ON_INDEX [/code]
hello,
you can use exception :
[code]
See lessEXCEPTION
WHEN DUP_VAL_ON_INDEX
[/code]
“What is the difference between Views and Materialized Views in Oracle? “
Hello Nall, The Materialized views are disk based and are updated periodically based upon the query definition. The Views are virtual only and run the query definition each time they are accessed. Regards, Mahmoud Morsy.
Hello Nall,
The Materialized views are disk based and are updated periodically based upon the query definition.
The Views are virtual only and run the query definition each time they are accessed.
Regards,
Mahmoud Morsy.
See lessORA-29280: utl_file.fopen invalid directory path with directory
utl_file.fopen takes the name of a directory, not the path. For example: (you may need to login as SYS to execute these) [code]Windows: CREATE DIRECTORY CUST_DIR AS 'C:'; Linux CREATE DIRECTORY CUST_DIR AS '/usr/tmp'; GRANT READ ON DIRECTORY CUST_DIR TO SCOTT;[/code] Then, you can refer to it in theRead more
utl_file.fopen takes the name of a directory, not the path. For example:
(you may need to login as SYS to execute these)
[code]Windows:
CREATE DIRECTORY CUST_DIR AS ‘C:’;
Linux
CREATE DIRECTORY CUST_DIR AS ‘/usr/tmp’;
GRANT READ ON DIRECTORY CUST_DIR TO SCOTT;[/code]
Then, you can refer to it in the call to fopen:
[code]
UTL_FILE.FOPEN(‘CUST_DIR’, ‘oraask_test.txt’, ‘W’);
[/code]
hope this help.
See lessError: PLS-00103: Encountered the symbol when trying to compile
You need to change ELSEIF to ELSIF . your code would be like this : [code]declare CURSOR abc IS select * from all_Objects; begin for rec in abc loop if rec.object_id is null then null; elsif rec.owner is null then null; end if; end loop; end;[/code]
You need to change ELSEIF to ELSIF .
your code would be like this :
[code]declare
See lessCURSOR abc IS select * from all_Objects;
begin
for rec in abc
loop
if rec.object_id is null then
null;
elsif rec.owner is null then
null;
end if;
end loop;
end;[/code]
What is the difference between procedure and function in PL/SQL?
1. Procedure may or may not return value where as function should return one value. 2. Function can be called from SQL statement where as procedure can't be called from the SQL statement. 3. Function are normally used for computation where as procedure are normally used for executing business logic.Read more
1. Procedure may or may not return value where as function should return one value.
2. Function can be called from SQL statement where as procedure can’t be called from the SQL statement.
3. Function are normally used for computation where as procedure are normally used for executing business logic.
4. Stored procedure is pre-compiled execution plan where as function are not.
5. We can call function within procedure but we can not call procedure within function.
6. A FUNCTION must be part of an executable statement, as it cannot be executed independently, whereas procedure represents an independent executable statement.
and for example of both syntax of function and procedure :
[code]CREATE OR REPLACE PROCEDURE test_proc
(p_id IN VARCHAR2) as begin … end
CREATE OR REPLACE FUNCTION test_func
(p_id IN VARCHAR2) return varchar2 as begin … end[/code]
hope this help.
See less