Все вопросы: [plsql]

450 вопросов

похожие теги: plsql-packageplsql-pspmod-plsql
0
голосов
1ответов
279 просмотров

Insert Record based on flag

I have a table say tb_load_files. It contains fields file_name, file_date,file_loc,file_status Now, File_status cane be x or y. The requirement is there can be multiple records for x status for combination of (file_name, file_date,file_loc) but only one record for y status. e.g. file_name f...

0
голосов
1ответов
1528 просмотров

DB2 9.7 cannot enable PL/SQL syntax

After I installed DB2 9.7 Express, I tried to enabled DB2 to support PL/SQL by following command: DB2SET DB2_COMPATIBILITY_VECTOR=ORA then I got result as below: DBI1301E Invalid value. Explanation: The value specified for the registry variable is invalid. User response: Refer to the DB2...

12
голосов
4ответов
64621 просмотров

What is wrong with this PL/SQL? Bind Variable * is NOT DECLARED

Here is: declare v_str1 varchar2(80); begin v_str1 := 'test'; print :v_str1; end When I run it using SQLDeveloper just in a sql worksheet I get this: Bind Variable "v_str1" is NOT DECLARED anonymous block completed

2
голосов
1ответов
3047 просмотров

Is using a SELECT inside a pipelined PL/SQL table function allowed?

The docs for pipelined functions say that DML is not allowed when they are used in a SQL statement (typically a SELECT), and in most examples the pipelined functions are used for data generation or transformation (accepting a custor as parameter), but not issuing any DML statements. Now, technic...

2
голосов
4ответов
4059 просмотров

Oracle external tables. Optimising select queries

I have to perform many selects from an Oracle external table. I have 10 cursors that look a lot like this (ext_temp is the external table) CURSOR F_CURSOR (day IN varchar,code Number,orig Number) IS select NVL(sum(table_4.f),0) from ext_temp table_4 where --couple of condi...

2
голосов
1ответов
123 просмотров

Selecting transactions as a single row

I have a requirement where I need to generate a report about current software projects. Two of the columns are the date of the latest milestone and the date of the milestone previous to that. Obviously, milestones are stored in a transaction table so we can have many milestone per project. I'v...

1
голосов
2ответов
55 просмотров

PL SQL - Resembling (!a?0:a) functionality

I have a simple query in a cursor Cursor some_cursor IS select sum(some_field) from some_table table_1 where table_1.TYPE =1 AND TO_CHAR(table_1.date,'YYYYMMDD') = '20090905' AND table_1.f2 = 962 AND table_1.f3 = 418...

0
голосов
3ответов
444 просмотров

column update without using trigger

SCENARIO: I have two tables, table1(col1 date) and table2(col2 varchar(20)). REQUIREMENT: Whenever anyone updated the value in col2, todays date should be inserted/updated in col1. It should be done without using triggers. Now, I cannot think of anything possible to do it. So, I need your hel...

3
голосов
3ответов
29071 просмотров

Oracle - difference or changes between two rows from two tables

I have two tables both have same schema, one will have previous day records other will have current. I want to compare both and find only changes or only rows that have different value in atleast one column. How is this possible in pl/sql, oracle? (I did code something similar using checksum i...

8
голосов
2ответов
16053 просмотров

Using an object type in a select statement in Oracle

I have a function that returns an object that has 3 values. Is there a way to call that function from a select statement and have each value be a different column? I could break it into 3 functions, but the values are related so I wanted to keep it as one for performance reasons. (So oracle doesn...

22
голосов
5ответов
30778 просмотров

Is there a way to get the line number where an exception was thrown?

Im working on a pl-sql script, in which I have about 10 TO_CHAR conversions. One of them is throwing an ORA-06502: PL/SQL: numeric or value error: character string buffer too small exception. Currently, im logging the message with this piece of code EXCEPTION WHEN OTHERS THEN DBMS_OU...

2
голосов
2ответов
65675 просмотров

Oracle - Number to varchar

I have a table containing a column of type Number create table tmp ( /*other fields*/ some_field Number ) and in a PL SQL script, I want to convert that field to a varchar. However, i don't know its length, so I get an exception Exception message is ORA-06502: PL/SQL: numeric or ...

3
голосов
5ответов
3353 просмотров

ORM for Oracle pl/sql

I am developing a enterprise software for a big company using Oracle. Major processing unit is planned to be developed in PL/SQL. I am wondered if there is any ORM like Hibernate for Java, but the one for PL/SQL. I have some ideas how to make such a framework using PL/SQL and Oracle system tables...

0
голосов
5ответов
2068 просмотров

PL/SQL - Working with a string!

I have a string. Let's say: "abcdea" I have to find out what is the most used letter in this string using a anonymous pl/sql block. How can I do this?

2
голосов
5ответов
7775 просмотров

convert xml into table

I need to convert an oracle table into xml and then return it to table form. I converted a table using xmlgen, but I don't know how to reverse the conversion. I'm looking for an example of converting an xml file into a table.

22
голосов
5ответов
72326 просмотров

C#: Oracle Data Type Equivalence with OracleDbType

Situation: I am creating an app in C# that uses Oracle.DataAccess.Client (11g) to do certain operations on a Oracle database with stored procedures. I am aware that there is a certain enum (OracleDbType) that contains the Oracle data types, but I am not sure which one to use for certain types....

2
голосов
4ответов
8265 просмотров

PL/SQL private object method

I'm a bit new to Oracle's PL/SQL (using 10g), I was wondering if there's a way to make a private method in an object type, as is often done for private helper methods in other languages (Java, C++, C#, etc...). I know it is possible to make private methods in packages, but I can't seem to find a ...

1
голосов
2ответов
3656 просмотров

How do I get entries in the next month from oracle sql?

Let's say I have a table that has "user_id, date, score", and every user has exactly one score every month, but not always on the same day. I want a query that has "user_id, date, score_delta" where score_delta is how much the score will change between "date" and the next month? Am I going to...

0
голосов
2ответов
90 просмотров

basic oracle question

I have this query: select total.lecgrouplecture(l.groupcode) lecturename, total.lecgrouptime(l.groupcode) lecttime from total.lecgroup l where term = (select term from total.CURENTTERM) and rownum < 10 order by lecturename I want to know what total.lecgro...

0
голосов
3ответов
823 просмотров

Explicit Opening and Closing cursors

I've been reading up on database cursors, and every bit of sample code I've seen explicitly opens and closes the cursor. I've only used them a few times and I've never had to do this. Can anyone tell me why it is necessary to do this? I know if you don't close a cursor you can create memory leake...

40
голосов
6ответов
172637 просмотров

How can I select records ONLY from yesterday?

I've spent hours searching the web for an answer to this question... Here's what I currently have: select * from order_header oh where tran_date = sysdate-1

0
голосов
2ответов
290 просмотров

Oracle 10g : Monthly stats with grouping by file size

I am on Oracle 10g. I have a table that contains all the files stored in the system during the past year. I want to make statistical monthly deposits, grouping them by file size. eg 0-1m 1m-10m 10m-100m 100m + So my results would look like : Month, 0-1m, 1m-10m, 10m-100m, 100mplus 2009-03, 99...

11
голосов
3ответов
127778 просмотров

Selecting Values from Oracle Table Variable / Array?

Following on from my last question (Table Variables in Oracle PL/SQL?)... Once you have values in an array/table, how do you get them back out again? Preferably using a select statement or something of the like? Here's what I've got so far: declare type array is table of number index by b...

10
голосов
2ответов
54963 просмотров

Table Variables in Oracle PL/SQL?

I've recently started a new position as a developer and I'm having a bit of trouble with PL/SQL. I've used MS SQL for a number of years but I'm finding PL/SQL a bit trickier. One of the things I used to do when writing functions and stored procedures in MS SQL was to put reoccuring result sets ...

0
голосов
1ответов
785 просмотров

Modifying an Oracle Ref Cursor

Given: Oracle 10.2g is the database I have a table called emp. emp has a VARCHAR2 column called SECRET. SECRET might contain a plaintext string, or it might contain an encrypted string, but I can distinguish one from the other A function called DECRYPT already exists that, given the encrypted str...

0
голосов
3ответов
8015 просмотров

Run a query inside an Oracle Stored Procedure

I have a query select * from myTable ...and I want to wrap this query inside a stored procedure, and have the store procedure output the results of this query. How do I do it? In ms-sql, i can store my query as a string to a string variable. And then do "Execute (variable)". Why no such thin...

3
голосов
4ответов
19465 просмотров

Log error messages in Oracle stored procedure

We plan to configure a stored procedure to run as a batch job daily using Oracle DBMS scheduler package. We would like to know what would be the best way to log an error message when there is an error occured. Is logging to a temporary table an option? or is there a better option. Thanks in advan...

3
голосов
4ответов
7940 просмотров

got ORA-01843 when I try to insert date & time to Oracle

I have A anb B in String format A = 14/01/2007 B = 22:10:39 I try to insert date and time: SQL = "insert into MyTbl(Tdate,Ttime) value ('" + Convert.ToDateTime(A) + "','" + Convert.ToDateTime(B) + "')"; i got ORA-01843 error, what I can do ? thank's in advance

0
голосов
1ответов
1893 просмотров

Oracle SQL LOOP (between 2 dates) and Counting

I'm stuck with a SQL Query. I have this table: [Reserve] ID NUMBER START_DATE, DATE END_DATE, DATE .......... (more cols) I need to look if the next count is more than 0 for any of the dates between START_DATE and END_DATE (including both). If the count is more than 0, the query must sto...

1
голосов
2ответов
2488 просмотров

PLSQL Collections - to create a collection of records or not?

I am new to Oracle PL/SQL and am having some difficulty conceptualizing collections with regard to records. I have the following problem: I want to compare various fields of a record from the client table to various fields of a record from the person table. e.g., LName, FName, Soc. (unfortunate...