New answers tagged

2

You can use TRUNC (date) function which returns date with the time portion of the day truncated to the unit specified by the format model. Syntax: TRUNC(date, fmt) or TRUNC(date). Where fmt can be 'DAY', 'YEAR','HH','MI'. For more format model please refer: ROUND and TRUNC Date Functions Example: SELECT 1 FROM sample_table WHERE ...


2

This line: if job_id<>jobidnew THEN is not part of the UPDATE which precedes it, so the identifier job_id does not refer to the column by that name but to a separate (undeclared) variable. The way to test whether the UPDATE did anything or not is to test the implicit cursor attribute SQL%notfound, as in if SQL%notfound THEN RAISE e; The ...


1

I implemented what you are trying to do this way: the transformation to a user defined object happens in a packaged procedure that is called by triggers. ---all comments and debugging removed to keep it simple---- CREATE OR REPLACE TRIGGER when_close_ini AFTER UPDATE OF end_date ON YOUR_TABLE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW ...


0

I addressed this with the PL/SQL project manager years ago, and was told that it is not possible. You can't address the :NEW or :OLD columns as a record and convert them to anything. You can create a temporary table that is either identical to the table that you are writing the trigger for or generic, i.e. table_name, column_name, column_type, clob. You ...


1

Return is basically translated as: "End execution of this routine, and pass control back to the caller .. oh, and hand this value back to them on the way .." So that code will NEVER reach the "return b3" or "return b4" ... just won't happen. also, this might be more "robust" logic for checking for a leap year ;) if add_months(trunc(sysdate,'YEAR'),2)-1 ...


0

If you are asking how to call the small procedure, the answer is that you have to declare the b1 variable in the caller. declare b1 varchar2(10); begin sma11(1000,b1); end; If you are asking why the procedure doesn't compile (as @vmachan points out), you have an extra & Create or replace procedure sma11( a in number , ...


1

I think you can just change the following line b1:=mod(&a,2); to (remove the &) b1:=mod(a,2); Hope this helps.. UPDATED to provide rationale: The & is not needed in PL/SQL procedures to access input parameters, and I think was causing the issue.


1

Yes, you need to bother about datatype, always know your datatypes and use the proper ones. The database can implicitly convert compatible datatypes, so it is not always mandatory to specify and convert to the correct datatype, but it is a really bad practice to rely on implicit conversion. The below is a typical scenario I saw countless times: select ...


1

Variables you define with the keyword variable can be used as bind variables, not like PL/SQL variables, so you need to add a colon (:). SQL> variable b number; SQL> create or replace procedure p11(a in number, b out number) is begin b:=a*a; dbms_output.put_line(b); end; / Procedure created. SQL> exec p11(10, :b); <----- ':' ...


0

Your outer select is incomplete, it has no FROM clause. Once that's fixed your query may work, if not try putting each of the sub selects inside of a pair of parenthesis, and possibly the whole operation. Insert Into Production(A, B, C) SELECT A , B , ( (SELECT SUM(Month_Production) FROM Production) / (SELECT COUNT(Month_Production) ...


2

Thats is not what you should execute. sma is the procedure, c4 is simply a cursor in it. You do not execute the cursor local to a procedure, you can not even access it. Execute the below: set serveroutput on begin sma; end; /


0

The script expects just 1 row. Not the 26 you get. You must use a cursor and loop through the rows that you get: set serveroutput on DECLARE CURSOR CRS IS SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'150OMGI') FROM dba_tables u WHERE owner='150OMGI'; C CLOB; BEGIN OPEN CRS; LOOP FETCH CRS INTO C; EXIT WHEN CRS%NOTFOUND; ...


1

As mustaccio pointed out, types must be the same. You either have to change TTabList definition to be collection of just one field (see example below), or select : vstrQuery := 'select * ... Everything else should work after fixing misprint with rowrype (should be rowtype) . For instance, if you prefer to select just field1, you can do set serveroutput ...


1

You can do this by generating the necessary script. select 'alter table '||quote_ident(ns.nspname)||'.'||quote_ident(tb.relname)|| ' drop constraint '||quote_ident(conname)||';'||chr(10)|| 'alter table '||quote_ident(ns.nspname)||'.'||quote_ident(tb.relname)|| ' add constraint '||quote_ident(conname)||' '|| ...


1

You can update the catalog tables directly. See http://www.postgresql.org/message-id/066E39B5-0E4F-11D9-965C-000A9578CFCC@kcilink.com


4

The simplest little dirty trick I know for this is xmltable(). With xmltable()you can convert a comma-seperated list of values into rows, for example: select * from xmltable('1, 2, 3') COLUMN_VALUE ------------ 1 2 3 Notice how the column is named column_value automatically. This is an XMLType column, that you need to convert to be able to compare it ...


3

Here is a solution using subquery factoring, regular expressions, and a hierarchical query on dual. CURSOR My_Cursor( vsStr1 ) IS WITH RawInputData As ( SELECT vsStr1 sStr1 FROM dual ), InputData As ( SELECT regexp_substr((SELECT sStr1 FROM RawInputData),'[^,]+',1,level) CurrentsStr1 FROM dual ...



Top 50 recent answers are included