Tagged Questions

Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. Questions about PL/SQL should probably be tagged "oracle" as well.

learn more… | top users | synonyms

0
votes
2answers
49 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 ...
1
vote
1answer
46 views

Oracle PLSQL code recovery

I cannot connect to my database as admin, even using connect descriptor neither via RMAN. I am getting TNS-12514 error. I consider to install new instance. Questions: 1) Can I restore/recover PLSQL ...
0
votes
0answers
19 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
1answer
39 views

Problem with procedure performance

I'm having problem in Oracle, where I have two procedures that are executed one after the other. Where the first procedure, I have a cursor that does insert into a table. This insert has about 30 ...
0
votes
2answers
45 views

Is there anyway to search PL/SQL reserved words with contains function?

Is there anyway to search PL/SQL reserved words with the contains function in the Oracle? We would like to search keywords such as "test for" or "test do" with the contains or catsearch functions ...
0
votes
2answers
41 views

How to detect in which schema a procedure is called?

I'm struggling with a problem in Oracle 12.1 regarding to procedures. We are using a data isolation approach based on schemas. We use one schema as PUBLIC and each project has exactly the same ...
0
votes
2answers
44 views

Oracle: How to split records across days

I have data with two timestamp columns startdate and enddate. The data is produced continuously everyday and some records are such that the startdate is one day and the enddate is the nextday as it ...
0
votes
1answer
31 views

Is it impossible to iterate table rows in Oracle PL/SQL without using a cursor?

I wanted to figure out if it was possible to avoid using cursor to iterate the table rows in PL/SQL and tried the following script: set serveroutput on; declare v1 tbl_plant.plant_id%type := 0; ...
0
votes
1answer
22 views

Problem with Oracle Rollback Segment Session

I am facing a problem in Oracle, which after performing a procedure that performs INSERT or UPDATE with a millions amount of record in a table, not the next procedure "sees" the data in the table ...
1
vote
1answer
27 views

How do you capture the output message from a successful MERGE statement when using it in a procedure?

Sorry if this sounds like a rather simple question.. When using the console, normally the statement outputs something similar to X records merged How do I capture this output into an out variable ...
-1
votes
0answers
31 views

How to refer/alter a table name when current privileges deny viewing them

How can i access / view the table names present in a database.Currently I am restricted on privileges(Deny permissions on View Definition ) to view the tables. I have to refer to a table containing ...
0
votes
2answers
38 views

Split a string in different columns on basis of no of spaces

i want to split a string in different columns in the following manner through oracle Input String: hello how are you doing Output: hello how, how are, are you, you doing please help guys.. !! ...
0
votes
0answers
17 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
30 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 ...
1
vote
0answers
36 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 ...
0
votes
1answer
29 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
2answers
99 views

I'm trying to use a variable in Oracle PL/SQL, but I'm getting an error. What's wrong here?

I come from an SQL Server background and I'm trying to figure out how to use Oracle PL/SQL variables. I'm trying to declare a variable and then select that variable. It sounds simple, but I am doing ...
1
vote
0answers
26 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
71 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' ...
1
vote
1answer
63 views

PL/SQL operating system call

I'm trying to lauch a system call from Oracle Apex, directly or indirectly. It seems to only accept PL/SQL procedures, so I iterated towards this: begin :p3_request_id := sequence.nextval; ...
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
0answers
38 views

plsql script continue excution even there ar promt statement

I am facing new problem in PL/SQL. I programmed a 200 line script, it appears there is nothing wrong. It's weird because Oracle does not give you an error message: EXAMPLE : I am using in the script ...
0
votes
0answers
87 views

Oracle Calling a Procedure Returning a CURSOR in another Procedure

I have 2 packages and a procedures that return a sysref cursor. Now i want to call either of the 2 packages and procedures in another package and procedure that will take the decision on an input ...
1
vote
1answer
63 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
2
votes
0answers
109 views

Split comma separated entries to rows

I have a Table like this: | ID | OtherID | Data +--------+-----------+--------------------------- | 5059 | 73831 | 5103,5107 | 5059 | 73941 | 5103,5104,5107 | 5059 | 73974 | ...
0
votes
1answer
94 views

Modify text file with Oracle PL/SQL

I'm trying to open and replace a string in a text file with the following procedure: BEGIN v_file_name := 'text_file.txt'; v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W'); v_id_nextval ...
0
votes
1answer
115 views

Oracle Triggers - Before Update and Before Insert - Separate or Combined?

I'm running Oracle DB 9i. I have a table with various constraints to ensure data integrity. In addition to the constraints, I have triggers on BEFORE INSERT and BEFORE UPDATE to ensure that ...
0
votes
0answers
656 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
1answer
192 views

Migrating stored procedure from Sybase to Oracle having temporary tables

I am migrating a few stored procedures from Sybase to Oracle using Oracle Developer tool. The tool migrates alomost 70%-80% of the stored procedures except a few cases where there is a temporary table ...
1
vote
1answer
211 views

Pass the unix variable to the ddl command inside a pl/sql block

I would like to create a db link dynamically, using the input given in the unix session. i.e. the remote db name and db instance would be given by the user, in unix, which are VAR1 and VAR2. Now, from ...
0
votes
1answer
117 views

How to add a column in table via stored procedure? [closed]

create or replace procedure SP_PARAMETER_TEST -- this is my table ( NAME_KEY in VARCHAR2 -- this contains a value assume NAME_KEY contains "Gypsum" ) is begin ALTER TABLE PARAMETER_TEST ADD ...
2
votes
0answers
108 views

How to avoid hardcoding a database link name in a package body

I have a PL/SQL package that copies data from a remote DB2 database using a database link. To keep the package configuration-independent I wrapped the remote table in a view. But a single field in a ...
1
vote
1answer
51 views

Mutating Error , How to solve it

CREATE OR REPLACE TRIGGER LOAN_RP AFTER INSERT OR UPDATE ON FUND_DISBURSE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE V_FA FUND_DISBURSE.FUND_AMT%TYPE; V_RC NUMBER (10); ...
0
votes
1answer
67 views

Insert Into does not work with multiple rows

Here Is my code : CREATE OR REPLACE TRIGGER LOAN_RP AFTER INSERT OR UPDATE ON FUND_DISBURSE REFERENCING NEW AS NEW OLD AS OLD DECLARE V_FA FUND_DISBURSE.FUND_AMT%TYPE; V_RC NUMBER ...
1
vote
1answer
68 views

How to create a function(table_name) that return a collection in Oracle?

I need to create a function in Oracle that accepts a table name and return a collection the content of which is based the table that accepted. I've been doing some search, many examples are of the ...
1
vote
0answers
67 views

dbms_audit_mgmt.clean_audit_trail not purging

my current database version is 11.2.0.3.0. I'm trying to purge my audit trail with a procedure. In my procedure I got this part of code: SYS.dbms_audit_mgmt.clean_audit_trail( audit_trail_type ...
2
votes
1answer
105 views

Is there a way to pass to execute immediate only once a variable but use it several times?

I have a Cursor that gets its data from a script along these lines: OPEN myCursor FOR SELECT value1 AS Value1, myPackage.function1 (value1, :myId) AS Result1, myPackage.function2 (value1, :myId) ...
0
votes
1answer
272 views

How to reference a table from another schema inside a package?

I am using TOAD to create a package in a Schema that I have am owner. My package uses tables of another schema. The package has a procedure that uses a cursor. When I create the package I get ...
0
votes
0answers
42 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 => ...
1
vote
1answer
100 views

Insert with different key values to avoid contraint violation

I have a set of Inserts traced of a database and I want to issue them into another similar one. The problem is that I'm getting contraint violation errors due the similar database already has the keys ...
0
votes
1answer
2k 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
56 views

Specific issue on data pump API in oracle

I have a client/server architecture. Using an Oracle dbms on the database server side. I need to perform a user-triggered (from client side) backup of the database, where the best way to perform that ...
0
votes
2answers
121 views

How to reuse code in Oracle Script?

I have a script where I am creating some test data. At the beginning of the script I create some tables where I have to insert the data. Before creating them I check that they do not exist. declare ...
0
votes
0answers
107 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 ...
1
vote
1answer
54 views

How can I send an email when objects get invalidated?

I have couple stored procedures and a package. Whenever this stored procedure and package goes invalid, I need to send an email. How can I achieve this? Can you please give me an idea how this ...
1
vote
1answer
59 views

How to use variable and drop in test script?

I have to modify certain Oracle Packages. Basically I will be extracting functionality and moving it to C. They provided me with some "Test" scripts that I shoud be able to run to test that my ...
0
votes
1answer
941 views

Using EXECUTE IMMEDIATE inside PL\SQL Block

Using EXECUTE IMMEDIATE inside PL\SQL block makes the whole block commit immediately. begin INSERT INTO Customer ( GUID, STATUS, NAME) VALUES (1,1,'xx'); EXECUTE IMMEDIATE 'CREATE TABLE Shop ( ...
0
votes
0answers
67 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
2answers
62 views

Decode Function in Oracle

I have a table named as transaction,in that table trans_type column is there.I want to check the trans_type like bellow if(trans_type = 'AR','AD','AF','AG') then trans_type:='A'; elsif trans_type = ...
0
votes
0answers
43 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 ...