1
vote
0answers
23 views

Adding custom meta data to a table in oracle

I'd like to know if there's a possibility to add some custom metadata to a oracle database table. The problem at hand is the following: I'm importing a table from another DB. It might happen that I ...
1
vote
0answers
21 views

PLSQL Compilation error for other users/

I created a plsql procedure that queries the database and dumps the result onto a CSV file. I am an application developer and I have dba rights to my oracle database. I'm able to compile and run the ...
0
votes
0answers
14 views

In PL/SQL what is the usage of row level and column level attributes?

I am new to PL/SQL... What is the usage of row level and column level attributes? Can any one tell me why these are using to access columns of the table? Explain with an example please..
0
votes
1answer
938 views

ora 20001 error in oracle dbms scheduler

i have written a test job like begin DBMS_SCHEDULER.CREATE_JOB ( job_name => 'test_job' ,job_type =>'plsql_block' ,job_action => 'null' ,enabled => true ); end; / after a while when ...
0
votes
0answers
53 views

Strange behavior with context index on matarialized view

We are experiencing a very strange behavior with a materialized view on oracle. Here is the statement that we've used to create the materialized view: CREATE MATERIALIZED VIEW MY_MATERIALIZED_VIEW ...
0
votes
0answers
38 views

From keyword missing in view

I am this view which I am trying to compile. Given below is the view. CREATE OR REPLACE FORCE VIEW "My_View" ("my_ORDER", "REQUESTOR", "DEPARTMENT", "HOD", "SHIP_TO", "SOLD_TO", "PRODUCT", ...
0
votes
1answer
194 views

converting varchar to number while passing to IN clause

I am really stuck here.. Given below is the miniature of what I am trying to achieve. set serveroutput on; declare rajesh varchar2(20); x number(10); begin select wm_concat(stocks_id) into rajesh ...
0
votes
0answers
716 views

'oracle.jdbc.driver.OracleSQLException': ORA-01031: insufficient privileges. (SYS.DBMS_XMLSAVE)

I have this function (written here by someone), placed in the package ENVER.ENCODING_VERIFIER: FUNCTION loadxml_table (p_owner IN VARCHAR2, p_tablename IN VARCHAR2, p_format IN VARCHAR2, p_tmpClob IN ...
0
votes
2answers
207 views

Example optimisic offline lock in SQL/PL?

Can someone show me an example of an optimistic offline lock in an Oracle Stored Procedure?
4
votes
2answers
536 views

Compare two Oracle database roles

I'm trying to re-engineer my corporates user managament and want to compare all the roles with each other to find duplicate ones. Doing it by hand is really tiring and I need to compare like 150 ...
0
votes
1answer
156 views

Dynamic where condition in a stored procedure

I am using Oracle 11g. I am trying to get the table data with this procedure: CREATE OR REPLACE PROCEDURE test(p_table IN varchar2) IS v_sqltxt varchar2(4000); BEGIN v_sqltxt:='select count(*) from ...
1
vote
1answer
245 views

need to use PLSQL to insert distinct rows

I need to take some data from Table A, use some logic, and then insert one or more rows into Table B. I have a PLSQL block that brings in data from Table A with a cursor, performs all the logic ...
2
votes
1answer
2k views

how to create and insert into a table in an anonymous block

I have PL/SQL anonymous block that creates a table (using execute immediate) and then inserts data into the table. When the block compiles, it gives me an ORA-00942: table or view does not exist ...
6
votes
1answer
523 views

Package state not re-intialized on raise_application_error

When a package has state and the header is changed, the first call gets an error stack something like this: ORA-04068: existing state of packages has been discarded ORA-04061: existing state of ...
4
votes
2answers
152 views

Is there a bug with PL/Scope in combination with associative arrays?

I believe I stumbled upon a bug with PL/Scope in combination with associative arrays, but perhaps I overlook something. I have the following package create or replace package tq84_pkg_c as ...
2
votes
2answers
12k views

How to store single result in variable and reuse it in a query (Oracle)?

I have a query like SELECT id FROM xyz WHERE ...; which has a more or less complex WHERE clause and returns exactly one row with one column (ID). I need this ID for several later queries in a ...
2
votes
0answers
616 views

Where can I find a syntax reference for Oracle SQL Developer's “Generate DB Doc” function?

Oracle SQL Developer (I'm using v3.2) has a feature called "DB Doc", which generates documentation for database objects. I mostly want to use it to generate documentation for my stored procedures, ...
8
votes
3answers
1k views

How can I ensure that only one copy of a procedure is running in Oracle?

We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error. Whats the best method ...
3
votes
1answer
877 views

How far should bind variables go in Oracle?

I am currently examining all database users's SQL code in our department for not using bind variables. We do experience an ORA-04031 error within a constant period of time. Now, I have a quite firm ...
0
votes
2answers
2k views

How do I manage column order when using PL/SQL's %ROWTYPE records?

I have a table with a large number of columns, which has data gathered from several other tables. I want to write some PL/SQL like: DECLARE TYPE t_toptab_list IS TABLE OF toptab%ROWTYPE; l_data ...
5
votes
3answers
748 views

Return a fully dynamic table from an Oracle function

I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of ...
2
votes
2answers
192 views

Overhead of DBMS_Profiler

If I use DBMS_Profiler to profile a long running (5 hour) PL/SQL script, how much longer should I expect the script to run with profiling? I realize this will be dependent on what the script is ...
2
votes
1answer
351 views

Virtual Column - Using it in view (Oracle)

I have made a virtual column in Oracle, using the following code. CREATE OR REPLACE function email_address (ID_ varchar2) return varchar2 deterministic as lname varchar2 (256); snumber varchar2 ...
2
votes
2answers
980 views

Piplelined Result Set over Database Link Alternatives

Since a pipelined result set cannot be returned over a database link (see MOS note 560743.1), what workarounds/alternatives are there. I can think of a few, but I'd rather not lead the question. I ...
2
votes
2answers
2k views

What does 'nls' in nls_date_format stand for?

For example: alter session set nls_date_format = 'DD-MON-YYYY hh24:mi:ss'; changes the date format for the session. What is nls here?
5
votes
2answers
515 views

Oracle Advanced Queuing LIFO

With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order? There is an indication through a lack of information that this is not an option, but perhaps ...
9
votes
3answers
5k views

How to differentiate between SQL and PL/SQL?

I know the question might sound too stupid, but I never understood this part. SQL*Plus works with both SQL and PL/SQL. How do I know whether some code is SQL or PL/SQL? If my code has a for ...
4
votes
2answers
813 views

Identify which parameters were passed to a procedure (Oracle)

Procedures in PL/SQL can have default values for parameters, but the caller can pass values that are identical to the default values. Is there a way from within the PL/SQL to determine whether a ...