SQL & PL/SQL :: Syntax To Convert Variable

Jan 23, 2011

I got a variale which passing by other problem to pl/sql colBuf="USER,NAME,LOC,DATE"

need to make it like this

tempcolBuf=||'^B'||bfary(i).user||'^B'||bfary(i).n ame||'^B'||bfary(i).loc||'^B'||bfary(i).date||'^B'

any command can use? or loop? get the solution by syntax...

View 14 Replies


ADVERTISEMENT

SQL & PL/SQL :: Convert Data In NCLOB Column To BLOB Variable?

May 17, 2010

How can i convert data in NCLOB column to BLOB variable? NCLOB now is tored in UTF16. The data character set is ISO8859P2. Need to keep BLOB at unicode.

View 11 Replies View Related

SQL & PL/SQL :: NOT LIKE IN Syntax?

Apr 6, 2011

Is there such a thing as:

NOT LIKE IN (record%, %record, etc)

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.)?

View 8 Replies View Related

PL/SQL :: Syntax 10g Vs 11g?

Sep 27, 2013

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 ( +)?

View 14 Replies View Related

Tnsping Syntax?

Feb 9, 2011

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:

tnsping server:port:instance

View 2 Replies View Related

Syntax To LOAD Value Of APEXPWD

Jan 12, 2010

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.

View 2 Replies View Related

SQL & PL/SQL :: Join Syntax In From Clause

Jul 21, 2010

I have this ORACLE SQL and just about understand join syntax in the From clause, i.e.

SELECT *
FROM TABLE 1 LEFT OUTER JOIN TABLE 2
ON TABLE1.FIELD_X = TABLE2.FIELD_X

However, I've inherited the sql below.

SELECT
RELOCATION.START_DATE,
STUDENT.SURNAME,
STUDENT.FORENAME
CURRENT_SCHOOL.BASE_ID
RELOCATIONS.STUD_ID
[code]......

I particularly don't understand this part

' 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!

View 3 Replies View Related

SQL & PL/SQL :: Syntax Of Materialized View?

Sep 9, 2010

Can u explain the syntax of materialized view??????

View 1 Replies View Related

SQL & PL/SQL :: Validate Sentence Syntax?

Dec 29, 2010

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.

View 6 Replies View Related

SQL & PL/SQL :: Syntax For Oracle Statements

Apr 26, 2011

The link from where I can get syntax of all the commands available in oracle?

View 6 Replies View Related

SQL & PL/SQL :: Ref Cursor Syntax Working In 8i But Not In 11g?

Feb 17, 2011

why this code work in 8i but not in 9i, 10g, 11g

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.

View 4 Replies View Related

SQL & PL/SQL :: Truncate Partition Using Syntax

Oct 10, 2013

I am trying to truncate a partition using syntax .ALTER TABLE SALES6 TRUNCATE PARTITION FOR(DATE '02-03-07')

but when i query back to table i can still see the data it is not truncating.

create table sales6
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
(
[code]....

View 12 Replies View Related

PL/SQL :: Syntax To Call Procedure?

Apr 8, 2013

I have 2 procedure defined in a package.Procedure is defined so that, it has 3 varchar2, which is used to store the insert statement.

ex:
CREATE OR REPLACE PACKAGE PK_LOGIC AS
PROCEDURE MOVE_table1_TO_table2;
PROCEDURE MOVE_table2_TO_table3;
END PK_LOGIC ;
/
[code]....

My problem is how can we execute this procedure in SQL DEVELOPER tooL?I tried using,

CALL PK_LOGIC .MOVE_table1_TO_table2(); or EXEC PK_LOGIC .MOVE_table1_TO_table2;

View 9 Replies View Related

SQL & PL/SQL :: External Table Syntax Error

Jul 5, 2012

SQL>

create table oldemp8
(
fname varchar2(30),
salary number(6),
job_id varchar2(20)
)
[code].......

Table created.

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.

os windows xp 2000. oracle 10g (10.2.0.1.0)

View 7 Replies View Related

Update With Join Syntax In Oracle

Apr 13, 2011

This is my working query in ms access...

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?

View 4 Replies View Related

SQL & PL/SQL :: Syntax - Case Statement In Where Clause?

Mar 12, 2013

I am trying to use the following case statement in my where clause. My problem here is, I get no rows.

tab1.col1 =
case
when (tab1.col1 = 'VAR') and (tab1.col2 is null or tab1.col2 >= tab2.datecol) then
tab1.col1
else
null
end

View 13 Replies View Related

SQL & PL/SQL :: Use ANSI Standard JOIN Syntax?

Oct 9, 2013

Use ANSI standard JOIN syntax for example i have this code.

SELECT resv_num, unit_date
FROM p_resv_unit ru, p_pm_unit_night pun
WHERE pun.property_id = in_property_id
AND pun.pm_unit_num = cvUnitNum
AND pun.unit_date BETWEEN start_date AND end_date
AND pun.resv_unit_id = ru.resv_unit_id;

[code]....

and is it a good idea to change it, because both ways it works?

View 15 Replies View Related

JDeveloper, Java & XML :: Syntax In Rtf Template?

Mar 17, 2011

i am using this syntax but its going in error, <?if:number(DAY_1)>=1 and number(DAY_1)<=25?><?attribute@incontext:background-color;'LightGreen'?><?end if?>

View 1 Replies View Related

SQL & PL/SQL :: How To Use Cursors / Loops And Proper Syntax

May 31, 2011

homework assignment using pl/sql based on 2 tables I have created below? I am not sure of how to use cursors, loops and proper syntax.

ASSIGNMENT:

1. Create a PL/SQL Procedure (cursor to loop through the records, check the LastName, then update the grade table
where id=id on grade table)

Rule:
A
‐ LastName ends with a character between A‐F
B
‐ LastName ends with a character between G‐K
C
‐ LastName ends with a character between L‐P
D
‐ LastName ends with a character between Q‐T
E
‐ LastName ends with a character between U‐Z

Create TABLE Registration (RegistrationID number(10), SectionID number(10), CourseID number(10),
SectionNumber varchar2(10),
StudentID number(10), FirstName varchar2(20),
LastName varchar2(20), CourseNumber varchar2(20), CourseName varchar(20));
[code].....

View 20 Replies View Related

SQL & PL/SQL :: Syntax For Optional ALL Parameter In Oracle SQL

Apr 17, 2012

I am trying to pass an Optional 'ALL' Parameter to Oracle in the SQL statement below...

USER.STATUS can be either 0 or 1 in the Source-Data (Inactive or Active). However, the :P_STATUS Parameter can be either 0, 1 or 2 (ALL).

I tried an IF/THEN/ELSE statement in the SQL below - but it doesn't work as is.

Gives and "ORA-00920: invalid relational operator" error...
-------------------
SELECT
i.LAST_NAME SURNAME,
i.FIRST_NAME GIVEN_NAME,
DECODE(u.STATUS, 1, 'Active', 0, 'Inactive') STATUS,
u.STATUS STATUS_CODE
[code].......

View 1 Replies View Related

OLE DB :: SQL Server Create Table Syntax?

Jun 28, 2012

am new to SQL server. I have below statement what is ON [PRIMARY]. I want to write a similar create script to be written for Oracle & need to understand the importance of ON [PRIMARY]

Create table [emp]
(
[e_name] [varchar] (255) NOT NULL,
[e_sal] [decimal] (20,0) NOT NULL
) ON [PRIMARY]

View 1 Replies View Related

Create Schema That Needs To Be Converted Using Non-oracle Syntax

Jan 27, 2011

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

CODE,file_line(1:6) char(40) null

View 4 Replies View Related

Translating Query From SQL Server To Oracle Syntax

Sep 10, 2009

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

View 2 Replies View Related

Syntax To Calculate Number Of Days Difference

Sep 27, 2010

setting up the query/correcting the syntax below so that it calculates the 'number of days difference' between whatever the 'Biggest Date' field value is and whatever the 'current date' is using the 'sysdate'. So far, I've only managed to get the query to calculate the number of days difference (days past due) between the 'need date' and 'estimated delivery date'.

CODESELECT
To_Date(need_date, 'YYYYMMDD') Need_Dt,
To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Biggest_Date,  
To_Date(need_date, 'YYYYMMDD') - To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Date_Diff
        
FROM tableT

WHERE
need_date <=  (Case when estimated_delivery > ' ' THEN  estimated_delivery ELSE need_date END)

ORDER BY Date_Diff ASC

View 6 Replies View Related

Syntax For Setting Pctfree And Pct Used For A Specific Table?

Apr 25, 2013

tell the syntax for setting pctfree and pct used for a specific table.

View 4 Replies View Related

SQL & PL/SQL :: CASE Syntax - Result To Be NULL As Don't Want Negative Values

May 6, 2010

i am doing a data transformation using the following function

FLOOR(ONSET_TPQ-VAX_DATE)

Where ONSET_TPQ and VAX_DATE are both dates. Now in a situation where the ONSET_TPQ precedes VAX_DATE I want the result to be null for those records. As an example if ONSET_TPQ= 2000 and VAX_DATE=2010

I want the result to be NULL as I dont want negative values. Any exact SQl syntax on how to do that. DO i write a case statement?

View 6 Replies View Related

Forms :: How To Check Syntax Of Resulting Insert In Oracle 6i

May 23, 2013

I have a package,

PACKAGE PK_EXCEL_TO_DB IS
TYPE tKeyValue IS RECORD (
CROUTE VARCHAR2(255),
VROUTE VARCHAR2(1000),

[code]...

Package body is

PACKAGE BODY PK_EXCEL_TO_DB IS
PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS
vcInsert VARCHAR2(3500);
BEGIN

[code]...

what i want is: 'output vcInsert to a forms-item and check the syntax of the resulting insert'. How can I do this

View 7 Replies View Related

Server Utilities :: Syntax For Exporting Only Procedures Of Particular User

Mar 7, 2011

What is the syntax for exporting only the procedures of a particular user.

View 1 Replies View Related

Server Utilities :: Syntax Error In Using Query Parameter In Expdp

Aug 17, 2013

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 `('

View 3 Replies View Related

Performance Tuning :: Run Explain Plan Syntax - Show Error?

Aug 3, 2010

when i runnung the explain plan syntax , show error :
running --- SELECT * FROM TABLE(dbms_xplan.display) ;
ERROR: an uncaught error in function display has happened;
please contact Oracle support
Please provide also a DMP file of the used plan table
PLAN_TABLE
ORA-00904: "OTHER_TAG": 無效的 ID

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved