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


ADVERTISEMENT

SQL & PL/SQL :: Execute Stored Procedure With Table Of Number As In Parameter?

May 31, 2011

I have one stored proc with three in parameters as

number, varchar and table of number

what statement I need to write in pl/sql to execute it ...

execute getdetails(1,'xyz', ????????????)

View 5 Replies View Related

SQL & PL/SQL :: Passing Parameter In Procedure

Jan 4, 2011

I have got this procedure:

CREATE OR REPLACE PROCEDURE GET_NUM_ROWS(TABLE_NAME VARCHAR2) AS
NUM_ROWS NUMBER;
BEGIN
SELECT COUNT(*) INTO NUM_ROWS FROM TABLE_NAME;
DBMS_OUTPUT.PUT_LINE(NUM_ROWS);
END;

When I try to compile it, the compiler says:

ERROR at line 4: PL/SQL: ORA-00942: table or view does not exist.

View 3 Replies View Related

SQL & PL/SQL :: Run Procedure Without Passing Value For IN Parameter

Mar 10, 2012

I have a pl/sql procedure having IN, OUT and IN-OUT parameters, this procedure in called from front end application. Now I need create a script to run this procedure from back end (sql prompt) and the result must be same as the front end application call to this procedure.

For the procedure I don't want to pass IN parameter instead to pick the value from the package where the derivation is defined. how to run this procedure from sql prompt without passing value for IN parameter.

View 6 Replies View Related

SQL & PL/SQL :: Passing Input Parameter To Like Variable In Procedure

Dec 27, 2012

I have a procedure named 'GetShipperinfo' which takes i_name as input and needs to build a cursor taking i_name as input

i.e.

The following sql when executed at sqlplus prompt gives correct results.

select dept, supplier, shipper_id
from shippers
where upper(shipper_name) like upper('Frank Robert%');

How can I transform this inside a cursor within a procedure passing 'Frak Robert' value as i_name input.

i.e I should be able to call the procedure as follows

sql> variable v1 varchar2;
sql> exec pkg_shipment.GetShipperinfo('Frank Robert',:v1);
sql> print :v1;

Should the cursor inside the procedure be built as follows

cursor c1 is
select dept, supplier, shipper_id
from shippers
where shipper_name like ''||upper(i_name'%''||)'';

Iam unable to build the sql for the cursor.

View 3 Replies View Related

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 View Related

SQL & PL/SQL :: Use Lexical Parameter In Stored Procedure

May 23, 2012

Can i use lexical parameter within my procedure

select '' employee_no, ptdv.fiscal_year, ptdv.trans_month, ptdv.location_code, ptdv.acc_code,ptdv.acc_desc,sum(ptdv.EARNING)-sum(ptdv.DEDUCTION) debit,0 credit,1 tag
from pr_trans_dtl_v ptdv
where ptdv.trans_month=nvl(mtrans_month, ptdv.trans_month)
[code].......

see the highlighted row. can i use and ptdv.trans_type_row in &vtype instead of and ptdv.trans_type_row in ('R','A')

where the vtype is varchar2(100) and vtype:='(''R'',''A'')';

View 1 Replies View Related

Forms :: How To Execute A Stored Procedure Without Parameter

Jun 17, 2010

I have created a procedure like this

CREATE OR REPLACE PROCEDURE SCOTT.GetEmpList(p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT * FROM scott.emp
WHERE deptno=10;
END GetEmpList;

Now i would like to execute this procedure in sqlplus which will display the following:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7782 CLARK MANAGER 7839 6/9/1981 2450 10
7839 KING PRESIDENT 11/17/1981 5000 10
7934 MILLER CLERK 7782 1/23/1982 1300 10

View 5 Replies View Related

SQL & PL/SQL :: Stored Procedure Using NVL To Allow Using Optional Date Parameter?

Mar 26, 2012

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

[code]....

View 8 Replies View Related

SQL & PL/SQL :: Removal Of Time In Input Parameter In Stored Procedure?

May 15, 2011

I'm creating a stored procedure wherein I'm the field wherein I will use for date input parameter is having a datetime format.

The format that we need for user's to input is 'DD-MON-YYYY' (without the minutes and seconds.)

how should I proceed with the creation of stored procedure for this?

View 2 Replies View Related

SQL & PL/SQL :: Create Stored Procedure With Array As Output Parameter?

Jul 14, 2011

how to create stored procedure with an array as an output parameter.I mean when we need to return multiple rows...

View 7 Replies View Related

SQL & PL/SQL :: How To Pass Type Object As A Parameter To Stored Procedure

May 9, 2012

I am fairly new to oracle, here's what I am doing.

Create or replace type csc_info as object( source_code varchar2(10),
Docno varchar2(10),
Key_value_1 varchar2(10),
Key_value_2 varchar2(10));

[Code]....

I need to test the procedure how do I pass the inputs for the type object csc_info?

View 2 Replies View Related

Input Parameter From Arraylist To Stored Procedure In Oracle?

Nov 7, 2013

i have created a package & stored procedure which is working fine when i am passing single value to my package or stored procedure. But what approach i should take if i have to pass multiple values (ArrayList) like (from eg empid like I1001,I1002,I1003,I1004,...) in my input parameter . I am using C# & Oracle

CREATE OR REPLACE PACKAGE PKG_x AS type t_cursor is ref cursor;
procedure Proc(cur_x out t_cursor,
param_emp in varchar2 DEFAULT NULL);
END PACKAGE PKG_x;

[Code].....

View 2 Replies View Related

DBMS_JOB - Pass Date As Parameter To Another Stored Procedure?

Mar 28, 2012

I have created a stored procedure that checks if a file exists and gets a date from the file if it exists. The date is then used as a paramter. See below:

CODEcreate or replace
PROCEDURE                     "P_Load_Stamp" AS
v_exists BOOLEAN;
v_length NUMBER;
v_blocksize NUMBER;

[code]...

The above codes works perfectly and I scheduled it using SQLPLUS as follows:

CODEvariable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;

[code]...

My problem is that I need to pass the date from the above procedure as a parameter to another stored procedure. So I modified my code as follows (the parts in red):

CODEcreate or replace
PROCEDURE                     "P_Load_Stamp" (vCTIDATE OUT varchar2) AS
v_exists BOOLEAN;
v_length NUMBER;

[code]...

Now it doesn't strike me as a rights issue since I created it in the schem schema. What could I be doing wrong here?

View 1 Replies View Related

Client Tools :: How To Pass Ambersand (&) As Input Parameter To A Stored Procedure

Oct 26, 2010

i have a stored procedure whose input parameter is a varchar2 datatype.i created this procedure for an interface and tibco would be calling my procedure by passing input parameters.my problem is when there is a input string with & (ambersand) then its not working.

even i tried to pass the parameter with & in TOAD, it asks me to enter value for string.look at the sample code below which i wrote for testing purpose:

procedure is:

create or replace procedure testproc(p_in in varchar2)
is
begin
null;
end;

i pass parameter as given below:

begin
testproc('abc & def');
end;

if i run above script, it asks me to input some string value as it sees & in the string. attached is the image that shows up in TOAD. if i run below script it works. but i dont know how many &'s will be there in the input parameter. hence i cant do. and also TIBCO cant modify the input paramter while calling the procedure.

begin
testproc('abc &'||'def');
end;

View 9 Replies View Related

SQL & PL/SQL :: Passing CLOB Data As Input Parameter?

May 24, 2010

I have a requirement where in I have to store large data in one of the database columns using stored procedure.

I have declared the column as CLOB as it can store upto 4GB and also the input parameter for the procedure as CLOB. But when I am trying to pass large data it is not allowing to store as it is throwing literal string too large error.

Is there any restriction in the data size to be passed to the stored procedure?

View 9 Replies View Related

SQL & PL/SQL :: Use Of Index For Searching Data In Table

Mar 5, 2010

i want to know the use of index for searching data in table...tell answer to my query with table example....

View 2 Replies View Related

Forms :: Passing Data From One Procedure To Another?

Apr 18, 2013

How to pass data of one procedure to another procedure in oracle forms.

View 1 Replies View Related

SQL & PL/SQL :: Pass Table As Input Parameter In Stored Procedures?

Feb 22, 2011

i need to pass table as input parameter in stored procedures. during the run time, i am getting error

CREATE OR replace TYPE emp_type IS OBJECT (
id NUMBER(4),
ename VARCHAR2(10));
CREATE OR replace TYPE emp_type_tab
IS TABLE OF EMP_TYPE;
CREATE OR replace PROCEDURE Test_proc (in_emp_type IN EMP_TYPE_TAB)
AS
BEGIN
FOR i IN 1.. in_emp_type.COUNT LOOP
dbms_output.Put_line(in_emp_type.Id(i));
END LOOP;
END;
/

View 10 Replies View Related

SQL & PL/SQL :: How To Export Data To Excel Through Stored Procedure

Aug 1, 2011

CREATE OR replace PROCEDURE T_name_column
AS
file_handle utl_file.file_type;
BEGIN

[Code]....

View 6 Replies View Related

Temp Table Within A Stored Procedure

Jul 15, 2013

I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table. I created the script but when they try to run in on the server it wont run.

CREATE OR REPLACE PROCEDURE UpdateFIDB_SP
IS
BEGIN
CREATE GLOBAL TEMPORARY TABLE myAAAA
AS
(SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,
DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,
[code]........

View 1 Replies View Related

SQL & PL/SQL :: Take Stored Procedure Last Run Without Using DBA_AUDIT_TRAIL Sys Table

Nov 18, 2010

How to take a stored procedure last run without using DBA_AUDIT_TRAIL sys table.

View 6 Replies View Related

SQL & PL/SQL :: Temp Table Within Stored Procedure

Jul 15, 2013

I have been developing in MS SQL for about 15. So I'm still getting use to the syntax and features within Oracle.I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table.I created the script but when they try to run in on the server it wont run.

CREATE OR REPLACE PROCEDURE UpdateFIDB_SP
IS
BEGIN
CREATE GLOBAL TEMPORARY TABLE myAAAA
AS
(SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,
DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,
AAAA.1D1D
[code].....

View 7 Replies View Related

PL/SQL :: Temp Table Within Stored Procedure

Jul 15, 2013

I'm still getting use to the syntax and features within Oracle. I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table.I created the script but when they try to run in on the server it wont run.

CREATE OR REPLACE PROCEDURE UpdateFIDB_SP IS BEGIN  CREATE GLOBAL TEMPORARY TABLE myAAAA    AS  (SELECT  AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,                       DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,                       AAAA.1D1D               FROM mySchema.FFFF_07 FFFF               RIGHT OUTER JOIN mySchema.EEEE EEEE ON FFFF.9999 = EEEE.1B1B               RIGHT OUTER JOIN
[code].........

View 10 Replies View Related

Forms :: How To Create Data Block Using Stored Procedure In 10G

Apr 30, 2010

How to create Data Block Using Stored Procedure in Forms10G ?

View 1 Replies View Related

PL/SQL :: Materialized View Based On Stored Procedure Data

Sep 23, 2013

is it possible to base a Materialized View on results returned from a stored procedure?If not, do you see any other way except of filling a table with data from the stored procedure and then basing the MV on it? 

View 4 Replies View Related

SQL & PL/SQL :: How To Go Ahead Of Another Line Of Stored Procedure If Value Is Not There In Table

Oct 16, 2012

i am running one store procedure and checking the value if it is there then it's storing in one variable. but by checking through breakpoint i got that if value is not there which condition it's checking it's stuck there.

is it not possible if value is not there it should ignore and don't store value in variable.

my query is like this:

BEGIN
SELECT 1 Into v_temp FROM MASTERPROCESSDAILYDATA WHERE Emp_ID = v_ReadEmpID AND PDate = v_ReadPDate AND STATUS='A';
IF v_temp = 1 THEN
BEGIN
DELETE MASTERPROCESSDAILYDATA WHERE Emp_ID = v_ReadEmpID AND PDate = v_ReadPDate;
END;
END IF;
End;

View 14 Replies View Related

SQL & PL/SQL :: Stored Procedure / Extract Data From Excel To Multiple Tables

Oct 14, 2010

This is my first time running a stored procedure. The procedure is already written.

We have various related table. I need to use this stored procedure and extract information from an excel sheet into the multiple tables.

View 8 Replies View Related

SQL & PL/SQL :: Debugging Stored Procedure / Populate Data Warehouse Dimension

Nov 20, 2011

The following code is a stored procedure I plan to use to populate a Data Warehouse dimension using data from two OLTP tables which already exist in my database. Notice that in my cursor select statement, I calculate an attribute using substr and instr, and I also assign a true or false value to a flag using a CASE statement.

CREATE OR REPLACE PROCEDURE populate_product_dimension
AS
v_Count NUMBER := 0;
v_NumRecs NUMBER;
/*Declare a cursor on the following query which returns mulitple rows of data from product and price_hist tables*/
[code]....

In my mind, Product_Code is declared correctly in the Cursor declaration Select statement.

View 10 Replies View Related

SQL & PL/SQL :: Oracle Stored Procedure To Load Flat File Data

Oct 24, 2013

I need to create an Oracle Stored Procedure to read a Flat file(pipe delimited) and load the data into an Oracle table. I believe the file should be located in any of the path as logged in dba_directories table or it can be anywhere on the local client machine?

View 14 Replies View Related







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