Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. IBM supports PL/SQL for DB2 since version 9.7. Questions about PL/SQL should probably be tagged "oracle" or "db2" as well.

learn more… | top users | synonyms

0
votes
1answer
87 views

How to create scheduler for manually refresh Materialized View if the MV get need_compile stallness status

I've a problem to Automatically Refresh Materialized View, if the stallness condition is NEED_COMPILE / STALE / UNUSABLE OR NOT FRESH. I've MV with this PL/SQL : CREATE MATERIALIZED VIEW ...
0
votes
0answers
24 views

Creating objects containing collections of themselves

I'm creating a simple data structure using PL/SQL to help me work with some rows in tables which have dependencies upon each other. In short, I'd like to be able to create a graph using any ...
0
votes
1answer
104 views

PL/SQL recursive lookup with cursor

I basically have a need for recursing through data in my Oracle table so I can find related rows and send them out. It is a single table, a single ANSI SQL query seems not possible due to turin ...
0
votes
1answer
83 views

How to select data out of an Oracle collection/array?

Due to needing some complex data manipulation and not being able to use PHP, I crafted a procedure (not stored, it's just run when we run the query) in Oracle to manipulate the data and store it in an ...
0
votes
0answers
18 views

FACT table loading

I have the below table structure : Ticket# Tckt_type Amount Acct Num 1 abc 10 1001 1 def 20 1002 So, for the same ticket number, there ...
0
votes
0answers
118 views

Reviewing ways to generate monthly tables using PL/SQL automatically

I am attempting to automate a set of tables that my colleagues produce monthly, using PL/SQL. The set of tables contain data about different metrics (e.g. Customer revenue, customer demographic ...
0
votes
0answers
184 views

SELECT from Function that return CURSOR

I would like to select from a function that return a SYS_REFCURSOR Type value. for example : CREATE OR REPLACE FUNCTION my_funtion ( my_param IN VARCHAR2) RETURN SYS_REFCURSOR IS ...
0
votes
0answers
72 views

how to update 'update trigger fired record's column?

I'm new on this task. I tried to update when I/F table insert after trigger. but it wasn't work.. There is my trigger source like.. create trigger T_ATABLE_I after insert on ATABLE for each row ...
0
votes
0answers
62 views

How to cast as type of a table column?

I have multiple views with union like this: select column1, column2 from mytable union select cast(extcol as nvarchar2(15)), cast(extcol2 as nvarchar2(10)) from exttable But later I change the size ...
0
votes
1answer
91 views

Rows to columns

I am new to Oracle. I know MySQL. I wrote below query in MySQL: Tables: CREATE TABLE Ass ( asset_id BIGINT(20) NOT NULL, caption VARCHAR(64) DEFAULT NULL, description VARCHAR(4000) DEFAULT ...
0
votes
0answers
258 views

Oracle - Slow queries without prepending schema name or providing hint

I am logged in as the schema owner in Oracle 11gR2 on Linux. I have 55 million rows in table. select max(col) from table; - takes 300 seconds (index full scan) select max(col) from schema.table; - ...
0
votes
2answers
109 views

ORA-01410 Error

I am getting ORA-01410 and ORA-06512 for the following block of code in a trigger im firing for deletion of a record in my table. Are the calls correct ? getlong ('xxx', 'YYY', :OLD.ROWID), getlong ...
0
votes
0answers
107 views

Oracle SQL Loader - BULK UPDATES

Can SQL loader be used to do bulk updates? I have a table that holds customer information. The table has a primary key and it has 5 columns pertaining to the customer. Now we created three more new ...
0
votes
0answers
225 views

Is it possible to alias a column with a constant variable defined in the package header of a PL/SQL package?

I am trying to alias some columns that are being used to interface with a 3rd party data aggregation system. This company has stated that they might change their naming scheme in the near future, so I ...
0
votes
0answers
140 views

Inserting into a table and getting the column names from a collection in PL/SQL

I want to copy data from one table to another and would like to have a rather dynamic insert statement. I have the column names I want to insert into in a collection col_names and was wondering if ...
0
votes
1answer
39 views

Writing a trigger for every time a user logs in

I want to write a trigger which runs every time a user logs in and saves the name of each user, for example. I have written trigger in this way: create or replace TRIGGER LOGON_TRG AFTER LOGON ON ...
0
votes
0answers
2k views

PL/SQL Error bad bind variable

Where is the problem with my code. It should work, but Compiler says: Error(5,14): PLS-00049: bad bind variable 'NEW.NAZWA_GRUPY' Error(6,1): PLS-00049: bad bind variable 'NEW.NAZWA_GRUPY' ...
0
votes
0answers
51 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
0answers
3k views

execute .sql script inside an anonymous pl/sql block which is invoked by a shell script

I got a shell script which connects to SQLPLUS and executes the .sql file like below: read a x=`echo $a | cut -d , -f 1` y=`echo $a | sed 's/.*,//'` c=`echo $USER | cut -c -2`"OPR"`echo $USER | ...
0
votes
0answers
128 views

Running DBMS_AUDIT_MGMT package under other user than SYS

I want to run DBMS_AUDIT_MGMT package under user CENTRUMADMIN. But error occurs even when I try to run this part of code: BEGIN DBMS_AUDIT_MGMT.init_cleanup( audit_trail_type => ...
0
votes
0answers
263 views

How to startup a oracle database with sql plus or with any developer tool without connecting to the Database

How to startup a database without connecting to Database. Actually what I did is I shutdown my database and then my sql plus screen hanged and so closed unhandled. then I tried to connect my ...
0
votes
0answers
172 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
95 views

My stored procedures often get invalidated

The reason for invalidation should be because of some DDL activity, but I don't have any DDL in the SPs. I need to find what is that DDL statement. If the object is in an invalid state, when you call ...
0
votes
0answers
81 views

Sorting the view on which the stored procedure runs

I want to sort the view on a particular column before my stored procedure runs. Is there a way to do this through the stored procedure? Can anyone show me a MySQL example? This is what the stored ...
0
votes
1answer
43 views

Delete all types created by a user

In Oracle 10g, how do I delete all the types owned by a specific user? This is the view that gives me the types I want to drop:SELECT type_name FROM user_types;
0
votes
1answer
578 views

PL/SQL cursors and records

I am a new to Pl/sql and I need to learn to write simple pl/sql scripts. Is there any difference between cursors and records in PL/sql? So far I find them both similar.
0
votes
0answers
314 views

export query results to file

I am facing the following issue. I want to execute a query and export all the results in a file. My query is saved as in varchar2 type and i can execute immediate on it and give me all the results. ...
0
votes
0answers
50 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
0answers
316 views

Oracle data obfuscation on specific columns with set field sizes

Would like to obfuscate data held in Oracle tables, but: Requirements only apply to specific columns e.g. personal data obfuscated value should not be longer then column size possibly only ...
0
votes
1answer
2k views

Oracle strong ref cursor with dynamic sql

I'm using Oracle 10g. I have a query in a stored procedure that selects entire rows from a table. Currently it returns a strongly typed ref cursor (tablename%rowtype). Now I have to add some dynamic ...
0
votes
0answers
49 views

Issue: PLS-00103

I have a package body: PACKAGE BODY "PKG_SYNONYM_TEST" AS type t_table as object ( synWord varchar2(500) ); type t_nested_table as table OF t_table; function search1(i_string varchar2) RETURN ...
0
votes
0answers
57 views

Logical or in oracle PL/SQL

I have this code: declare error1 number; error2 number; error3 number; begin error1 := 1; error2 := 0; error3 := error1 | error2; -- should be error3 = 1 end; But I dont know how to ...
0
votes
0answers
346 views

Error ORA-06550

CREATE OR REPLACE PROCEDURE pro_department_report AS v_did department.deptid%TYPE; v_dname department.dname%TYPE; v_sname student.sname%TYPE; v_count NUMBER(2); v_sdid student.deptid%TYPE; CURSOR ...
0
votes
0answers
417 views

Creating and tuning a materialized view in oracle for use by Cognos BI

I am currently in the middle of a Cognos BI implementation and am having difficulty in tuning a view in order for SQL Server to select its contents for fulfilling the incremental load in a timely ...
0
votes
1answer
605 views

Call pl/sql function and discard result

I'm calling a function from pl/sql, but I do not require the result in this case. Is there a cleaner way to call this function without using a dummy variable? Works: junk := performWork(); Does ...
0
votes
0answers
615 views

Error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

i've problem here. i wanna create a Stored procedure like below, but i encountered error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records. create or ...
0
votes
0answers
3k views

12154 error when using PL/SQL Developer

I have installed a few versions of Oracle on my machine. I have tried 64 bit and 32 bit versions. I have installed 11G. I need Oracle 10G 32 bit. Using the following guide I removed all versions of ...
0
votes
1answer
6k 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
1answer
672 views

Installing Oracle Database 12c in Windows 7, INS-08109 error [closed]

I'd like to learn PL/SQL so I'm trying to install Oracle. I downloaded the 2 zip files and extracted to the same directory. I run the "setup.exe" file and the install GUI starts: In step 1 I enter ...
0
votes
1answer
4k views

PLS-00049 bad bind variable when compiling a trigger

I'm trying to implement a dedupe trigger to a table. CREATE OR REPLACE TRIGGER USER.TBL_ACTION_DEDUP BEFORE INSERT ON USER.ERRORTABLE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF ...
-1
votes
1answer
2k views

Commit and rollback in Oracle stored procedure

In my Oracle stored procedure, I have multiple insert and update statements like below: create or replace PROCEDURE SPTest AS BEGIN insert into emptest(empid,empname,deptno) (1,'ravi',10); ...
-1
votes
1answer
31 views

sequence id in oracle

I am using Oracle 11g. I have a senario like: I need to increase auto increment id like below 00000001 00000002 00000003 00000004 For example, my table looks like below id empid name ...
-2
votes
1answer
62 views

Single-row subquery returns more than one row

I try to run the follwoing query but it gives me 'Single-row subquery returns more than one row' error. BEGIN FOR C2 IN (SELECT S.bom_header, S.sap_cage_cd, ...
-3
votes
1answer
210 views

Why DBMS_METADATA.GET_DLL not work with EXEC command ?

Using Oracle 11g I just trying to understand the concept here.... Why the command bellow not work? (executed at SQL Developer) execute dbms_metadata.get_ddl('VIEW', 'VW_CARGA','INT') Error ...