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.
ORA-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 lesshow to check if the column value is number or character
one example to create a function to return 'Y' if parameter value is number otherwise which is exception in this case to return ('N') ex: [code]CREATE OR REPLACE FUNCTION is_number (p_string IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE IS l_num NUMBER; BEGIN l_num := TO_NUMBER (p_strinRead more
one example to create a function to return ‘Y’ if parameter value is number otherwise which is exception in this case to return (‘N’)
ex:
[code]CREATE OR REPLACE FUNCTION is_number (p_string IN VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC
PARALLEL_ENABLE
IS
l_num NUMBER;
BEGIN
l_num := TO_NUMBER (p_string);
RETURN ‘Y’;
EXCEPTION
WHEN VALUE_ERROR
THEN
RETURN ‘N’;
END is_number;[/code]
and here you can call the function created above to identify the value passed is number or not like :
[code]SELECT (CASE
WHEN (is_number (mycolumn) = ‘Y’)
THEN
‘your column value is number’
ELSE
‘your column value is not number’
END)
FROM myTable;[/code]
hope this help you.
See lessHow to count specific values from table ?
Hi, try this query : [code]SELECT SUM ( CASE WHEN Col1 = 2 THEN 1 ELSE 0 END + CASE WHEN Col2 = 2 THEN 1 ELSE 0 END + CASE WHEN Col3 = 2 THEN 1 ELSE 0 END) FROM table_name WHERE Col1 = 2 OR Col2 = 2 OR Col3 = 2;[/code]
Hi,
try this query :
[code]SELECT SUM (
See lessCASE WHEN Col1 = 2 THEN 1 ELSE 0 END
+ CASE WHEN Col2 = 2 THEN 1 ELSE 0 END
+ CASE WHEN Col3 = 2 THEN 1 ELSE 0 END)
FROM table_name
WHERE Col1 = 2 OR Col2 = 2 OR Col3 = 2;[/code]
How do i redirect user from page to another in JavaScript ?
Hi Saly, Actually you can achieve this by simulate HTTP redirect by using one of the following : // similar behavior as an HTTP redirect [code]window.location.replace("http://www.oraask.com");[/code] or // similar behavior as clicking on a link [code]window.location.href("http://www.oraask.com");[/cRead more
Hi Saly,
Actually you can achieve this by simulate HTTP redirect by using one of the following :
// similar behavior as an HTTP redirect
[code]window.location.replace(“http://www.oraask.com”);[/code]
or
// similar behavior as clicking on a link
[code]window.location.href(“http://www.oraask.com”);[/code]
at the end this your choice but for my suggestion i prefer using (window.location.replace) because this doesn’t keep originate page in the session history.
hope this help.
See lessHow to add a new column to a table only if not exist?
You can find the following view to access all metadata about the columns :user_tab_cols; -- For all tables owned by the userall_tab_cols ; -- For all tables accessible to the userdba_tab_cols; -- For all tables in the Database.and lets consider you want to add new column only if doesn't exists you cRead more
You can find the following view to access all metadata about the columns :
user_tab_cols; — For all tables owned by the user
all_tab_cols ; — For all tables accessible to the user
dba_tab_cols; — For all tables in the Database.
and lets consider you want to add new column only if doesn’t exists you can use this pl/sql to check and add new column
[code]DECLARE
v_column_exists number := 0;
BEGIN
Select count(*)
into v_column_exists
from user_tab_cols
where column_name = ‘ADD_COLUMN’
and table_name = ‘departments’;
if (v_column_exists = 0) then
execute immediate ‘alter table departments add (ADD_COLUMN NUMBER)’;
end if;
end;[/code]
hope this help.
See lessORA-06550: line , column : PLS-00201: identifier must be declared
ORA-06550: error causes are: You tried to execute an invalid block of PLSQL code (like a stored procedure or function), but a compilation error occurred. in your example, you selected a value inside the variable (v_last) that is not declared in your block. So to correct your block of code, you canRead more
ORA-06550: error causes are: You tried to execute an invalid block of PLSQL code (like a stored procedure or function), but a compilation error occurred.
in your example, you selected a value inside the variable (v_last) that is not declared in your block.
So to correct your block of code, you can rewrite it like this:
API to create and update Inventory Items in Oracle Apps R12
Hello @Jone, you can create or update inventory item by using API (ego_item_pub.process_item) and this an example : Note : before using both API's be aware that we are not committing the changes. You have to perform explicit commit manually from IDE or set parameter (p_commit = 'T') to commit yoRead more
Hello Jone, you can create or update inventory item by using API (ego_item_pub.process_item) and this an example :
Note : before using both API’s be aware that we are not committing the changes. You have to perform explicit commit manually from IDE or set parameter (p_commit = ‘T’) to commit your changes.
DECLARE l_inventory_item_id NUMBER; l_organization_id NUMBER; l_return_status VARCHAR2 (4000); l_msg_data VARCHAR2 (4000); l_msg_count NUMBER; x_message_list error_handler.error_tbl_type; BEGIN fnd_global.apps_initialize (user_id => 1318, --> OPERATIONS resp_id => 50583, --> Inventory, Vision Operations (USA) resp_appl_id => 401); --> Inventory ego_item_pub.process_item (p_api_version => 1.0 ,p_init_msg_list => 'T' -- (F:- False), (T:- True) ,p_commit => 'F' -- (F:- False), (T:- True) ,p_transaction_type => 'CREATE' -- UPDATE FOR Updating item ,p_segment1 => 'Oraask_Item01' -- ITEM CODE ,p_description => 'Oraask Test Item Description' -- ITEM DESCRIPTION ,p_long_description => 'Oraask Test Long Item Description' -- ITEM LONG DESCRIPTION ,p_organization_id => 204 -- Vision Operations ,p_apply_template => 'ALL' ,p_template_id => 2 -- Purchased Item — select * from mtl_item_templates_vl -- P_TEMPLATE_NAME => '@Purchased Item', -- P_ITEM_TYPE => 'P', ,p_inventory_item_status_code => 'Active' ,p_approval_status => 'A' ,x_inventory_item_id => l_inventory_item_id ,x_organization_id => l_organization_id ,x_return_status => l_return_status ,x_msg_count => l_msg_count ,x_msg_data => l_msg_data); IF l_return_status = fnd_api.g_ret_sts_success THEN dbms_output.put_line ('Item is Created Successfully, Inventory Item ID : ' || l_inventory_item_id); ELSE dbms_output.put_line ('Item Creation is Failed'); error_handler.get_message_list (x_message_list => x_message_list); FOR i IN 1 .. x_message_list.count LOOP dbms_output.put_line (x_message_list (i).message_text); END LOOP; ROLLBACK; END IF; --> EXCEPTIONS HANDLING PART EXCEPTION WHEN OTHERS THEN FOR i IN 1 .. l_msg_count LOOP dbms_output.put_line (substr (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255)); dbms_output.put_line ('message is: ' || l_msg_data); END LOOP; END;DECLARE l_inventory_item_id NUMBER; l_organization_id NUMBER; l_return_status VARCHAR2 (4000); l_msg_data VARCHAR2 (4000); l_msg_count NUMBER; x_message_list error_handler.error_tbl_type; BEGIN fnd_global.apps_initialize (user_id => 1318, --> OPERATIONS resp_id => 50583, --> Inventory, Vision Operations (USA) resp_appl_id => 401); --> Inventory ego_item_pub.process_item (p_api_version => 1.0 ,p_init_msg_list => 'T' -- (F:- False), (T:- True) ,p_commit => 'F' -- (F:- False), (T:- True) ,p_transaction_type => 'UPDATE' -- UPDATE FOR Updating item ,p_Inventory_Item_Id => 53899 ,p_description => 'Oraask test description' -- ITEM DESCRIPTION ,p_organization_id => 204 -- Vision Operations ,x_inventory_item_id => l_inventory_item_id ,x_organization_id => l_organization_id ,x_return_status => l_return_status ,x_msg_count => l_msg_count ,x_msg_data => l_msg_data); IF l_return_status = fnd_api.g_ret_sts_success THEN dbms_output.put_line ('Inventory Item has been updated Successfully, Inventory Item ID : ' || l_inventory_item_id); ELSE dbms_output.put_line ('Update inventory item is Failed'); error_handler.get_message_list (x_message_list => x_message_list); FOR i IN 1 .. x_message_list.count LOOP dbms_output.put_line (x_message_list (i).message_text); END LOOP; ROLLBACK; END IF; --> EXCEPTIONS HANDLING PART EXCEPTION WHEN OTHERS THEN FOR i IN 1 .. l_msg_count LOOP dbms_output.put_line (substr (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255)); dbms_output.put_line ('message is: ' || l_msg_data); END LOOP; END;— By executing above code we have updated item description to be “Oraask test description” for inventory item id “53899”
See lessWhat is FNDLOAD command to move Value Sets from one instance to another ?
Hello Matheo, You can use this command to download and upload value sets : FNDLOAD to Download Valueset: $FND_TOP/bin/FNDLOAD username/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name" FNDLOAD to Upload Valueset: $FND_TOP/Read more
Hello Matheo,
You can use this command to download and upload value sets :
FNDLOAD to Download Valueset:
FNDLOAD to Upload Valueset: