SQL & PL/SQL :: Create View With Functions

Jan 17, 2011

I have created a package with some functions in it.I want to create a view with from 2 tables but also i wanto to use the functions from some fields of the view. (e.g. if the wview has 10 fields the 5 of them will be from the 2 tables and the other from package functions).

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Use Window Functions In Where Clause Without Inline View

May 16, 2013

Below is the query where I need to filter the data using window function.

I don't want to use inline view for mt query because i need to use the same query in OWB which it wont support.

Select a.Physical_Id,
a.Booking_Begin_Date,
a.Booking_End_Date,

[Code]....

I Cannot use below query in OWB Which is Inline View
----------------------------------------------------------------------------

Select b.* From
(Select a.Physical_Id,
a.Booking_Begin_Date,

[Code].....

View 2 Replies View Related

SQL & PL/SQL :: Create Primary Key On View And Use This View For Creating Foreign Key?

Oct 8, 2010

is it possible to create primary key on view and use this view for creating foreign key .

View 3 Replies View Related

Forms :: How To Create Library Functions To Show Alert

May 24, 2010

Am using Oracle 10g Forms and Databse.

My form Consists data block blk_user with two items username and password

Both user name and Password are required Fields.

When user left these items Empty To show Alert

I Created Below Procedure and Called in Form_Level ON-ERROR Trigger.

PROCEDURE pcd_io_alert IS
itm_name VARCHAR2(20);
BEGIN

[Code].....

how to call or create this procedure as Library functions and call the created library in form To Show Alert. Actually I want to create library functions to Validate and Show Alert Mesages.

View 1 Replies View Related

Application Express :: How To Create A Display Image Item With Apex Functions

Jul 28, 2012

I'm working on Apex 4.1.1.00.23 in a 11g XE Data Base, I want to display a Image in a Report, I can do it using the "+BLOB Download Format Mask+", but the images displays at original size and I want to display them smaller.

I've read that you have to create a procedure, a SYNONYM and call them in the src of the img tag. as Zsuzsi Vadasz mentions here [URL] But I remember that I was able to create some items in reports with Apex functions, I think they were from APEX_ITEM Package, so I want to know if there is a way to create the "+Display Image+" item with one function of APEX.

View 2 Replies View Related

SQL & PL/SQL :: Create View In Another View?

Dec 1, 2011

Can we create view in another view

View 10 Replies View Related

SQL & PL/SQL :: How To Create View

Feb 25, 2011

I want to create view as follows.

Example:

Create or replace v_name
select job,sal, <funcation>
from emp;

Note:- Funcation returning 4 values.

Requirement:- I want to create view 6 columns.

how to create view.

View 3 Replies View Related

SQL & PL/SQL :: How To Create A View Using Five Tables

Oct 30, 2012

I am trying to create view ..Using totally Five tables....

The main driven table almost 10 GB Data

When i am creating a view..

End of communication channel Error raise...

At the same time I am running query ..Running Fine...

View 5 Replies View Related

PL/SQL :: Create Or Replace View

Sep 11, 2012

I am about to pass 1Z0-051 and have been cramming. Ran across this scenario: Create or replace view. Correct answer: allows insert from view in multitable insert statement. Can I actually insert data from a view? My understanding is no data actually exists in a view. This has created a cavernous feeling of inadequacy.

View 3 Replies View Related

Create View Off Split String

May 22, 2012

How do i split a string based on a space. And then create a view with three columns of that split string.

For example if i have a table with Full name as a column how do i split that string and create a view with firstname, middlename, lastname as column names with the split string as data.

If you need anything clarifying write back.

View 1 Replies View Related

How To Create Index On Materialized View

Aug 6, 2010

How to create index on materialized view which is refreshed daily basis.

View 3 Replies View Related

SQL & PL/SQL :: Create A Table Through Inline View?

Oct 1, 2010

there is a diff. problem for me.when i create table through inline view then it shows 2246 records but if i check these records only in select statement then it shows 124 records. i cant understand how table shows 2246 records even then atual records in inline view shows only 124 records.

following is a query

create table sam as
select * from
((
select distinct stck.item_code
from (
select item_code,bal

[code]...

View 4 Replies View Related

SQL & PL/SQL :: Create View Without Base Table?

Jun 25, 2012

How to create a view without base table . some example?

View 8 Replies View Related

SQL & PL/SQL :: Currency Conversion In SQL To Create A View

Apr 27, 2012

I need to implement the Currency Conversion logic in SQL to create a view.

Sample data

Acount Table

Accno Amt Crncy_id
--------------------
101 1000 1
102 500 2
103 500 2
104 500 3
107 500 4
.
.
.
Currency Table

Crncy_id Crncy_nm
-------------------------
1 INR
2 USD
3 GBP
4 EUR

My local currency is INR, I need to write a query to convert the foreign currency to loacl currency INR.In my case i need to write aSQL for a view.It should not implement through PL/SQL

View 6 Replies View Related

SQL & PL/SQL :: How To Create A View Having All Rows From Tables

Apr 21, 2010

I have a table as shown below,

SQL> select * from Main_Table;

AA
--------------------
SUB1
SUB2

Here the SUB1 and SUB2 are "tables" and are similar in their structure. The "Main_Table" will be update dynamically and the no of rows in this table will vary.

Now my question , i need to create a view which will have all the rows from these tables ,in the current case it is something like

create or replace view sample
as
select * from SUB1 union all
select * from SUB2

How can this be achived. I tried as shown below:

spool file_to_exe.sql
select 'select * from ' || AA ||' union all ' from Main_table;
spool off

i will end up in a union all "extra" , if i do like this.

how can achieve this ?..

View 5 Replies View Related

How To Create Unique Index On A View

Aug 24, 2012

11.2.0.1...How do I create an index on a view or any workaround that my view won't get duplicates?

SQL> create unique index indx01 on db_backup_details_vw(id);
create unique index indx01 on db_backup_details_vw(id)
*
ERROR at line 1:
ORA-01702: a view is not appropriate here

View 7 Replies View Related

SQL & PL/SQL :: How To Create Materialized View On Partition Table

May 16, 2012

how to create materialized view on partition table?

View 1 Replies View Related

Replication :: Oracle 10g - Create Materialized View

Jun 16, 2008

We are using Oracle 10g rel 2. The replication is setup on 1 server which is in City A, and the snapshot server is in City B.

City A .
Create materialized view log on table-a
with primary key
including new values.

City B database.
Create user test_rep identified by test
grant connect, resource, create any materialized view , table, view , procedure to test_rep.

Create materialized view city-A_db_MV
refresh fast
select * from cityA.Tablea@city-Adb

When i select from city-A_db_MV, it showed the complete table-A of city-A database.

Now if we make any changes to City-A table at the Master site, will it be propagated automatically to the MV site.

I guess we need to create jobs to push / or refresh fast .. isnt it. But exactly how to do it is a question.

Secondly if we make a replication group at Master site at city-A db, how do we refresh that Group and how to monitor whether it is refreshing on time or not? do we need to see the jobs every now and then.

but still a lot of questions unanswered, even though i had read the documents earlier.

1-The MView was created without identifying that after what interval it will be fast refreshed.
2- How to Manually refresh it. Does it support On Commit, I think it is not.
3- Where should be we make a group and then add the table to that group and refresh that group.

Should this group belongs to the Master Site or to MV site?

View 1 Replies View Related

SQL & PL/SQL :: Cannot Create Materialized View - Insufficient Privileges

Aug 3, 2010

i am trying to create materialized view but it give me insufficient privilege i give it all grant and i run it from sys it give the same.

View 3 Replies View Related

Replication :: Unable To Create Materialized View

Mar 13, 2008

I have to create a materialized view for a table which does not have index on any field.

While creating a Mview i am getting an error "TABLE DOES NOT HAVE THE PRIMARY KEY CONSTRAINT".

application developers do not want to create an index on the base table onto which MView is to be created.

is there any way to create a materialised view for the table without index, or is it necessary to have the index on the base table before creating MView on it.

View 1 Replies View Related

SQL & PL/SQL :: Trigger To Execute Immediate Create Or Replace View?

Jan 30, 2013

i try to make trigger to execute immediate create or replace view

(
CREATE OR REPLACE TRIGGER xxxx
BEFORE INSERT ON table1
REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW
declare
l number ;
v_ddl varchar2(4000);
v_job number;
[code]....

it give me error for insuffition privilage in execute immediate and after i make GRANT CREATE ANY TRIGGER TO user give me error can't commit in trigger.

View 8 Replies View Related

Replication :: Create Materialized View Without Having Any Data In It

Nov 5, 2008

How I can create a Materialized View without having any data in it.

For e.g.

I create a Materialialized View based on a View.

CREATE MATERIALIZED VIEW test_mv
REFRESH FORCE ON DEMAND
AS
SELECT * FROM test_view

In the above case the data fetched by the view test_view gets stored in the Materialized View test_mv. Suppose I want materialized view test_mv to get created with all the columns of test_view but not the data. I will refresh the materialized view test_mv later for data as and when required.

What shall I do for immidiate formation of materialized view test_mv without data.

View 4 Replies View Related

Server Administration :: Create View With Parameters?

Oct 26, 2011

I created a view with parameters l_id and l_name, how can i find them in oracle view?

Create Table tb_test
(
Id Number,
Name Varchar2(64)
);

Create Or Replace View vw_tb_test(l_id,l_name)
As
Select Id,Name From tb_test;

View 3 Replies View Related

SQL & PL/SQL :: Create A View To Show Counter For Occurrences?

Jul 24, 2011

I am trying to create view that selects a number of rows from a table and has a calculated field which is a counter form 1 to n next to each row.

In the below example, s_name is repeated many times each with a differnet serial, I want to cteate a view that shows a counter for the occurences of s_name next to the serial.

CREATE TABLE XYZ
(
SERIAL NUMBER PRIMARY KEY,
S_NAME VARCHAR2(30)
);
INSERT ALL
INTO XYZ VALUES (1, 'ABC')
INTO XYZ VALUES (11, 'ABC')

[code].....

View 3 Replies View Related

SQL & PL/SQL :: Create Materialized View For Accessing Data

Feb 29, 2012

1.As we can create materialized view for accessing data from other schema but same database. will it be effective or it will act as a normal view.

2.Will materialized views can be created in Fast mode for the above scenario?

View 4 Replies View Related

SQL & PL/SQL :: Create Directory - Table Or View Does Not Exist

Oct 2, 2010

I am trying to do the following-

CREATE DIRECTORY my_dir AS 'd:oraclefiles';
GRANT READ,WRITE ON my_dir TO PUBLIC;

The above throws up an error-

ORA-00942: Table or view does not exist

View 9 Replies View Related

PL/SQL :: Create View And Procedures - Not Getting Desired Results?

Jul 16, 2012

Im doing some create view and create procedures for my work.In creating view, its just done perfectly.with create procedures work, I got some problem with the result.

So here is the coding:

create or replace
PROCEDURE "USP_EDW_CASH_MARGIN"
(
result_cursor          OUT     TYPES.cursor_type
)

[code]....

After i run this coding.
I got these errors :
1)Error(46,5): PL/SQL: SQL Statement ignored.
2)Error(46,5): PLS-00394: wrong number of values in the INTO list of a FETCH statement
It says that the error is with this code "FETCH v_cursor INTO v_row;"

View 6 Replies View Related

SQL & PL/SQL :: Display Time Format In Oracle By Create View

Nov 16, 2011

I am not able to find any information regarding inserting data with timestamp format without putting the date.

This is what i want to do:

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH24:MI TZR';

I create the column data type as timestamp with time zone. This is value i have inserted:

Insert Into TEST Values('05-OCT-2013 01:00 +08:00','05-OCT-2013 23:00 +08:00','SCIENCE);

And now i wish to display it in 'HH24:MI TZR' only after i deduct both of the time. This is the view i have created:

Create View TESTRECRDS As Select (STARTTIME- ENDTIME)*24 As DURATION, Science;

But when i select it display as weird timing because it would default in 'DD-MON-RR HH24:MI TZR' format.

BUT IF i create the view and display as 'HH24:MI TZR', it would gives me error:

Create View TESTRECRDS As Select To_Char(STARTTIME- ENDTIME, 'HH24:MI TZR')*24 As DURATION, Science;

Error would be invalid number. How can i display only 'HH24:MI TZR' after subtracting?

View 3 Replies View Related

SQL & PL/SQL :: Using Create View And Joining Multiple Tables In Oracle

Apr 8, 2012

I'm stuck on the CREATE VIEW part and the SELECT statement to get the output of the CREATE VIEW. We were instructed not to use AND and simply use JOIN on this exercise.

Question - For employee John Smith, give the project(s) he is working on, his department's name and the name of his division. See crows foot diagram on a separate file.

CREATE TABLE DIVISION
(div_code NUMBER(5),
emp_num NUMBER(5),
div_name VARCHAR(25),
CONSTRAINT pk_division primary key (div_code));
[code].......

SELECT - this SELECT statement is supposed to generate the output of the CREATE VIEW CODE above. SEE Crows Foot diagram attached.

View 9 Replies View Related

Replication :: Create Materialized View - Insufficient Privileges

Feb 14, 2008

CREATE MATERIALIZED VIEW Matview1
NOLOGGING
NOCACHE
NOPARALLEL
REFRESH COMPLETE ON DEMAND
START WITH sysdate
NEXT sysdate + 1
WITH ROWID
ENABLE QUERY REWRITE AS
select Query;

if i run select query it works fine .. also the user has create materialized view and query rewrite privs .. not sure why i am getting insufficient privileges error still ..

View 1 Replies View Related







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