SQL & PL/SQL :: Store All Rows Of Columns Into Single Variable / Use In Inside Of Stored Procedure

Mar 6, 2012

i want to store all rows of columns into single variable and then use in inside of SP

declare
CUR_REC SECURITY_TYPE%rowtype;
begin
select *
into CUR_REC
from SECURITY_TYPE;
[code]....

it return ORA-01422: exact fetch returns more than requested number of rows error. Is any chance to implemented above scenario in oracle 10g

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: DML In Execute Immediate Inside A Stored Procedure

Apr 28, 2011

Requirement is to build procedure where it has 10-12 input variables but some of them (input variables) may at times be NULL.Based on this, i thought of getting into EXECUTE IMMEDIATE but this would just return rows i mean DML stmt for EXECUTE IMMEDIATE.Also, on the requirment is all parameters are present then result set be based on range on start and end date.

View 3 Replies View Related

How To Store Total No Of Updated Rows (number) In A Variable

Jun 21, 2011

how to store total no of updated rows (number) in a variable after executing an updation query using script

View 2 Replies View Related

SQL & PL/SQL :: How To Set Environment Variable Inside Procedure

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

SQL & PL/SQL :: Bind Variable Inside Procedure?

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

SQL & PL/SQL :: Declaring Arrays Inside Stored Procedure?

Jun 17, 2010

how can i declare an array inside a stored procedure in Oracle. Right now, I have the following declaration.

procedure MarkLoanMappings(
p_AL_LA_ID in ACTIVE_LOAN.AL_LA_ID%TYPE,
p_AL_ASG_ID in ACTIVE_LOAN.AL_ASG_ID%TYPE,
p_AL_CFH_ID in ACTIVE_LOAN.AL_CFH_ID%TYPE,
p_Period in ACTIVE_LOAN.AL_PRCS_PERIOD%TYPE)

[code]....

When I try to compile it, I get the error "component EXISTS must be declared".

View 14 Replies View Related

PL/SQL :: How To Get Some Optional Parameters Inside A Stored Procedure

Oct 1, 2013

Can we call one or more parameters inside a stored procedure call (like func_get_args in PHP) ? Example : 

create or replace PROCEDURE test_proc (
val1 in varchar DEFAULT NULL,
val2 in varchar DEFAULT NULL,
val3 in varchar DEFAULT NULL,
[code]..........

View 4 Replies View Related

SQL & PL/SQL :: How To Pass In Dynamic Variable For Calling Store Procedure

Apr 9, 2012

I have a table that has 10 columns which is used to store the customer information (e.g Gender, Age, Name). And i have wrote a store procedure to compare the before and after value of column since there has a parameter to control which column need/no need to be updated while the value being changed.

For example, master table "CUST" has column (NAME, GENDER, AGE). "CUST_TEMP" is a temporary table to store the input image which has the same table structure as "CUST".

DECLARE
bef_val CUST%ROWTYPE;
aft_val CUST_TEMP%ROWTYPE;
BEGIN
SELECT * INTO bef_val FROM CUST WHERE name = 'ABC';
SELECT * INTO aft_val FROM CUST_TEMP WHERE name = 'ABC';
[code]....

For the above case, i need to type 3 times of "sp_compare_val ( bef_val.xxx, aft_val.xxx )" on the program. And if the table has more than 10 columns, i need to type more than 10 times.Thus, is it possible to pass in a dynamic variable while calling the store procedure. let say, where the 'xxx' can be definable?

View 8 Replies View Related

PL/SQL :: Calling A Function In Remote Database Inside A Stored Procedure

Apr 9, 2013

There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:

CREATE OR REPLACE PROCEDURE TEST
(
V_COUNT OUT NUMBER
)
AS
V_FOO NUMBER(2,0);
BEGIN

[Code]...

There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................

where my mistake is?

View 7 Replies View Related

PL/SQL :: Declare Variable In Stored Procedure

Dec 21, 2012

how do you declare a variable in a store procedure

View 11 Replies View Related

SQL & PL/SQL :: Fetch All Rows Into Single Variable

Feb 21, 2012

i want to load all rows of single columns into one variable. After that, that variable will use in any side of the pl sql block.

SET serveroutput ON
DECLARE
CURSOR CUR_DATA
IS
SELECT DAS_SECURITY_CODE FROM SECURITY_TYPE WHERE ASSET_TYPE='DERIVATIVES';
rec_data CUR_DATA%rowtype;

[Code]....

how to resolve this. above query,variable holds last value which fetched from cursor.

View 9 Replies View Related

Creating And Dropping Global Temp Tables Inside A Single Procedure

Aug 20, 2010

I am writing a procedure that will be called from a java wrapper.

The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.

Create or replace proc_name ()

update table........

delete from ..........

CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)

insert into............

drop table TSAAG;

End;

creating a global temp table inside a procedure is expensive...

Do we have anything like creating table before and calling the instanse of it in procedure.

Do we have any alternatives to this..

View 3 Replies View Related

SQL & PL/SQL :: Select Into Temporary Variable On Stored Procedure For Second Insert?

Mar 30, 2011

the moment my 11g database is connecting to a php web front end. this following procedure is the one I'm having trouble with.

CREATE OR REPLACE PROCEDURE "BSISSONS"."CREATE_EXCURSION" (
min_places IN excursion.min_places%TYPE,
max_places IN excursion.max_places%TYPE,
additional_charge IN excursion.additional_charge%TYPE,

[code]...

I can select into an output variable to return the value of the primary key of the newly inserted row back into the webpage, but i need to be able to 'select into' a temp variable to insert this value into another table on the same procedure. I get complie errors when i try to 'DECLARE' a variable after the 'AS' keyword

View 2 Replies View Related

SQL & PL/SQL :: Updating Table For Different Number Of Fields Via Single Stored Procedure?

Jun 14, 2012

I've a table with fields:

create table test
( f1 varchar2(10),
f2 varchar2(10),
f3 varchar3(10)
)
insert into test values ('d1','d2','d3');
insert into test values ('d10','d20','d30');

I want to update the fields of the table as per need i.e update only one field leaving all the data of the fields as it is. Suppose I want to update only f1 (from d1 to x1) field leaving f2, and f3 as it is. I've written stored procedure to update all the fields but do not know how to do it?

Quote:CREATE OR REPLACE PROCEDURE UPDATE_TEST
( U_F1 TEST.F1%TYPE,
U_F2 TEST.F2%TYPE,

[Code]....

View 7 Replies View Related

SQL & PL/SQL :: Create Stored Procedure To Update Columns In Table

Dec 8, 2010

I have three tables fixtures, fixture_teams and team_tbl

fixtures consists of:

create table Fixture_tbl(
fixt_id varchar2(8),
fixt_date date,
fixt_time varchar2(10),
fixt_location location_t,
umpire_id varchar2(8),
player_of_match player_of_match,
home_team varchar2(20),
away_team varchar2(20),
[code]....

creating a stored procedure that updates the points column in the teams_tbl , the value that is updated in to the points column will be retrieved from the fixture_team table. so if team a has more goals than team b then the points column for team a will be increased by 6 else if the scores are equal they get 4 points each.

View 13 Replies View Related

Reports & Discoverer :: How To Call Oracle Reports Inside A Stored Procedure

Oct 14, 2010

Currently some jobs created in WBT scripting need to converted into oracle,plsql.There is one job in WBT scripting, which will invoke the oracle reports inside and generate the PDF files in the destination path as follows:

a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?USERID=%LOGONINFO%+server=rep_pscm9722+destype=file+desname=D:ORACLE10G\%CCALLRptName%+desformat=PDF+PARAMFORM=no+report=PCCALL.RDF")
a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?

[code]...

Now, i want to convert this into oracle,plsql? Is it possible or not?

View 3 Replies View Related

PL/SQL :: Concatenating Columns (in Multiple Rows) To One Column In Single Row

Nov 28, 2012

column1    column2            column3    column4

12                Mar-21-2005 BDW        blah blah blah
11               Feb-07-2001    ZV            ha ha ha
12                Jan-02-2002   YM           zuck zuck zuckI want a view that has that data like this:

column1    column2         

12                Mar-21-2005 - BDW - blah blah blah; Jan-02-2002 - YM     -      zuck zuck zuck
11               Feb-07-2001    ZV            ha ha haCan you help with SQL ?

I tried to use this Oracle LISTAGG function in the SQL, but got a "string concatenation limit exceeded"

View 3 Replies View Related

Return Multiple Rows From Stored Procedure?

May 26, 2010

How to return multiple rows from the stored procedure in ORACLE..

View 2 Replies View Related

SQL & PL/SQL :: Inserting Multiple Rows From Stored Procedure

Jun 20, 2012

Below are the data for rows that I want to insert into CUSTOMER_PRODUCT table from a stored procedure.

Instead of making round trips twice to insert these two rows, I looking for a way to pass in the data for both those rows and then insert them from within the stored procedure in one shot.

The stored procedure will be invoked by Java and .NET.

Sample Data for CUSTOMER_PRODUCT:

ROW 1:
CUSTOMER_ID : 1000
PRODUCT_TYPE : PROD123
IS_MEMERSHIP : Y
IS_EMAIL_SUBSCRIPTION: Y

ROW 2:
CUSTOMER_ID : 1001
PRODUCT_TYPE : PROD123
IS_MEMERSHIP : Y
IS_EMAIL_SUBSCRIPTION: Y

Question 1:
Should collection be used? (or) is there any other approach that could be utilized?

Question 2:
Are there any performance concerns in passing collection and iterating it to fetch value to insert into CUSTOMER_PRODUCT table?

I'm running Oracle 10g.

View 3 Replies View Related

Forms :: Code To Store Images Inside Database

Jan 11, 2011

//To load an image from the Hard Disk
//Level : Item
//Type : Trigger
//Name : When Button Pressed
Declare
[code]......

Now what should be the code to store this image from the image box inside the database? I done a lot of time with Oracle Data Base 11g with the Form Builder 6i and 10g but all in vain . No image store inside the database but browse button work well.

View 10 Replies View Related

PL/SQL :: Merge Multiple Rows Into Single Row (but Multiple Columns)

Oct 17, 2012

How to merge multiple rows into single row (but multiple columns) efficiently.

For example

IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100

where different attributes mean different thing for each Information_type. For example for Information_Type=Location

Attribute_1 means Country
Attribute_2 means State and so on.

For example for Information_Type=Stats

Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.

I want to create a view that shows like below:

IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100

View 1 Replies View Related

SQL & PL/SQL :: How To Store Value In Date Variable

Oct 17, 2012

i am having one query which is

select to_char(date '1900-01-01' + 1439/(24*60),'DD/MM/YYYY HH24:MI:ss')from dual;

when i am running this query i am getting this value: 01/01/1900 23:59:00 and this value i am inserting in one variable which has date datatype.here i am storing like this:

v_nshiftmax := to_char(date '1900-01-01' + 1439/(24*60),'DD/MM/YYYY HH24:MI:SS');

while storing getting error: not a valid month....how to store it in this variable?

View 3 Replies View Related

SQL & PL/SQL :: Using Variable Inside A View

Mar 24, 2013

i want to put a variable inside a view. create or replace view loading as

SELECT H.ORGANIZATION_ID ORG_ID,H.HEADER_ID,H.REQUEST_NUMBER, H.DATE_REQUIRED ISSUE_DATE,H.ATTRIBUTE1,
H.ORGANIZATION_ID,L.INVENTORY_ITEM_ID,SS.SALES_ID,S.SECONDARY_INVENTORY_NAME SUB_INV_CODE,S.DESCRIPTION SALESPERSON,I.SEGMENT1 ITEM_CODE,
[code]....

i want this condition to be like and to_date(H.DATE_REQUIRED,'dd-mm-rrrr') = to_date('Vdate','dd-mm-rrrr') i want Vdate as a variable parameter.

View 2 Replies View Related

PL/SQL :: How To Store More Than 32767 Bytes In A CLOB Variable

May 7, 2013

I am trying to build an XML document in a CLOB PLSQL variable. We are using Oracle 11gr2 database.

But when I am reaching more than 32767 bytes my code is failing.

Is there anyway we can store more than 32767 bytes of data in a PLSQL variable of type CLOB.

I am capturing the below error message

(ORA-06512: at "SCMSA_HIST.SCMSA_POC_HANDSET_MOBILITY_PKG", line 1480
AND LENGTH OF xmlfile -> 33078
)

I am adding my code also here for further clarification

PROCEDURE GET_HANDSET_DATA_PRC (p_ntlogin_id IN VARCHAR2,
p_handset_data OUT NOCOPY CLOB)
IS
/******************************************************************************
NAME: GET_HANDSET_DATA_PRC
PURPOSE:
Date Ver By Description
---------- --- --- -----------

******************************************************************************/
CURSOR c_region_data
IS
SELECT NVL2 (T.ntlogin, T.ntlogin, pos.ntlogin) AS ntlogin,
NVL2 (T.first_name, T.first_name, pos.first_name)
AS first_name,

[Code]....

View 8 Replies View Related

SQL & PL/SQL :: Variable Value Not Displaying Inside Loop

Jul 8, 2011

I have below peice of SQL,Im trying to form the P_attribute1 value by using the Loop index(i) but the P_attribute1 value which i assigned in declaration not displaying in the output.

DECLARE P_attribute1 VARCHAR2(100) := '110000027';
P_attribute VARCHAR2(100);
l_cnt NUMBER := 10;
v_buffer VARCHAR2(500);
v_buffer1 VARCHAR2(500);
BEGIN
FOR i IN 1 .. l_cnt
LOOP
v_buffer := v_buffer || '' || 'P_attribute' || i || '' || '!';
END LOOP;
dbms_output.put_line('v_buffer :' || v_buffer);
END;
v_buffer :P_attribute1!P_attribute2!P_attribute3!P_attribute4!P_attribute5!P_attribute6!P_attribute7!P_attribute8!P_attribute9!P_attribute10!

View 6 Replies View Related

SQL & PL/SQL :: Declaring Global Variable Inside Package?

Sep 11, 2012

I want to declare global variable inside package. get the correct query. how to assign value for that variable.

SQL> create or replace
2 PACKAGE new_pack
3 AS
4 g_id employee_details1.employee_id%type;
5 PROCEDURE emp_project(
6 st_date IN DATE,
7 Prj_id out VARCHAR2,
8 prj_name out VARCHAR2,
9 Prj_location out VARCHAR2);
10
11 END new_pack;
12 /

Package created.

SQL> CREATE OR REPLACE
2 PACKAGE body new_pack
3 AS
4 PROCEDURE emp_project(
5 st_date IN DATE,
6 Prj_id OUT VARCHAR2,
7 prj_name OUT VARCHAR2,
8 Prj_location OUT VARCHAR2)

[Code] ..........

Warning: Package Body created with compilation errors.

SQL> show error
Errors for PACKAGE BODY NEW_PACK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/12 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
constant exception
table LONG_ double ref
char time timestamp interval date binary national character
nchar

View 10 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

Forms :: Checkbox Checked On Runtime And Store In Global Variable

Feb 6, 2010

I have more then one checkbox on my form,I want to get which one is checked on runtime and store it in global variable?

View 6 Replies View Related

Server Administration :: Configure Archivelog Stored Inside ASM Diskgroup

Sep 16, 2011

I'm new on 11g, due to my limit storage with mounted internal disk, so I think, may I can store archivelog in ASM without FRA? However, I did not find any document to verify.

View 5 Replies View Related

ORA-01458 - Invalid Length Inside Variable Character String

Sep 30, 2010

Where I am copying the data from one database to another I am getting this error.

ORA-01458: invalid length inside variable character string

This is applicable for only character string.

The source Database is
Oracle9i Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

The destination database is
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved