PL/SQL (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. Questions with regular DML or DDL statements should be tagged with "sql" and "oracle", NOT with "plsql".
1
vote
2answers
46 views
Inserting log statement inside SQL queries
Below Query will insert two rows which are 999,666 and Oracle will give you logs as 2 Rows Inserted.
insert into departments(department_id, department_name, manager_id,location_id)
...
0
votes
2answers
28 views
Successor of dbms_random.random
I was typing a question a problem with the package/function dbms_random.random and found, that the usage is deprecated.
What is the successor for generating a random number?
Googling for oracle 12c ...
0
votes
0answers
18 views
Pass c# user defined object to pl/sql function as parameter
I need to pass c# user defined object:
[DataContract]
public class SEmployeeIncome
{
[DataMember]
[OracleObjectMappingAttribute("CARDID")]
public string CardId { get; set; }
[...
0
votes
0answers
21 views
Alter Filename of External Table WIthout Re-writing Access Parameters
I Have the following External Table Definition that will allow users to modify the Filename using a Concurrent Program.
CREATE TABLE NEWHIRE_EMPLOAD_EXT
(
EMPLOYEE_NUMBER varchar2(...
0
votes
0answers
12 views
Can multiple fields (more than 50) be assigned to a single Oracle APEX g_fxx array?
I have an APEX report that will need to have more than 50 columns in each row that a user can enter data into. I would like to pass that data to a pl/sql procedure, but there are only 50 APEX array ...
1
vote
0answers
32 views
PL/SQL batch/cursor approach
Here’s my situation. There’s an imported table that has about a million records. I need to perform a lot of updates, inserts, etc. to this and other tables based on what is in each record.
I could do ...
1
vote
1answer
24 views
Looping through cursor and passing record to procedure
I'm parsing through xml and i need to update table record in autonomous transaction. For this i need to pass cursor record to procedure, how can i do that? f.e.
procedure process_message(
p_xml in ...
0
votes
3answers
52 views
How to get monthly periods between a date range in Oracle PL/SQL
I have say, start and end date as "6/11/1996" and "3/1/2002" in "mm/dd/yyyy" format respectively.
I need to get all the monthly periods as given below.
Start Date End Date
6/11/1996 - 6/30/...
0
votes
0answers
44 views
Passing List of Lists of objects to PL SQL procedure
Currently i can pass List<CellHolder> collection to pl sql procedure and it is easy and works fine.
But problem comes when i was trying List<List<CellHolder>> collection.
Is here ...
-3
votes
0answers
16 views
Excel data save to Oracle table
.xls , .csv file importing to oracle table using store procedure
xls or csv file
Sold to party Name Invoiced Qty Batch Material
aaa AND SONS 100 16CREM0212 MD5-M-30
ddddddd LIMITED 200 ...
2
votes
0answers
38 views
Run Oracle scheduler with error: ORA-20001: This procedure must be invoked from within an application session
I am building an interactive report with Oracle Apex. Currently, I created an trigger using apex_mail package to send an email whenever the table is updated. I also created a scheduler that run a ...
0
votes
2answers
42 views
How to use multiple cursor in stored procedure for merge query?
I'm trying to insert data in a table from procedure, from below code it
is inserting one row at a time even though more rows are available from
the location I'm picking data. Please help me to ...
0
votes
2answers
37 views
Prevent Insert Trigger
How can I get this trigger to prevent the insert where the advance is not greater than 0 or less than 100? Thanks.
DROP TRIGGER CheckAdvance;
CREATE OR REPLACE TRIGGER CheckAdvance
BEFORE INSERT OR ...
1
vote
1answer
12 views
Recieving Error “no more data from to read from socket” when using trigger
I created a table that is used for tracking the creation of tables in the schema but when the trigger compiles it is throwing an error "no more data from socket" and I lose connection to the database.
...
0
votes
1answer
22 views
PL/SQL Error Return Multiple Rows
I want to store some results in a temp table after I update a table but it is giving me an error for categories/book types that have multiple entries. Everything works fine when I try to do it with a ...
0
votes
1answer
22 views
How do I attach file from directory to email sent with utl_smtp
Can someone please share the list of steps to be followed or a sample code to attach a simple csv or Xls file from utl file directory and send it through email using utl smtp package.
-4
votes
3answers
24 views
Error with variable names when creating an update trigger
I am trying to create a trigger that allows for the input of clothe articles that have the sizes small-large, but the trigger will change the size to S M or L depending on the insert.
I am ...
0
votes
1answer
21 views
APEX download files app for customer
I have theoretical question as I'm not familiar with Oracle APEX yet and I don't know if it's worth to start with my idea.
case:
let's say I'll do everything locally on my machine (just to simplify ...
0
votes
2answers
38 views
Dynamic delete in Oracle
I have Some static data for that i am creating select statement with the help of union all and i am comparing those data with DB table (departments).. with the help of minus i will get extra records ...
0
votes
0answers
15 views
sending mail attachment using UTL_SMTP plsql package [duplicate]
I am learning how to send one email and attachment using oracle plsql package : utl_smtp.
Can someone please share one working code which will send one simple file named : attachment1.txt having a ...
1
vote
1answer
20 views
Dynamic Insert in Oracle
I am trying to insert a resultSet of another select query in below query i am not sure where is my mistake.. getting SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"
Here ...
-1
votes
0answers
10 views
Oracle XMLQUERY: How Remove few child tags inside xml
I have the following xml:
<root>
<parent>
<child_1>1A</child_1>
<child_2>1B</child_2>
<child_3>1C</child_3>
</parent>
<parent>
<...
0
votes
2answers
22 views
PL/SQL Block Function & Test
EDIT:
Seem to have sorted it, think it was just a problem with my table inserts.
Okay so I am pretty new to PL/SQL so be easy on me but I have this code I have to write where I have to check the ...
0
votes
0answers
24 views
plsql trigger on updating values
I need to create a trigger to prevent updating salary from persons table if the person is found in the studio table and the new salary<25000.
Those are my tables:
CREATE TABLE persons
(id_person ...
0
votes
0answers
21 views
PL/SQL Trigger check [duplicate]
I've been working on PL/SQL script which validate data before inserting. I decided to use trigger for this purpose.
I have table which is created with the following script:
CREATE
TABLE DOCTORS....
-1
votes
3answers
29 views
SELECT CASE to use NOT EXISTS in Oracle failing
I have been trying to find a solution to use an If_Exists() style statement in Oracle PL SQL. I am trying to create a trigger which checks to see if a certain airsoft gun exists in the guns table when ...
-1
votes
4answers
34 views
how to exit the procedure if condition met in a loop PL SQL
Let's say I have a for loop
for i in array.first .. array.last loop
boolean := c(i) > d(i);
if boolean --is true then
exit the loop immediately and also exit the entire procedure
else if the ...
0
votes
1answer
45 views
How can I compile PL/SQL source saved in table?
How do I compile PL/SQL source code that I currently have saved in an Oracle table? (I'm making a copy of the source code from the USER_SOURCE view, then I'm deleting those objects and want to ...
3
votes
1answer
44 views
Oracle SQL - SELECT query locks index & blocks DML sessions
We have some very weird locking happening in production. We have setup a PL/SQL script that finds objects that have been locked for more than 5 seconds and send us an alert e-mail.
Below is the ...
0
votes
0answers
31 views
When to use PIPELINED table function [on hold]
Ok, I’m aware of PIPELINED table function, what I want to know is:
1) What situations should we consider to use PIPELINED table functions OR in other words, what business scenarios are probable ...
0
votes
1answer
48 views
How to create a trigger that only updates one row
I researched my question, but still don't know what the syntax would be. This did not answer it: Trigger that updates just the inserted row
Using Oracle SQL Developer, in this Employees table, ...
1
vote
0answers
14 views
How to test (sql developer unit tests) procedure with dbms_sql.date_table/dbms_sql.timestamp_table? Got error Invalid PL/SQL Index Table element type
I have a problem with running Unit Test using Sql Developer for procedure containing e.g. dbms_sql.timestamp_table like:
create or replace procedure foo (p_arg_1 in dbms_sql.timestamp_table) is
begin
...
1
vote
1answer
34 views
PLSQL - Create dynamic field foreach records using rules table columns
I'm searching the best way (code-simplest and fastest method) to build a Stored Procedure (Oracle 11g) to insert all records (all fields) from a fact_table (source) into a target_table (destination), ...
0
votes
3answers
46 views
Fetch multiple values in a single variable and use that to compare with a single value in another variable
I have a requirement to fetch multiple values through a select query and into a single variable and then compare the value of that variable with another variable having only single value.
I am new to ...
2
votes
1answer
34 views
Efficient way to query for the existence of data in a partitioned table
I am in Oracle 11G Enterprise Edition 11.2.0.4.0
I have a table that has about 12M rows per partition. The partitioning is by SnapshotDate.
I need to evaluate whether the last 15 days worth of ...
0
votes
1answer
30 views
How many times does a range of values contain the end of the month
I have a table that has 2 values.
Value a phone was activated and a value when phone was deactivated.
I want to find out how many times that phone was still active on the last day of any given month.
...
1
vote
1answer
28 views
Iterate through all rows in table PL/SQL
From table1 I would like to gather values from certain columns. First of all I have tried to copy one table to another but I stuck when tried to:
for row in row_count
for column in column_count
...
0
votes
2answers
60 views
Oracle - Suggestion sought on populating a look up table
Converting a legacy system over to a current Oracle system, and seeking suggestions to condense data from 4 tables, into a look-up table. Current process entails a mix of java and PL scripts to ...
-3
votes
0answers
27 views
How can i Write a PL/SQL block that will print ‘Happy Anniversary X’ for each employee X whose hiring date is today. Use cursor FOR loop for the task? [on hold]
I'm having problem with writing codes in oracle pl/sql so i have gotten a new task to do anyone please help me to told the way to write the pl/sql code easily
0
votes
0answers
28 views
What considerations Do You have about my PLSQL package?
I had a little doubts about my code but yesterday I finally understood some points about to how to start coding my final package project. I share this code with the purpose if You want suggest me some ...
0
votes
3answers
33 views
Error when trying to create procedure, why do you think that is?
all.
I've tried creating a procedure in Oracle Apex 5. However, I get this issue 'ORA-24344: success with compilation error'. I assume it's a syntax error. What do you think it is? *generate_password ...
0
votes
0answers
21 views
ORACLE_PL/SQLtriggers [closed]
Question(id_q, question)
Answer(id_q, id_a, answer, correct)
Test(id_t, points)
Answer_Test(id_t, id_q, id_a, correct)
How can i make a trigger updates the points every time i insert an answer? (1 ...
0
votes
1answer
19 views
PL/SQL getting binary_float data from a file
I have a file on a Unix box that I need to read into my PL/SQL procedure. The file is 2904 bytes of data which it transpires is a contiguous string of 726 numbers of 4 bytes, so probably BINARY_FLOAT ...
0
votes
1answer
22 views
Propagating message's through multiple queues
Is it possible to propagate messages through couple of queues? f.e. I have two databases remote and local, in remote i have one aq table AQ1, in local i have two aq tables AQ2 and AQ3. Is it possible ...
0
votes
1answer
32 views
Oracle9i send HTML email with specific characters
Context : I am trying to send mails with an Oracle Procedure (only html/text, not text/plain). I use Oracle 9i.
Problem : When the html message contains some characters like & eacute; (é), it ...
0
votes
1answer
42 views
PL/SQL - Call new instance of package procedure
I have package that uses global variables.
From one of the procedures i need to call "new instance" od another procedure (of the same package). I need this second procedure to use it's own version of ...
0
votes
2answers
37 views
How to Update Column of one table if Another table column value becomes zero In Oracle?
I have two tables of ITEM(Menu_Id,Menu_Status) & STOCK(Stock_Id,Menu_Id,Stock_Quantity).
Now I want to update the Menu_Status column of ITEM table, when the Stock_Quantity column of STOCK table ...
1
vote
2answers
29 views
PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got USER_NAME.VARCHAR_ARRAY
The below is a function that I am creating to accept an array of varchar2 items and return the internal pk of that record which is a NUMBER for each record. I am struggling to get the syntax right to ...
0
votes
2answers
36 views
How to transfer cursor to a procedure?
DECLARE
CURSOR cursor1 IS SELECT STUDENTNAME, COURSEID, COURSEDESCRIPTION, COURSECREDITS, GRADE
FROM STUDENTINFO
WHERE STUDENTNAME = '&student'
GROUP BY (STUDENTNAME, COURSEID, ...
0
votes
0answers
22 views
Generating script [ORACLE] [closed]
How do you perform a procedure that generates a script that creates a role containing all the permissions that the user whose name is received as a parameter has been assigned to it directly or ...
