When referencing a procedure during a trigger, can I split the parameters across multiple lines? Similar to a backslash in perl? I've written a simple send mail procedure and it works well, though the parameter list is large and I'd like to be able to format the code for readability, i.e.:
BEGIN
send_mail('from@domain.com', 'to@domain.com, ???
'Subject', 'Message');
END;
What would I replace ??? with to extend the procedure to the next line?
I know this sounds like a very elementary question, but I've yet to figure it out via queries on these forums or Google. Perhaps I'm not choosing the right words.
When creating the procedure, i was able to use || to extend the utl_smtp function parameters, but I get an error when using the same syntax during trigger creation.
As you see, I only need the Customer one time followed by part, amount and address - separated hyphen - comma, next part, amount, address and so on ...I've tried the PIVOT way and STRAGG (wm_concat) but all failed so far.The main problem is that when one customer fits more than one condition, ora throws: ORA-01427: single-row subquery returns more than one row
I have some tables (below), but I'm having trouble entering multiple lines of data.Here are the tables I have created.
Quote: CUSTOMER table SQL> create table customer 2 (customer_no char(6) not null, 3 name varchar2(30) not null, 4 address varchar2(50) not null, 5 credit_limit number(6,2), 6 constraint customer_customer_no_pk primary key (customer_no));
Table created. SALESMAN table SQL> create table salesman 2 (salesman_id char(8), 3 name varchar2(20) not null, 4 address varchar2(50) not null, 5 emaill_address varchar2(30), 6 constraint salesman_salesman_id_pk primary key (salesman_id));
Table created. ITEM table SQL> create table item 2 (ISBN char(13) not null, 3 title varchar2(30) not null, 4 price number(4,2) not null, 5 constraint item_ISBN_pk primary key (ISBN));
Table created. INVOICE table SQL> create table invoice 2 (invoice_no char(1) not null, 3 invoice_date date not null, 4 salesman_id char(8),
[code]...
Table created. DELIVERY table SQL> create table delivery 2 (invoice_no char(1) not null, 3 ISBN char(13) not null,
[code]...
Table created. I can enter data into the customer, salesman and item table without any problems. However, I need to enter multiple lines of data using the same invoice_no (which is 1). The data is generally the same, except for 2 things need changing but I keep getting the following error...
I would like to write a query on USER_SOURCE that can display the number of code lines for each procedure/function in a package. Is it possible to write such a query? Maybe by using analytical functions?
for example in the following example i would like to count the lines between
"PROCEDURE proc1 IS" and "END proc1;" and between "PROCEDURE proc2 IS" and "END proc2;" SQL> select text from user_source where name='PKG_TEST' and type='PACKAGE BODY';
TEXT -------------------------------------------------- PACKAGE BODY PKG_TEST IS /************************************************* ****/ PROCEDURE proc1 IS BEGIN update t1 set EDITION_NAME = 'AAAAAAA'; commit; END proc1; [code]....
Insert into temp_a values ('1','002.0 AND 002.9'); Insert into temp_a values ('2','729.90 AND 079.99 AND 002.9');
Output :
1 002.0 1 002.9 2 729.90 2 079.99 2 002.9
So, once we get the output, it needs to be joined to another table. I did Google search, but most of them are retuning collections / arrays as output. Not sure how I join the collection with the table.
create or replace function splits ( p_list varchar2, p_del varchar2 ) return split_tbl pipelined is l_idx pls_integer; [code].......
Scenario 1 Query should check for priority record(25), if the start_date and end_date of that priority record is the max in that group, records will not have any split.output will be the same.
DC Store St Date End date Priority 955 3 1/1/2010 12/31/9999 25 966 3 4/5/2011 10/10/2011 50 977 3 10/12/2011 12/12/2012 100
output
DC store St Date End date Priority Rank 955 3 1/1/2010 12/31/9999 25 1 966 3 4/5/2011 10/10/2011 50 2 977 3 10/12/2011 12/12/2012 100 3
Scenario 2 If priority record is not covering the max range, then split the records as shown below,
1. during the time period 1/1/2011 & 4/30/2011 there were no other DC for that store so rank would be 1
2. the next range would be 5/1/2011 to 6/29/2011 we have 2 records in service so the record with low priortiy would be ranked 1 and second priority would be ranked 2
3. similarly, for 6/30/2011 to 10/1/2011 we have 3 records in service and it will be ranked accordingly on the priority.
DC Store St Date End date Priority 966 3 6/30/2011 10/1/2011 25 955 3 5/1/2011 11/30/2011 50 977 3 1/1/2011 12/31/2011 100
output
DC store St Date End date Priority Rank 977 3 1/1/2011 4/30/2011 100 1 955 3 5/1/2011 6/29/2011 50 1 977 3 5/1/2011 6/29/2011 100 2
[code]....
Scenario 3 This works similar to scenario 2
DC Store St Date End date Priority 966 3 2/1/2011 12/31/2011 25 955 3 1/1/2011 12/31/2012 50 977 3 5/1/2011 06/31/2011 100
output
DC store St Date End date Priority Rank 955 3 1/1/2011 1/31/2011 50 1 966 3 2/1/2011 12/31/2011 25 1 955 3 2/1/2011 12/31/2011 50 2 977 3 5/1/2011 6/30/2011 100 3 955 3 1/1/2012 12/31/2012 50 1
Note: Number of records in the input can vary and ther can be duplicates in the date interval
I want to output the data like this: FILE_IDKEYWORD1SMITH1ALLEN1WARD1BRADY2S&P5002TOPIXetc
I'm using this query and it works: SELECT STG.FILE_ID, REGEXP_SUBSTR(STG.KEYWORD,'[^;]+', 1, LEVEL) AS KEYWORD FROM STG_TABLE STGCONNECT BY REGEXP_SUBSTR(STG.KEYWORD,'[^;]+', 1, LEVEL) IS NOT NULL
But its sooooo slow, its unusable. Is there a quicker way to return this output? Other info:KEYWORD is varchar2(4000) but rarely more than 100 bytes are usedOracle 11g2 !
I want to set both nls_language and nls_date_language, and set them as different languages. fnd_global.set_nls_context() doesn't work. So I think maybe I can use the EXECUTE IMMEDIATE, and add them together into one statement.
I am trying to put transactions into 3 different "buckets" for each month of the year.
I am trying to figure out if the syntax for the "else" part of this code is correct... this will basically appear 12 times for each month of the year changing the value for month:
case when floor(sum(total_adjusted_net_amount)/100) < 0 and month in (01) then 0 when floor(sum(total_adjusted_net_amount)/100) > 29 and month in (01) and then 30 else when floor(sum(total_adjusted_net_amount)/100) and month in (01) end Groupin_01
Ex: Cursor C_employees(C_empid number, C_cityname varchar2) is select emp_name, office_name from employee where employees where empid = c_empid and city = c_city_name;
I know we can pass one parameter to the cursor but I do not know how to pass multiple parameters.
I have excel file which I am reading through plsql procedure using UTL_FILE utilities, one of the column in the excel has multiple values in the same column, I am getting the values into plsql, but when it is coming to where clause its not working.
Example: in excel the column has : 'ABC','GEH','HGT',LTP'
create or replace procedure abc(temp_col varchar2) . .... .... ... SELECT COLA, COLB, COLC FROM TABLE_TEMP WHERE TCOL IN temp_col;
This is not working, if the column in excel has one value say ('ABC') then the above sql is working, if it has more than one value its not working.
I am currently studying a Foundation degree in computer software development, and one of my assignment in PL/SQL I am stuck on one of the tasks.
I have to create a procedure where one of the parameters needs to have a default value of one, if no value is entered when the procedure is called. I have trued to use the NVL function which worked when using a anonymous block, but now I have to convert that to a procedure. My problem is I'm getting an error.
The code for the procedure is
CREATE OR REPLACE PROCEDURE add_new_classes (p_number_of_classes NUMBER := NVL(NULL,1), -- This will enter a default value of 1 if the user does not specify a number p_course_id classes.course_id%TYPE, p_period classes.period%TYPE, p_frequency classes.frequency%TYPE,
[code]....
I then use this to test it
BEGIN add_new_classes(1002,'first','daily',3002); END;
and the error I get is
Quote:ORA-06550: line 2, column 4: PLS-00306: wrong number or types of arguments in call to 'ADD_NEW_CLASSES' ORA-06550: line 2, column 4: PL/SQL: Statement ignored 1. BEGIN 2. add_new_classes(1002,'first','daily',3002); 3. END;
i created one procedure with 4 in parameters and 1 out parameters(return the value) but whenever execute this procedure i got the error i.e pl/sql: Compilation unit analysis terminated.
PLS-00201:identifier T_USER_PLDATA' must be declared.
CREATE OR REPLACE PROCEDURE GET_USER_PLDATA1( V_PROD_LINE_CD IN VARCHAR2, V_BUS_GROUP_CD IN VARCHAR2, V_BUS_UNIT_CD IN VARCHAR2, V_COUNTRY_CD IN VARCHAR2, V_USER_PLDATA OUT T_USER_PLDATA)[c [code]....
I would like to create a procedure which have 3 parameters. suppose in Date_birth, in Dept , out number_of_records
if i pass null value to one of the parameter it should return all values . if a pass value to the parameter it should return as per parameter values
Create or replace Procedure Emp_Test (P_dop in Date,P_Dept in number , P_noof_records out Number, p_recordset Out Sys_Refcursor) as Begin OPEN p_recordset FOR Select Emp_cd,Emp_name, Date_of_Birth,Dept,Count(emp_Cd) noof_records From Emp_Master Where Date_of_birth =P_date_of_Birth and Dept=P_dept ; End ;
My Oracle procedure works on two parameters, file type and file name.
exec xml_trans.load_xml('OMG','Sample.xml');
First parameter is xml file type and second parameter is xml file name. XML file is generated by by a web service and keep in a particular location.
Now the requirement is my procedure should pick up the in parameters, once the xml file is generated and my procedure should be running as per schedule. I can execute the procedure in schedule but how can i pick the file name and pass to procedure as soon the xml file is generated?
I am having an Oracle procedure which return ref cursor. I also want to result one more out parameter result. How Can I call the procedure in SQL. Below is the way I am calling my stored procedure with one parameter.
proc_Test (p_resultset=> My_cursor)
How can I call the procedure when I have one more OUT parameter. Second parameter returns 0 or 1.
CREATE TABLE t2 ( id NUMBER, ename2 VARCHAR2(20), sal2 NUMBER, job2 VARCHAR2(20), conid NUMBER
[Code]...
My requirement is like when I am calling the procedure P1 with some values then it should check the table "t2".And table "t2" is linking with table "t3".
So what ever the column "verify" is there, it should check the incoming values against it. If matches success otherwise reject it.Later the incoming values is stored different tables.I am doing it in the above way by hard coding some value.
BEGIN p1(1,'MILLER',500,'ADMIN'); --REJECT p1(1,'MILLER',5000,'ADMIN'); --ACCEPT P1(2,'MILLER',5000,'SALESMAN');--ACCEPT END;
I am trying to create a procedure that inserts parameters into a table and then returns the number of rows inserted back to calling block. the procedure is compiling fine but is not returning the number of rows inserted. My code is as follows;
STORED PROCEDURE CREATE OR REPLACE PROCEDURE CarMasterInsert_sp ( registration IN VARCHAR2, model_name IN VARCHAR2, car_group_name IN VARCHAR2, date_bought IN DATE, cost IN NUMBER, miles_to_date IN NUMBER, miles_last_service IN NUMBER, status IN CHAR, rowsInserted OUT NUMBER) [code]....
I am writing a procedure for the front-end. The end-users need to insert multiple rows of data into history tables in the database (11G). My problem is: the multiple actually parameters is not a fix amount, this time, the amount could be 5, next time, it could be 12. I currently used one string and pass the actual parameter (P_id, number) as '2, 4, 5, 7, 8', the procedure was executed successfully, but cannot insert any data into history table.
See my procedure below (the base table has clob data, I have to consider insert ... select *), I tried to use to_number (CONTACT_MSG_ID), it doesn't work well:
PROCEDURE ARCHIVE_XREF_CONT_EMAIL(P_ID IN VARCHAR2) IS BEGIN INSERT INTO TRC_XREF_CONT_EMAIL_MSGS_HIST SELECT * FROM TRC_XREF_CONT_EMAIL_MSGS [code].......
I studied a document about lexical parameter in that it says "Lexical parameters are used to substitute multiple values at run time and are identified by a preceding '&'. Lexical s can consist of as little a one line where clause to an entire select statement"
Select * from emp, deptno &where.
and i know about substitution variables using & is this are same (lexical and substitution) or different.
I have a Table with 4 columns and I am creating a procedure with 4 input parameters and inserting into the table as 4 parameters and how to use loop to insert multiple records.The data to be taken from excel.please suggest how to use the loop to insert multiple records.
create or replace procedure PRC_add_data( P_Emp_No varchar2, P_Member_Name varchar2, P_IDvarchar2, P_UHID varchar2 ) is BEGIN INSERT INTO UploadData (Emp_No,Member_Name,ID,UHID) values (P_Emp_No,P_Member_Name,P_ID,P_UHID) END; /
I need a stored proc that takes list of ids as input parameters. For all these Ids. the proc will send out data from another table as out ref cursor. Sounds very simple yet I am stuck with how do I pass the input list of ids.
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,
