SQL & PL/SQL :: Table Variable Or Collection

Jul 11, 2012

Is there any table except (global temp table and permanent table) which can be used to store data and be used in inner, left and right join for a session.

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: Using Collection Variable As OUT Parameter

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

SQL & PL/SQL :: Append Collection Variable

Oct 11, 2012

Declare
Cursor c1...;
Cursor c2...;
begin
open c1 ;
fecth c1 bulk collect into v1;
close c1;

[Code]...

Is there any way by which if condition gets true then v1 gets appended rather than being overwritten?

declare
type lst_deptno is table of dept.deptno%type index by binary_integer;
type lst_deptno_emp is table of emp.deptno%type index by binary_integer;
v_deptno lst_deptno;
v_deptno_emp lst_deptno_emp;
cursor c1 is select deptno from dept;

[Code]...

View 8 Replies View Related

PL/SQL :: Using Collection Variable With OUT Parameter?

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

PL/SQL :: Open Ref Cursor Using Collection As Bind Variable

Feb 26, 2013

Is it possible to bind collection while opening a ref cursor. Find below the code that I am trying. My goal is to open cursor once using collection variable. Can it be done using DBMS_SQL ?

DECLARE
TYPE typ_emp_rec_in IS RECORD
(
deptno NUMBER,
sal NUMBER
[code]......

View 4 Replies View Related

SQL & PL/SQL :: Error In Reading Data From Collection Variable For Ref Cursor

Jun 19, 2013

While reading data from collection variable using ref cursor . I am getting the below two errors.

PLS-00382:Expression is of wrong type
ORA-22905 Cannot access rows from a non-nested table item.

CREATE OR REPLACE PACKAGE APPS_GLOBAL.GIIOMEGAORDERLIST
AS
TYPE BU_LIST_TYPE IS TABLE OF VARCHAR(50);
TYPE OFFER_DETAIL IS RECORD
(
GII_BU VARCHAR(50),
GII_OFFER NUMBER,
[code]........

View 4 Replies View Related

SQL & PL/SQL :: Sorting Collection In Nested Table

Sep 11, 2012

I'm trying to sort a collection in a nested table in PL/SQL so these value can be used later for a display and for export to Excel. The sort is failing.

-- PLS-00642: local collection not allowed in SQL statements

-- PL/SQl: ORA-00902: invalid datatype

The error message are also noted below in the code on the line that fails.

A quick overview of this code- Using a nested table MyNestedTable the values from several select queries are combined into MyRecordsetZero using CURSOR, and MULTISET UNION. I'm trying to either sort MyRecordsetZero or populate MyRecordsetSorted with the sorted values for futher use.

IMPORTANT: The code is running in an enviroment that does not have permission to create.

Versions:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Developer: 8.0.0.1480

DECLARE
-- Declare the variable MyTID to be used by select statements and
-- set its data type and max character count
MyTID varchar2(10);
[code]....

View 13 Replies View Related

SQL & PL/SQL :: Sorting Nested Table / Collection

Nov 10, 2011

In test.pks file I declared the following type

TYPE tab_tests is table of NUMBER(15);

In test.pkb, I have the following procedure

PROCEDURE report (
i_cid IN NUMBER
)
IS
test1 tab_tests := tab_tests();
test2 tab_tests;
BEGIN

-- I populate test1 with the data and it works fine. But when I tried to make a sorted nested table with the following command.

I got this error 'ORA-00902: invalid datatype',

a nested table than the following, like a built in Oracle collection method?

execute immediate 'select cast( multiset (select * from table(test1) order by 1) as tab_tests) INTO test2 FROM dual';
END;

View 16 Replies View Related

SQL & PL/SQL :: Replace Temp Table With Collection?

Nov 4, 2011

how I can replace gtt in the following procedure with an Oracle Collection?

gtt or Collection is more efficient?
PROCEDURE report_gen (
i_table IN NUMBER
)
IS
BEGIN
create global temporary table test_gtt (tid NUMBER(15));

[code].....

View 6 Replies View Related

SQL & PL/SQL :: Use Collection In Select Statement As Table?

May 26, 2011

i'm trying to use a collection in a select statement as a table, but i've got an error which i don't understand.

This exemple is very simple. I'm trying to validate the solution. The object will contain the result of a query from a big table, and i have 4 request to make on it. I don't want to select 4 times the big table to get the result, but i need the whole result to make my requests (intersect, minus and union)

CREATE OR REPLACE
TYPE zy_w AS OBJECT(
CODE_INFORMATIONVARCHAR2(4),

[Code]....

****************
Rapport d'erreur :
ORA-06550: Ligne 12, colonne 3 :
PL/SQL: ORA-00947: nombre de valeurs insuffisant
ORA-06550: Ligne 5, colonne 3 :
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

View 9 Replies View Related

SQL & PL/SQL :: Collection Data - Get All Records From Table?

Nov 5, 2012

CREATE TABLE table_a(f_table_type_id NUMBER, f_bet_amount NUMBER, f_win_amount NUMBER);
CREATE TABLE table_b(f_table_type_id NUMBER, f_bet_amount NUMBER, f_win_amount NUMBER);
CREATE TABLE table_c(f_table_type_id NUMBER, f_bet_amount NUMBER, f_win_amount NUMBER);
INSERT INTO table_c VALUES(3,100,100);

[code]...

for each of the is which I get in v_ids, if there are records in a,b,c then I should get all those records, but the above loop is not working.

My final result should be LIKE :
f_table_type_id f_bet_amount f_win_amount
1 200 200
2 100 100
3 100 100

View 7 Replies View Related

SQL & PL/SQL :: Use A Select Statement On Collection Table?

Sep 1, 2011

there is a possible to use a select statement on collection table?

View 1 Replies View Related

Application Express :: Automatic Update Of Collection And Table?

Oct 29, 2013

I am trying to update a collection and a table.I got some example code and installed on oracle.com, but I just can't seem to get it to work.I always get an errorORA-20001: Current version of data in database has changed since user initiated update process.I think the problem lies with comparing the checksums, but I cannot spot the mistake 

View 2 Replies View Related

PL/SQL :: Creating Collection And Joining List To Database Table?

Jul 31, 2012

based on a provided list of key values, joining the collected list against a source table to retrieve additional information related to the key. In this simple example, the procedure accepts a list of employee numbers. The goal is to print a list of names associated with those numbers.

The method is to materialize the list of employee numbers as rows and join those rows to a source table to get the names. I have used BULK COLLECT. Why we cannot cast PLSQL tables using a type defined in the procedure's specification (why the type needs to exist as an object before we can cast it, like this:

SELECT * FROM TABLE ( CAST ( SOME_FUNCTION(&some parameter) AS SOME_TYPE ) );

here is my demo SQL, which you should be able to execute against the SCOTT schema without any change

declare
type employee_numbers is table of emp.empno%type index by binary_integer;
type employee_names is table of emp.ename%type index by binary_integer;
type employees_record is record (empno employee_numbers, person_name employee_names);
records employees_record;

[code]...

View 12 Replies View Related

SQL & PL/SQL :: Creating PLSQL Collection Table Type With Dynamic Query?

Jul 12, 2013

How can I create PLSQL table type dynamically?

I will have to take the Employee Names and create a table structure. Number of employee names can vary from day to day. So, whenever I execute my procedure with Table type, I will have to build the table columns with the employee names.

View 2 Replies View Related

PL/SQL :: Related Collection Within A Collection

Sep 20, 2012

There is a nested table with in a nested table type and i want to print the value and again assign a new value to the next subscript and i have tried a lot but couldn't find any solution.

declare
type type_name is table of varchar2(10);
type type_name1 is table of type_name;
names type_name1:=type_name1(type_name('hello'));
begin
-----HOW TO PRINT A VALUE--------
-----HOW TO ASSIGN A NEW VALUE TO NEW SUBSCRIPT
null;
end;

1) need to print the values of names(1)
2)Assign a value to names(2)

View 3 Replies View Related

SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable

Aug 30, 2011

I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.

Explanation:

In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.

I need to query on this dynamic table and return the result as return result. But i am not able to do it.

Here T_web_loylty_report_table is a type.

CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......

View 2 Replies View Related

PL/SQL :: How To Create One-column Collection From Another Multi-column Collection

Mar 22, 2013

I am describing a SQL statement to get it's column list:DECLARE

cur     NUMBER;
col_cnt INTEGER;
rec_tab DBMS_SQL.DESC_TAB;

[Code]....

Now I need to get out the columns list from rec_tab.col_name and put it to my_colls collection. Have Oracle any build-in to do that?

View 4 Replies View Related

SQL & PL/SQL :: Table As Out Variable?

Mar 2, 2012

I have plsql table as out parameter in one function.From another function i called that function ,its executing perfectly but while returning to called place its saying a character to numeric conversion error.I checked all the l

Note: i can not able post all that functions here.

View 12 Replies View Related

PL/SQL :: Selecting Table Name Under A Variable

Mar 21, 2013

correct this one.

declare
v_ename varchar2(10):='emp';
begin
for j in (select ename from v_ename)
loop
dbms_output.put_line(j.ename);
end loop;
end;

getting error v_ename table does not exists. i should use v_ename, as i dont know the table name.

View 4 Replies View Related

PL/SQL :: How To Use Value Taken In Variable As Table Name In Query

Jul 22, 2013

I am fetching a value in a variable as: 

<select application_short_name into l_appl_nm from fnd_application where application _id=:p_appl_id> 

Now I need to use the value fetched in variable "l_appl_nm" as a table partition name in next query. 

View 8 Replies View Related

Inserting Table Data Into A Variable

Sep 3, 2012

I am trying to insert a column into a variable from a trigger.

Here is the code that i have:

CREATE OR REPLACE TRIGGER BUYER_after_update AFTER UPDATE ON buyer
FOR EACH ROW
DECLARE
v_key varchar2(10);
BEGIN
select ID into v_key from buyer;
insert into message_log_table (table_name, message_comments)
values
('Buyer', 'Buyer '||v_key||' has been updated');
end;
/

When I run the above I get the following compiler error:

[Error] ORA-00904 (6: 12): PL/SQL: ORA-00904: "ID": invalid identifier

Since ID is defined in my BUYER table I do not understand what the error means.

Here is my create table statement:

CREATE TABLE BUYER
(
ID VARCHAR(50) NOT NULL PRIMARY KEY,
FNAME VARCHAR(50) NOT NULL,
LNAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(50) NOT NULL,
CITY VARCHAR(50) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP_CODE NUMBER(5) NOT NULL
);

View 1 Replies View Related

SQL & PL/SQL :: Bind Variable - Invalid Table Name

Jul 13, 2010

create table t (
col1 varchar2(2),
col2 varchar2(1),
tab_name varchar2(50));

[Code]....

In this case :v will be replaced with t1. I got the error invalid table name.

where table t1 has its own structure.

View 5 Replies View Related

PL/SQL :: Declare Variable As Row Type For A Table?

Jul 19, 2012

Can I declear a variable in PLSQL as the row type for a table, who's name is unknown during compile time, but will be determined when the PLSQL is runnning. The code is like following:

Procedure operTable( tableName IN VARCHAR2)
IS
TYPE ty_Row IS tableName%ROWTYPE
v_Row ty_Row;
CURSOR v_quey_cur

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Create Table - Tablename With Data From Subselect (variable)

Sep 2, 2013

I wish to create a table with week-number as suffix _36 or _30

CREATE TABLE TEST1_$WEEKNUMBER AS (SELECT * FROM TEST1);

$WEEKNUMBER is this statement:

SELECT TO_CHAR (TO_DATE(SYSDATE, 'dd.mm.yyyy'), 'IW') from DUAL
CREATE TABLE doesn't accept subselect

How to do that with SQL?

View 8 Replies View Related

SQL & PL/SQL :: Add Variable Number Of New Columns To Existing Table Temp

Feb 24, 2010

I want to add a variable number of new columns to an existing table temp (with column provided).

Example:

NewColumnNo = 4
-> the columns shall be named rate_1, rate_2, rate_3 and rate_4
-> the values shall be = Column / NewColumnNo

The result shall be like this:

create table temp_res (prodid integer, rate_1 number, rate_2 number, rate_3 number, rate_4 number);
insert into temp_res values(1, 0.25, 0.5, 0.75, 1);
insert into temp_res values(2, 0.25, 0.5, 0.75, 1);
insert into temp_res values(3, 0.25, 0.5, 0.75, 1);
insert into temp_res values(4, 0.25, 0.5, 0.75, 1);
insert into temp_res values(5, 0.25, 0.5, 0.75, 1);
insert into temp_res values(6, 0.25, 0.5, 0.75, 1);
insert into temp_res values(7, 0.25, 0.5, 0.75, 1);
insert into temp_res values(8, 0.25, 0.5, 0.75, 1);
insert into temp_res values(9, 0.25, 0.5, 0.75, 1);

View 8 Replies View Related

SQL & PL/SQL :: Inserting Result Set Of Query Into Corresponding SQL Table Type Variable?

Aug 28, 2012

I am unable to insert the result set of query into corresponding SQL Table type variable where as same functionality can be accomplished by PL/SQL table type variable. Can't we access the same by using SQL type variable?

Ex:

Step 1:

SQL Object type , Table type Objects creation :
drop type sql_emp_tab_type ;
drop type sql_emp_type ;
create or replace type sql_emp_type as object
(
empno number,
ename varchar2(20),

[code]...

Step 2: Accessing the table type object from PL/SQL block

SQL>
SQL> declare
2
3 tab_type_var sql_emp_tab_type := sql_emp_tab_type();
4
5 begin
6
7 tab_type_var.extend(10);

[code]...

Step 3: Instead of SQL Table type , if we define the corresponding PL/SQL table type variable

SQL> declare
2
3 --tab_type_var sql_emp_tab_type := sql_emp_tab_type();
4
5 type pl_sql_emp_type is record
6 (
7 empno number,

[code]...

View 3 Replies View Related

SQL & PL/SQL :: How To Get Left Padded Sequence Values In Variable To Insert It In Table For ID Creation

Feb 22, 2010

I have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile. The sequence is used to generate ids as :

CREATE SEQUENCE AAA_FOC.TRAINEE_ID INCREMENT BY 1 MINVALUE 0 MAXVALUE 999 NOCACHE NOCYCLE NOORDER

I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......

I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........

The code is given below:

CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES
(sTraineeNo IN NUMBER,
nReturned_O OUT NUMBER)
IS
ln_insert_cnt PLS_INTEGER :=0;
nSequence NUMBER:=0;
[code]......

View 2 Replies View Related

Variable Usage In Type Of Table Declaration Statement And Execute Immediate Statement

Aug 10, 2011

HOW to use variable P_TMPLID in following statement

TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;

because its throwing error while compiling

and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION

View 2 Replies View Related

SQL & PL/SQL :: Collection Over Cursor?

Jun 30, 2010

What is the advantage of collections over CURSOR? What is the use for collections and when collections is used?

View 1 Replies View Related







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