Table Creation Inside Function

Feb 25, 2012

i am trying to create table inside function where in after creating table when am trying to access the table with select statement oracle is throwing error 'Table/view doesnot exist -00942', below is the code snippet

create or replace function example (mkey in varchar2) return varchar2
is
g_key varchar2(100);
l_tbl_ntext exception;
pragma exception_init(l_tbl_ntext , -942);
begin

begin
execute immediate 'select * from example1';

exception
when l_tbl_ntext then
null;
end;
execute immediate 'create table example1(skey varchar2, g_key varchar2) storage(buffer_pool, keep)';
end example;
/

select * from example;

View 8 Replies


ADVERTISEMENT

SQL & PL/SQL :: Function Returning A Table Inside Where Clause?

Apr 5, 2010

DECLARE
cnt number(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP
IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227')));
DBMS_OUTPUT.PUT_LINE (cnt);
END;

MY_PACK.STR2TBL() is a function which takes '|' delimited string, extracts values and returns a table of zipcodes. The function works fine and returns 46227 but the count returned is 0 instead of 280(count returned by replacing inner select with '46227').

View 22 Replies View Related

SQL & PL/SQL :: Function That Returns A Table Type Inside A Package Body

Jul 26, 2011

CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....

I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?

I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?

View 10 Replies View Related

SQL & PL/SQL :: To Use A Function Inside A View

Sep 7, 2011

I am trying to create a view of a query that i would be using regularly...the query contains a function call in it...can i use it..

If yes when i try to do it . It gives out an oRa-01031:in sufficient privileges.

View 3 Replies View Related

SQL & PL/SQL :: Using Procedure Inside Function?

Nov 8, 2011

I have question.Using procedure inside the function ?can I get better performance?

View 8 Replies View Related

PL/SQL :: Doing DML Inside Pipeline Function

Oct 10, 2013

In the follow code example, is it possible to save the seeds that I generated into a table when I call this table function without expliciting doinginsert into <some_table>select select * from table(pkg_seed.getSeed(200)); I try the automonous_transaction clause but it does not work.  

--drop package pkg_seed--drop type seed_tab   CREATE or replace TYPE seed_rec AS OBJECT( id number,seed number);    CREATE or replace TYPE seed_tab AS TABLE OF seed_rec;    CREATE or replace PACKAGE pkg_seed IS      function getSeed(maxrow in number  default 100)    RETURN seed_tab PIPELINED;END pkg_seed;/    CREATE or replace PACKAGE BODY pkg_seed IS    function getSeed(maxrow in number  default 100)   RETURN seed_tab PIPELINED  IS   cursor cur_seed(vmaxrow number) is    select rownum id, floor(dbms_random.value(1,1000) ) seed  from dual connect by level <= vmaxrow;    l_seed cur_seed%rowtype;   BEGIN  open cur_seed(maxrow);   LOOP   FETCH cur_seed into l_seed;   pipe row(seed_rec(l_seed.id,l_seed.seed));   END LOOP;  RETURN; -- the function returns a single result      END getSeed;END pkg_seed;/    select * from table(pkg_seed.getSeed(200));

View 15 Replies View Related

SQL & PL/SQL :: Cursor With Parameter Inside Function

Mar 7, 2010

I have code inside function
.....
cursor cur1 is
select *
from sarchkler
where sarchkler_appl_no = in_appl_no
begin
select max(saradap_appl_no) into in_appl_no from saradap;
for rec1 in cur1 loop
......
my question I get variable for cursor after cursor declaration

View 7 Replies View Related

SQL & PL/SQL :: Using Function Inside Query And Without Cursor?

Mar 17, 2013

To display highest marks,least marks,average marks,total marks of the student name entered.

desc stud;
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER
NAME VARCHAR2(20)
M1 NUMBER
M2 NUMBER

How do I do that using PL/SQL and without Cursor.

View 4 Replies View Related

Forms :: Calling Function Inside The Procedure

Jun 2, 2011

I have created a function in form field(when validate item) this should be called in separate procedure. How to call this function in procedure?

View 4 Replies View Related

SQL & PL/SQL :: Function Batch - Cannot Perform DML Operation Inside A Query

Oct 8, 2013

Am calling the Function Batch to insert an update statemtnt into Batch_statement table in the DOWNLOAD_FUNC .But its failing with the error

SQL Error : ORA-14551: cannot perform a DML operation inside a query

Below Is the

FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS
BEGIN
INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID)
VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1);
RETURN 1;

[code].....

View 27 Replies View Related

Function Or Pseudo-column EXISTS May Be Used Inside SQL Statement

Oct 10, 2013

I am encountering error in this code.

WHILE EXISTS ( SELECT * FROM tblOrgChart WHERE fxOrgID = v_chrTempKeyDept )
LOOP
v_intDept := CAST(v_chrTempKeyDept AS NUMBER) + 1 ;
v_chrTempKeyDept := LPAD('',3 - LENGTH(CAST(v_intDept AS VARCHAR2)),'0') || CAST(v_intDept AS VARCHAR2) ;
END LOOP;

Error: PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL statement only

View 1 Replies View Related

SQL & PL/SQL :: How To Bypass Putting Select Inside Count Function In Query

Oct 21, 2012

I have 2 tables, ASSIGNMENT and RESEARCH_PAPER. For each research paper, I need to find out :

1. The number of assignments created from it (after a given constant assign date)

2. The number of assignments created from it that have been approved.

3. The number of unique users who have either created or approved an assignment from it

Test data :

create table research_paper (id int, name varchar2(100));
create table assignment (id int, r_paper_id int, assigner_id int, assignee_id int,
approver_id int, assign_date timestamp, approved_yn varchar2(10));
insert into research_paper values (1, 'A');
insert into research_paper values (2, 'B');

[code]....

Assignment :

id r_paper_id assigner_id assignee_id approver_id assign_date approved_yn
-----------------------------------------------------------------------------------------------------------
11 100 200 100 23-10-12 12:00:00.000000000 AMY
22 200 100 200 22-10-12 12:00:00.000000000 AMN
32 100 200 101 24-10-12 12:00:00.000000000 AMY

[code]....

Research_paper:

id name
----------
1A
2B

Expected result :

r_paper_id created approved unique_users
-----------------------------------------------
1 3 2 4
2 3 2 3

I wrote the following query for that :

SELECT rp.id r_paper_id,
COUNT(*) created,
COUNT(
CASE
WHEN a.approved_yn = 'Y'

[code]....

But it fails, saying that 'single-row subquery returns more than one row' when I introduce the 'unique_users' clause. The remaining fields of the output are correct.

View 7 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

SQL & PL/SQL :: Call Function Generates ORA-14551 /cannot Perform A DML Operation Inside Query

Aug 9, 2011

Calling function

select PACK.MAIN('blah') from dual

generates:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 756
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 245

Unfortunately the Body is not accessible to see.The spec of the function is:

FUNCTION MAIN (mvar IN varchar2) RETURN varchar2; I read somewhere that I can call it like:

var myVar VARCHAR2; call PACK.MAIN('blah') into :myVar

But this generates: ORA-01008: not all variables bound

View 3 Replies View Related

ASM Disk Groups Creation After ASM Instance Creation

Jul 9, 2012

During ASM Disk Groups creation after the ASM instance creation, receive the following error: Disk Group ORAASMGROUP2 already exists. Cannot be created again

The Grid infrastructure was deinstall one time and still the same issue.

View 4 Replies View Related

SQL & PL/SQL :: New Table Creation From Different Databases?

Apr 9, 2013

I have two tables having same name in two different databases.i have to create a new table with these two tables in a new database.

But if one table have 2 columns and another table having three columns I have to create a new table with three columns..

View 6 Replies View Related

SQL & PL/SQL :: External Table Creation

May 23, 2011

i had small query on external tables. i had (.csv) file outside the database. In this file, one column will be added monthly. i need to create the external table dynamically by adding the column for every month.

How the procedure can be created for this requirement.

View 9 Replies View Related

Adding Check After Table Creation

May 23, 2009

I created a table but I want to add the Unique check to it as I forgot to apply it to the table when I created it.Is it possible to make the field Unique after having created the table or do I have to drop the table and re-create it?

View 3 Replies View Related

Relationship - Table Creation With Keys Looks?

Feb 24, 2007

the relationship on my script.What I want to do is make it possible to assign many courses to a student.But I have read a few books on foreign keys and primary keys, and I still dont get how to go on doing this....

This is how the table creation with the keys looks right now...

 CREATE TABLE CAMPUS (
   CAMPUSID            NUMBER(4) NOT NULL,
   NAME                VARCHAR2(80) NOT NULL,
   CONSTRAINT CAMPUS_PK PRIMARY KEY (CAMPUSID));

[code]...

View 3 Replies View Related

SQL & PL/SQL :: Creation Of Temp Table Within Procedure

Aug 17, 2010

I need to create a temp table within a stored procedure. I want to apply some logic on the table, and then delete it when it is completed in runtime -- all inside the stored procedure.

View 13 Replies View Related

SQL & PL/SQL :: Dynamic Table Creation Using Timestamps?

Nov 28, 2010

I have a requirement to create the table monthly.The tablename will be the o/p of the following select query.

select 'TBR_AND_SECURITY_DEPOSIT'||TO_CHAR(sysdate, 'Monyy') from dual

I am trying to create the table for creating the backup of the existing table TBR_AND_SECURITY_DEPOSIT.

create table (select 'TBR_AND_SECURITY_DEPOSIT'||TO_CHAR(sysdate, 'Monyy') from dual) select * from TBR_AND_SECURITY_DEPOSIT

but it is throwing error as invalid table name.

View 2 Replies View Related

SQL & PL/SQL :: Table Creation With First Column To Be Sequence

May 6, 2010

I want to create a table, whose first column , patient_id, should be a sequence.

View 9 Replies View Related

SQL & PL/SQL :: Assign Sequence Name During Table Creation?

Aug 22, 2011

is it Possible to Assign the Sequence Name During the Table Creation.

View 1 Replies View Related

SQL & PL/SQL :: Cluster Creation On Already Existing Table

Feb 20, 2012

I have table property and flat , in both of these tables I have cvp_id common colomn .Now I want to add cluster on this colomn so how can I add cluster to table which is already exists.

View 3 Replies View Related

SQL & PL/SQL :: Nested Table Creation Error

Apr 12, 2010

I am getting compilation error when create nested table type depend on table structure

SQL> CREATE OR REPLACE TYPE typ$nttb$1 IS TABLE OF emp%rowtype
2 ;
3 /

Warning: Type created with compilation errors.

SQL> show error
Errors for TYPE TYP$NTTB$1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/29 PLS-00329: schema-level type has illegal reference to SCOTT.EMP
SQL>

View 5 Replies View Related

PL/SQL :: Creation Of A Table After Checking Whether It Is Existing

Apr 22, 2013

declare
vnum number;
vname varchar2(50):='t1';
begin
begin
select count(*) into vnum from dba_tables where table_name=vname;
dbms_output.put_line('table count '||vnum);
exception

when others then

vnum := 0;
end;
begin
if vnum>0 then
execute immediate 'drop table '||vname;
dbms_output.put_line('table dropped');
end if;
exception

when others then

dbms_output.put_line('table does not exists');
end;
execute immediate 'create table '||vname ||' ( n number)';
dbms_output.put_line('table created');
end;

View 14 Replies View Related

Index Creation In Partitioned Table

Jul 20, 2012

Facts:
Oracle 10.1.0.5
AIX 5.3

My table, HMTX have 10 partitions each of one have 6 millions of rows (average). We have 7 partitioned LOCAL indexes in that table.
Every month we load data into a new partition (6 million of rows aprox) and drop the oldest partition in table HMTX.

In order to do that we have a script that contain the next statements:

drop of all indexes
drop index n1;
drop index n...;
drop index n7;

[Code]...

create indexes again with tha same storage and degree parameters
CREATE INDEX hmtx_TST_N1 ON hmtx (campo1, campo2, campo3 .... campo8)
TABLESPACE xxxx
PCTFREE 0
INITRANS 2

[Code]....

My problem is in rhe index creation section, despite use parallel with degree 8 and nologging the index was created in :

Elapsed: 02:43:50.85.

In past months that index was created in :
Elapsed: 01:43:36.94
Elapsed: 04:48:31.24
Elapsed: 00:57:16.28

there are another way in order speed the index creation ?? o another way to disable ths index ??

View 4 Replies View Related

Forms :: Pivot Table Inside Group

Mar 21, 2011

I want to use the pivot table inside the group.

View 3 Replies View Related

SQL & PL/SQL :: Create And Drop Table Inside The Procedure

Jun 10, 2013

I've to create a table every time a procedure is run, initially the table should be dropped and then created every time. From this procedure, the table is neither created nor dropped.

I cant track the error. Why is it so?

CREATE OR REPLACE PROCEDURE TESTPROC IS
S_SQL VARCHAR2(1000);
BEGIN
S_SQL := 'DROP TABLE MYTEST PURGE';
EXECUTE IMMEDIATE S_SQL;
[code]........

View 18 Replies View Related

Dynamic Pivot Table (Partition) Creation

Oct 31, 2010

I have 2 tables , which can be mapped with a common column and the second table's rest of the columns should become like columns(like pivot) and in the second table itself 1 more column called value , this sum of the value becomes a value to the pivot column value. For this I am using CASE structure and I am writing the individual case for each column , but I am sure after some extent case don't work at all and I would like to do it dynamically( when ever the new entry will come into the source table , my proc has to pick automatically the new value and put the new value as part of pivot structure) , so pls share your inputs and if possible provide some sample code.

View 3 Replies View Related







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