SQL & PL/SQL :: Writing Procedure In User 2 Schema Accessing Table Product - Not Compiled?

Dec 17, 2010

I have got 2 users as user1 and user2.I have used the following statements from user 'user1':

create role GENEVAOBJECTS;
grant select, insert, update, delete on PRODUCT to GENEVAOBJECTS;
grant GENEVAOBJECTS to user2;

In the above statements, product is a table. Now, I could able to access this table from user 'user2'. But however if I write a procedure in user2 schema accessing the table product, then the procedure is not getting compiled.

create or replace procedure test_prc as
v_test number(9);
begin
select product_id into v_test
from PRODUCT where rownum=1;

[code]...

why I cannot access that table from procedure?

View 8 Replies


ADVERTISEMENT

Server Administration :: Accessing Single Schema From Multiple Schema Logging?

May 16, 2011

A single master schema where many developers are accessing. all share same password.

now i would like to trace all the changes made by each users. so i create a individual users for all and grant permission to access that schema.do i have a possibility of auditing the changes did by each user for that particular schema

View 2 Replies View Related

When Are Statements Within Stored Procedure Compiled

Jul 23, 2010

connect the following concepts/information I've been collecting. This is not my field but I'm interested in filling some of mine conceptual/technical gaps.

From a JDBC perspective, one of the benefits of Prepared (and so Callable) statements have over the regular ones is that the statement is "compiled"(*) once and then reused (performance gain).

(*) for SQL statements: building of parse tree and exec.plan

In which way can this notion be extrapolated to invocation of Oracle Stored Procedures through CallableStatements? (After clearing my doubts, I may end concluding that the only relevant feature of CallableStatements is their capacity to deal with stored procedure invocations)

According to procedure's precompiled execution plan SQL compilation implies execution plans generation PL/SQL compilation implies P-code generation and, SQL statements (from PLSQL code) are treated no differently by Oracle than SQL from Java or C/C++. These SQLs will be parsed and execution plans for those SQLs created.
...
When the PL code executes the SQL statement, only then does the SQL engine receive the SQL, parse it, and create an execution plan for it.

Therefore, even when the stored procedure can be parsed and cached in SGA (through the OracleConnection.preparedCall("proc") invocation), the SQL statements won't be effectively compiled until they are executed, right? And going deeper, will those SQL statements be cached to be reused in future invocations of the containing stored procedure? Is this a characteristic of the regular stored procedure execution in Oracle? or is it due to the CallableStatement "origin"?

View 2 Replies View Related

Product User Profile - Restrict User From Running Queries On DB From Third Party Tools

Apr 25, 2011

There is a requirement in my database that I want to restrict the user from directly running queries on database from third party tools such as pl/sql developer and toad.

There is a utility in SQL product_user_profile through which this can be done but it is only restricted if you run the query through sql plus. If I want to restrict and (give suppose select,insert) to a user for directly running queries through PL/SQL.

View 1 Replies View Related

SQL & PL/SQL :: How To Check Procedure Compiled Warnings And Errors

Sep 26, 2013

I have compiled some procedures, at the time of compilation i saw some warnings & errors but i did not notice, Now can we see that errors or warnings in db console or any other way.

View 7 Replies View Related

SQL & PL/SQL :: Procedure Compiled Successfully But Not Show Result

Aug 17, 2010

I made one code for understanding purpose its complied successfully, but not show result...its give me message procedure successfully completed.

Code is :

BEGIN
For emp_rec in (SELECT * FROM EMP)
LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.ename);
END LOOP;
END;
/

View 4 Replies View Related

PL/SQL :: Valid Objects Need To Be Compiled While Running Packaged Procedure

Aug 8, 2012

I have a requirement to create a packaged proc which lists down a set of database objects and its statuses whenever the status of objects is changed as valid/invalid in user_objects. Also, those valid objects need to be compiled while running the packaged proc.

View 5 Replies View Related

SQL & PL/SQL :: Accessing Schema Object From Another Schema?

Apr 20, 2010

I have create one procedure under my user schema. In that procedure , I am selecting data from another schema's table.

While compiling that I am getting following error->

PL/SQL: ORA-00942: table or view does not exist
PL/SQL: Statement ignored

how I grant access of one schema object to another schema. Currently I am using oracle 10g

View 2 Replies View Related

Writing Same Data In Two Database Schema

Aug 3, 2011

I have two same DB schema (same structure, same data) and I need to provide update in one of them when data in the other one is updated. It is singe direction only (we change data in DB Schema A and synchronize data in the DB Schema B; there is not opposite direction). Only small portion of data (compared to the size of DB Schema) might be changed or added this way.

View 1 Replies View Related

SQL & PL/SQL :: Format For Accessing Data From Other Database Or Schema?

Aug 25, 2011

Can we use this format for accesing data from other DB or Schema?

In From clause

database_name.schema_name.table_name

View 7 Replies View Related

SQL & PL/SQL :: Searching PRODUCT Column Based Upon User Input?

Apr 30, 2010

I have table in Oracle with one column PRODUCT. Column PRODUCT have following values -

Account Management
Active Directory
Adobe Acrobat Reader
NT Account
Application Security

[code]....

I am designing application where I need to search for PRODUCT based upon user's input. Lets say user wants search on 'Laptop Account Broken'. I want to search for all products which contains any of words in user's input. So based upon user's input I want output like below.

Expected Output:

Account Management
NT Account
WebSite Account
HP Laptop

View 2 Replies View Related

Forms :: Writing In User Profile

Jul 14, 2010

The Scenario is that we have to restrict clients to write file on C drive for which we have to grant admin privileges(OS) which we don't wanna give. Rather they can write on their own profile.

In CMD we can find their profile with %userprofile% command,but i am confused of using it in oracle form. so is there any possibilities to redirect O/P to userprofileyour_file.txt

Just for an instance ,code is like this which is currently writing in C: drive

Declare
in_file TEXT_IO.FILE_TYPE;
linebuf VARCHAR2(80);
your_file VARCHAR2(50) := 'C:TEMPyour_file.dat';
text_line VARCHAR2(400);
[code].......

View 3 Replies View Related

SQL & PL/SQL :: Finding User Who Made Changes In Table Structure Or Schema

Sep 21, 2011

i want to find the name of user who make changes in the table structure or create any index or constraint or unique key or alter the column? Is there any way to find in Oracle. in which table what change has been done as well?

following Output needed

userid, username, schemaname, schemachangetime, "what_change_has_been_made", IP address or Computername

View 11 Replies View Related

SQL & PL/SQL :: How To Restrict User (Schema) From Deleting Data From Table

Nov 2, 2012

I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.

Below is the example.

I have created a table employee in abc schema which has two values.

EMPLOYEE
ABC
XYZ

In the above scenario the abc user can only fire select query on the EMPLOYEE table.

SELECT * FROM EMPLOYEE;

He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.

View 10 Replies View Related

PL/SQL :: User Copied Table Data From Prod To Other Schema?

Apr 22, 2013

has copied one table data from Production to any other schema , know which user has done this ?

View 1 Replies View Related

PL/SQL :: How To Restrict User (Schema) From Deleting Data From A Table

Nov 2, 2012

I have scenario here.

I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.

Below is the example.

I have created a table employee in abc schema which has two values.

EMPLOYEE
ABC
XYZ

In the above scenario the abc user can only fire select query on the EMPLOYEE table.

SELECT * FROM EMPLOYEE;

He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.

View 6 Replies View Related

PL/SQL :: Writing User Defined Function The Same Way As The Oracle Functions

Sep 26, 2012

In one of the interviews , I have attended the guy asked me to write a user defined function which will take the column name and list all the values .

For example

Table Name:Employees
Column Name: Employee_Name

Employee_name
Scott
Ivgun
Jack
Shane

The query should be in this fromat

SELECT col_agg(Employee_name) emp_name from Employees;

The output should be

Emp_names
scott,ivgun,jack,shane

View 6 Replies View Related

Forms :: Restrict User To Enter Serial Numbers Of Product In 3 Level Block

Apr 24, 2012

I have a sale invoice form with 3 data blocks.

Data block 1- master_blk : For date/customer of sale invoice
Data block 2- detail_blk1 (detail of the master block - For products and qty)
Data block 3- detail_blk2 (detail of DETAIL_BLK1 For entering serial numbers of products)

My requirement is that whatever quantity user enter in data block 2 against each product he must enter equal number of serial numbers of that product in data block 3.

For this I have created on item (cnt_iteml : to count product's serial numbers in block3 ) in data block 2, and on summary item (t_serial_no ) in block3.

Whenever user changes in quantity, cnt_iteml: item is populated with t_serial_no in block3 of that product by following trigger on quantity column.

POST-Change Trigger ( quantity column )

:stock_transactions.cnt_itl:=:serial_numbers.t_serial_no;

Following trigger is written on block level at data block-3 to populate cnt_iteml with t_serial_no.
PRE-RECORD

IF GET_BLOCK_PROPERTY('SERIAL_NUMBERS',STATUS) IN ('CHANGED') THEN
:stock_transactions.cnt_itl:=:serial_numbers.t_serial_no;
END IF;

Above triggers are fulfilling my requirement except following condition.

If user after entering serial numbers in block 3 and without saving goes back to block2 and try to navigate to another record he gets a message asking him to save changes by forms. At this time if user presses no then cnt_itl item is not been populated with t_serial_no item's value.

What I want in above condition is that if user was inserting new record cnt_it item should be populated with 0, so that he shouldn't be able to save this record. And If he was updating then cnt_itl item should be populated with actual no of records in database against that product.

View 2 Replies View Related

SQL & PL/SQL :: Oracle - Getting Error While Writing Procedure?

May 3, 2012

I am Getting the error when writing the procedure. How to Solve this Error .

Error: PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'

View 5 Replies View Related

SQL & PL/SQL :: Difference Behind Using Of Either IS Or AS When Writing Stored Procedure?

Mar 10, 2011

Is there any difference behind that using of either IS or AS when writing Stored Procedure?

View 1 Replies View Related

Writing A PLSQL Procedure To Housekeep Files?

May 22, 2007

I am trying to implement a Houskeeping program for files generated in 4 different servers. This housekeeping program is run as a batch job and I need to use PLSQL to implement it. The files need to be housekept on the basis of the file creation date.

how I can go about doing this using a PLSQL stored procedure.

View 3 Replies View Related

Prevent Other Sessions From Writing To The Tables In Procedure?

Jun 26, 2013

here' my code.

delimiter //
SELECT CONNECTION_ID()//
LOCK TABLES source.jos_daikin_control_card_fcu_model WRITE//

[Code].....

ERROR 1192 (HY000): Can't execute the given command because you have active lock
ed tables or an active transaction

Is there a way to prevent other session from accessing the tables called in the procedure?

View 3 Replies View Related

SQL & PL/SQL :: Procedure To Accept Schema And Produce CSV File For Table

Mar 23, 2012

The Utility should have a procedure that will be able to accept a schema and table name and produce CSV file for that table.

This is what i have so far:

CREATE OR REPLACE Procedure print_table (schema_name varchar2, tab_name varchar2) IS
BEGIN--begin procedure
DECLARE
vpath varchar2(100) := 'C:UsersUserDocumentsDocsDBAProject';
[code].....

It works outside of the procedure but the nature of the question requires a stored procedure.

View 26 Replies View Related

Security :: Schema Consolidation And User Schema Mapping Based On Service

Jul 24, 2011

We have an application with many separate databases (one per customer). Given they share the same business requirements (service hours, change mgmt etc), we're interested in potentially consolidating the separate DBs (which are relatively small) into separate schemas within a fewer no of databases to reduce the overhead.

Our issue is that the application is hard-coded to use a specific administrator and application connection user name. Changing this is unfortunately not an option.

Given this limitation, is there any possibility to map a generic user into a customer-specific schema based on the database service that they connect to? Each customer connects to different database services but may use the same user name. We considered using private synonyms but this seems to acheive the opposite (i.e. many different users could connect and map to a single users schema). One thing to point out is that where there is a single user name, it is acceptable for a single password to be used across the different customer DBs as they will be a single admin/user.

View 5 Replies View Related

SQL & PL/SQL :: Writing A Procedure For Updating Table2 Based On Table3?

Sep 14, 2011

In my application i have a requirement as follows.I have 3 tables table1,table2,table3.I have 4 tickets for one license number which is related to client table as follows.

table1
=====
license_nbr(pk) name address
=============== ===== =======
LicNo1 test testing

Table2
=====
ticket number(pk) amountto be paid balance_amount license_nbr(fk table2)
tk1 200 200 0 LicNo1
tk2 300 300 0 LicNo1
tk3 400 400 0 LicNo1
tk4 500 500 400 LicNo1

table3
=====
payment table
ticket_number(fk table2) amount paid payment status license_nbr(fk table1)
tk1 1000 excess paid. LicNo1

so now the excess paid amount to be adjusted for the remaining tickets through tk2 to tk4.and only tk4 should remain with 400 the balance amount should be updated accordinglyand i have a license number which is a foreignkey of client table. writing a procedure for updating the table2 based on the table3.

View 14 Replies View Related

Client Tools :: Grant User Schema To Another User

Feb 9, 2011

I have two users say A and B. I have all the tables,views,indexes, types,procedures,packages etc. User B wants to access all the objects from user A.

View 4 Replies View Related

Accessing Something From Table Not Joined To Others

Jun 17, 2008

I have a schema whereby a table is not joined with other tables.

the info on that table can be gotten manually (by doing a query) and then using that info in another query. so is there a way of getting info from that table?

View 8 Replies View Related

SQL & PL/SQL :: Accessing Dmp Via External Table

Aug 5, 2011

when i am writing dump from external table, it is accessing records from dump.but when i am trying to access other dumps(create thru expdp) it is giving error.the logic i am following is mentioned below-

CREATE OR REPLACE DIRECTORY "DIR_GMS" AS 'D:Gopal_works est_env_files'

GRANT READ ON DIRECTORY dir_gms TO gopal;
GRANT WRITE ON DIRECTORY dir_gms TO gopal;

-- creating dump file in directory

CREATE TABLE emp_ext
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir_gms
LOCATION ('emp_ext_dmp.dmp')
)
[code]......

i am able to see records.

New point:
-- taking export thru expdb
expdp hr/hr tables=EMPLOYEES directory=DIR_GMS dumpfile=HR_EMP.dmp logfile=expdpEMP.log
then i created one EXTERNAL TABLE TO access it.

CREATE TABLE emp_xt (
EMPLOYEE_ID NUMBER(5),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50))
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir_gms
LOCATION ('HR_EMP.DMP')
);

while accessing, it is giving error:

SELECT * FROM EMP_XT

ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-31619: invalid DUMP FILE "D:Gopal_works est_env_filesHR_EMP.DMP"
ORA-06512: AT "SYS.ORACLE_DATAPUMP", line 19

View 13 Replies View Related

SQL & PL/SQL :: Accessing XML Values From A Table?

Jan 29, 2013

i have a table which has 2 columns.1st column has userId and the other contains an xml data as a link.on clicking that link a new file opens containing the data in xml format.

<fields>
<field key="Public Email">piyush@chand.com</field>
<field key="Location">bangalore</field>
<field key="Website" />
<field key="Birthday">0001-01-01 00:00:00</field>
<field key="Gender">Male</field>
<field key="Language">English</field>
</fields>

i need to access location of a particular userId.How can i do that?

View 6 Replies View Related

SQL & PL/SQL :: Procedure That Displays Customer ID / Customer Name / Product Name

Dec 7, 2011

I'm trying to write a procedure that displays customerID, customer name, product name, and the total quantity of products the customer purchased, and the total amount the customer paid.Here's the relevant Schema tables:

CREATE TABLE Product (
Product_ID NUMBER(6) NOT NULL,
Description VARCHAR2(30),
Product_Code CHAR(4));
[code]....

Now I'm trying to wrap the above query in procedure code. I believe that I need a cursor, but I don't know what kind of cursor variable to store the result of the SELECT statement in because the query selects columns from several different tables, and I'm not sure how to terminate the FOR loop (but I think probably I can use the EXIT WHEN cursor%NOTFOUND;Here's the procedure code I have written thus far:

CREATE OR REPLACE PROCEDURE find_customer_statistics IS
DECLARE
TYPE cust_stats IS REF CURSOR; weak ref cursor declaration

SELECT sales_order.customer_id, customer.name, product.description, SUM(line_item.quantity),
SUM(line_item.subtotal)
FROM sales_order, customer, product, line_item
WHERE customer.customer_id = sales_order.customer_id
AND line_item.order_id = sales_order.order_id
[code]....

View 7 Replies View Related







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