I am asking about *pgAdmin* internal user management , not *Postgres* users-and-roles. For teaching a beginning database class, I am trying to procedurally configure *pgAdmin* in Server Mode, using shell scripts, *Podman*, etc. In addition to the Administrator and User roles, I want to create a Student role with restricted permissions. The setup.py feature offers a command-line interface for configuring pgAdmin. It offers commands like: - add_user - get_role 👉🏽 Is there some procedural way to define a Role with Permissions? I need a console command equivalent for the *Permissions* tab in the *pgAdmin* GUI: I want to procedurally create a Role that effectively has some of those checkboxes unchecked.

I am planning to enable this tag to solve an issue related to some SSTables that are fully expired and are blocking some other expired SSTables from being expired. I have looked for information on which procedure we should follow, but I cannot seem to find information. For instance: should I apply these changes in all nodes in a cluster and then restart the whole cluster, or can this be done on a one node at a time fashion? I am wondering if there can be some inconsistency issues if one node at a time is changed and restarted, as the configuration would be different between different nodes in the cluster. For the record, we are working with a 4 node cluster, working with Time Window Compaction Strategy and which has a replication factor 3 and Consistency Level Local Quoruml.

I cannot run a procedure within a package with a user that also defined it unless I specify "authid current_user" for some reason. Here is my original code that produces insufficient privileges error create or replace package hr.table_pkg is procedure make ( p_table_name varchar2, p_col_specs varchar2); end table_pkg; create or replace package body hr.table_pkg is procedure make ( p_table_name varchar2, p_col_specs varchar2) is sql_stmt varchar2(32767); begin sql_stmt := 'Create table '|| p_table_name||' ('||p_col_specs||')'; dbms_output.put_line(sql_stmt); execute immediate sql_stmt ; end make ; end table_pkg; / So the user that defines the package is HR. when I query view USER_OBJECTS I can see the package and the body aswell but when I, as the HR user try to run execute table_pkg.make('my_contacts','id number(4), name varchar2(40)'); I'm gonna get an error that I cannot create the table. It also does not work when I change the procedure in the body to create the table in a specific schema HR: sql_stmt := 'Create table **hr.**'|| p_table_name||' ('||p_col_specs||')'; the call to the procedure only works when I recompile the header of the package to create or replace package hr.table_pkg authid current_user is Can please anyone explain to me what am I missing, I do not get why the definer ( HR user) cannot run the procedure with definer rights Thanks a lot!

i have this query to get all sp's and depending(nested) sp's on those sp's. I need to create all these procedures on another database . Any idea how to get them in the correct order. select distinct procs.NAME AS ProcedureName ,OBJDEP.NAME as DEP_ProcedureName_NAME FROM sysdepends INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID inner join sys.procedures procs on sysdepends.id = procs.object_id where OBJDEP.type='P' AND OBJ.type='P'

My goal: Insert a series of csv files into my database by creating procedures for each individual table and then looping over them. My csv files will all be named very similar to this: - 1_to_be_inserted_into_table_1 - 1_to_be_inserted_into_table_2 - 1_to_be_inserted_into_table_3 - 1_to_be_inserted_into_table_4 - 2_to_be_inserted_into_table_1 - 2_to_be_inserted_into_table_2 - 2_to_be_inserted_into_table_3 - 2_to_be_inserted_into_table_4 - 3_to_be_inserted_into_table_1 - 3_to_be_inserted_into_table_2 - 3_to_be_inserted_into_table_3 - 3_to_be_inserted_into_table_4 This is the pseudocode for the final loop where I'd like to reference all of my procedures:

CREATE OR REPLACE DIRECTORY all_the_data AS 'D:\Favorites\1. Programming\Projects\LYS_database\DATA TO INPUT';

DECLARE @file_selector INT
SET @file_selector=1
    
BEGIN
    FOR files IN all_the_data LOOP 
        
        EXEC procedure_1 ((file_selector || 'to_be_inserted_into_table_1'|| '.csv')),
        EXEC procedure_2 ((file_selector || 'to_be_inserted_into_table_2'|| '.csv')),
        EXEC procedure_3 ((file_selector || 'to_be_inserted_into_table_3'|| '.csv')),
        EXEC procedure_4 ((file_selector || 'to_be_inserted_into_table_4'|| '.csv')),
        
    SET @file_selector= file_selector+1
    commit;

END;
/

QUESTION 1: What am I doing wrong with creating the procedure below? It worked perfectly fine to insert data into a table before I tried to make it a procedure.

CREATE OR REPLACE PROCEDURE INSERT_CPP 
    (file_name IN varchar2)

IS
    cpp_data VARCHAR(200) := 'D:\Favorites\1. Programming\Projects\LYS_database';

BEGIN
    insert into cpp
    SELECT * FROM EXTERNAL (
        (
      cpp VARCHAR2 (50),
      rfu1 NUMBER (6, 2),
      rfu2 NUMBER (6, 2),
      mean_rfu NUMBER (6, 2),
      charge_ph7_4 NUMBER (2),
      hydropathy NUMBER (3, 1))
    
        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY (" || cpp_data || ")
        ACCESS PARAMETERS (
            RECORDS DELIMITED BY NEWLINE
            skip 1
            badfile (' || cpp_data || '\badflie_cpp.bad')
            FIELDS TERMINATED BY ','
            MISSING FIELD VALUES ARE NULL 
            ) 
        LOCATION (file_name)
        REJECT LIMIT UNLIMITED) ext
        where not exists (
            select * from cpp c
            where c.cpp = ext.cpp );
END;
/

I get an error:

5/5       PL/SQL: SQL Statement ignored
16/27     PL/SQL: ORA-00922: missing or invalid option
30/1      PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ;

QUESTION 2. Is there a way to write a

files IN all_the_data LOOP

in SQL? I tried this solution but the code from the first step wasn't recognized as a command. I did

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

and got

RECONFIGURE
Error report -
Unknown Command

QUESTION 3. Can i write

;

at the end of every loop so that if something goes wrong on the very last file it doesn't rollback everything? Will that work?

Assume I have a table looks like this: Scores { Id uniqueidentifier, ScoredAt datetime, Value int } Basically I want to create a stored procedure that works similar to this DATEDIFF(DAY, @day, GETDATE()) which can use DAY/WEEK... as parameter. This is what i did: CREATE PROCEDURE GetHighScores @scope int --- <<== THIS GUY HERE AS BEGIN SELECT * FROM Honor.Scores WHERE DATEDIFF(@scope, Honor.Scores.ScoredAt, GETDATE()) = 0 END GO What do I have to put into GetHighScores parameter so that I can do this: EXEC GetHighScores(MONTH) As far as I know, MONTH/WEEK/DAY.. those are not actually a value but more of a tricky macro. But that guy DATEDIFF can use it, why couldn't I? Anyone have any idea to get this done?

I want to compare stored procedures that should be identical in several different databases, by retrieving and comparing the definitions from sys.sql_modules. Currently I'm thinking about procedures which are "identical" except that one or other copy has blank lines or other white space at the start or end. These seem to be added by making a script of a procedure. I'd like to remove the blank lines and such and compare what's left. So I could do: IF ( LEFT(@definition1, 2) = NCHAR(13) + NCHAR(10) ) SET @definition1 = SUBSTRING(@definition1, 3, 1048576) That will work if the length of the procedure is less than 1048576 bytes. But what is the actual limit on length - if any? I suppose actually I should ask: what is a good way to compare procedures? (Apart from "Buy the SQL Compare utility from Red Gate.") Updated ------- (revised) I just realised that sys.sql_modules.definition has data type nvarchar(max) and that has a specified length... in bytes, POWER(2, 31)-1, except that that formula doesn't work ;-) In nchar characters, no more than POWER(2, 30)-1. So this ought to do it: IF ( LEFT(@definition1, 2) = NCHAR(13) + NCHAR(10) ) SET @definition1 = SUBSTRING(@definition1, 3, 2147483647) Yes, that is 2^31-1, it ought to be 2^30-1, but as long as it's bigger than @definition1 is or can be, I get away with it. Until they increase "max" to 2^63-1 bytes. Before there was "max", I used 8000. And maybe I should include double-checking the DATALENGTH() in case it accidentally gets truncated to the first 8000 bytes somewhere. As for your wise words about good code management to not have this need in the first place - useful ammunition, thank you (now to extract the bullets from my own hide). I recognise the value of methodical working practices, even when they have to apply to me, as well. But a problem is management who let's just say hypothetically just want a report or a result produced -now-, and who have been getting and expecting service like that for many years - and now they've learned to call it "Agile".

--Procedures Exercise: create or replace procedure Display is cursor ABC is select empno, ename, sal from emp where deptno=10; emp_rec ABC%rowtype; Begin Open ABC; Loop fetch ABC into emp_rec; exit when ABC%notfound; end Loop; dbms_output.put_line(emp_rec.empno||' ' ||emp_rec.ename||' '||emp_rec.sal); end; / When I execute the following code there is only one output shown for deptno=10, while deptno=10 contains 3 records.

This is my procedure: DELIMITER $$ CREATE PROCEDURE sp_test_final6() BEGIN INSERT INTO chef_ratings(chef_id,avg_total_no_votes,avg_total_rating,no_of_votes,avg_rating) SELECT chef_id,(SELECT count(chef_id) FROM rates)/( SELECT count(DISTINCT chef_id) FROM rates), (( SELECT sum((question_1 + question_2 +question_3 )/3) FROM rates ) / ( SELECT count(chef_id) FROM rates)), ( SELECT count(chef_id) FROM rates ),avg((question_1+question_2+question_3)/3) FROM rates GROUP BY chef_id; END $$ DELIMITER ; I'm getting error : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PROCEDURE sp_test_final6() BEGIN INSERT INTO chef_ratings( chef_id, `avg_t' at line 1 Any idea why it is failing? **Edited:** It works fine in mysql version 5.5 but in Server version: 4.1.22 it is giving this problem. How we can fix without upgrading?

When I request my database server (MySQL), with "show grants for current_user" I notice that I have the grant to execute procedure: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, EXECUTE But when I execute a sql file that contains those instructions : DROP FUNCTION IF EXISTS DROP FUNCTION IF EXISTS DROP PROCEDURE IF EXISTS CREATE FUNCTION CREATE PROCEDURE DECLARE CALL I have this error : ERROR 1044 (42000) at line 16: Access denied for user The line 16 contains : CREATE FUNCTION function_name( .. ) Should I have other grants ?

I want to write PL/SQL Procedure that updates salary which is less than 2000. I wrote this procedure.And when i call it with integer id 'ORA-01422: exact fetch returns more than requested number of rows' error is thrown by TOAD. My procedure is like below: DECLARE PROCEDURE update_salary(ID customers.id%type) is c_sal customers.salary%type; BEGIN SELECT salary INTO c_sal FROM customers WHERE id = ID; dbms_output.put_line ('Before update operation salary is:' || c_sal); --dbms_output.put_line ('Parameter :' || ID); IF (c_sal <= 2000) THEN UPDATE customers SET salary = salary + 1000 WHERE id = ID; dbms_output.put_line ('Salary updated'); END IF; SELECT salary INTO c_sal FROM customers WHERE id=ID; dbms_output.put_line ('After update operation salary is:' || c_sal); END; BEGIN update_salary(1); END; / I print parameter id with dbms_output. The parameter is coming correctly. How can i fix this error !

My requirements are, I want to create a ddl script which will check if a stored procedure exists on DB, if yes then drop it and recreate it. What I tried is, IF EXISTS (select procname into Migration_procname from sysibm.sysprocedures where procname like 'GIAM_PRIVILEGE_MIGRATION') THEN DROP PROCEDURE ITIMUSER.GIAM_PRIVILEGE_MIGRATION; ELSE CREATE PROCEDURE ITIMUSER.GIAM_PRIVILEGE_MIGRATION() SPECIFIC ITIMUSER.GIAM_PRIVILEGE_MIGRATION LANGUAGE SQL BEGIN ...... ...... update/select statements ...... END @ which didn't work. so I even tried with creating another procedure which is doing same task still no success. The error message was > DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "IF EXISTS (select procname" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "". SQLSTATE=42601 Any pointers will be helpful. NOTE: I am using DB2/LUW 9.5

Just can't understand why it's throwing this error? Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1 drop procedure if exists dividir_tabla_por_usuario; delimiter // create procedure dividir_tabla_por_usuario(tabla varchar(100), columna_usuario varchar(100), columna_fecha varchar(100)) begin set @i = 0; set @q1 = concat('select count(*) from (select distinct ', columna_usuario ,' from ', tabla , ' order by ', columna_usuario ,' asc) as t1 into @c'); prepare p1 from @q; execute p1; while @i <= @c do set @r = 0; set @q2 = concat('select * from ', tabla , ' where ', columna_usuario ,' = (select ', columna_usuario ,' from (select @r:=@r +1 as ord, ', columna_usuario ,' from (select distinct ', columna_usuario ,' from ', tabla , 'order by ', columna_usuario ,' asc) as t2) as t3 where ord = ', @i ,' ) order by ', columna_fecha ,' asc limit 30000'); prepare p2 from @q2; execute p2; set @i := @i + 1; end while; end// delimiter ; call dividir_tabla_por_usuario('mytable', 'mycolumn_1', 'mycolumn_2');

Good evening, Is there an Object Oriented replacement for PL/SQL, allowing server side procedures to be written [and then called client or server side]? (for MySQL, PostgreSQL, Oracle or etc.)

Could someone give me a brief explication about these two mechanisms in oracle : **stored** and **external** procedures. Are they distinct ? Are they differ when we integrate in some language (Java, C++ or C ...) ?

Well last Christmas I was very happy, because we ceased support for SQL Server 2000. I could stop twisting my brain and use friendly analytical functions. (Believe me, when it comes to migrate stored procedures from SQL-Server to Oracle Analytical functions are one of your best friends). Later we discovered that we overlooked a tiny annoyance. When customers replaced there old machines by new ones, by default their compatibility level is set according to the old machine and problems are postponed until later. Which problem? Analytical functions work in SQL Server 2005 despite compatibility level 80. I'm afraid to tell that other new features like PIVOT is inhibited. Now it is good time to try to get rid of cmptlevel 80, but how? For me it is mostly scanning the procedures, views, functions and triggers for old style outer joins like '\*=' and '=*'. First let us find the defining text from the system tables. Initially it was saved in small chunks in syscomments. Hhm how to deal with the case when '*' and '=' are split onto to succeeding chunks. Next don't waste your time with Select ROUTINES from INFORMATION_SCHEMA.ROUTINES it truncates the definition to 4000 characters. Finally this is the code to query for the problematic objects: select o.name , o.type, datalength(definition) len, len(definition) len, definition from sys.sql_modules m join sysobjects o on o.id = m.object_id where (definition like '%*=%' or definition like '%=*%') But there is a further hurdle. Because default is derived from default the default width for text results is too small ( I think it is 256, but as it is the first thing I change to 8192 in SSMS, I wont bet). Any case this helps to identify the rows which need deeper examination. Perhaps sp_help displays the full definition for **all** possible cases. Perhaps it does, I don't know for sure, because I got tired of that problems created by punchcard heads. Normally I use ADO and PowerShell, but they may not be installed on the customers machine. Are there any 100% waterproof T-SQL scripts to do the job of displaying or exporting the full definition of procedures found by some condition?