SQL & PL/SQL :: How To Pass Parameter To Cursor And Print Details
Aug 15, 2010
Create a PL/SQL block that declares a cursor called DATE_CUR. Pass a parameter of DATE data type to the cursor and print the details of all the employees who have joined after that date.
DEFINE P_HIREDATE = 08-MAR-00
Test the PL/SQL block for the following hire dates: 08-MAR-00, 25-JUN-97, 28-SEP-98, 07-FEB-99.
I don't know how to pass parameters. So far I have this:
SET serveroutput ON;
DECLARE
p_HIREDATE DATE := 08-MAR-00;
CURSOR c_DATE_CUR(P_HIREDATE DATE) IS
BEGIN
OPEN c_DATE_CUR();
View 6 Replies
ADVERTISEMENT
Nov 16, 2012
How to pass parameter in a view.
create or replace view spic_bags as
select distinct(a.item_code) Material_Code,a.uom,
(case when a.card_code = '60' then
case when a.ccn <> '091' then
[code]........
in this above view i have hardcoded dates, well how to pass parameter for this view without hardcoding? here i need to pass the date as a parameter from a select query to view the data from the above view!
View 4 Replies
View Related
Jun 22, 2010
In a procedure i need to pass date parameter, to check how to do this. I create a simple procedure, but it giving
PLS-00049: Bad Bind Variable 'FD'
PLS-00049: Bad Bind Variable 'TD'
How to resolve .......
CREATE PROCEDURE pass_date_parameter
AS
fd VARCHAR2 (10) DEFAULT '01-01-2010';
td VARCHAR2 (10) DEFAULT '01-06-2010';
ffd VARCHAR2 (10) DEFAULT '01-01-2010';
ttd VARCHAR2 (10) DEFAULT '01-06-2010';
[Code]....
View 19 Replies
View Related
Dec 16, 2010
I want pass the tablename to cursor. The tablename will be a dyanamic one, as we have the data in different sources tables[multiple systems - all systems will provide the data dump to oracle] with same set of columns.
I have inserted all the tablenames in a parameter table and based on conditions daat should be fethced from each system.
I need to pass the tablename to the cursor to fetech the coloumn list from the table(the table that is passed as parametr).
View 3 Replies
View Related
Mar 5, 2010
i had a chart developed by graphics builder in which I am passing one parameter(ie year) and displaying the chart.Now i want to pass two parameter into chart.How to do this here i have attached my chart view and procedure which executes and display the chart from forms.
coding when button pressed
PROCEDURE CHART_DISPLAY IS
pl_id ParamList;
pl_name VARCHAR2(100) := 'tempdata';
[Code]....
In the above procedure i had one parameter directly am passing value of year '2009' now i want to pass two parameter like between 'year1' and 'year2' for getting chart between two years.
View 1 Replies
View Related
Jun 2, 2010
I am trying to pass a PL/SQL table as a parameter to a procedure and then using that table, update the records, but I am getting an error like:
ORA-06550: line 30, column 10:
PLS-00306: wrong number or types of arguments in call to 'UPDATE_STATUS'
Find the code below:
CREATE TABLE test_pl(empno VARCHAR2(20), empname VARCHAR2(40), empsts VARCHAR2(10));
INSERT INTO test_pl
VALUES ('0001', 'A', 'Y');
INSERT INTO test_pl
VALUES ('0002', 'B', 'N');
INSERT INTO test_pl
VALUES ('0003', 'C', 'Y');
INSERT INTO test_pl
VALUES ('0004', 'D', 'Y');
[code]....
View 4 Replies
View Related
Dec 18, 2012
If I want to pass lots of informations (100 parameter) from standard HR assignment form to my custom form. I am using fnd_function.execute('called form') to launch new form.
I thinking of creating 100 parameter object but not sure it has limitation of number or not. Another ways I googled are shared PL/SQL library or parameter list which I still doubt.
View 3 Replies
View Related
Sep 1, 2010
I want to pass a table as a parameter to a procedure.
As an example:
TYPE my_tab IS TABLE OF my_rec INDEX BY BINARY_INTEGER;
However, I want to give this parameter a default of null... is this possible?
procedure myproc(p_param1 in varchar2, p_tab in my_tab default null)
View 11 Replies
View Related
Nov 8, 2012
I have written as function :
create FUNCTION Cov(p_str1_x IN VARCHAR2,p_str2_x IN VARCHAR2, p_str1_y IN VARCHAR2,p_str2_y IN VARCHAR2) RETURN NUMBER is
TYPE ty_varchar_assoc IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
v_x ty_varchar_assoc;
v_y ty_varchar_assoc;
v_inter ty_varchar_assoc;
[Code]....
Now i need to pass the values to the parameter from a table? How can i do that?
View 2 Replies
View Related
Feb 23, 2011
I have created a simple form & report based on 2 tables Cust & cntry.I just want to display all customers of region selected in the criteria form. I am uploading the files.
Other than selection criteria report is fine.
View 4 Replies
View Related
Feb 26, 2012
I am trying to pass many characters to the in mode parameter using procedure , but i am getting the below error.
ORA-06550:
PLS-00172: string literal too long
CREATE TABLE USR_DETAILS
(
LOAD_ID NUMBER NOT NULL,
LOAD_DATE DATE,
USER_VALUE VARCHAR2(4000 BYTE),
USERID VARCHAR2(4000 BYTE)
)
insert into user_details values('1','2/10/2011','PROD1','USER1');
insert into user_details values('2','2/10/2011','PROD2','USER2');
[code]......
I have written the code as below...
Procedure concept :
Here i will seperate the strings(input parameters) using comma(,) symbol . Here users may pass more than 5000 characters to single in mode parameter, so i have tried with clob datatype but no luck.
CREATE OR REPLACE procedure user_details_proc (user_value1 IN varchar2,
user_value2 IN varchar2,
user_value3 IN varchar2,
user_value4 IN varchar2,
user_value5 IN varchar2,
userid IN varchar2
) as
[code].......
View 2 Replies
View Related
Jan 23, 2013
How can i pass the parameter from reports to form. means how can i open particular form from my report. means if i write query
select * from emp
this emp table report open now i just want open only emp no 10 form from this report so which parameter i have to pass.
View 2 Replies
View Related
May 10, 2013
I have a doubt how to pass input parameter for nested table which is declared as input parameter in procedure.
CREATE TYPE t_example AS OBJECT(msg_text VARCHAR2(100), bundle_msg_text VARCHAR2(100), version NUMBER(10))
/
create type t_msg_details ia table of t_example
/CREATE TABLE table_nested_sample
(msg_codes NUMBER(10),
language_id NUMBER(10),
[Code]...
How to call this procedure I want to insert data like this
Msg_codes Language_id Language_name msg_text_details
101 1 ENG "msg_test" "Bundle_msg_text" 1
"msg_test_1" "Bundle_msg_text_2" 2
View 5 Replies
View Related
Apr 26, 2013
I am populating a time dimension table. One job is to assign business day with sequence number on monthly basis and by year. Business day does not include weekends and all federal holidaies.
I created a PL/SQL block to handle this job. It works. However, I haven't figure out how to pass the month number and year number into PL/SQL block automatically. Now I have to manually enter year and month number (on Toad) to pass to block to make it work. If I create it as stored procedure and write another block to call it, I can pass year and month number into parameter there like this:
exec my_sp_name ('1', '2013');
But all of these are not good enough. I want to use code to automatically pass yesr and month number into PL/SQL block. So that I can make it better. . here is the PL/SQL block I created.
declare i number := 1;
begin
for r in (select * from time_dim where calendar_month_number = &month and calendar_year= &year and business_day_flag = 'B' order by date_sk_id)
loop
[Code]....
View 11 Replies
View Related
Jan 26, 2012
create table a_user
(
u_name varchar2(30)
, grp varchar2(30)
)
;
INSERT INTO a_user VALUES ('abc', 'new');
[code].....
sql:
SELECT
a.grp
, a.g_tot
FROM a_user b
,
(
SELECT
[code].....
getgrpname is a function to get grp when I input the u_name
Now I want to create a proc to display the output from the sql when I give the p_u_name as input. I am thinking some thing like this:
create or replace procedure get_u_name_data (p_u_name IN VARCHAR2, rc_data OUT SYS_REFCURSOR) IS
IS
BEGIN
OPEN rc_data FOR
SELECT
a.grp
[code].....
how to print output in SQL developer
View 2 Replies
View Related
Oct 5, 2013
I can not Pass parameter from forms to Report.
I use three parameter for report. I want to show the employee who has been hired between from_date to_date,it work well in report builder. but I want to pass parameter from forms to report and pdf format. I upload my work (this is actually a zip File, change file type txt to zip just before open).
View 5 Replies
View Related
Dec 21, 2011
have a customer table with following columns.
1 - Cust_ID
2 - Cust_nam
3 - Cntry_id
I have another table country.
1 - Cntry_id
2 - Cntry_nam
3 - Region
Now I want to view report for all customers of a certain region selected by user in criteria form.How can I do this?
View 1 Replies
View Related
May 9, 2012
I am fairly new to oracle, here's what I am doing.
Create or replace type csc_info as object( source_code varchar2(10),
Docno varchar2(10),
Key_value_1 varchar2(10),
Key_value_2 varchar2(10));
[Code]....
I need to test the procedure how do I pass the inputs for the type object csc_info?
View 2 Replies
View Related
Feb 22, 2011
i need to pass table as input parameter in stored procedures. during the run time, i am getting error
CREATE OR replace TYPE emp_type IS OBJECT (
id NUMBER(4),
ename VARCHAR2(10));
CREATE OR replace TYPE emp_type_tab
IS TABLE OF EMP_TYPE;
CREATE OR replace PROCEDURE Test_proc (in_emp_type IN EMP_TYPE_TAB)
AS
BEGIN
FOR i IN 1.. in_emp_type.COUNT LOOP
dbms_output.Put_line(in_emp_type.Id(i));
END LOOP;
END;
/
View 10 Replies
View Related
Apr 20, 2013
Is there any way to pass table of record as parameter to the plsql server page? For instance, I can define a new type in package specification:
create or replace package tst_type
as
type tab_expr_sec is table of varchar2(50) index by pls_integer;
type rec_expr is record (
prim_expr varchar2(50),
expr tab_expr_sec
);
[code]....
View 5 Replies
View Related
Sep 18, 2012
I need to get multiple code values and put it into a variable which later need to pass into the where clause of an sql. But i am not getting any results even i pass those values in the variable of an where clause: below is my Procedure:
declare
TYPE crMain_record is RECORD (
v_code dummy.v_code%type,
n_no dummy.n_no%type,
[Code].....
END;"lv_character" is going to hold the multiple code values which i need to pass into whare clause of the above SQL: the totlal number of these mulitipe codes can be more then 50..
And lv_character values are commung from a setup table
lv_character varchar2(32767):= '('||''''||'COMMIS'||''''||' , '||''''||'AGY BUILDING BENS'||''''||')';
--And lv_character values are commung from a setup table.where "lv_character" holdes multipe code values...
And lv_character values are commung from a setup table and upper(d.v_code)in lv_characterif the
View 3 Replies
View Related
Mar 28, 2012
I have created a stored procedure that checks if a file exists and gets a date from the file if it exists. The date is then used as a paramter. See below:
CODEcreate or replace
PROCEDURE "P_Load_Stamp" AS
v_exists BOOLEAN;
v_length NUMBER;
v_blocksize NUMBER;
[code]...
The above codes works perfectly and I scheduled it using SQLPLUS as follows:
CODEvariable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
[code]...
My problem is that I need to pass the date from the above procedure as a parameter to another stored procedure. So I modified my code as follows (the parts in red):
CODEcreate or replace
PROCEDURE "P_Load_Stamp" (vCTIDATE OUT varchar2) AS
v_exists BOOLEAN;
v_length NUMBER;
[code]...
Now it doesn't strike me as a rights issue since I created it in the schem schema. What could I be doing wrong here?
View 1 Replies
View Related
Jul 20, 2011
In my application (forms6i) initially data will be displayed in a non-database block after a lot of calculations and validations which is time consuming.
Now, if the user want to print it as a report, we store the displayed data in a dummy table and run the report using that dummy table and then delete records from it.
Is it possible to pass the records displayed to the report as a parameter so that I can avoid Unnecessary add/delete records?
View 1 Replies
View Related
Sep 13, 2012
I am using regexp_substr to break the pipe delimited string. Want to use the output as the NUMBER input to a cursor.Its not working and not getting any error also.
Here is an example.
initial input : '5545|4124|12456'
using the sql below to break the string into columns.
select regexp_substr('5545|4124|12456','[^|]+', 1, level)) from dual
connect by regexp_substr(, '[^|]+', 1, level) is not null;
But when i try to pass the output of the above query to a cursor with input defined as number, its not accepting. I tried using to_number for the output of above query.
View 12 Replies
View Related
Feb 23, 2013
why we pass null as a argument in any method parameter....What is its use in method reference..
View 1 Replies
View Related
Aug 19, 2013
steps to achieve this report I need a Date Column on top of the page Depending on the selected date, Result should be displayed below for e.g.
suppose i select a date as 14-aug-2013
Enter Report Date ___________________ ( Date Picker) Location Name Report Date Quantity valueXYS 14/08/2013 5000 1200000ABCD 14/08/2013 2000 24000-------------------------------------------------------------------------------------------------TOTAL 7000 1224000
View 9 Replies
View Related
Mar 2, 2011
In my below query example , i have to pass more than 4000-5000 paramter in "a1.num" in below query. what is the best way to handle this, also if I pass more than 2000 paramter , the query takes a long time to execute. How can we solve the performance issue as well how I can pass more parameter.
SELECT c1, c2,
TO_CHAR (c3, 'HH24:MI'),
c4,
c5,
[code]...
View 12 Replies
View Related
Jun 15, 2012
what document I should look for to pass a cursor generated by PL/SQL to java script code in APEX?
View 15 Replies
View Related
Dec 23, 2012
My need is to pass multiple values as single input parameter into pipelined function. For example - "2" and "3" are values of input parameter "t":
with data as (
select 1 as t from dual union all
select 2 as t from dual union all
select 3 as t from dual union all
select 4 as t from dual union all
select 5 as t from dual
)
select * from data where t in (2,3)
View 2 Replies
View Related
Oct 26, 2010
i have a stored procedure whose input parameter is a varchar2 datatype.i created this procedure for an interface and tibco would be calling my procedure by passing input parameters.my problem is when there is a input string with & (ambersand) then its not working.
even i tried to pass the parameter with & in TOAD, it asks me to enter value for string.look at the sample code below which i wrote for testing purpose:
procedure is:
create or replace procedure testproc(p_in in varchar2)
is
begin
null;
end;
i pass parameter as given below:
begin
testproc('abc & def');
end;
if i run above script, it asks me to input some string value as it sees & in the string. attached is the image that shows up in TOAD. if i run below script it works. but i dont know how many &'s will be there in the input parameter. hence i cant do. and also TIBCO cant modify the input paramter while calling the procedure.
begin
testproc('abc &'||'def');
end;
View 9 Replies
View Related
