I have need to know the best (least expensive) way to create a stored procedure that creates a new records in a table using a sequence and return the primary key (sequence value) for this inserted record:
CREATE TABLE TEST_A (SERIAL NUMBER PRIMARY KEY, NAME VARCHAR2(20)); CREATE SEQUENCE SEQ_TESTA_PK START WITH 1 NOCACHE NOCYCLE; CREATE OR REPLACE TRIGGER TRG_TESTA_PK BEFORE INSERT ON TEST_A
I want to take an export of table MESSAGE, and filter it for the day of 17 JUL 2013 (just to limit the size). i used the following expdp command but its not working.
expdp SYSTEM directory=DATA_PUMP_DIR dumpfile=DB_16_08_2013.dmp logfile=FA0001P_BG_16_08_2013.log TABLES=schema.MESSAGE QUERY=schema.MESSAGE:where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS')
But with select query i am able to retrieve the rows for the specific date.
select * from MESSAGE where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS') Here is the command with syntax error. [oracle@orcl log]$ expdp SYSTEM directory=DATA_PUMP_DIR dumpfile=DB_16_08_2013.dmp logfile= DB_16_08_2013.log TABLES=schema.MESSAGE QUERY=schema.MESSAGE:where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS') -bash: syntax error near unexpected token `('
UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));
This doesn't work in oracle, I googled and read that update doesnt work with inner join in oracle..translate this query to work on oracle?
I am new to oracle and sql in general, I received an oracle create schema that needs to be converted using non-oracle syntax. I have never seen this syntax before.
What does the following syntax mean? CODE,line_status(1:20) char(2) null
Translate following SQL query from SQL Server syntax to Oracle syntax.
SELECT ID, [LMT(MTR)] = MAX(case when TYPE = 'LMT' then VALUE end), [AAD(KGM)] = MAX(case when TYPE = 'AAD' then VALUE end), [VOL(MTQ)] = MAX(case when TYPE = 'VOL' then VALUE end) FROM yourtable GROUP BY ID
I need to use funtion optional parameters to construct an update statement in the funtion body. I have two scenarios, either both parameters are not null or the 2nd is null. Do I have to use
IF (param2 is null) THEN udpate using param1 only ELSE update using both param1 and param2 END IF; or is there a shortcut to do this?
I have created a simple function in database that take 2 arguments, the last one is optional.
Now i call the function in Forms 6i PL/SQL Trigger and omitt the default (optional) parameter the form builder crashes when i compile the form (CTRL + SHIFT + K)
i have tried to used both words "DEFAULT" and ":=" in the function's signature for making an optional argument.
As there is no boolean datatype in oracle (I wonder why, it exists in ms-sql, and I'm still quite new to oracle) you have several ways to 'simulate' a boolean. (1/0, 'Y'/'N', in german 'J'/'N', or this in small caps...)
Now for a checkbox you have to include the 'true'-value in the LOV. But what, if your database doesn't use '1', as you have implemented, but 'Y', or what, if the 'false'-value is not '0' but null? Each time you would have to adopt your STATIC2:...
So I am wondering about a flexible solution, and I would like to ask if and how it is possible. (Havn't found anything via searching for that yet.)
Instead of writing:
STATIC2: ;Y ... and having only the 'Y/N' implementation working, how about it would look something like that: STATIC2: ;NOT('0' OR 'N' OR 'n' OR null) ... and a lot of reasonable boolean-implementations are incorporated. You could use a standard LOV for all checkboxes, dealing with different 'booleans'
But just like that, its not working...
So, a) it there a syntax for that at this point? and b) what do you think about that in general, or whats your 'best practice'?
There is XMLType table with structural storage. Is there a way to make schema validation disabled on some elements of complex type?
It is unpractical to maintain a schema for the element due to high volatility. Ideally it will be stored in CLOB and extracted as is a whole branch without validation, none of the elements under this complex type will be extracted separately.
I have a procedure that has a number of "optional" parameters.
procedure get_files( file_name_in in varchar2 default 'dummy_file', layout_in in number default 1, client_in in number default null, data_supplier_in in number default 99999 );
This procedure can be called with any combination of the input parameters.I can set up program(s) using the DBMS_SCHEDULER.CREATE_PROGRAM procedure using a program_type => 'PLSQL_BLOCK' like this:
begin sys.dbms_scheduler.create_program( program_name => 'GET_MY_FILES', program_action => ' declare begin get_files( layout_in => 11111, client_in => 2222 ); end ;', program_type => 'PLSQL_BLOCK', number_of_arguments => 0); end;
My question is: Can I set up programs(s) using the DBMS_SCHEDULER.CREATE_PROGRAM procedure using a program_type => 'STORED_PROCEDURE' when I have "optional" parameters? It appears that ALL of the program input parameters must be defined and there is no way to indicate that a parameters is "optional".
What I need is NOT IN with the ability to query by wild card. NOT LIKE IN doesn't seem to work for me. Is there another way to do this without having to write out each statement (WHERE NOT LIKE record% AND NOT LIKE %record, etc.)?
We have just migrated from DB version 10 to version 11.2.0.3.We have found out, that we have to do a revision of old queries, because there are probably differencies in the syntax. where are these differencies described? Here is an example, what google didn't told me.... I have some condition where table_1.id = table_2.id ( +) What doesn't mean ( +)?
I am familiar with using tnsping with the standard sqlnet.ora/tnsnames.ora or other oracle connection definition methods. Can I specify the actual connection definition on the tnsping command line? Something like:
i have a SQL query . In the where clause of the query , there is function called dimension_intersect which takes 2 parameters.Now , when the 2 dimensions passed intersect , the function returns "Y" and the query works as expected
Function in where clause is as below
Dimension_intersect(Dimension1,select dimension2 from product where product_sys_id=1)='Y'
The above function works fine till only 1 record is returned by the inner subquery used in above function. But when "select dimension2 from product where product_sys_id=1" return 2 dimensions then the function fails as it can accept only one dimension at a time . I am not allowed to edit this function. I need to find a way to pass both the dimensions one at a time.
DECLARE T_PASSWORD VARCHAR2(40) :=null; BEGIN T_PASSWORD := select MAX(TPASSWORD) from APEXPWD; if :p101_PASSWORD = T_PASSWORD then
I'm not getting the syntax to LOAD the value of APEXPWD.TPASSWORD from the Database into the VARIABLE T_PASSWORD in APEX so that I can compare it against the users PASSWORD. This allows me to compare if the user has a temp password and then redirects them to the correct change password page. Everything is working except getting the T_PASSWORD variable set correctly.
' FROM (MYDATABASE.STUDENT STUDENT LEFT OUTER JOIN MYDATABASE.BASES CURRENT_SCHOOL '
why the table name student is referenced twice?And again for ' MYDATABASE.BASES CURRENT SCHOOL '?
When I put this into SSRS it shows only links between the tables STUDENT, RELCOATIONS and CURRENT_SCHOOL. Bases isn't mentioned in the tables diagram. it is still referred to in the raw SQL.
The above SQL works fine, i just don't understand what it's doing!
Is there some way to validate the syntax of sql sentence without actually executing it? I dont want to check if the objects exist or not, just want to check the syntax.
declare type typ_curseur is ref cursor; l_cursor typ_curseur;
[Code]....
When running in 9,10 or 11 I get the error ORA-22806: not an object or REF. After investigation I found that the problem is the bind variable ":bind.variable". Notice the dot between bind and variable. If i remove the dot or replace it by underscore everything work fine.
I am just looking for some documentation about this problem to know if it was some new restriction starting in 9i and if there is something to set to make it work like in 8i or maybe it will be better to change all our program that are using this kind of syntax. Actually i did not find anything on metalink.
SQL> select * from oldemp8; select * from oldemp8 * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-00554: error encountered while parsing access parameters KUP-01005: syntax error: found "(": expecting one of: "comma, defaultif, nullif, )" KUP-01007: at line 7 column 16 ORA-06512: at "SYS.ORACLE_LOADER", line 19
SQL>
what is the syntax error in the above command. I place the notepad file properly.i create external table before many time but cant find any this type of error.
