SQL & PL/SQL :: Setting Variable In Procedure Based On Parameter?
May 24, 2011
I am reading in a selection of parameters. I have created a new variable which I want to set according to the value of one of the input parameters.
I am doing this straight after declaring the variable, but before the cursors and BEGIN statement It is throwing an error when I do this - but I have to do it before the cursors.the variable I am setting is: v_fptransType you can see the IF statement towards the end of the code.
the error I am getting is:Error(28,3): PLS-00103: Encountered the symbol "IF" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor The symbol "begin" was substituted for "IF" to continue.
beginning of the
create or replace
PROCEDURE "P_GLPOST" (i_entity IN varchar2, i_transType IN varchar2, i_startDate IN VARCHAR2,
i_endDate IN VARCHAR2, i_accountPeriod IN VARCHAR2, i_includeInternals IN NUMBER, i_chargeable IN NUMBER, i_trialPost IN NUMBER,
i_postingReport IN NUMBER, TESTER IN VARCHAR2) is
--set serveroutput on size 1000000;
[code].....
View 8 Replies
ADVERTISEMENT
Dec 27, 2012
I have a procedure named 'GetShipperinfo' which takes i_name as input and needs to build a cursor taking i_name as input
i.e.
The following sql when executed at sqlplus prompt gives correct results.
select dept, supplier, shipper_id
from shippers
where upper(shipper_name) like upper('Frank Robert%');
How can I transform this inside a cursor within a procedure passing 'Frak Robert' value as i_name input.
i.e I should be able to call the procedure as follows
sql> variable v1 varchar2;
sql> exec pkg_shipment.GetShipperinfo('Frank Robert',:v1);
sql> print :v1;
Should the cursor inside the procedure be built as follows
cursor c1 is
select dept, supplier, shipper_id
from shippers
where shipper_name like ''||upper(i_name'%''||)'';
Iam unable to build the sql for the cursor.
View 3 Replies
View Related
Apr 1, 2011
I need to execute a procedure based on a value in a form. So the procedure name will be changing for value selected in a list.
I need to know a method where i could store the procedure name in a table and when ever i select a value from the list, the respective procedure needs to be executed.
View 1 Replies
View Related
Dec 12, 2012
i have the below query
select to_char(report_date, 'YYYY MM Mon'), count(1) no_of_times
from (
select to_date('&&YYYYMMDD', 'YYYYMMDD')+rownum report_date
, mod(rownum,14) mod_result
from all_objects
[code]...
need to convert as procedure based on input date parameter.I will pass the input date from java environment and need to see the sql query output in front end.
View 7 Replies
View Related
Apr 12, 2011
I'm trying to return the number of records in my link table that contains the excursion_id I pass in by counting them. It doesn't seem to like the select count(*) into my output variable.
create or replace
PROCEDURE BOOK_PASSENGER(
EXCURSION_ID IN excursion_booking.excursion_id%TYPE,
PASSENGER_ID IN excursion_booking.passenger_id%TYPE,
NUMBER_BOOKED OUT NUMBER)
AS
BEGIN
INSERT INTO EXCURSION_BOOKING VALUES(EXCURSION_ID, PASSENGER_ID, NULL);
SELECT COUNT(*) INTO NUMBER_BOOKED FROM EXCURSION_BOOKING WHERE EXCURSION_ID = EXCURSION_ID;
END BOOK_PASSENGER;
View 6 Replies
View Related
Nov 24, 2012
If I have to create a database on server which has already 5 databases running , how will I set the memory (pga,sga) values . AFAIK it depends on o/s is linux and database version is 10g and 11g .
1)free avaialble memory on server .
2)kernel parameters .
what else has to be considering while initialize memory parameters .
View 6 Replies
View Related
Mar 26, 2013
I have a simple question about database sessions. The value of parameter "sessions" is set to 500 and the users connect to database through an application server(Jboss). There are more than 500 users connect to the database through application.
My question is, how more than 500 users can connect to the database without any issue, if we set the value of "sessions" parameter to 500?
View 2 Replies
View Related
Jun 27, 2012
I am planning to set QUEUESIZE parameter in listener.ora. In a non-rac database, we can set in %ORACLE_HOME%/network/admin/listener.ora
(ADDRESS = (PROTOCOL = TCP)(HOST = servername)(PORT = portnumber)(QUEUESIZE=n))
How to set the QUEUESIZE in a RAC Database with SCAN feature. listener.ora inside the grid home contains the following information
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
and the endpoints_listener.ora contains the following (edited the host and ip details for maintaining confidentiality)
LISTENER_HOSTDB02=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostdb02vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=<ip of hostdb02>)(PORT=1521)(IP=FIRST)))) # line added by Agent
As per my understanding, QUEUESIZE parameter can be set inside the endpoints_listener.ora
how to make changes in a oracle 11g rac environment.
View 4 Replies
View Related
Aug 8, 2012
The following procedure stores the retrieved data in o_ref_primary_dept collection variable, suppose I want to display the data from that variable while executing what code I have to write.
CREATE OR REPLACE PROCEDURE sp_ost(
o_ref_primary_dept OUT PRIMARY_DEPT)
IS
l_primary_dept LONG;
[code].....
how to use collection variables as OUT parameters in procedure.
View 1 Replies
View Related
Nov 25, 2012
how to set the value of variable with parameter like below?
example :
I want the value of these variables define like below
var1 := 1;
var2 := 2;
var3 := 3;
[Code]....
error of the syntax is ... bad bind variable 'var' how to interpret variable var1 from var||j.
View 3 Replies
View Related
Aug 8, 2012
The following procedure stores the retrieved data in o_ref_primary_dept collection variable,suppose I want to display the data from that variable while executing what code I have to write.
CREATE OR REPLACE PROCEDURE sp_ost(
o_ref_primary_dept OUT PRIMARY_DEPT)
IS
l_primary_dept LONG;
BEGIN
l_primary_dept :=
'SELECT emp_obj(empno,ename)'
|| ' FROM emp';
EXECUTE IMMEDIATE l_primary_dept BULK COLLECT INTO o_ref_primary_dept;
END;
how to use collection variables as OUT parameters in procedure.
View 1 Replies
View Related
Mar 6, 2010
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]....
View 8 Replies
View Related
Apr 17, 2002
I'm wondering how I can use a counter to number records in a table I'm inserting into. I need the counter to reset based on changing data in my table. For example, I have the following
seq_number name_type
1 Short Name
1 Short Name
1 Short Name
2 Short Name
2 Short Name
I'd like my results to be the following:
seq_number name_type
1 Short Name - 1
1 Short Name - 2
1 Short Name - 3
2 Short Name - 1
2 Short Name - 2
I'd like my counter to increment so that I can add a sequence number to the end of my name type, but when my seq_number field changes I'd like to reset and restart my counter.
View 3 Replies
View Related
Apr 26, 2010
In the query below, I'm attempting to replace task codes with task descriptions by left joining to a multi-purpose control table.
In our corp, the description for the task code varies based on the value of change_program in each record, so rather than referencing the value of code_index.tabl statically as I do in the query below (201), I need a dyanamic variable to be defined for each record based on the value of work.change_program, which would represent the value for code_index.tabl.
For each record
if change_program =1 then v_tabl = 201
elseif change_program =2 then v_tabl = 202
elseif change_program =3 then v_tabl = 203
else v_tabl = 201
how to declare and use variables.
SELECT account,
change_program,
task_code_01,
task_code_02,
task1.longdesc,
task2.longdesc
FROM work
[code]....
View 4 Replies
View Related
Aug 30, 2011
how to use different where caluse based on different in parameter. say I pass 1,2 as parameter if it is 1 then the where condition > 10 else if it is 2 then the where condition <5 the where conditions are placed in cursor for loops.
View 2 Replies
View Related
Jan 11, 2013
I need to check the condition based what string I am sending. Suppose I am calling with
1.'a,b,c' then it should check all the if blocks.
2.'a,b' then it should check only 'a' and 'b' if blocks.
Is it possible to do so?
DECLARE
a VARCHAR2(5):=0;
b VARCHAR2(5):=0;
c VARCHAR2(5):=0;
PROCEDURE p1(
p_str VARCHAR2)
IS
BEGIN
[code]....... I
View 7 Replies
View Related
Jun 29, 2011
I have created the Procedure P_GET_CURRENCIES. I want to sort the cursor query based on the Input parameter P_SORT_ORDER. For example if i pass 2 for P_SORT_ORDER then my query should be sorted by 2nd column. But i'm not getting correct Output.
PROCEDURE P_GET_CURRENCIES(P_START_ROW_NUM IN INTEGER
,P_END_ROW_NUM IN INTEGER
,P_SORT_ORDER IN number
,P_CURRENCY_DATA OUT SYS_REFCURSOR
,P_RETURN_MESSAGE OUT VARCHAR2 )
AS
[code]....
View 3 Replies
View Related
May 26, 2010
Below is the code as currently written which works fine for Delta records processing (based on the field called activ_dt).
/*
Custom extract
Project: XYZ Price Data Extract
Product: EOWin 4.02 - Oracle database
Use : Script to create the above XYZ Extract and spool the results to a text file
Input Parameters:
[code]....
My problem is I am trying to add a 3rd parameter which will tell the extract to pull all of the data or just the deltas. So I added &3 to the comments and I have tried putting CASE or DECODE functions into the WHERE clause with no luck. I also tried to do a CASE or DECODE with two different SELECTS, again with no luck.
An example of my failed attempt to add &3 and handle F or D
/*
Custom extract
Project: XYZ Price Data Extract
Product: EOWin 4.02 - Oracle database
Use : Script to create the above XYZ Extract and spool the results to a text file
Input Parameters:
[code]....
View 16 Replies
View Related
Aug 2, 2013
I need to order the result set with different data types based on the input parameter.
select * from scott.emp sc order by decode('&input_parameter',1,sc.empno,2, sc.ename);
If the input_parameter is equals to 1 then,ordering should be based on EMPNO which is Number data type.
If the input_parameter is equals to 2 then,ordering should be based on ENAME which is Character Data type.
Above query was failed for input_parameter 2,as we know that decode should return same data type.
View 6 Replies
View Related
May 10, 2013
I am new to PL/SQL, worked mostly on SQL server, I have to change the table name dynamically based on the parameter.and used a ref_cursor to display the results in a report. when I execute it throws me an error.
create or replace procedure test1 (
p_eod_date IN VARCHAR2,
p_link IN NUMBER,
c_rec IN OUT SYS_REFCURSOR)
[code]....
View 10 Replies
View Related
Apr 9, 2013
I need to accept value for some variables using substitution variable. I have a number of variables for which I need to take the value on run time. I need to prompt a parameter based on the value provided earlier.
For example;
There is prompt to enter country. If the user enters country as India it should prompt for the state else it should not prompt for state.
How can this be handled in SQL?
View 3 Replies
View Related
Nov 10, 2010
Need to access data in a table base on user parameter input where the data is stored like a spreadsheet with column headings JAN, FEB, MAR... and the rows are the years. Is there a way to create a generic SQL statement so that I don't have to have 12 if statements in the procedure?
View 5 Replies
View Related
Apr 26, 2013
I have created the following procedure. Since I am using this first time I don't know how to execute this.
CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.ram_package
IS
TYPE type_ots IS TABLE OF ORDER_TREND_SCORE%ROWTYPE INDEX BY PLS_INTEGER;
PROCEDURE InsertTrend( P_TYPE_OTS_REC IN type_ots );
END;
/
[Code]...
View 2 Replies
View Related
Nov 24, 2010
I am calling a select query inside a procedure but i need to set environment variable 'set linesize 200' inside that procedure but i am not able to create the procedure due to some error. I am attaching the procedure query here with:
before the select query i need to insert this environment variable : "set linesize 200"
create or replace
procedure TABLESPACE_USAGE
is
l_mailhost VARCHAR2(64) := 'ip address';
l_from VARCHAR2(64) := 'email id';
l_subject VARCHAR2(64) := 'TABLESPACE_USAGE1';
l_to VARCHAR2(128) := 'email id';
[code]......
View 2 Replies
View Related
May 20, 2013
I am running this procedure but it will not compile. I get the error "PLS-00356: 'REC.XX' must name a table to which the user has access"
All of the query results from the cursor are correct.
create or replace procedure SWDCADMIN.Hard_Delete_Client( cltId IN number)
IS
cursor c1 IS
select
t1.table_name xx,
t1.owner || '.' || t1.TABLE_NAME uu,
[code]...
View 15 Replies
View Related
Sep 16, 2011
I am using ORACLE SQL developer.. I am trying to schedule a package to run daily..here is the overview of my package..
-----
create or replace
PACKAGE BODY xxx_MTO_yyy
AS
PROCEDURE yyy_mto (p_message OUT VARCHAR2, p_detail OUT VARCHAR2, p_value OUT VARCHAR2) IS
XXXXXX
CXXXXXX
XXXXXXX
END yyy_mto;
end xxx_MTO_yyy;
Now I created schedule as shown (The sql is processed)
begin
dbms_scheduler.create_schedule
(schedule_name => 'MTO_DAILY',
start_date=> trunc(sysdate)+6/24, repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;',
comments=>'Runtime: Every day (Mon-Sun) at 6:00 );
END;
Now I am creating SCHEDULER PROGRAM as shown (sql processed)
begin
dbms_scheduler.create_program
(program_name=> 'mto_DATA',
program_type=> 'STORED_PROCEDURE',
program_action=> xxx_MTO_yyy.yyy_mto',
enabled=>true,
comments=>'Procedure to collect session information'
);
end;
Now i am creating the scheduler jobs as shown (here also the sql processed)
begin
dbms_scheduler.create_job
(job_name => 'str_data',
program_name=> 'mto_DATA',
schedule_name=>'MTO_DAILY',
enabled=>true,
auto_drop=>false,
comments=>'nil');
end;
Now I have two questions.
a) the job should have been visible under the "jobs" tab in sql developer. I dont see that.
b) When I tried to run the job manually using
**********
(BEGIN
DBMS_SCHEDULER.RUN_JOB (
JOB_NAME =>'STR_DATA'
);
END;
it failed , saying that "ORA-06553: PLS-ORA-06553: PLS-306: wrong number or types of arguments in call to 'yyy_MTO'". When defining the "dbms_ scheduler. create_program" object , how can I define the arguments?. My procedure has 3 variable out arguments?
View 9 Replies
View Related
Jun 18, 2013
I have two procedure , from first procedure having some ref cursor output.
from second procedure I need to call first procedure and i need to process ref cursor output from first procedure so I decide to use bind variable to process ref cursor output but it showing error .
can I define bind variable inside the procedure , then how can I define it .
SQL> CREATE OR REPLACE PROCEDURE emp_by_job (
2 p_job VARCHAR2,
3 p_emp_refcur OUT SYS_REFCURSOR
4 )
5 IS
6 BEGIN
[code].....
View 10 Replies
View Related
May 28, 2013
I have started reading ORACLE 11g R2 PL/SQL programming doc, and I am stuck at bind variable. I just copied the example in the books and found error -
First I have written below procedure and compiled successfully -
CREATE OR REPLACE PROCEDURE FORMAT_STRING ( string_in IN OUT VARCHAR2) AS
BEGIN
string_in := '[' || string_in || ']';
END FORMAT_STRING; Then I have written below function and also compiled successfully -
create or replace function join_strings (str1 varchar2, str2 varchar2)
[code]......
View 3 Replies
View Related
Aug 30, 2012
how can I pass the sh variable (.i.e file name stored in sh variable called($F)) as a input of below mention procedure (YODEL_XL_ INS_SDG_ COMMER_ PROD)
for F in *.dat; do
#
echo $F
#
#sqlldr apps/apps control=$CONTROL data=$F
# Below Part is used for Add the file name into table
[code]...
View 8 Replies
View Related
Dec 21, 2012
how do you declare a variable in a store procedure
View 11 Replies
View Related
