SQL & PL/SQL :: Create Table - Tablename With Data From Subselect (variable)

Sep 2, 2013

I wish to create a table with week-number as suffix _36 or _30

CREATE TABLE TEST1_$WEEKNUMBER AS (SELECT * FROM TEST1);

$WEEKNUMBER is this statement:

SELECT TO_CHAR (TO_DATE(SYSDATE, 'dd.mm.yyyy'), 'IW') from DUAL
CREATE TABLE doesn't accept subselect

How to do that with SQL?

View 8 Replies


ADVERTISEMENT

Stored Procedure For Searching Data From A Table By Passing Tablename As A Parameter

Feb 6, 2012

This procedure is not working properly.

create or replace procedure bank_search_sp
(
p_tablename in varchar2,
p_searchname in varchar2,
p_bankcode out varchar2,
p_bankname out varchar2,
p_dist_code out number
)
as
v_tem varchar2(5000);
begin
v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || '
where bankname like '''|| p_searchname||'';
execute immediate v_tem into p_bankcode,p_bankname,p_dist_code using p_searchname ;
commit;
end bank_search_sp;

the Procedure is getting created but i dont know what actually happens when it was executed ,This is the error shown..ORA-01756: quoted string not properly terminated

ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 14
ORA-06512: at line 1

View 1 Replies View Related

Create Sequence Using Subselect / Value From Select

Jul 8, 2008

CREATE sequence customer_id start with (select max(customer_id) from customer) increment by 1

i am trying to do that, can it be done or should i just find the max id myself and replace that select with a no?

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable

Aug 30, 2011

I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.

Explanation:

In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.

I need to query on this dynamic table and return the result as return result. But i am not able to do it.

Here T_web_loylty_report_table is a type.

CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......

View 2 Replies View Related

Inserting Table Data Into A Variable

Sep 3, 2012

I am trying to insert a column into a variable from a trigger.

Here is the code that i have:

CREATE OR REPLACE TRIGGER BUYER_after_update AFTER UPDATE ON buyer
FOR EACH ROW
DECLARE
v_key varchar2(10);
BEGIN
select ID into v_key from buyer;
insert into message_log_table (table_name, message_comments)
values
('Buyer', 'Buyer '||v_key||' has been updated');
end;
/

When I run the above I get the following compiler error:

[Error] ORA-00904 (6: 12): PL/SQL: ORA-00904: "ID": invalid identifier

Since ID is defined in my BUYER table I do not understand what the error means.

Here is my create table statement:

CREATE TABLE BUYER
(
ID VARCHAR(50) NOT NULL PRIMARY KEY,
FNAME VARCHAR(50) NOT NULL,
LNAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(50) NOT NULL,
CITY VARCHAR(50) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP_CODE NUMBER(5) NOT NULL
);

View 1 Replies View Related

SQL & PL/SQL :: Subselect Have Too Much Values

May 2, 2012

I'm new to Oracle so I try to become familiar with SQL.

In my script I want to become only this results back in the select of the Where clause which have more than 1 entrys per job_id.

But my try didn't work ... error: too much entries ??!!

SELECT
last_name
, job_id
, salary
, AVG(salary) OVER(PARTITION BY job_id) Average

[Code]....

View 5 Replies View Related

SQL & PL/SQL :: Concate String Into Tablename?

Jan 24, 2012

i have one query regarding string concation into table name.

select * from abc_20102011;

Here i post one sql in which tablename is abc_20102011, i want to derive last 8 charectors dynamically by using function. function getFinYear returns financial year in varchar2, i tried bellow query but its not working.

select * from 'abc_'||getFinYear(sysdate);

View 2 Replies View Related

Client Tools :: How To Query Tablename That Don't Exist And Not Get Any Error

Oct 24, 2012

Here is the query I want to be able to run...

select * from NonExistingTableName

I want the query above to be ignored and return no results (rather than generating an error) when the tablename does not exist.

View 14 Replies View Related

SQL & PL/SQL :: Proc Create Partition With Variable Name / Value / Tablespace

Aug 18, 2010

developing a procedure to create a partition every month passing in parms for the name and values, and also need to determine the appropriate tablespace that has adequate space available. Working within the constraints of the environment. I would like to pass in the table name as well. This code gives the general idea..

CREATE OR REPLACE PROCEDURE FS.CREATE_PART_test
declare
v_monyy varchar2;
v_yyyymm varchar2;
v_tblspc dba_data_files.tablespace_name%type;
[code]...

View 2 Replies View Related

SQL & PL/SQL :: Create Object Then Use As Data Type Of New Table

Nov 11, 2012

I have existing table which needs to be copy to new table with object using the cursor. The exist table has a rank column which has some duplicate rank, which need to be remove and provide a series of numbers, like 1,2,3,4,5,...

create type UNIVERSITY as object (
U_RANK number(2),
U_SCHOOL varchar2(150),
U_COUNTRY varchar2(150),
U_SCORE number(3)
)

[code].....

DBMS_OUTPUT:
------------
Warnings: --->
W (1): Warning: execution completed with warning
<---
0 record(s) affected
[Executed: 11/11/2012 9:12:19 PM] [Execution: 31ms]

View 4 Replies View Related

Create Trigger On Table / Populate With Data From Additional Tables

Nov 7, 2010

I would like to create a trigger on a table which populates a log table. In addition to using the table where the trigger will exist, I would like to populate a couple more fields in the log table with with data from 2 other tables.

e.g.

NAME_TABLE
-reg_id
-name

ADDRESS_TABLE *trigger to be fired when a new record is created here.
-reg_id
-srv_id

PROCESS_TABLE
-srv_id
-start_time
-end_time

This is what I would like the logging table to look like:

LOGGING_TABLE
-address_table_reg_id
-address_table.srv_id
-name_table.name
-process_table.start_time
-process_table.end_time

How can I go about creating this type of trigger?

View 2 Replies View Related

PL/SQL :: Create Record Variable That Refers Dynamic Query Assigned To Ref Cursor?

Nov 11, 2012

Just explaining what I am trying to achieve:

1) i have a hr.departments table that was loaded in hr schema on 1st oct 2012 with 4 columns(department_id, department_name, manager_id, location_id)

2) now I have a new schema by my name 'rahul' and I have loaded departments table but now an additional column has come into picture,ie created_date, this table got loaded on 1st-Nov-2012

3) Now going forward my columns could be dropped from the departments table (it can be a case), for example might be my departments table in my schema 'rahul' one day could comprise of only 3 columns(department_id,department_name,manager_id)

4) Now in the next step, I have managed to extract common column names(in a single line where columns are delimited using a comma) from both the tables(hr.departments and rahul.departments) which are (department_id, department_name, manager_id, location_id) using all_tab_cols table and I have written a function for it which i will be pasting below.

5) now going forward, using the above column names line with column names delimited using comma, I have used a ref cursor and assigned a query to it using the line of columns that I have extracted from the above point

6) Now I want to create a record variable which refers to my ref cursor, something like we do when we create a record variable by reffering to an explicit cursor defination that we give in the declaration block.

PS:

1) I have been out of touch with plsql for a long time so I have lost a lot of mmeory regarding plsql.

2) basically I need to compare data in hr.departments table with rahul.departments table for only columns that are common to both the tables, rest new or discarded columns information will go in one of the log tables that I have created(this is done already)

Code :
===================================================================================================
create or replace procedure p_compare_data(fp_old_table_name in varchar2, fp_new_table_name in varchar2)
is

[Code].....

View 5 Replies View Related

Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table

Apr 29, 2011

What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?

Is CREATE EXTERNAL TABLE included in CREATE TABLE?

View 3 Replies View Related

SQL & PL/SQL :: How To Determine The Data Type Of A Variable

Sep 15, 2010

Code to determine the data type of a variable in oracle 10g.

View 5 Replies View Related

SQL & PL/SQL :: Table As Out Variable?

Mar 2, 2012

I have plsql table as out parameter in one function.From another function i called that function ,its executing perfectly but while returning to called place its saying a character to numeric conversion error.I checked all the l

Note: i can not able post all that functions here.

View 12 Replies View Related

XML DB :: Variable Datatypes In Select Of Data From XML To Oracle

Jan 30, 2013

I'm new to XML to Oracle data integration. I do have clob column that has the XML data and trying to bring it into the Oracle relational tables, as a part of it we are developing PL/SQL procedure that calls this CLOB column, I want to know if we can variablize the data types ( Just like tablename. column name%type) in the select of the XML data:

select v.instrid, v.endtoendid, v.txid, v.cd, v.ccy,
replace(v.intrbksttlmamt,'.',',') as intrbksttlmamt, v.chrgbr, v.bic1, v.nm1, v.adrline11, v.adrline12, v.ctry1, v.iban1,
v.bic2, v.bic3, v.nm2, v.adrline21, v.adrline22, v.ctry2, v.iban2, v.cdtrref, v.addtlrmtinf
from the_data w,
xmltable(xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:pacs.008.001.01'),
[code]....

I do define the data types after every column name , the problem is incase there is change in datatype I do need to manually edit the procedure to change the datatype is there a way I can variablize them something like tablename.columname%type.

View 3 Replies View Related

SQL & PL/SQL :: Table Variable Or Collection

Jul 11, 2012

Is there any table except (global temp table and permanent table) which can be used to store data and be used in inner, left and right join for a session.

View 7 Replies View Related

PL/SQL :: Selecting Table Name Under A Variable

Mar 21, 2013

correct this one.

declare
v_ename varchar2(10):='emp';
begin
for j in (select ename from v_ename)
loop
dbms_output.put_line(j.ename);
end loop;
end;

getting error v_ename table does not exists. i should use v_ename, as i dont know the table name.

View 4 Replies View Related

PL/SQL :: How To Use Value Taken In Variable As Table Name In Query

Jul 22, 2013

I am fetching a value in a variable as: 

<select application_short_name into l_appl_nm from fnd_application where application _id=:p_appl_id> 

Now I need to use the value fetched in variable "l_appl_nm" as a table partition name in next query. 

View 8 Replies View Related

Need Clob Variable To Write Large Volume Of Data?

Oct 6, 2010

I am facing a problem with utl_http.write_text in my pl/sql application. My requirement is to write data of size>32k. So I used a clob variable in write_text. But still it is showing numeric or value error when the data size is above 8k.

I have read that chunked transfer encoding will work. But I couldn't find out how this is done.

View 5 Replies View Related

Client Tools :: Inserting Data With Substitution Variable?

May 24, 2012

I want to insert the value with '&'. But this is treated as substitution variable by oracle.So how can it is possible to do like this

INSERT INTO DEPT (DEPTNO, LOC, DNAME)
VALUES (50, '&NEW YORK', 'SALES');

View 3 Replies View Related

Reports & Discoverer :: How To Display Data Using User Variable

May 27, 2010

following is my query model

SELECT INV.VENDOR_ID,inv.invoice_date,inv.po_header_id,
INV.INVOICE_ID,
INV.INVOICE_NUM,

[Code]....

WHAT i have to write in select field

View 3 Replies View Related

SQL & PL/SQL :: Error In Reading Data From Collection Variable For Ref Cursor

Jun 19, 2013

While reading data from collection variable using ref cursor . I am getting the below two errors.

PLS-00382:Expression is of wrong type
ORA-22905 Cannot access rows from a non-nested table item.

CREATE OR REPLACE PACKAGE APPS_GLOBAL.GIIOMEGAORDERLIST
AS
TYPE BU_LIST_TYPE IS TABLE OF VARCHAR(50);
TYPE OFFER_DETAIL IS RECORD
(
GII_BU VARCHAR(50),
GII_OFFER NUMBER,
[code]........

View 4 Replies View Related

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 :: Bind Variable - Invalid Table Name

Jul 13, 2010

create table t (
col1 varchar2(2),
col2 varchar2(1),
tab_name varchar2(50));

[Code]....

In this case :v will be replaced with t1. I got the error invalid table name.

where table t1 has its own structure.

View 5 Replies View Related

PL/SQL :: Declare Variable As Row Type For A Table?

Jul 19, 2012

Can I declear a variable in PLSQL as the row type for a table, who's name is unknown during compile time, but will be determined when the PLSQL is runnning. The code is like following:

Procedure operTable( tableName IN VARCHAR2)
IS
TYPE ty_Row IS tableName%ROWTYPE
v_Row ty_Row;
CURSOR v_quey_cur

[code]...

View 5 Replies View Related

Application Express :: 4.2 Translate (Function And Global Variable Declaration) Data?

May 1, 2013

i 'm using APEX 4.2.1.00.08 and i 'm wondering if there is a way to translate "Function and Global Variable Declaration" textarea. Every other script textarea is available for translation, but not this.

I know that this is code is loaded on header but may contains important alert messages of global functions.

View 1 Replies View Related

Client Tools :: Passing Variable And Displaying Select Data In TOAD?

Apr 22, 2010

All I want to do is run a procedure (DO_H_RUN) that returns a number into "v1" and then use that variable to return the result sets in three selects. This is all in TOAD. If I do not have a where clause, the code executes fine (allowing me to see the data in multiple Grid tabs)! But, I want to filter the rows.

I've tried doing a number of different things (finding all sorts of stuff on the Web) like declaring cursor variables and the like but after spinning on this for a couple of hours, I'm stuck.

Here's my attempt (the names have been changed to protect the innocent!):

declare
v1 Numeric;
BEGIN
DO_H_RUN('Me', v1);
--DBMS_OUTPUT.PUT_LINE(v1); --This line works okay!
select * from h_run where h_run_id=v1; --NO GO

[code]....

View 10 Replies View Related

SQL & PL/SQL :: Add Variable Number Of New Columns To Existing Table Temp

Feb 24, 2010

I want to add a variable number of new columns to an existing table temp (with column provided).

Example:

NewColumnNo = 4
-> the columns shall be named rate_1, rate_2, rate_3 and rate_4
-> the values shall be = Column / NewColumnNo

The result shall be like this:

create table temp_res (prodid integer, rate_1 number, rate_2 number, rate_3 number, rate_4 number);
insert into temp_res values(1, 0.25, 0.5, 0.75, 1);
insert into temp_res values(2, 0.25, 0.5, 0.75, 1);
insert into temp_res values(3, 0.25, 0.5, 0.75, 1);
insert into temp_res values(4, 0.25, 0.5, 0.75, 1);
insert into temp_res values(5, 0.25, 0.5, 0.75, 1);
insert into temp_res values(6, 0.25, 0.5, 0.75, 1);
insert into temp_res values(7, 0.25, 0.5, 0.75, 1);
insert into temp_res values(8, 0.25, 0.5, 0.75, 1);
insert into temp_res values(9, 0.25, 0.5, 0.75, 1);

View 8 Replies View Related

SQL & PL/SQL :: Inserting Result Set Of Query Into Corresponding SQL Table Type Variable?

Aug 28, 2012

I am unable to insert the result set of query into corresponding SQL Table type variable where as same functionality can be accomplished by PL/SQL table type variable. Can't we access the same by using SQL type variable?

Ex:

Step 1:

SQL Object type , Table type Objects creation :
drop type sql_emp_tab_type ;
drop type sql_emp_type ;
create or replace type sql_emp_type as object
(
empno number,
ename varchar2(20),

[code]...

Step 2: Accessing the table type object from PL/SQL block

SQL>
SQL> declare
2
3 tab_type_var sql_emp_tab_type := sql_emp_tab_type();
4
5 begin
6
7 tab_type_var.extend(10);

[code]...

Step 3: Instead of SQL Table type , if we define the corresponding PL/SQL table type variable

SQL> declare
2
3 --tab_type_var sql_emp_tab_type := sql_emp_tab_type();
4
5 type pl_sql_emp_type is record
6 (
7 empno number,

[code]...

View 3 Replies View Related







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