assigning values to a particular variable that i need for my button trigger. I Understand that you can assign multiple values to a variable that has a varchar or char data type....is there a way to assign multiple values to a variable that has a 'number' data type?? I need this for my 'where' clause
declare
usergrade varchar(4) := 'pass';
user_unitcode number(6) := ;--needs three unit codes to equal pass
The goal is to create a stored procedure that will retrieve multiple values from a table.
GUI is in Java and they will trigger our procedure to show list of all employees and their roles , doj etc.
So I wrote the following procedure.
---------------------------------
create or replace
PROCEDURE emp_test(
c_cursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN c_cursor
FOR
SELECT emp_name, emp_doj, emp_role FROM emp_table ;
END;
---------------------------------
I'm using sql developer, stored procedure is compiled and I can manually run it by right clicking on the procedure and click 'Run'.
When I intend to run it by executing the script "Execute Procedure name ", I get errors.
In SQL Developer, I open new SQL file and key in
EXECUTE emp_test;
Highlight it and run the script, here is the list of errors that I get.
-------------------------------------------
Error starting at line 18 in command:
execute frm_lst
Error report:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'emp_test'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
--------------------------------------------
Issue 2:
Instead of using cursor, is there a way to use multiple parameters and insert the data retrieved from select column_name from table into these parameters and build it in a stored procedure. I tried something like below which did not work.
____________________________________________________
CREATE OR REPLACE PROCEDURE emp_test1
(e_name OUT emp_name%TYPE,
e_dob OUT Edob%TYPE)
IS
BEGIN
SELECT emp_nam, Edob
INTO e_name, e_dob
FROM emp_table
END emp_test1;
End;
______________________________________________________
Just so you know, there is no input parameter or input feed, when called the procedure should return all the values.
I am developing form, but there is issue when I Press F11 to query data.I make trigger form when-new-record-instance to assign values for item.But when i Press F11 then Block no clear.
View 1 Replies View RelatedI have this requirement, I have a following Record type within a package, instead of selecting datas into this i need to assign variables to this record type
TYPE xx_delivery_detail_rectype IS RECORD ( p_delivery_id NUMBER, p_ultimate_dropoff_location_id NUMBER, p_creation_date DATE, p_last_update_date DATE, p_container_name VARCHAR2 (30), p_inventory_item_id NUMBER, p_shipped_quantity NUMBER, p_shipment_line_id NUMBER ); TYPE xx_delivery_table_rectype IS TABLE OF xx_delivery_detail_rectype INDEX BY BINARY_INTEGER;
Say i have variables to assign to all the values within the record type, How do i do it within the package
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
View 4 Replies View RelatedI am currently working in form 10g and i m newbie to that technology...I want to debug my form and also check step step by values of variables which i defined.
View 1 Replies View Relatedneed to dynamically assign value of each column of a row type variable:The example is like that:
Table "Student_list" is as follows:
---------
COL1 VARCHAR2(50),
COL2 VARCHAR2(50),
COL3 VARCHAR2(50)
[code]....
Is there any work around where i can dynamically built my variable or table.column name and assign a value to it?
how to assign values from a "rule table" to a rowtype-variable. The ruletable contains values for different columns in different tables.Now i need to assign those given values for given columns out of that rule table to the equivalent column in a rowtype-variable.
CREATE TABLE TBRULES
(
TABLE VARCHAR2(50 BYTE)
, COLUMN VARCHAR2(50 BYTE)
, VALUE VARCHAR2(200 BYTE)
);
[code].....
find the Test Case below.
--Creation of Table
create table tb1
(ID number(4),
event varchar2(20),
vdate date);
--Inserting Values into the Table.
INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')
[Code]...
--Selecting data from Table.
SELECT * FROM TB1;
ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10
10 rows selected.
how can i display the data as below format using Oracle 9i SQL.
IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10
I have a SELECT INTO statement as follows:
-----
SELECT SUM(s1.PRODUCT_QTY) INTO anz
FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3
WHERE s1.SALES_ORDER_ID = s2.SALES_ORDER_ID
AND s2.REGION = 'ANZ'
AND s1.GCM_OPP_ID = s2.GCM_OPP_ID
[Code]..
The statement sums up all the products attributed to the region ANZ and stores it in a NUMBER variable called anz.
The issue is that I have multiple regions: ANZ, JP, ASEAN etc. Wat I would do is to create a statement for each and every region. Is there a way to consolidate them all into a single statement. that means something like
-----
SELECT SUM(s1.PRODUCT_QTY),SUM(s1.PRODUCT_QTY),SUM(s1.PRODUCT_QTY) INTO anz, jp, asean
FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3
WHERE ..................
-----
I need to extract values from string to variables as below.
declare
str varchar2(100):='Acknowledgment=1234,Order Requester=5678,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
l_a varchar2(100);
l_or varchar2(100);
l_s varchar2(100);
l_ot varchar2(100);
l_pre varchar2(100);
Begin
l_a:='1234';
l_or:='5678';
l_s:='9999';
l_ot:='1456';
l_pre:='1234,23445,56767';
end;
/
But here challenge is order of alignment change dynamically. ex as below.
str varchar2(100):='Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
str varchar2(100):='Pre=1234,23445,56767,Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456';
So how to extract Acknowledgement to l_a,Order requester to l_or ...
I have two tables A and B.
A
--
variable value
-------- ------
a 10
b 20
B
--
Exp
---
b-a
b*a
b/a
How can the variables be replaced with values(10,20) using a single query...?
I executed the following PL/SQL block in SqlDeveloper :
VARIABLE max_dept_no NUMBER
DECLARE
v_dept_name VARCHAR2(30) := '&p_dept_name';
v_max NUMBER(4,0);
BEGIN
SELECT MAX(department_id) INTO v_max FROM departments;
:max_dept_no := v_max + 20;
INSERT INTO departments VALUES (:max_dept_no,v_dept_name, NULL,NULL) ;
END;
/
And it gave the error : Quote:Error report:
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 7
01400. 00000 - "cannot insert NULL into (%s)"
The same code when executed in iSqlPlus gave no error.
Assign employees to their jobs in consideration the maximum number of employees to each jobs is 5 employee plus each job has own the maximum number of employees
we need the maximum number of employees for each job 5 to be variable when need to change this maximum for certain job , change this number from database (form the from of job ) not form code )
tables
emp
emp_no
name
manager
hiredate
salary
job
job_no
job
you can add tables or attributes to tables to complete you business.
I have two forms, FormPARN, FormCHLD.
One Text item is there in FormPARN, near to that Text item, Button is there.While clicking Button FormCHLD will be opened. Inside the FormCHLD, i will do some calculation and i need to store the result in FormPARN's Text Item.
How can i do it?
I have two blocks in Form (Forms 10g) .On the Block B2 I have populated the Company Id by using the PLSQL Table.Now I selected more than two company ID from that Block ...I just want to keep the Company ID in the Temp Field on the Block B1.which I have selected on the Block B2. how can I assign more then one Company ID to That Temp Field on B1.
View 8 Replies View Relatedwe are currently migrating from forms 6i to 11g. We would like to cleanup our global variables at runtime.
Is there any way to list the global variables at runtime?
Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.
Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?
Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=p1;
END;
------------
Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
v1 varchar2(100):=p1;
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=v1;
END;
We have an application, where we invoke a URL pointing to a folder on the Application Server, so that users can download files from the same.
There is user authentication provided for the URL.
Example :
Our application ABC (example) which is built with Oracle Forms 10g, hosted on Application server (itxxx123) inovkes a module GEN9999 which on pressing a button calls a URL which is open in the Internet Explorer browser.
If the URL has been called from the Forms or the Application (ABC), then it should be allowed.
If the URL is called standalone, by Opening IE Browser and invoking the URL, this should not be allowed.
However, there have been security concerns raised during Audit, saying that users can access the URL directly from the Browser instead of the Application. Also the URL is saved the Browser history, so even if the user is no longer working in the Application Team, and has moved out of the team, then the ex-user can still access the URL and view and download files.
Is there any way to restrict access to the URL such that is only accessible when it is called from the Application, and that access is restricted when directly called from the Browser.
I have seen that environment variables in httpd.conf may be usable for the above. However, not sure how to use them from Oracle Forms.
I have a field that may look like this:
AS01 AB CD EF
I want to check to see if that string contains any of the values in:
select code from codes_table;
For example, if
select code from codes_table;
returns:
code
-----
AB
LM
NO
PQ
then the query should return 'True'. Using the string above it would return 'True' because 'AB' from the string exists in the table codes_table. Pseudocode would look something like this I guess:
if the input string
contains any of the codes
in the field 'code' from table 'codes_table'
then
'OK'
otherwise
'No good!'
i'm trying to put more than one value in one column (by putting value '26','63' in column), so i have next problem:
1. Query select cr_pjid from acc_users where username='ACCBTPS121' give result
CR_PJID
---------
'26','63'
2. Query select * from acc_accbtp_nova_view where ed_id=2 and to_char(pj_id) in ('26','63'); return 186 rows.
3. Query: select * from acc_accbtp_nova_view where ed_id=2 and
to_char(pj_id) in (select cr_pjid
from acc_users
where username='ACCBTPS121'); doesn't return any row...
Most of the code is working properly except when it come to a person with more than one email type in the table:
Below is the
select emal_pidm, emal_email_address, emal_emal_code,
case
when emal_emal_code = 'PER'
and lag(emal_emal_code,1,'?') over (partition by emal_pidm order by emal_email_code) = 'EMPL'
then emal_email_address
[code]......
Below is Sample data for testing
INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code)
VALUES (1024069, 'emmaus.ferdinand@xxxx.edu','EMPL');
INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code)
VALUES (1024069, 'emfer1@xxxx.edu','PER');
[code].......
The attachment is what the output should look like.
I am trying to do something like this..
declare
a constant variable (1,2,3);
b number;
begin
select x
into b
from y
if b in a then...
What is the correct way of declaring multiple values in a constant and then using the IN clause.
i am using the following within queries:
decode(qt_flag,'KT','Y',decode(qt_flag,'IT','Y','N'))
It works fine but when i use in ETL tool it is not working. Is there a way to use decode just once with multiple values like above.
Here is my requirement..... I am pretty new to PL/SQL.
This is the procedure
CREATE OR REPLACE PROCEDURE ABCPROC.SP_ABC
(
XML IN CLOB,
P_refcursor OUT SYS_REFCURSOR
)
AS
BEGIN
[code]........
For each account number (I_AC), we have multiple rows in ABC_SVC table. I want to return these values in the refcursor. The issue with above SQL program, the row is returning only when the account(I_AC) have values for all the products 100,101,102. If the row does not exist for one account, then the account row is returned with other products.
I want my output should be in the below format
ACCOUNT_NUMBER COMMISSION CONSUL CONTRA
1YYN
2NN
3N
I have used sql for my school projects and work projects. not too extensively though. I came across this issue and read a lot of blogs but still not luck.
hopefully my issue can be solved here.
Data:
Sub_grp_nbr Prodt_ctgy_cd Extra_column
ABC123 05 1
ABC123 02 2
ABC012 05 3
ABC456 02 4
ABC456 05 5
ABC789 05 6
I need to obtain all the sub_grp_nbr's that have a prodt_ctgy_cd of 05 but not 02. so according to the data above, i should only get the results of where extra_column = 3 and 6
I have a source view where I have some invalid records and those should be found based on codes present in another table.
For eg. from source the records come like
****************SIINNSFDFD****FDFDF2******8
**********TABLE****************FDFSFSSFASFAS********
and if my reference table has values
SIINNSFDFD
TABLE
then these values are present as substring in the particular column in the source view. So I need to flag those records. For every record, I need to check whether all the values present in the reference table matches or not. If it matches then it should be flagged.
I can use in operator as we are not checking for the exact match and we are checking whether that value is present anywhere in that column record.
Looping results in performance issue. We can use PL/SQL for this. As the source view is put into a ETL internal file.
I am trying to do something like this
SELECT clave_grupo FROM SS_TTABLAS
WHERE CLAVE_GRUPO IN
(
CASE WHEN (condition) THEN 'v1'
ELSE ('v1','v2',v3')
end
)
This is the error msg:
ORA-00907: missing right parenthesis
just before ,'v2',v3')
For the below query:
SELECT id, pob,exp
FROM emp
where exp= (:exp)or @exp is null
O/P:
id pob exp
_____________________
1 CT 2
2 NJ 3
3 NY 2
It takes only one value of :exp but, I would like to give multiple values separated by ','. My problem is the parameter can be either single value or Blank or multiple values. My code do work for single value and Blank, Now how to implement multiple values .
I have a table as follows
create table teststr (indname varchar2(20),
counter1 number,counter2 number,counter3 number,counter4 number);
insert into teststr values('a',10,20,30,30);
insert into teststr values('b',10,20,5,3);
insert into teststr values('c',2,4,5,2);
insert into teststr values('d',1,2,3,4);
insert into teststr values('e',4,5,4,4);
Now i need the output if any of the column values are same.
output should be
select indname from teststr where counter1=counter2
or counter1=counter3 or counter1=counter4
or counter2=counter3 or counter2=counter4
or counter3=counter4
a
c
e
Is ther any other way to write the query instead of the numerous or conditions if i want to compare the column values in a table.