I have created two Stord procedures, which does have same input type of input parameter but different output parameters. I want to call these two procedures from a single call and get the combined output. Below is the code I have tried:
CREATE OR REPLACE PROCEDURE sp_all_attribute_difference ( i_product_id_new IN NUMBER, o_product_attr_diff_list OUT product_attr_diff_list, o_usage_attr_list OUT usage_attr_diff_list ) IS [code]....
What is the best way to send the output of a PL / SQL function / procedure to the application in Oracle 11g. I understand that it could be different for applications built in JAVA , .Net , SAP etc.
We are running a store procedure by calling it from shell script.The shell script is running in Server1 and the stored procedure is running in server2(which is the data base server.).
Functionality of the stored procedure:Generating a tab delimited file and place in a directory.
Requirement: The requirement is that the store should be modified in such a way that it should drop the output file to Server1 itself(The server from where it is getting called).
Attaching the store procedure for reference. mIn the store procedure,we have a variable "lv_file_dir" which currently refers to the directory location in Databse server(Server 2) We have to change this SP such that it drops the generated file in Server1 itself(From where the shell script is running).
Attached File(s) Procedure.txt ( 3.28K ) Number of downloads: 4
create a procedure so that I could get minus query of 2 table as a result.
I have two table 1- src_table_list ,2- tgt_table_list both tables have 2 columns : serial_no,table_name and 100 records each. and details mentioned in column "table_name" are actually tables name which present in my testing database.
so I need one procedure which will pick one table_name from src_table_list and one table_name from tgt_table_name each time recursively and provide minus query as a result. as below.
select c1,c2,c3,c4 from table1 --(fetched from src_table_list) minus select b1,b2,b3,b4 from table2 --(fetched from tgt_table_list)
create the procedure..as I have to prepare minus query for more than 200 tables and then I need to test them for integration testing..
when i am calling stored procedure with input and output parameters from batch file .. I am getting the following message ..
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 4 11:48:51 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 14
PROCEDURE SP_SELCT_EMPLOYEE_DATA ( -- A_EMPLOYEE_ID IN VARCHAR2, --A_JOB_ID IN EMPLOYEES.JOB_ID%TYPE, P_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE, P_EMPLOYEE_NAME IN EMPLOYEES.EMPLOYEE_NAME%TYPE, P_EMAIL IN EMPLOYEES.EMAIL%TYPE,
DECLARE JOBSFILE UTL_FILE.FILE_TYPE; -- TAKE ALL JOB TITLES FROM JOBS CURSOR JOBSCUR IS SELECT * -- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID FROM TARGET_UCM ; [code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
Currently I have a requirement where I need to create 2 more output rows using each result row.
In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.
how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me.
xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.
I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.
CREATE OR REPLACE PROCEDURE dflt_pr_acnt ( l_item_code IN VARCHAR2, l_main_acnt_code OUT VARCHAR2 ) [code]....
Here i have one PL/SQL block which will returns the age of an employee using his id.
SET SERVEROUTPUT ON; DECLARE v_num NUMBER; v_days NUMBER; [code]...
It will returns an output of 27..I tried the same in Forms 6i using a text field and a button with a trigger "when_button_pressed". when i am entering the same id of employee i am getting a totally different answer
DECLARE v_num NUMBER; v_days NUMBER; BEGIN SELECT to_date(sysdate)-to_date(dob) INTO v_num FROM customer_details WHERE application_id=:block3.day; v_days :=floor(v_num/366); MESSAGE(v_days); MESSAGE(v_days); END;
It is giving me a result of -73...i cleared my problem. but i cound not understand the internal work happening inside the forms and pl sql machine.
percentage_pointsmarksage95336.590346.575376.550496.595347903777549750337 if i fired the statementselect percentage_points from table1 where marks=33 and age=6.5 then output should beuser has score percentage between 90 and 95i can write case condition between 1 to 100 percentages but any other condition which easy
1. I would like to know if any of the fields are empty I would like to eliminate the comma character from the string. 2. Can I replace the comma with a new line character and what character to be used in the syntax.
I am joining two oracle provided basic tables emp and dept and want to show result as a department detail and followed by employee detail belong to that.
Standard result of joining two tables looks like below:
deptno dname empname 10 ACCT SCOTT 10 ACCT MILLER 20 SALES JOHN 20 SALES XYZ 30 FINANCE AAA
Now I need the output as below as per a report requirement. entity_type name/no DEPT 10 EMP SCOTT EMP MILLER DEPT 20 EMP JOHN EMP XYZ
I am using oracle 10g release 10.2.0.1.0. Can I use oracle analytic function here?
A function returns the metadata of named objects (Directories, Users, Tablespace....) in the form of DDL. When i execute the function in the schema having the privileges of CONNECT, RESOURCE, DBA, SELECT ANY TABLE, UNDER ANY VIEW AND EXECUTE ANY PROCEDURE, function returns the empty clob without any error. But he same function created and executed in the User having SYSDBA privilege, we get output.how to get output without SYSDBA privilege ?
CREATE OR REPLACE FUNCTION SCHEMA.DBLINK RETURN CLOB AS v_meta_handle NUMBER; v_meta_handle_trans NUMBER; V_DOC CLOB; V_LOB CLOB; [code]....
Formatting the mail message sent using utl_mail, i have created one table and it has around 17 rows inside as sample and it may increase as well, my present email format is like very distorted and i have to have it as readable in single line .Please find below the code i have written along with test case, you may substitute your emailid and test, especially my main focus is on the lable aff.mark number where all the mark numbers are distorted and want to be arranged in line.I have attached the email format that i am getting at present also.
CREATE TABLE FAB_LOG ( MRK VARCHAR2(30)) INSERT ALL INTO FAB_LOG VALUES ('1018017M-6001') INTO FAB_LOG VALUES ('1018017M-6002') INTO FAB_LOG VALUES ('1018017M-6003') INTO FAB_LOG VALUES ('1018017M-6004') [code]......
How do I modify SQL to sum and also add 2 columns to hold calculation results?
In functional terms, we need to sum two pay periods' retirement deduction amounts for a BDCA deduction code R03 to arrive at the monthly total for those deduction amounts.
In other words, to sum payroll deduction data for 2 pay periods PAYNO = 21 & 22 for (EMPLOYEE_AMT and EMPLOYER_AMT) and have the totals in new columns named EENOVR03 and ERNOVR03. Column names representing EE for employee, ER for employer, NOV for November, and R03 for deduction code.
Here's the basic SQL without calculations. I need to modify SQL to produce both EENOVR03 and ERNOVR03.